Guideline of SQL Tuning Advisor

1. Create a SQL Tuning Task
-----------------------------------------------
DECLARE

alan_task_name VARCHAR2(30);
alan_sqltext CLOB;
BEGIN

alan_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd';

alan_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => alan_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'irmadmin',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'alan_sql_tuning_task',
description => 'Task to tune a query on ......');
END;
/

select task_name from dba_advisor_log where wner=''

2. Executing a SQL Tuning Task
-----------------------------------------------
exec dbms_sqltune.execute_tuning_task(task_name=>'alan_sql_tuning_task');

3. Checking the Status of a SQL Tuning Task
----------------------------------------------------------------
select status from user_advisor_tasks where task_name='alan_sql_tuning_task';


4. Checking the Progress of the SQL Tuning Advisor
-------------------------------------------------------------------------
select status from user_advisor_tasks where task_name='alan_sql_tuning_task';

5. Display the Result of a SQL Tuning Task
-----------------------------------------------------------
set long 1000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task('alan_sql_tuning_task') from dual;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-545660/，如需轉載，請註明出處，否則將追究法律責任。