Storage Indexes - Oracle Exadata Recipes: A Problem-Solution Approach - page 541

Listing 19-1. lst19-01-exastorind-mysess.sql

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;

Statistic Value (MB)
-------------------------------------------------------------------- -----------------------
cell physical IO bytes eligible for predicate offload 2,255.09
cell physical IO bytes saved by storage index 1,344.27
cell physical IO interconnect bytes 1.51
cell physical IO interconnect bytes returned by smart scan 1.49
SQL>

In this output, we can see that our query completed in under a second and we saved over 1,344 megabytes of I/O
to Exadata's cell disks by use of storage indexes. Part of the reason for the very fast performance was certainly related
to Smart Scan, but a large part of this was related to I/O savings via storage indexes.

■ to learn more about exadata's Smart Scan feature and ways to measure the performance impact of Smart
Scan, please refer to Chapter 15.

Note

One important consideration to be aware of with storage indexes is that in order for Exadata to produce storage
index I/O savings, the data in these indexes will need to be written to the index's storage regions. In other words,
storage index data needs to be “primed” on the cell server's region index memory structures in order for them to be
utilized. If we were to run the previous query the first time, you would see no storage index I/O savings because the
region indexes would contain no relevant data. In the following example, we will bounce our database and execute
the same test case twice to demonstrate the point:

[oracle@cm01dbm01 source]$ srvctl stop database -d edw
[oracle@cm01dbm01 source]$ srvctl start database -d edw
SQL> select count(*) from d14.myobj_uncomp
2 where object_id between 100 and 200;
COUNT(*)
----------
101000
Elapsed: 00:00:18.95 <− First execution
SQL> set echo off
SQL> @lst19-01-exastorind-mysess.sql