SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'dy0m9rfgf660u');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
To run the SQL TUNING TASK
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_1874');
end;
/
To monitor the processing of the tuning task with the statement
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = '&TASK_NAME';
e.g. TASK_1874
To see the recomendation when the task has a status=COMPLETED
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&TASK_NAME') AS recommendations FROM dual;
e.g. TASK_1874
To accept the best SQL profile
begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => '&TASK_NAME', task_owner => 'SYSTEM', replace => TRUE);
end;
/
e.g. TASK_1874
To check the database sql profiles
select * from dba_sql_profiles;
To disable an sql profile
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/
