Oracle has now included an Oracle tuning advisor. Its purpose is to advise on a SQL statement. For me, it provides an answer on whether to add an index or not. I was taught that one should always start with tables that have no indices. Only when it is demonstrated that indices are used, one should create an index. The idea is that maintenance of indices cost time, which must be offset against the gains from an index. If no gains are expected, it is useless to create such index. Of course, we could use the explain plan for that. This reveals whether an index is used or not.
First, such a SQL statement should be properly included in a structure where it can be analysed. Such a structure can be created with (with SQL Plus:):
DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := 'select * from C##HR.GROOT where n=500000'; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_text => my_sqltext , user_name => 'C##HR' , scope => 'COMPREHENSIVE' , time_limit => 60 , task_name => 'STA_SPECIFIC_EMP_TASK' , description => 'Task to tune a query on a specified employee'); END; /
The existence of this structure can be shown if we look into the enterprise manager under the heading Performance > SQL Tuning Advisor > Manual.
There, we see the entry that we just created.
Subsequently, we may execute this entry with:
BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'STA_SPECIFIC_EMP_TASK'); END; /
Clicking on it shows a suggestion:
It states that an index could be added to improve performance.
One could also receive such information with:
SET LONG 10000; SET PAGESIZE 1000 SET LINESIZE 200 SELECT DBMS_SQLTUNE.report_tuning_task('STA_SPECIFIC_EMP_TASK') AS recommendations FROM dual; SET PAGESIZE 24
If that is done (an index on the filter), and the entry is executed again, the suggestion is disappeared.
We may finally remove the entry with:
exec dbms_sqltune.drop_tuning_task('STA_SPECIFIC_EMP_TASK');