Create an Oracle SQL TUNING TASK with DBMS_SQLTUNE

You can create an SQL TUNING TASK manually adhoc with the following simple steps.

Step1: Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids.
In case this is a current sql running use the v$session.

select sql_id from v$session where sid = :x

Step2: Login as SYSTEM (or any other user) at sqlplus and create the tuning task:

SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '7yfnm5x60f3gm',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 7200,
task_name => 'STA1_7yfnm5x60f3gm',
description => 'Tuning task for statement 7yfnm5x60f3gm');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
task_id: STA1_7yfnm5x60f3gm

Step3: Run the SQL TUNING TASK

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

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

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

Step5: When the task has a status=COMPLETED, then run:

SET LONG 20000;
SET PAGESIZE 2000
SET LINESIZE 300
SELECT DBMS_SQLTUNE.report_tuning_task('STA1_7yfnm5x60f3gm') AS recommendations FROM dual;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp