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

How It Works
Oracle's documentation states that Exadata creates storage indexes for up to eight columns in a table based on the
nature of query predicates issued against the table, and dynamically adjusts the storage indexes based on your
query workload.
More accurately though, cellsrv maintains high and low values for up to eight columns in a table for a specific
storage region , and not all storage regions will necessarily have the same storage indexes on the same columns for a
table. The nature of the data, as stored on disk, in combination with the mix of query predicates, will dictate which
columns will have storage indexes created for them at any given point in time. Contrast the following excerpt from the
lst19-07-storindsum.pl script, which displays a different set of columns for different storage regions at nearly the
same point in time. The differences are outlined in bold:

Object: {29606 12 2273376219}
2012-11-29 02:40:02.993332*:: Region Idx => 208643
2012-11-29 02:40:02.993332*: Col id [1] numFilt 14 flg 2:
2012-11-29 02:40:02.993332*: Col id [2 ] numFilt 4 flg 2:
2012-11-29 02:40:02.993332*: Col id [4] numFilt 0 flg 2:
2012-11-29 02:40:02.993332*: Col id [5] numFilt 0 flg 2:
2012-11-29 02:40:02.993332*: Col id [6] numFilt 0 flg 2:
2012-11-29 02:40:02.993332*: Col id [7] numFilt 1 flg 2:
2012-11-29 02:40:02.993332*: Col id [9] numFilt 0 flg 2:
2012-11-29 02:40:02.993332*: Col id [10] numFilt 2 flg 2:
Object: {29606 12 2273376219}
2012-11-29 02:40:03.004267*:: Region Idx => 208685
2012-11-29 02:40:03.004267*: Col id [1] numFilt 1 flg 2:
2012-11-29 02:40:03.004267*: Col id [3] numFilt 1 flg 2:
2012-11-29 02:40:03.004267*: Col id [4] numFilt 2 flg 2:
2012-11-29 02:40:03.004267*: Col id [5] numFilt 3 flg 2:
2012-11-29 02:40:03.004267*: Col id [6] numFilt 4 flg 2:
2012-11-29 02:40:03.004267*: Col id [8] numFilt 0 flg 2:
2012-11-29 02:40:03.004267*: Col id [9] numFilt 0 flg 2:
2012-11-29 02:40:03.004267*: Col id [10] numFilt 0 flg 2:

You may be asking yourself whether this entire topic is worthwhile to understand or what practical use you would
have for knowing how to trace storage index behavior in general. In our opinion, the likelihood of needing to know
this information is relatively low, but it comes in handy when attempting to diagnose performance variances for your
applications over time. For example, a full offloaded table scan that sometimes runs in 30 seconds but other times has
sub-second response times is frequently a result of storage index benefits on Exadata. This response time variance
may be acceptable, but in the event it isn't, being able to diagnose how cellsrv is “using” storage indexes
can be useful.

■ as documented and stated a number of times in this chapter, the exadata dMa has no direct way to influence
storage indexes, only indirect means such as ordering data intelligently, limiting your query predicates, and so forth.
additionally, storage indexes are not persistent across storage cell reboots and also need to be “primed” before exadata
can leverage them. For these reasons, we like to tell people that storage indexes are great to have when exadata decides
it can use them, but do not rely on them as an optimization tool to guarantee a performance SLa.

Note