SQL Tuning Advisor（根据sql_id推荐优化）

附注:可改进部分（使用SQL Tuning Advisor可减少分析时间，较快出结果==或者使用OraGlance【群商业软件】工具）：

 

1、执行以下sql，需要知道sql_id

DECLARE
my_task_name VARCHAR2(30);
sql_id VARCHAR2(30);
BEGIN
sql_id := '&sqlid';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => sql_id,
scope => 'comprehensive',
time_limit => 60,
task_name => 'my_sql_tuning_' ||
sql_id,
description => 'Tuning Task');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_' || sql_id);
END;
 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_&sqlid') FROM DUAL;
--BEGIN dbms_sqltune.drop_tuning_task('my_sql_tuning_btdjzt659wtmk'); END;
BEGIN dbms_sqltune.drop_tuning_task('my_sql_tuning_&sqlid'); END;

2、第一条sql生产库执行了7分钟，补snap_id(待体验时间对比)

DECLARE
my_task_name VARCHAR2(30);
sql_id VARCHAR2(30);
BEGIN
sql_id := '&sqlid';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => sql_id,
begin_snap      => 8389,
end_snap        => 8390,
scope => 'comprehensive',
time_limit => 60,
task_name => 'my_sql_tuning_' ||
sql_id,
description => 'Tuning Task');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_' || sql_id);
END;
 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_&sqlid') FROM DUAL;
--BEGIN dbms_sqltune.drop_tuning_task('my_sql_tuning_btdjzt659wtmk'); END;
BEGIN dbms_sqltune.drop_tuning_task('my_sql_tuning_&sqlid'); END;