Monitoring Exadata Storage Indexes

Storage indexes are unique Exadata structures whose goal is to reduce the amount of IO returned and required from the Exadata cell. When queries run in which storage indexes are eligible, Oracle tracks minimum and maximum predicate values and stores these into 1Mb memory regions in each cell. When a query is passed to the storage cell, CELLSRV scans these storage indexes to determine whether the requested data can be satisfied based on existing storage index, and if so, it will return this data without reading from disk and/or skipping entire sets of blocks and potentially cells. As documented, storage indexes are created and maintained automatically and in fact, there is no way for an administrator or developer to impact the behavior of them. They provide additional performance boost to the overall solution and although not necessarily predictable or deterministic, they will never negatively impact performance.

The scripts below show storage index statistics at a system level, session level, and for the current session.

Script: exa_storind_sysstat.sql

set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select inst.instance_name,
b.name,
value
from gv$sysstat a,
gv$statname b, gv$instance inst
where a.statistic# = b.statistic#
and b.name in ('cell physical IO interconnect
bytes returned by smart scan',
'cell physical IO bytes saved by storage
index')
and a.value > 0
and inst.inst_id=a.inst_id
and inst.inst_id=b.inst_id
order by 2 desc
/

Script: exa_storind_sesstat.sql

set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select b.name,
value
from v$sesstat a,
v$statname b
where a.statistic# = b.statistic#
and a.sid = &sid
and b.name in ('cell physical IO interconnect
bytes returned by smart scan',
'cell physical IO bytes saved by storage
index')
and a.value > 0
order by 2 desc
/

 

Script: exa_storind_mystat.sql

set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select b.name,
value
from v$mystat a,
v$statname b
where a.statistic# = b.statistic#
and b.name in ('cell physical IO interconnect
bytes returned by smart scan',
'cell physical IO bytes saved by storage
index')
and a.value > 0
order by 2 desc
/

 

Summary

Measuring impact of Exadata Storage Indexes is important to understanding how well your Exadata Database Machine is working in relation to non-Exadata solutions.