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.