Missing Indexes

754320

Any idea if there is a tuning advisor (or any other tool) for Oracle DBAs which proposes the creation of an index to improve the performance of a particular query? Similar to the Microsoft SQL server Activity Monitor explain plan?

Ludwig

Hoek

Yes, check the Oracle docs:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28301/montune003.htm#ADMQS103
http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/montune.htm#ADMQS103

Home of the Oracle docs:
http://tahiti.oracle.com

Gaff

Absolutely! Oracle Grid Control/Enterprise Manager. Run your query and you'll be able to see it in the OEM GUI and have OEM analyze the query (or set of queries) and suggest tuning advise. The link is called SQL Adviser I believe? (Don't have it in front of me at the moment.)

You don't say what version you have, and OEM has slightly different looks depending on its version, but this should get you started.

http://developer.emc.com/developer/downloads/FAQ_Perf_Tuning_With_Oracle_Tuning_Advisor.pdf


You can also do it through SQL, but I never do.
http://www.databasejournal.com/features/oracle/article.php/3387011/Introduction-to-Oracle-10gs-New-SQL-Tuning-Advisor.htm