Exadata – Controlling Storage Indexes

Oracle is trying to make everything automated, in general they are trying to reduce the administration. Nothing bad with that, but as we know the automated features in Oracle Database are good in most, but not in all situations. In those situations where the algorithms are not handling well, a human interaction is needed.

The same is the case of Storage Indexes. They are transparent for the database, you cannot control them in documented way and they are fully automated. In general they are working well, but in some cases for some objects they are created for the wrong column set. Some objects don’t sound seriously. But what if those objects are in the core of your system, what would happen if the performance of the base reports is based exactly on those particular storage indexes. Well that’s bad because you cannot control them. You can just restart the cell nodes and hope… hope that the “Exadata” will create them in the right way for you. Well, there is another way.

cellsrv is providing us the following commands:
alter cell events="immediate cellsrv.cellsrv_storidx('purge|disable|enable','all|GridDiskName', objd, tsn, dbid)";

purge - purge storage indexes for specified disk/db/object
disable - disable storage indexes for specified disk/db/object
enable - disable storage indexes for specified disk/db/object
all/griddiskname - dump for all griddisk/dump for specific griddisk
objd - data_object_id from all_objects
tsn - tablespace number – ts# from ts$
dbid - ksqdngunid from x$ksqdn

Unfortunately the dbid parameter is not working with cell v11., I think there is a problem related with integer overflow. For more information check here.

So for now we can control them only on grid disk level, which is actually not so bad. I’ll test them on cellsrv v12. when I have time, maybe the dbid parameter will work well in there.


About these ads

2 thoughts on “Exadata – Controlling Storage Indexes

  1. This is great. Have been successfull to manually enable storage indexes. I would like to see how I as a DBA can create storage indexes for my critical queries.

  2. SI (Storage Indexes) are enabled by default, you don’t need to enable them manually. In my opinion, the most useful command form here is purge. In short, lets say that we have a SI which is created for the wrong columns and you have to recreate it for the right ones. In that case you can just execute the purge command to purge it and then you have to run the appropriate query to recreate it in the right way. Of course before running the query you have to ensure that the offloading will kick in and be aware that it’s possible the SI to not be created from the first execution. Also, If itis appropriate, you can use custom queries for the recreation.
    Without using this command /purge/, the only way to recreate the SI is to restart the cellsrv. And as you know, when you restart it, there is performance degradation /one node less/, disks resilvering after it start again and so on.

    Thanks for reading.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s