Hi All,
Following is the quick example to show us whether Storage Index is been used and how much blocks been saved when using the Storage index.
Small description:
We cannot control the Storage Index which resides in Storage server like we control our indexes in database level like create/drop/rebuild etc.
Storage Index is automatically built and drop when storage server is started and stopped respectively.
However there is a way to efficiently use storage index when executing SQL queries in oracle database. Its recommended to load table a table in sorted manner with respect to column which you usually use as a predicate in SQL queries.
For Example
SQL> insert /+* append */ into test_storage select * from T1 order by C1;
Here we are inserting direct load operation and we are also storing the data orderly using C1 column.
Now in future if your queries uses C1 as a filter predicate then storage indexes will work out of its full capacity. But its should be also notes that each 1 MB of storage region in server only contains 8 columns at time.
Lets check the Index storage how much it helps in our query: Please be noted that there is no real time monitoring available for Storage index and we have to fall back to out v$mystat view. The event name is
"cell physical IO bytes saved by storage index". So if its value gets incremented this means storage index is being used other then that there is no monitoring for its usability.
For additional info see here
SQL> set time on timing on
15:50:08 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
Note- view like v$mystat is cumulative so we have to check its value every time we issue query,
Elapsed: 00:00:00.00
15:50:14 SQL> select count(ACCT_ID) from acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:01.49
15:50:26 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 79011840
Elapsed: 00:00:00.00
Following is undocumented parameter to disable the Storage index, so dont set it explicitly at production database.
15:50:29 SQL> alter session set "_kcfis_storageidx_disabled"=TRUE;
Session altered.
Elapsed: 00:00:00.00
15:50:44 SQL> select count(ACCT_ID) from bob_prod_ods.acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:01.78
15:50:52 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 79011840
Elapsed: 00:00:00.00
So we see here value dont change at all.
15:50:54 SQL> alter session set "_kcfis_storageidx_disabled" = FALSE;
Session altered.
Elapsed: 00:00:00.00
15:51:07 SQL> select count(ACCT_ID) from bob_prod_ods.acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:01.44
15:51:24 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 158023680
So not value is incremented after returning to default
Following are some parameters of related to cell:
15:46:21 SQL> show parameter cell
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction string ADAPTIVE
cell_offload_decryption boolean TRUE
cell_offload_parameters string
cell_offload_plan_display string AUTO
cell_offload_processing boolean TRUE
Lets check with Offload to false:
15:47:27 SQL> alter session set cell_offload_processing=FALSE;
Session altered.
Elapsed: 00:00:00.00
15:47:38 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 66502656
Elapsed: 00:00:00.00
15:47:50 SQL> select count(ACCT_ID) from bob_prod_ods.acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:17.18
15:48:13 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 66502656
Elapsed: 00:00:00.00
15:48:19 SQL> alter session set cell_offload_processing=TRUE;
Session altered.
Elapsed: 00:00:00.00
15:48:37 SQL> select count(ACCT_ID) from bob_prod_ods.acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:01.84
15:48:44 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 145514496
So as you noticed even if storage index in enabled(we didnt put it on disable) storage index cannot be used if smart scan doesn't happen.
Following is the quick example to show us whether Storage Index is been used and how much blocks been saved when using the Storage index.
Small description:
We cannot control the Storage Index which resides in Storage server like we control our indexes in database level like create/drop/rebuild etc.
Storage Index is automatically built and drop when storage server is started and stopped respectively.
However there is a way to efficiently use storage index when executing SQL queries in oracle database. Its recommended to load table a table in sorted manner with respect to column which you usually use as a predicate in SQL queries.
For Example
SQL> insert /+* append */ into test_storage select * from T1 order by C1;
Here we are inserting direct load operation and we are also storing the data orderly using C1 column.
Now in future if your queries uses C1 as a filter predicate then storage indexes will work out of its full capacity. But its should be also notes that each 1 MB of storage region in server only contains 8 columns at time.
Lets check the Index storage how much it helps in our query: Please be noted that there is no real time monitoring available for Storage index and we have to fall back to out v$mystat view. The event name is
"cell physical IO bytes saved by storage index". So if its value gets incremented this means storage index is being used other then that there is no monitoring for its usability.
For additional info see here
SQL> set time on timing on
15:50:08 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
Note- view like v$mystat is cumulative so we have to check its value every time we issue query,
Elapsed: 00:00:00.00
15:50:14 SQL> select count(ACCT_ID) from acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:01.49
15:50:26 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 79011840
Elapsed: 00:00:00.00
Following is undocumented parameter to disable the Storage index, so dont set it explicitly at production database.
15:50:29 SQL> alter session set "_kcfis_storageidx_disabled"=TRUE;
Session altered.
Elapsed: 00:00:00.00
15:50:44 SQL> select count(ACCT_ID) from bob_prod_ods.acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:01.78
15:50:52 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 79011840
Elapsed: 00:00:00.00
So we see here value dont change at all.
15:50:54 SQL> alter session set "_kcfis_storageidx_disabled" = FALSE;
Session altered.
Elapsed: 00:00:00.00
15:51:07 SQL> select count(ACCT_ID) from bob_prod_ods.acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:01.44
15:51:24 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 158023680
So not value is incremented after returning to default
Following are some parameters of related to cell:
15:46:21 SQL> show parameter cell
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction string ADAPTIVE
cell_offload_decryption boolean TRUE
cell_offload_parameters string
cell_offload_plan_display string AUTO
cell_offload_processing boolean TRUE
Lets check with Offload to false:
15:47:27 SQL> alter session set cell_offload_processing=FALSE;
Session altered.
Elapsed: 00:00:00.00
15:47:38 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 66502656
Elapsed: 00:00:00.00
15:47:50 SQL> select count(ACCT_ID) from bob_prod_ods.acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:17.18
15:48:13 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 66502656
Elapsed: 00:00:00.00
15:48:19 SQL> alter session set cell_offload_processing=TRUE;
Session altered.
Elapsed: 00:00:00.00
15:48:37 SQL> select count(ACCT_ID) from bob_prod_ods.acct where ACCT_ID > '0718293411';
COUNT(ACCT_ID)
--------------
67282041
Elapsed: 00:00:01.84
15:48:44 SQL> select name,value from v$mystat s,v$statname n where s.STATISTIC#=n.STATISTIC# and n.name like '%storage%';
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 145514496
So as you noticed even if storage index in enabled(we didnt put it on disable) storage index cannot be used if smart scan doesn't happen.
No comments:
Post a Comment