Oracle SQL - check missing indexes on SQL query

Question

I need to check if in a query, index or indexes are missing.

For check indexes in a table I use syntax:

SELECT index_name
     , column_position
     , column_name
  FROM user_ind_columns
 WHERE table_name = 'table_name'
 ORDER BY index_name, COLUMN_POSITION;

 SELECT index_name 
 FROM user_indexes
 WHERE table_name = 'table_name';

It is possible to use a program or SQL script to find automatically if is missing indexes on a query and show it for creation them?

Thanks!

Answer 1

Not really. Checking the execution plan will tell you which indexes, if any, the optimizer will use; but if the plan shows no indexes it might be that

1. There are no indexes.
2. There are indexes but they cannot be used for the particular query.
3. There are indexes that can technically be used, but the optimizer calculates that they will not improve the performance.

In the case of #3, it is always possible that it is wrong (e.g. due to incorrect stats or a query beyond the optimizer's ability to model it accurately). If this is the situation you are looking for then there is no simple way to detect it.

You might look at dbms_sqltune for automated tuning suggestions. It'll generally tell you to gather stats, or sometimes suggest a SQL profile or a new index. You can call it from Enterprise Manager or from a script:

http://www.williamrobertson.net/documents/automated-sql-tuning-advice.html http://www.williamrobertson.net/documents/automated-sql-tuning-advice-sqlid.html

Answer 2

With an Oracle Autonomous Database you can enable Automatic indexing. Auto Indexing automates the index management tasks and eliminates the need to knowledge and expertise in creating, maintaining, and retiring/not-using indexes appropriately based on your workload patterns.

Enable Auto Indexing

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

Disable Auto Indexing

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Refer to Auto Indexing in Oracle documentation for more details.

Answer 3

This is one good resource: https://www.dba-scripts.com/scripts/diagnostic-and-tuning/troubleshooting/find-missing-index/

ACCEPT SCHEMA_NAME PROMPT 'Choose the schema to analyze:'

select * from (
  select 'the column ' || c.name || ' of the table ' || us.name || '.' || o.name || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index
  from sys.col_usage$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.user$ us
  where u.obj# = o.obj#
  and   u.obj# = c.obj#
  and   us.user# = o.owner#
  and   u.intcol# = c.col#
  and   us.name='&SCHEMA_NAME'
  and   c.name not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME')
  and   (u.equality_preds > 100 OR u.equijoin_preds > 100)
  order by u.equality_preds+u.equijoin_preds desc)
WHERE rownum <11;

Answer 4

A reliable method I have found for index suggestion (and many other suggestions for improvement) is the SQL Tuning Advisor in OEM (Oracle Enterprise Manager). It sometimes finds a better plan for your statement which it adds into a SQL Profile which you can check under dba_sql_profiles.

SQL Advisor will give you index suggestions in this format:

Consider running the Access Advisor to improve the physical schema design or creating the recommended index.

<schema.table>(field list)

Creating the recommended indices significantly improves the execution plan of this statement.

You will have to run your SQL first and find it in OEM under Top Activity of your instance, or search for it by SQL ID.