SQL Tuning Advisor

SQL Tuning Advisor



----------------------------------------

-- Tuning task created for specific a statement from the AWR.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 3223,
                          end_snap    => 3230,
                          sql_id      => 'aak5zghvqpfva',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'aak5zghvqpfva_AWR_tuning_task',
                          description => 'Tuning task for statement aak5zghvqpfva in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'aak5zghvqpfva_AWR_tuning_task');


SELECT DBMS_SQLTUNE.report_tuning_task('aak5zghvqpfva_AWR_tuning_task') AS recommendations FROM dual;

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task');
END;
/


BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => '88td84pgvjh3m_AWR_tuning_task');
END;
/

------------------------------------------------------