Run SQL Tuning Advisor For A Sql_id

STEP 1. Create Tuning Task:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '7n73r5q1kwxgr', ---<< use sqlid which needs to be tuned
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '7n73r5q1kwxgr_tuning_task1',
                          description => 'Tuning task1 for statement 7n73r5q1kwxgr');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;

STEP 2. Execute Tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '7n73r5q1kwxgr_tuning_task1');

STEP 3. Get the Tuning advisor report:

select dbms_sqltune.report_tuning_task('7n73r5q1kwxgr_tuning_task1') from dual;

STEP 4. Get list of tuning task present in database:

SELECT task_id,task_name, description, advisor_name, execution_start, execution_end, status
     FROM dba_advisor_tasks
     WHERE task_name='7n73r5q1kwxgr_tuning_task1'
     ORDER BY task_id DESC;

SELECT * FROM DBA_ADVISOR_LOG WHERE task_id ='6268';

STEP 5. Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('7n73r5q1kwxgr_tuning_task1');