Use the SQL Tuning Advisor

SQL Tuning Advisor使用方法
1.使用DBMS_SQLTUNE.CREATE_TUNING_TASK创建一个调优任务，用于一个或一组SQL语句。
2.使用DBMS_SQLTUNE.EXECUTE_TUNING_TASK执行创建好的任务
3.使用DBMS_SQLTUNE.REPORT_TUNING_TASK函数，生成调优任务的结果。
4.使用DBMS_SQLTUNE.SCRIPT_TUNING_TASK函数，根据Advisor的推荐，创建可执行的sql脚本。
 
dbms_sqltune.create_tuning_task有四种使用方式：
1.通过SQL语句文本创建一个单条语句的调优任务
2.通过在游标缓存中的标记，例如sql_id，hash_value，创建一个单条语句当前的调优任务
3.通过sql_id、hash_value和AWR快照范围内，创建一个单条语句的调优任务
4.创建一个用于SQL tuning set的调优任务，可以包含多条SQL
 

使用SQL Tuning Set的方法：
1.使用DBMS_SQLTUNE.CREATE_SQLSET创建一个SQL tuning set对象
2.使用DBMS_SQLTUNE.LOAD_SQLSET过程，挑选SQL语句加入这个SQL tuning set
3.使用DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET过程，根据间隔时间从游标缓存中收集一段时间内的SQL语句信息。
 

一、使用SQL Tuning Advisor对一条SQL语句进行优化
 
1.创建一条SQL语句的优化任务，可以用以下3种方式创建
a).
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '5nybpc80mdc3d',
time_limit => 600,
task_name => 'tuning_task_sqlid',
description => 'Tuning task for statement 5nybpc80mdc3d');
END;
/
 
b).
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 1,
end_snap => 2,
sql_id => '5nybpc80mdc3d',
task_name => 'tuning_task_snap',
description => 'Tuning task for statement 5nybpc80mdc3d in snap');
END;
/
 
c).
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext:='select * from PART_TAB1 where name =''V$LOGFILE''';
my_task_name:=dbms_sqltune.create_tuning_task(
sql_text => my_sqltext,
user_name => 'TEST',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tuning_task_text',
description => 'Tuning task for statement text');
END;
/
 
2.执行优化任务：
task创建好后可以在视图中查到：
select * from DBA_ADVISOR_TASKS where owner='TEST';
 
执行这个task：
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_task_sqlid');
end;
/
 

3.查看优化报告：
查看该sql tuning task的报告，找到了更好的执行计划：
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_task_sqlid') REPORT from DUAL;
 
报告中显示，建议在PART_TAB1表的NAME字段上建立索引，并在报告中有建立索引之后的执行计划的对比。
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。
 
  Recommendation (estimated benefit: 98.24%)
  ------------------------------------------
  - 考虑运行可以改进物理方案设计的 Access Advisor 或者创建推荐的索引。
    create index TEST.IDX$$_01D90001 on TEST.PART_TAB1("NAME");
 
  Rationale
  ---------
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "Access Advisor"
    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。
 
4.生成脚本
报告中已经包含了该脚本内容，如果要生成脚本文件可以使用以下方式查询，spool到文件中
select DBMS_SQLTUNE.SCRIPT_TUNING_TASK('tuning_task_sqlid') SCRIPT from DUAL;

二、使用SQL Tuning Advisor，结合SQL Tuning Set对多条语句进行优化
 
1.创建一个SQLSET
 
begin
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'my_sqlnet1',
description => 'complete application workload');
end;
/
 
2.将游标缓存中的SQL语句添加到sqlnet中，该步骤可以省略不操作
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM
table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'sql_id in (''9qw5mc9j9sjxu'',''5nybpc80mdc3d'')')
) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_sqlnet1',
populate_cursor => cur);
END;
/
 

3.从游标内存收集SQL语句内容
本例为每5分钟，共30分钟
建议该过程用job执行，否则需要在前台运行完设置的时间才停止。
 
BEGIN
dbms_sqltune.capture_cursor_cache_sqlset(
sqlset_name=>'my_sqlnet1',
time_limit=>'100',
repeat_interval=>'10',
sqlset_owner=>'TEST');
END;
/
 
4.使用这个sqlset，创建一个tuning task
DECLARE
l_sql_tune_task_id  VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'my_sqlnet1',
scope       => DBMS_SQLTUNE.scope_comprehensive,
time_limit  => 60,
task_name   => 'tuning_task_sqlset',
description => 'Tuning task for an SQL tuning set1.',
sqlset_owner => 'TEST');
END;
/
 
5.执行优化任务：
执行这个task：
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_task_sqlset');
end;
/
 
6.查看报告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_task_sqlset') REPORT from DUAL;
 
看到报告中包含关于SQL写法的优化建议：
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  优化程序不能取消执行计划的行 ID 1 处的子查询的嵌套。
 
  Recommendation
  --------------
  - 考虑用 "NOT EXISTS" 代替 "NOT IN", 或者确保已通过添加 "NOT NULL" 约束条件或 "IS NOT NULL"
    谓词将 "NOT IN" 运算符的两边所使用的列声明为 "NOT NULL"。
 
  Rationale
  ---------
    "FILTER" 操作的开销非常大, 因为它为父查询中的每一行计算子查询。子查询在非嵌套的情况下可以大大缩短执行时间, 因为 "FILTER"
    操作已转换为联接。要注意的是 "NOT IN" 和 "NOT EXISTS" 可能会对 "NULL" 值产生不同的结果。