the Oracle Tuning Advisor

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');

Door tom