使用DBMS_SQLTUNE优化SQL

--创建优化任务

DECLARE

my_task_name VARCHAR2 (30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from test_tune t where t.owner=''SCOTT''';
my_task_name := dbms_sqltune.create_tuning_task (
sql_text=> my_sqltext,
user_name => 'LEE',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name =>'sql_tuning_test',
description => 'Tuning Task'
);
END;

/

--执行优化任务

exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

--查看任务状态

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_test';

--查看优化结果
SET LONG 999999
SET SERVEROUTPUT ON SIZE 999999

SET LINESIZE 100

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;

--删除优化任务
exec dbms_sqltune.drop_tuning_task('sql_tuning_test');