调用dbms_sqltune 调用包调优SQL

在日常运维中，碰到不容易看出问题的SQL，可以调用系统自带的包进行调优尝试：

1.创建自动调优任务：

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
select sql_fulltext into my_sqltext from v$sql where sql_id='************' and rownum=1;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext1,
user_name => 'wusx', 
scope => 'COMPREHENSIVE',
time_limit => 360,
task_name => 't1');
END;
/

2.执行自动调优任务：
exec dbms_sqltune.execute_tuning_task('t1');
3.检查调优任务状态：
SELECT task_name,status
FROM USER_ADVISOR_TASKS
WHERE task_name ='tuning_sql_test';
4.显示自动调优任务报告：
SET LONG 999999
SETserveroutput on size 999999
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 't1') from DUAL;
5.删除自动调优任务：
exec dbms_sqltune.drop_tuning_task('t1');