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

How It Works
You can set the _kcfis_storageidx_diag_mode parameter, at both the session and system level, to enable storage
index tracing. When you set this value to 2, cellsrv will enable storage index tracing in debug mode and generate a
trace file for each cellsrv process thread that performs I/O and uses storage indexes.

■ Note the default value for _kcfis_storageidx_diag_mode is 0. Setting to 1 disables tracing.

Why would you want to trace storage index behavior? Other than the pure academic joy of the endeavor, storage
indexes are unique in that they are structures that you have no direct means of managing, yet provide potentially
dramatic performance gains to your applications. These significant performance gains are at times accompanied
by some unexpected results. As stated in Oracle's documentation, Exadata maintains region indexes for up to eight
columns on a table and, as such, a storage index trace file can provide you with insight into which columns have
associated storage indexes and which ones do not. This can be vital information for a performance engineer tasked
with explaining why queries sometimes run for a minute and sometimes execute in a fraction of a second.

■ one thing you may observe from storage index trace files is that only the first eight bytes of the column
values are tracked. So, if your columns are populated with a common set of characters in the first eight bytes, storage
indexes will not be effective as cellsrv will have no way of distinguishing different ranges of high and low values for
your columns.

Note

19-5. Tracing Storage Indexes When More than Eight
Columns Are Referenced
Problem
You wish to measure and understand how Exadata dynamically maintains storage indexes when your applications
issue queries with predicate conditions on more than eight columns for a table.

Solution
In this recipe, you will learn how to trace storage indexes, similar to the examples provided in Recipe 19-4 and identify
how Exadata dynamically adjusts region index contents when more than eight columns are used in query predicates
for a specific table.

1. Begin by identifying or creating a test case. In this recipe, we will create a test table called

MYOBJ_TEST and populate it with two NUMBER columns and eight VARCHAR2 columns, each
containing a value from DBA_OBJECTS.OBJECT_NAME . We will load the table with over 20
million rows to encourage Smart Scan and make the queries issued against it eligible for
storage indexes:

SQL> create table d14.myobj_test
2 tablespace tbs_test nologging as
3 select * from (
4 select rownum as col1 from dual connect by level <=1000),