Running SQL Advisor from sqlplus for a sql_id

We can run the sql advisop from sqlplus.

First find the sql_id for the session, based on something like the following

select SID, SERIAL#, INST_ID, USERNAME, STATUS, PROGRAM, SQL_ID, event from gv$session where username = 'REPORTS' and status = 'ACTIVE' ;

Now we need to create the the tuning task based on the sql_id

DECLARE v_tune_taskid VARCHAR2(100); BEGIN v_tune_taskid := dbms_sqltune.create_tuning_task ( sql_id => '74ztaz3u3wzab', scope => dbms_sqltune.scope_comprehensive, time_limit => 30, task_name => 'sqltune_74ztaz3u3wzab', description => 'Tuning task sql_id 74ztaz3u3wzab'); dbms_output.put_line('taskid = ' || v_tune_taskid); END; /

Now we can execute the task

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

check the status of the job

select task_name, status from dba_advisor_log where owner = 'OWNER_YOU_RAN_TASK_AS';

Once completed we then need to show the ouput

set long 10000; set pagesize 1000 set linesize 220 set pagesize 24 select dbms_sqltune.report_tuning_task('74ztaz3u3wzab_tune_report') as output from dual;

******************************************
keywords: sql_id advisor
******************************************
rdbms version: 11g
******************************************