When is Exadata’s storage indexes used?

Exadata’s Storage indexes provides data pruning at the storage layer.The storage indexes eliminating disk IO, by storing a summary of the data distribution on the disks.MOS Note ID 1094934.1 provides the following summary:

Each disk in the Exadata storage cell is divided into equal sized pieces called storage regions (default 1MB). There is an index entry for every storage regions (1MB of data stored on disk). Each entry contains the minimum and maximum value for columns seen in ‘where’ clause predicates. Information for up to 8 columns can be stored. The index is then used to eliminate disk IO by identifying which storage regions don’t match the ‘where’ clause of a query.

The question I’ll try help answer in this blog post is, when Oracle can take advantage of the storage indexes, by sharing some of the test results and findings I came across when testing storage indexes. The data used for these tests are simple versions of a dba_tab_columns including an id column.The tests were performed on a quarter rack ExaData database machine (2 db nodes – with 16 cores each and 3 storage servers)

I tried a few more test scenarios, with forcing direct reads, using parallel query but it did not make any difference for this SQL.For the between test, I could only get the storage index to kick in for the ordered data.

Check the storage session statistics before testing

Name

Value

cell physical IO bytes saved by storage index

93359243264

SQL> select avg(pba_id) from pba_order where pba_id <100;-- LESS than on sorted data

AVG(PBA_ID)-- 50

Elapsed: 00:00:00.04

Name

Value

cell physical IO bytes saved by storage index

103721861120

SQL> select avg(pba_id) from pba_rnd where pba_id <100;-- LESS than on unsorted data

Again I tried a few more test scenarios, with forcing direct reads, using parallel query but it did not make any difference in this case.For the equal sign predicate test, I could only get the storage index to kick in for the ordered data.

Before finishing up, let’s check if storage indexes are kicking in on tables compressed with EHCC.

In Exadata, the Storage Index can evaluate predicates like <, <=, =, !=, >=, >, is NULL, is NOT NULL, as you saw above I was not able to get the storage indexes to kick in for the between and equal predicate for the random sorted data.It may work in other circumstances or test-cases; currently it is very hard to evaluate, as there is no public tracing tool available.

In general we saw marginal better IO savings on the test-case with sorted data and that storage indexes also worked on EHCC compressed tables. I have also performed the same tests on a global temp table and the storage indexes did work with global temp tables as well.

I tested using your sample but always the statistic cell physical IO bytes saved by storage index is zero!!
Tried forcing _serial_direct_read=always and _kcfis_storage_idx_disabled=false but made no difference.
Always zero. Can you tell me if there is anything else to check for?
I have 24G SGA on each node of a 4 node half rack. is the table too small??

Haven't had much time to update my entries on this site, but i just wanted to mentioned a few notes that i had recorded on this topic, from earlier:
1) For uncompressed blocks and OLTP compressed blocks, storage index is maintained during writes
2) For HCC compressed blocks, writes invalidate region index (not the whole storage index)
3) SI is effective for tables encrypted using tablespace encryption
4) For tablespace encryption as well, writes invalidate region index
5) Queries on tables encrypted using column level encryption are not helped by SI

It has been a year or so since i collected this information so this might be outdated by now or better documented elsewhere.
Thanks & Regards, - Peter

Sorry for the late reply, just came across you question, which i suspect that you have already found an answer for by now. From the database you can use the wait event system to check for %storage% or %cell%, but apart from the wait event system i have not come across anything else yet - this blog post is from 2010 and i'm sure a lot have changed since, and somebody else might have found something by now. I know that Tanel Poder (http://blog.tanelpoder.com/) is working on a utility (ExaSnapper), which i hope will include more visability into the storage cell in general. As you may have found out regarding the storage indexes, you don't really have the full control of when they are kicking in, but when they do they often work wonders.

Sorry for the late reply, just came across you question, which i suspect that you have already found an answer for by now. From the database you can use the wait event system to check for %storage% or %cell%, but apart from the wait event system i have not come across anything else yet - this blog post is from 2010 and i'm sure a lot have changed since, and somebody else might have found something by now. I know that Tanel Poder (http://blog.tanelpoder.com/) is working on a utility (ExaSnapper), which i hope will include more viability into the storage cell in general. As you may have found out regarding the storage indexes, you don't really have the full control of when they are kicking in, but when they do they often work wonders.

> I tried a few more test scenarios, with forcing direct reads, using parallel query but it did not make any difference for this SQL. For the between test, I could only get the storage index to kick in for the ordered data.

A direct read is fundamental for being able to use cell depended optimisations, like smart scans or storage indexes. This means a "normal" session will do direct reads if it's doing a scan which would do multiblock IO like TABLE ACCESS FULL or FAST FULL INDEX SCAN, which is the default starting from Oracle version 11.

Hi Frits,
Thanks for detailed explaination. Given it is default bahavior the extra tests may have been pointless, i just tempt to get extra careful when dealing with Storage indexes as it is an area which have not been fully explored yet.
-Peter

I meant to follow up with you. The biggest thing I noticed in my recent little spat of testing was that implicit conversions were the biggest impediment to Storage Indexes getting used. I mentioned that in the post about SI's and bind variables, but it wasn't very prominent in the post. The implicit conversion issue is a little counter intuitive since we're not used to worrying about that when we're already doing a full scan. In particular, dates were an issue because of the funky format they apparently use ('syyyy-mm-dd hh24:mi:ss'). It makes sense if they are storing it as a string (which they probably are). But they seem to have limited conversion capability at this point since '10-aug-2010' gets converted to the proper format to use the SI, but '10-aug-10' does not. Anyway, it's fun trying to figure it all out. Thanks for the post and the plug.