Steps to run the tuning adviser manually

Steps to run the tuning adviser manually

1)

set serveroutput on
declare
  l_sql_tune_task_id  varchar2(100);
begin
  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
                          sql_id      => 'xyxasdf',
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'test_1123',
                          description => 'tuning task for statement your_sql_id.');
 dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

2)

exec dbms_sqltune.execute_tuning_task(task_name => 'test_1123');

3)

-- displaying the recommendations
set long 100000;
set longchunksize 1000
set pagesize 10000
set linesize 100
select dbms_sqltune.report_tuning_task('test_1123') as recommendations from dual;

select NAME,CREATED,LAST_MODIFIED,TYPE,STATUS from dba_sql_profiles order by 2;