STORAGE INDEX EXADATA
Storage inde(SI) itself is not an index but a filter which is a cache area used in cell server to reduce disk IO on the database layer.
Note:In this article,i will use the term SI instead of storage index so dont get confused
Research say that SI is stored in heap of cellsrv which also has swapping mechanism due to insufficient memory.SI has a regional index which has min and max value column upto eight columns which stores the cached data in 1MB chunks of storage
Storage index also stores nulls to track all the null values from the table
There are certain conditions whenever storage index should be used
Smart scan,At least one predicate and comparison operators.Below are the operators which are most commonly used and conditions which satisfy SI
=,<,>,BETWEEN,>=,<=,IN,IS NULL,IS NOT NULL
There are three hidden parameters which can be used to influence storage index
kish@exdbx<>select KSPPINM from x$ksppi where (KSPPINM like '%kcfis_sto%') or (KSPPINM like '_cell%mode');
KSPPINM
--------------------------------------------------------------------------------
_kcfis_storageidx_disabled
< == used to disable storage index (default = enabled)
_kcfis_storageidx_diag_mode
< == database layer cause storage cell to generate trace files on affected cell nodes
_cell_storidx_mode
< == used for filter options w.r.t conditions(>,<,= ...) (EVA,KDST,ALL)How to identify if a storage index is used or not
‘cell physical IO bytes saved by storage index‘ is the statistic name used for storage index in exadata which denote the total IO saved by using storage index
We can get these details of storage in combination of v$sysstat,v$statname and v$mystat views
Also storage index(SI) requires sufficient memory for utilization because of its nature.If there are less pages to handle the incoming processes,then storage filter cant be used.But all time,there is sufficient memory to use SI though
kish@exdbx<>select name from v$statname where name like '%storage%';
NAME
----------------------------------------------------------------
cell physical IO bytes saved by storage index
Zero indicates that storage index is not being used and some value indicates the amount of IO (mb) saved by storage index
I just perform a test to check how storage index works
First i connect to fresh session and there is no statistics(0 MB) for offloading in database.Every time we disconnect from session,all these statistical information are wiped out from the memory.Hence output of query values are zero
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like '%cell physical%'); 2 3 4
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
physical write total bytes 0
cell physical IO interconnect bytes 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO interconnect bytes returned by smart scan 0
cell IO uncompressed bytes 0
10 rows selected.
I check the count of total rows from sales table.Observe that there is no predicate on the query.As we should be aware that,storage index doesnot work on query without predicate clause.This is an evidence that storage index cannot be used in non predicate query.
kish@exdbx<>set autot traceonly
kish@exdbx<>select count(*) from sales;
Elapsed: 00:00:01.09
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3222 (1)| 00:00:39 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| SALES | 1048K| 3222 (1)| 00:00:39 |
----------------------------------------------------------------------------
Check the statistics ‘cell physical IO bytes saved by storage index‘ row.It is zero.
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like '%cell physical%'); 2 3 4
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 185.6875
physical write total bytes 0
cell physical IO interconnect bytes 24.8948441
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 185.359375
cell physical IO bytes saved by storage index 0
<==========
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO interconnect bytes returned by smart scan 24.5667191
cell IO uncompressed bytes 185.539063
10 rows selected.
EXAMPLES:
lets query with equality predicate.I have just demonstrated multiple examples belowIf it bores you,i suggest you to skip the examples and go to performance part
kish@exdbx<>set autot traceonly
kish@exdbx<>select count(*) from sales where order_id = 1234;
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3226 (1)| 00:00:39 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS STORAGE FULL| SALES | 1 | 6 | 3226 (1)| 00:00:39 |
------------------------------------------------------------------------------------
2 - storage("ORDER_ID"=1234)<================================
filter("ORDER_ID"=1234)<================================Above output shows storage filter in predicate information section of execution plan which means that oracle has used the storage index column values to filter the unique value.If you see below output,you can see the statistics which shows the IO utlization of 92 MB from the storage layer which saved almost X% of IO
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 91.6875
<===========
If i check the size of the particular row,it shows 6 bytes as in execution plan.As an assumption,there should be some additional processing from storage index to scan from min value(1) to 1234 which lead to 91 MB IO cost.
kish@exdbx<>set pages 200
kish@exdbx<>set lines 1000
kish@exdbx<>SELECT NVL(vsize(COUNTRY),0)+1+NVL(vsize(ITEM_TYPE),0)+1+NVL(vsize(SALES_CHANNEL),0)+1+NVL(vsize(ORDER_PRIORITY),0)+1+NVL(vsize(ORDER_DATE),0)+1+NVL(vsize(ORDER_ID),0)+1+NVL(vsize(SHIP_DATE),0)+1+NVL(vsize(UNITS_SOLD),0)+1+NVL(vsize(UNIT_PRICE),0)+1+NVL(vsize(TOTAL_REVENUE),0)+1+NVL(vsize(TOTAL_COST),0)+1+NVL(vsize(TOTAL_PROFIT),0)+1 as ROWSIZE_IN_BYTES from sales where order_id='1234';
ROWSIZE_IN_BYTES
----------------
67
Lets query with less than operator
kish@exdbx<>select count(*) from sales where order_id < 1234;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ORDER_ID"<1234)
filter("ORDER_ID"<1234)In below output,you can find an increment in storage IO statistics which is double the value (184MB) compared to previous
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 184
<===========Lets query with between operator
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;
COUNT(*)
----------
11112
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
2 - storage("ORDER_ID"<=12345 AND "ORDER_ID">=1234)
filter("ORDER_ID"<=12345 AND "ORDER_ID">=1234)
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 274.6875Again,the output value of storage IO is incremented to 275 MB.These examples proves that using certain operators in predicate leads to SI scan thus reducing the need for a traditional BTREE index in database
kish@exdbx<>select count(*) from sales where order_id in ('1234','12345');
COUNT(*)
----------
2
Elapsed: 00:00:00.03
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 365.375
<= operator:
kish@exdbx<>select count(*) from sales where order_id <='1234';
COUNT(*)
----------
1234
Elapsed: 00:00:00.03
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 457.6875
PERFORMANCE TEST:
Lets test the performance difference between storage index and no storage index
kish@exdbx<>alter session set "_kcfis_storageidx_disabled"=true;
Session altered.
Elapsed: 00:00:00.01
kish@exdbx<>alter system flush shared_pool;
System altered.
Elapsed: 00:00:01.74
kish@exdbx<>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:08.35
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;
COUNT(*)
----------
11112
Elapsed: 00:00:03.99
<=========
It took 4 milliseconds for the select query to execute
Lets test the select query with storage index enabled
kish@exdbx<>alter session set "_kcfis_storageidx_disabled"=false;
Session altered.
Elapsed: 00:00:00.01
kish@exdbx<>alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
kish@exdbx<>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.06
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;
COUNT(*)
----------
11112
Elapsed: 00:00:00.10
<===It took 0.10 milliseconds to complete which is almost 4 – 0.10 = 3.90 seconds faster than previous run.Amazing is it ! The query executed 3 times faster than normal run without SI
Now let us create a BTREE index on order_id column of sales table and check if there is any additional performance impact or not.I didnot want to focus on the importance of BTREE here but just curious to compare the performance
kish@exdbx<>create index BTREE_ORDER_ID on sales(order_id);
Index created.
Lets check how the index impacts optimizers plan.Note that i have not disabled the storage index.
kish@exdbx<>set autot on;
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;
COUNT(*)
----------
11112
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 1371073561
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 27 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| BTREE_ORDER_ID | 11113 | 66678 | 27 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_ID">=1234 AND "ORDER_ID"<=12345)
The above output shows that eventhough i didnot disable the storage index optimizer choose BTREE over storage index
BTREE gives a tough fight when it comes to index scans.If the goal is to perform single block IO, then BTREE index comes into picture but if the goal is to scan multiblock IO,then storage index is better.However it depends on various factors of how data stored in the table and optimizer shortest possible path.
If we want to check if the columns in a table are marked by storage index,then we should identify the object id of the table,tablespace id where the object is stored and database unique id of the database.Then enable dump trace from cell server
Identify the Object id of table
kish@exdbx<>select data_object_id from dba_objects where owner = 'SYS' and object_name = 'SALES';
DATA_OBJECT_ID
--------------
88693
Next,identify ts# where the object reside
kish@exdbx<>select ts# from v$tablespace t, dba_segments s, dba_objects o where t.name=s.tablespace_name and s.segment_name=o.object_name and o.data_object_id = 88693;
TS#
----------
0
6
6
6
6
6
6
6
6
6
6
6
6
6
6
6
6
4
Get the database unique from kernel view
kish@exdbx<>select ksqdngunid from x$ksqdn;
KSQDNGUNID
----------
3727006149
Dump the contents of the storage index on each celldisk to a tracefile
[root@exceladm00 trace]# cellcli
CellCLI: Release 11.2.3.2.1 - Production on Sat Feb 20 16:52:46 GMT+05:30 2021
Copyright (c) 2007, 2012, Oracle. All rights reserved.
Cell Efficiency Ratio: 4,545
CellCLI> alter cell events = "immediate cellsrv.cellsrv_storidx('dumpridx','all',88693,6,3727006149);
Dump sequence #1 has been written to /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/exceladm00/trace/svtrc_4014_27.trc
Cell stocell successfully altered
Get ready to find valuable insights from the tracefile
you can see the message from all storage griddisk
[root@exceladm00 trace]# grep '$$$ Dumping storage idx summary for griddisk DATA_CD_cell0' /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/exceladm00/trace/svtrc_4014_27.trc
2021-02-20 16:53:18.391463*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell06_stocell:
2021-02-20 16:53:18.391463*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell05_stocell:
2021-02-20 16:53:18.396365*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell02_stocell:
2021-02-20 16:53:18.396365*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell04_stocell:
2021-02-20 16:53:18.396365*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell03_stocell:
2021-02-20 16:53:18.396365*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell01_stocell:
2021-02-20 16:53:18.406747*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell07_stocell:
2021-02-20 16:53:18.406747*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell08_stocell:
2021-02-20 16:53:18.406747*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell09_stocell:
Again inside the tracefile,you can see object id and count of the rows of table
lo(low) and hi(high) indicate the min and max value data stored in 1MB chunks of storage index and there is col id [6] which represents column number 6 in table sales.Check also the scn which has an unique identifier for record in index.I marked the important points with “[[[ … ]]]” square braces in the below trace to understand the points covered in this paragraph.Isnt that fascinating to see how storage index works !To me it is 🙂
2021-02-20 16:53:18.391463*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell06_stocell:
2021-02-20 16:53:18.391463*: Dump sequence #1:
2021-02-20 16:53:18.391463*: RIDX (0x2b8f85f785cc) : st 2 validBitMap 0 tabn 0 id [[[88693]]] 0 3727006149}
2021-02-20 16:53:18.391463*: RIDX: strt 0 end 2048 offset 234881024 size [[[1048576]]] rgnIdx 224 RgnOffset 0 [[[scn: 0x0000.00fe57b3]]] hist: 0x2
2021-02-20 16:53:18.391463*: RIDX validation history: 0:FullRead 1:Undef 2:Undef 3:Undef 4:Undef 5:Undef 6:Undef 7:Undef 8:Undef 9:Undef
2021-02-20 16:53:18.391463*: Col id [6] numFilt 10 flg 2:
2021-02-20 16:53:18.391463*: [[[lo: c3 18 2a 18 0 0 0 0 <================]]]
2021-02-20 16:53:18.391463*: [[[hi: c3 1a 41 39 0 0 0 0 <================]]]
2021-02-20 16:53:18.391463*: RIDX (0x2b8f85f786a0) : st 2 validBitMap 0 tabn 0 id {88693 0 3727006149}
2021-02-20 16:53:18.391463*: RIDX: strt 0 end 2048 offset 235929600 size 1048576 rgnIdx 225 RgnOffset 0 scn: 0x0000.00fe57b9 hist: 0x2
2021-02-20 16:53:18.391463*: RIDX validation history: 0:FullRead 1:Undef 2:Undef 3:Undef 4:Undef 5:Undef 6:Undef 7:Undef 8:Undef 9:Undef
2021-02-20 16:53:18.391463*: Col id [6] numFilt 10 flg 2:
2021-02-20 16:53:18.391463*: lo: c3 19 35 59 0 0 0 0
2021-02-20 16:53:18.391463*: hi: c3 1b 4d 18 0 0 0 0
SUMMARY
Storage index is a boon for exadata which acts as an alterntive to partitioning.However,the way to get the full potential from SI depends on the data stored in physical storage.And that being said,sorted data attracts storage index and performance compared to unsorted data