Statistics on Index use

[DaPi]

Hi,

Has anyone out there collected stats on index use?
I suspect that I have several (lots?) of indexes that are never used – if so, there might be a chance to improve OLTP (and save space – 50% of the db is indexes) by dropping the unused ones.

I raised a TAR with MetaLink, who replied that Oracle keeps no stats and Explain Plan was the only way to know which indexes are used.

If that is so, then my game plan is:
1) grab all the SQL from the Shared Pool (making sure it’s not been full)
2) replace the bind variables with ‘123’
3) run than through Explain Plan
4) pick out the references to indexes from the output

Has anyone any better ideas?

Thanks - DaPi

 

[carp]

Your plan has one flaw. By changing the bind variable to a constant value, you will not necessarily get the same execution plan. When Oracle encounters a bind variable, it makes assumptions about what will be the best execution plan and THEN plugs the value of the bind variable in. When a constant is supplied, Oracle may very well determine a better execution plan.

A better approach might be to substitute the bind variables with just one bind variable, define the variable in your session, and then run it to get the execution plan. Of course, you may still run into trouble if the variable is defined as one datatype and the query requires another.

 

[bppbme]

Another possibility is outlined in Tom Kyte's book, expert one-on-one Oracle, in the chapter on Optimizer Plan Stability. Namely the section entitled, To See the Indexes Used (pg 515), where he describes using stored outlines to grab the names of the indexes being used


I know you posed to the 8/8i group, but just in case you're on 9i, there's ALTER INDEX MONITORING USAGE, where you would turn on monitoring for the indexes you were interested in, let the system run for whatever you deem an appropriate time window, and query v$object_usage to see if the index was used.

e.g.

SQL> alter index pk_emp monitoring usage;

Index altered.

SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME            TABLE_NAME                     USE
--------------------- ------------------------------ ---
PK_EMP                EMP                            NO

SQL> select ename from emp where empno=7788;

ENAME
----------
SCOTT

SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                TABLE_NAME                     USE
------------------------- ------------------------------ ---
PK_EMP                    EMP                            YES

 

[DaPi]

Thanks to both!

carp - Yes, I realised the bind variables could be a problem. I think can do something like your suggestion. txs

bppbme - I'll look at the Kyte ref. I'm on 8.1.7 so . . .

 

[DaPi]

Hi bppbme, I found the stored outlines in the Oracle designing & tuning doc chapter 10. It looks like it will do the trick - and no need to worry about the bind variables. No noticable effect on OLTP response. Great stuff!