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 v220.127.116.11.0, 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 v18.104.22.168.0 when I have time, maybe the dbid parameter will work well in there.