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.
1050 Wilshire Drive,
Suite 170,
Troy, MI 48084
Phone: (248) 465-9533
Toll free: 1-877-868-1753
Email: [email protected]
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy
Read the Gartner report to learn more about how to lead a successful ERP initiative as a CIO.