DBMS_SQLTUNE

Step 1: Creating Tuning Task DECLARE my_task_name VARCHAR2 (30); my_sqltext CLOB; BEGIN my_sqltext := 'SELECT e.last_name, d.department_name, d.department_id FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id = :bnd'; my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext, bind_list => sql_binds (anydata.convertnumber (9)), user_name => 'HR', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'vega_tuning_task', description => 'Tuning Task' ); END; / Create_tuning_task functions returns name of the task created. Step 2: Executing SQL Tuning Task BEGIN dbms_sqltune.execute_tuning_task (task_name => 'vega_tuning_task'); END; / Step 3: Checking Status of SQL Tuning Task SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'vega_tuning_task'; Step 4: Retrieving results of SQL tuning task After task is executed results can be obtained by calling REPORT_TUNING_TASK function SET LONG 1000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'vega_sql_tuning_task') FROM DUAL;