Exadata: Storage Indexes

Brief: 
Storage Indexes are not indexes that are stored in the database like Oracle’s traditional B-Tree or bitmapped indexes. A storage index is a memory-based (heap of cellsrv) structure and its purpose is to tell Oracle with absolute certainty that a requested extent does not exist in specific locations, and Exadata uses this information to bypass I/O requests to the physical storage locations. 

By default, storage indexes are created and maintained automatically by Exadata’s cell services software but you can influence storage index behavior using a number of methods, including forcing direct path reads with full table scans, disabling storage indexes. They are used during direct path read operations, when the queries contain predicates (WHERE clause) and if the underlying ASM disk group has the cell.smart_scan_capable=TRUE attribute set.

Structure

On the Exadata storage cell disks, data is physically stored inside an Oracle ASM allocation unit. ASM allocation units are comprised of a set of 1 MB chunks called storage regions, and as I/O is issued, Exadata’s cell services software conducts I/O requests in 1 MB units based on these storage regions. Each storage region has a small memory structure associated with it that is used to store a region index, which is another name for a storage index. Region indexes are populated as data is requested via Smart Scan, and based on the query predicates used to access the data, the region indexes are populated with the high and low values found when servicing the I/O request. These region indexes track the high and low values for up to eight columns per segment, based on the nature of the query predicates.

Measure the I/O savings
By using the "cell physical IO bytes saved by storage index" statistic, you can measure the I/O savings as a result of storage indexes.
-- Run a query qualified for smart scan twice
--  First execution
SQL> select count(*) from exatest.objlist
2 where object_id between 200 and 300;
COUNT(*)
----------
202000
Elapsed: 00:00:19.85
--  Second execution
SQL> select count(*) from exatest.objlist
2 where object_id between 200 and 300;
COUNT(*)
----------
202000

Elapsed: 00:00:10.17

-- Run the query to know IO saving after each execution of above query

SQL> select stat.name,

sess.value/1024/1024 value
from v$mystat sess,
v$statname stat
where stat.statistic# = sess.statistic#
and stat.name in
('cell physical IO bytes eligible for predicate offload',
'cell physical IO interconnect bytes',
'cell physical IO bytes saved by storage index',
'cell physical IO interconnect bytes returned by smart scan')
order by 1;

--  First execution output
Statistic Value (MB)
---------------------------------------------------------- -----------------------
cell physical IO bytes eligible for predicate offload 2,355.09
cell physical IO bytes saved by storage index .00 
cell physical IO interconnect bytes 1.72
cell physical IO interconnect bytes returned by smart scan 1.68

--  Second execution output
Statistic Value (MB)
-------------------------------------------------------------------- -----------------------
cell physical IO bytes eligible for predicate offload 2,355.09
cell physical IO bytes saved by storage index 1,444.27
cell physical IO interconnect bytes 1.61
cell physical IO interconnect bytes returned by smart scan 1.59
SQL>

Storage index trace information:
Storage index trace information is stored on the storage cell, not the diagnostics directory of database server. The context of the trace files is the cell services operating system processes on the storage cells, not database instance.

You can enable tracing for storage indexes and interpret the storage index trace files by setting a parameter for your session as below.
SQL> alter session set "_kcfis_storageidx_diag_mode"=2;

The above parameter enables storage index tracing in debug mode.

After this run the query qualified for smart scan like (Select count(*) from exatest.objlist where object_id between 200 and 300). When the query completes log in to one of your storage cells and navigate to your diagnostics/trace file directory (/var/log/oracle/diag/asm/cell/[cell server name]/trace) eg; /var/log/oracle/diag/asm/cell/exacell1/trace
On the above location there will be number of trace files that begin with the svtrc* to view.

[root@exacell1 trace]# ls -alrt svtrc*|tail −5
-rw-r----- 1 root celladmin 3363 Dec 03 00:38 svtrc_12910_75.trc
-rw-r----- 1 root celladmin 265 Dec 03 00:38 svtrc_12910_71.trm
-rw-r----- 1 root celladmin 1414 Dec 03 00:38 svtrc_12910_71.trc

[root@cm01cel01 trace]# vi svtrc_12910_75.trc

In the trace file you will search for the string RIDX which stands for "region index". The contents of the trace file will have the RIDX section which contains information (like SQLID) about region indexes (storage indexes). You can further query the V$SQL against the SQLID to know the SQL Statement issued.

Disabling Storage Indexes
You can use ALTER SYSTEM command to disable storage indexes for your database.
SQL> alter system set "_kcfis_storageidx_disabled"=TRUE scope=both sid='*';
System altered.

Troubleshooting Storage Indexes:
If your query is not benefiting from storage indexes, you will need to identify conditions under which Exadata will not use storage indexes and determine whether your query is subject to these limitations.
- Ensure that the ASM disk group that stores your segment has the cell.smart_scan_capable attribute set to TRUE.
- Ensure that your query does not use LIKE operations in the query predicate with wildcard literals.
- Check physical IO bytes eligible for predicate offload statistic to determine whether your query was Smart Scan eligible.
- Ensure that your query contains a query predicate, or WHERE clause. Storage indexes will only be used for queries imposing a limiting condition on the rows returned.

Mindmap
Below is the old mind map that I created while studying the topic.