Get explain plans of a sql query from AWR using DBMS_XPLAN.DISPLAY_AWR

set lines 2000 pages 2000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('7q4fs7yuu5fyk'));

Run SQL tuning advisor for a sql_id

Method 1.  
@?/rdbms/admin/sqltrpt.sql


Method 2. 

a. Create Tuning Task

consider sql_id is 7q4fs7yuu5fyk

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '7q4fs7yuu5fyk',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '7q4fs7yuu5fyk_tuning_task11',
                          description => 'Tuning task1 for statement 7q4fs7yuu5fyk');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


b. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '7q4fs7yuu5fyk_tuning_task11'); 


c. Get the Tuning advisor report.

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('7q4fs7yuu5fyk_tuning_task11') from dual;