从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.