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 for storage index which show the IO saved by 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 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 you all 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.6875

Again,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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s