Sql Tuning Advisor

从oracle 10g开始，oracle推出了自己的SQL优化工具，即使用DBMS_SQLTUNE包。执行DBMS_SQLTUNE包需要授权advisor。

创建任务

declare
 task_name varchar2(300);
 tune_sqltext CLOB;
begin
        tune_sqltext := 'SQL文本';
        task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
            sql_text    => sqltext,
            user_name   => 'SYS',
            scope       => 'COMPREHENSIVE',
            time_limit  => 60,
            task_name   => 'tuning_sql',
            description => 'Task to tune a query on a specified table');
            DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql');
 END;
/

执行任务

SQL>exec dbms_sqltune.execute_tuning_task('tuning_sql');

PL/SQL procedure successfully completed.

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql';

TASK_NAME                      STATUS
------------------------------ -----------
tuning_sql                COMPLETED

SQL> SET LONG 999999
SQL> set serveroutput on size 999999
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql') from DUAL;

删除任务

SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql');
PL/SQL procedure successfully completed.