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.