Oracle database tuning: DBMS_SQLTUNE

1- Create tuning task:

DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sql_text := 'SELECT /*+ ORDERED */ * ' || 'FROM employees e, locations l, departments d ' || 'WHERE e.department_id = d.department_id AND ' || 'l.location_id = d.location_id AND ' || 'e.employee_id < :bnd';   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sql_text, bind_list => sql_binds(anydata.ConvertNumber(100)), user_name => 'USERADV', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'my_sql_tuning_task01', description => 'Task to use for tuning a query'); END;

2- Execute tuning task:

BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task01' ); END;

3- Report tuning task:

SET LONG 1000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task01') FROM DUAL;