How to Create an Oracle SQL TUNING TASK manually with the SQL_ID

Step 1: Get the sql_id of the oracle session you would like to tune. There are a number of ways to get this, this is probably the easiest.

select x.sid
,x.serial#
,x.username
,x.sql_id
,x.sql_child_number
,optimizer_mode
,hash_value
,address
,sql_text
from v$sqlarea sqlarea
,v$session x
where x.sql_hash_value = sqlarea.hash_value
and x.sql_address = sqlarea.address
and x.username is not null;

Step 2: Create the tuning task by using the SQL identified from. We can also increase the time to avoid “Error: ORA-13639: The current operation was interrupted because it timed out.”

SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '4ztz048yfq32s', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 7200);
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
task_id: TASK_47302

Step 3: Run the SQL TUNING TASK

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_47302');
end;
/

You can monitor the processing of the tuning task with the statement

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_47302';

Step 4. When the task has a status=COMPLETED, then run:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_47302') AS recommendations FROM dual;

Step 5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile.

begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_47302', task_owner => 'SYSTEM', replace => TRUE);
end;
/

Step 6. You can check the database sql profiles with the statement:

select * from dba_sql_profiles;

In case you want to disable an sql profile use the statement:

begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/