[Oracle] Managing Oracle Database tuning tasks

Hi all, we are going to discuss on how to use Oracle tuning tasks to improve query performances.

First, we need to limit the time interval of the analysis. Since Oracle works with snapshots, we get the list of all the snapshots that contain our query and then choose the START_SNAP and END_SNAP of our analysys:

select SNAP_ID
from DBA_HIST_SQLSTAT
where SQL_ID='ID_SQL'
order by SNAP_ID;

The, we create the task:

DECLARE
  L_SQL_TUNE_TASK_ID VARCHAR2(100);
  BEGIN
    L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
      BEGIN_SNAP => START_SNAP,
      END_SNAP => END_SNAP,
      SQL_ID => 'ID_SQL',
      SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
      TIME_LIMIT => 600,
      TASK_NAME => 'TASK_NAME',
      DESCRIPTION => 'Tuning task for ID_SQL');
    DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
  END;
/

Then we can check the status of the task:

select TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
from USER_ADVISOR_LOG
where TASK_NAME = 'TASK_NAME';

Then, we run the task:

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

And when it finishes we check if there is any error:

select TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
from USER_ADVISOR_LOG
where TASK_NAME = 'TASK_NAME';

In the end we generate an HTML report:

set LONG 100000
set LONGCHUNKSIZE 100000
set LINESIZE 32767
spool ID_SQL.html
select DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_NAME')
from DUAL;
spool off

And eventually we drop the task:

BEGIN
  DBMS_SQLTUNE.DROP_TUNING_TASK ('TASK_NAME');
END;
/

That’s all. See you next time!