In this case, the storage index indicates
that rows 1, 2, and 3 of the table (region
1) will definitely not satisfy the WHERE
cust_level IS NULL condition, whereas one
or more of rows 4, 5, and 6 (region 2) may
satisfy the condition.
As you can see, Oracle Exadata storage
indexes do not locate the areas of the table
that contain the values of interest to the
user; rather, they identify the areas that
definitely will not contain the values, thus
eliminating them from I/O processing. In
a manner of speaking, they act as negative
indexes, just the opposite of traditional database indexes, which are for locating—not
eliminating—the database blocks that may
contain the information.
Storage indexes are not stored on disk;
they are resident in the memory of the storage
cell servers. They are created automatically after the storage cells receive repeated
queries—with predicates—for columns. No
user intervention is needed to create or maintain storage indexes. And because they are
memory-resident structures, they disappear
when the storage cells are rebooted.
To use storage indexes, Oracle Exadata
queries must use smart scans, so not all
types of applications can benefit from
storage indexes. Applications with queries
that include predicates and perform a lot of
full table scans or fast full scans of indexes—
typically those used in data warehousing
environments—will benefit greatly from
storage indexes. Online transaction processing (OLTP) applications, on the other
hand, typically access a small number of
rows through standard indexes and do not
perform full table scans, so they may not
benefit from storage indexes.
Code Listing 1: Checking for I/O savings attributable to storage indexes
col stat_value format 9,999.9999
select
decode(name,
'cell physical IO bytes saved by storage index',
'SI Savings',
'cell physical IO interconnect bytes returned by smart scan',
'Smart Scan'
) as stat_name,
value/1024/1024 as stat_value
from v$mystat s, v$statname n
where
s.statistic# = n.statistic#
and
n.name in (
'cell physical IO bytes saved by storage index',
'cell physical IO interconnect bytes returned by smart scan'
)
/
Cell physical IO interconnect bytes returned •
by smart scan. This metric shows how
many bytes of I/O were returned by a smart
scan to the database server.
The code in Listing 1 returns the names
and values for these two metrics. To save
space in the output of Listing 1, I present the
metrics with shorter names: “SI Savings”
for cell physical I/O bytes saved by storage
index and “Smart Scan” for cell physical I/O
interconnect bytes returned by smart scan.
And I show the values for both metrics, in
megabytes.
After starting the session on Oracle
Exadata, I issue
particularly in those cases where storage
indexes are not used or are not created yet.
select avg(amt) from sales where
sales_dt = '13-MAR- 11';
select avg(amt) from sales where
cust_level is null
I then execute the query in Listing 1 and
examine the output to see the effect of
storage indexes:
and then execute the query in Listing 1 to see
the effect of using storage indexes. Here is
the output:
STAT_NAME STAT_VALUE
—————————————— ——————————————
SI Savings 0.0000
Smart Scan 0.9035
CHECKING FOR SAVINGS
Let’s see how much I/O can be reduced
with storage indexes. I’ll check for I/O
savings on Oracle Exadata by querying the
V$MYS TAT view.
This view includes the values of several
metrics for the current session. The two
metrics I am interested in are
Cell physical IO bytes saved by storage •
index. This metric shows how many bytes
of I/O were eliminated by the application of
storage indexes at the storage cell level.
STAT_NAME STAT_VALUE
—————————————— ——————————————
SI Savings 545.9234
Smart Scan 0.0012
As the output shows, the I/O savings
attributable to storage indexes is about 546
MB and the storage cells returned 0.0012
MB to the database. These numbers are
highly dependent on workload and environ-
ment, but the measurement concept is still
the same. Using this query, you will be able
to examine the savings in your specific case,
The results may surprise you. The I/O
savings attributable to storage indexes is
0; that is, storage indexes were not used.
However, you can see from the “Smart Scan”
statistic that a smart scan reduced the I/O
significantly—to just 0.9 MB (from a table that
contains a few hundred gigabytes of data).
