Since Exadata storage indexes are automatically created on the first 8 coulmns of the table accessed by the smart scan and it is stored in memory, I have following questions on the storage indexes
1) How can I query the existing storage indexes in the database , can we get the listing on the table and column basis? is there any X$ table which gives this information.
2) Can I control which columns of a table will have storage indexes, rather than Oracle creating it on the first 8 columns of the table accessed by the smart scan.
Appreciate your help on this.
1. Short answer is these are maintained by the cells and hence opaque to the database. only way to know what is going on is to debug cell services which is completely unsupported by oracle... you will find the required information in expert oracle exadata.
2. Short answer not exactly. See my question here about prewarming the cache... I surmise that the first time a storage region of a table is accessed the cell server will determine which 8 columns it will use for storage indexes , so if you utilise a sql statement with 8 where conditions for the 8 columns to prewarm the cache after dropping and recreating your flash cache ( need since flash cahce became read-write and hence persistent across reboots) then its likely that the cell will choose these for candidates...
but this is currently theoretical as I still need to schedule exclusive time on our machine to test this out....
If there is any kind soul from the exadata dev team who knows whether this algorithm would actually work do please chime in :)
Edited by: robinsc on Jan 10, 2013 11:44 AM
Storage indexes are a bit more complex than simply 8 columns per table. They store values for up to 8 columns for each 1m storage region, and don't necessarily index the same columns for all storage regions. Since they're managed inside the storage servers and not the instance SGA, the X$ fixed table structures don't apply.
You can use the statistic "cell physical IO bytes saved by storage index" to see if storage indexes were used by a given session, and their impact. If you really want to dig deeper, setting kcfisstorageidx_diag_mode to 2 will give oodles of tracefile output showing exactly which SQL statements, objects, and storage regions were used. But since there's no way to directly change what is indexed, it's of mostly academic interest.
Thanks for your response.
-1) Agree, I was just hoping that there might be a way to refer the storage indexes from the database layer.
-2) Since storage indexes are never stored to the disk and only kept in memory,so I am not sure if there is any way to prewarm the storage indexes. Only possible way I could think of is as posted in your prewarming thread to run the smart scan operation ahead of the time when the database is available to the users.
Thanks for the response.
yes the storage index are stored on the storage server , but as they are never written to storage disk, so I was hoping that there might be a way to refer those from the database layer.
Yes the tracing technique will only list whatever happens withing the storage indexes and which ones are being used. But I was basically looking to see if there is actually a way where we can control the creation of the storage indexes. I am sure there must be way out for this, if not in the current version then surely in future release or may be Oracle hasn't released any information on this and kept it behind the scene and may unveil it later :-)
I personally believe this behavior is by design: Exadata is built to be easy to manage, and one way to do this is to eliminate manual tunables. In the case of storage indexes, I'm not convinced that there are any tunables that would make a noticeable impact on performance anyway: if a column combination exists that benefits storage indexes, the built-in logic will eventually find it.
Although Storage Indexes are maintained automatically by the cells, there is something to tune for the DBA in relation to them: When tables are loaded / bulk inserted, do it ordered by the columns that appear often in WHERE conditions afterwards. This increases the chance that Storage Indexes will be beneficial subsequently.
"Don't believe it, test it!"
I would agree that 8 columns / storage indexes per table is the maximum. Keep in mind, though, that the popularity of a column is what causes a storage index to be created. You may start out with 8 storage indexes that you expect to have, but over time, the columns on which storage indexes exist can change.
For this reason, I say that Storage Indexes are gravy. Great if you get hits on them, but don't be too disappointed if you don't. As Uwe noted, sorting your data can make Storage Indexes more effective, but only if you get them on the sorted columns.