Oracle How To ...
Identifying missing indexes
There is no guaranteed way of finding missing indexes. The following is intended to help identify where beneficial indexes do not exist.
To find the top SQL statements that have caused most block buffer reads:
Select buffer_gets, sql_text from v$sqlarea where buffer_gets > 10000 order by buffer_gets desc;
If this returns a large number of rows then increase the number of ‘buffer_gets’ required, if it returns no rows then decrease this threshold.
Typically, most of these will be select statements of some sort. Considering each in turn, identify what indexes would help with the query and then check that those indexes exist. Create them if necessary.
To find the most frequently executed SQL:
Select executions, buffer_gets, sql_text from v$sqlarea where executions > 10000 order by executions desc;
If this returns a large number of rows then increase the number of ‘executions’ required. If it returns no rows then decrease the number of executions required.
This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.