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

How It Works
Exadata enables storage indexes by default when queries execute with Smart Scan, and cellsrv can determine that storage
indexes are created on the storage regions being accessed, as discussed in multiple recipes in this chapter. To disable storage
indexes for a specific database, simply use the ALTER SYSTEM command and set _kcfis_storageidx_disabled to TRUE .
Why would you ever want to do this? Outside of a purely academic exercise, there are cases in which organizations
consider consistent application performance to be of higher importance than variable performance, even when the variable
performance is always “for the positive.” Storage indexes are unique for many reasons, but one of the interesting features
is that their use will never make performance worse, only better. Simply put, the goal of storage indexes is to avoid issuing
physical I/Os when not needed. Avoiding work means less work to do, and less work means your queries will run faster.

19-8. Troubleshooting Storage Indexes
Problem
You have determined that your query is not benefiting from storage indexes and you would like to determine why this
is the case.

Solution
In this recipe, you will learn how to identify conditions under which Exadata will not use storage indexes and
determine whether your query is subject to these limitations.

Ensure that your query contains a query predicate, or WHERE clause. Storage indexes will
only be used for queries imposing a limiting condition on the rows returned.

1.

2. Ensure that your query is using Smart Scan. Recipe 15-3, 15-4, and other recipes in

Chapter 15, along with the script in Listing 19-1, demonstrate how to use the cell
physical IO bytes eligible for predicate offload statistic to determine whether
your query was Smart Scan eligible.

Ensure that the ASM disk group that stores your segment has the cell.smart_scan_
capable attribute set to TRUE . Recipe 9-10 demonstrates how to measure this.

3.

Ensure that your query does not use LIKE operations in the query predicate with wildcard literals. 4.

5. If your storage index savings is minimal or zero and all of the above conditions are met,

ensure that the data in your segment is well ordered with respect to the query predicate.
Recipe 19-2 discusses the importance of ordered data and storage indexes in more detail.

6. Generate a storage index trace file and examine the number of columns in use for your

segment. Recipe 19-4 outlines the steps required to generate and understand the contents
of a cellsrv trace file with storage index diagnostics.

How It Works
As discussed in the How It Works section of Recipe 19-1, storage indexes are used in the following situations:

• During direct path read operations

• With smart scans

WHERE clause) and do not contain wildcards • When the queries contain predicates (in other words, a

Furthermore, storage indexes are most beneficial in terms of performance when the data is well ordered with
respect to the query predicate and the WHERE clause is able to eliminate large numbers of rows from the result set.