How to run SQL Tuning Adviser manually??

Step 1: Replace SQL_ID and TASK_NAME (Unique):

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => 'XXXXX',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 1800,

                          task_name   => 'XXXXX_A',

                          description => 'Tuning task for statement 8p9qh5xwgc3fx_B in AWR.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

Step 2: Validate if SQL Tuning Task is created or not (Status should be INITIAL):

select * from dba_advisor_log  where task_name='XXXXX_A' order by task_id desc;

Step 3: Run the Advisor

EXEC DBMS_SQLTUNE.execute_tuning_task('XXXXX_A');

Step 4: Check the status in other session using command below: (It should show EXECUTING)

select * from dba_advisor_log  where task_name='XXXXX_A' order by task_id desc;

Step 5: Once the Advisor task is completed. Generate report using command below:

SELECT DBMS_SQLTUNE.report_tuning_task(‘XXXXX_A') AS recommendations FROM dual;

Step 6: Analyze the report and implement the solution if necessary.

Source: Thanks Arbind!!!