Run SQL Tuning Advisor Manually for a SQL ID

1. Replace the SQL ID and the Task Name and Execute the below PL/SQL Block.

DECLARE
  v_task_id varchar2(200);
  v_sql_id VARCHAR2(20);
  v_task_name VARCHAR2(50);

BEGIN
  v_sql_id :='53smvk9gm9018';
  v_task_name := 'SQL_TUNE_ADV_'||v_sql_id;
  v_task_id :=dbms_sqltune.create_tuning_task(sql_id => v_sql_id, task_name=>v_task_name);
  exec dbms_sqltune.execute_tuning_task(v_task_name);
END;
/

2. The Tuning task will take few mins to complete. You can monitor the progress of the task with these SQLs:

SELECT TASK_ID, TASK_NAME, STATUS
FROM USER_ADVISOR_TASKS
WHERE TASK_NAME = 'STA_SPECIFIC_EMP_TASK';

COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999
SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$ADVISOR_PROGRESS
WHERE TASK_ID = <task_id>;

COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM USER_ADVISOR_LOG;

3. Run the below command to get the Output and report/recommendation from the above SQL Tuning Task:

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_SPECIFIC_EMP_TASK' ) FROM DUAL;