SQL Tuning Advisor （SQL 优化指导/顾问）

原文地址：Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor (文档 ID 262687.1)

You can run SQL Tuning Advisor from the command line using $ORACLE_HOME/rdbms/admin/sqltrpt.sql

1  给SH用户授予相应的权限

CONNECT / AS SYSDBA
GRANT ADVISOR TO SH;
GRANT SELECT_CATALOG_ROLE TO SH;

GRANT EXECUTE ON DBMS_SQLTUNE TO SH;

@>conn / as sysdba
Connected.
SYS@test>alter user sh identified by oracle account unlock;

User altered.

SYS@test>grant advisor to sh;

Grant succeeded.

SYS@test>grant select_catalog_role to sh;

Grant succeeded.

SYS@test>grant execute on dbms_sqltune to sh;

Grant succeeded.

SYS@test>

2 创建任务 
DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
BEGIN
  my_sqltext := 'SELECT * '   ||
                'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                           sql_text => my_sqltext,
                           user_name => 'SH',
                           scope => 'COMPREHENSIVE',
                           time_limit => 60,
                           task_name => 'TEST_sql_tuning_task',
                           description => 'Task to tune a query on a specified PRODUCT');

END;

SH@test>DECLARE
  2    my_task_name VARCHAR2(30);
  my_sqltext CLOB;
BEGIN
  my_sqltext := 'SELECT * '   ||
                'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                         3    4    5    6    7    8    9      sql_text => my_sqltext,
                           user_name => 'SH',
                           scope => 'COMPREHENSIVE',
                           time_limit => 60,
                           task_name => 'TEST_sql_tuning_task',
                           description => 'Task to tune a query on a specified PRODUCT');
END; 10   11   12   13   14   15  
 16  /

PL/SQL procedure successfully completed.

SH@test>

3 查询创建的task 名称

select task_name from dba_advisor_log where owner='SH'; 

SH@test>select task_name from dba_advisor_log where owner='SH'; 

TASK_NAME
------------------------------
TEST_sql_tuning_task

SH@test>

4 执行

Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');

SH@test>Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');

PL/SQL procedure successfully completed.

SH@test>

5 查看执行后，task的状态

select status from dba_advisor_log where task_name='TEST_sql_tuning_task';

select status from dba_advisor_log where task_name='TEST_sql_tuning_task';
SH@test>select status from dba_advisor_log where task_name='TEST_sql_tuning_task';

STATUS
-----------
COMPLETED

SH@test>

6 查看建议 

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

End