SQL Tuing Advisor(STA) 是Automatic Tuning Optimizer(自动优化调整器)的一部分。在前面的文章使用SQL tuning advisor(STA)自动优化SQL中描述了SQL Tuing Advisor(STA)的相关背景并给出示例。本文主要是描述STA底层到底为我们作了什么使得SQL语句得以优化，同时演示绑定变量的情形下接受sql profile后，后续SQL是否采纳对应的sql profile的执行计划的情形。最后给出了awr中的SQL通过STA tuning的脚本。
1、使用STA优化library cache中的SQL
--演示环境
hr@CNMMBO> select * from v$version where rownum<2;BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production--下面直接根据sql_id优化library cache中的SQL语句
hr@CNMMBO> @tune_cache_sql
Enter value for input_sql_id: 8rnmr2dpnjvk8
Enter value for input_task_name: hr_queryRECS
---------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : hr_query
Tuning Task Owner : HR
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 06/07/2013 11:40:27
Completed at : 06/07/2013 11:40:28
Number of SQL Profile Findings : 1
Number of SQL Restructure Findings: 1-------------------------------------------------------------------------------
Schema Name: HR
SQL ID : 8rnmr2dpnjvk8
SQL Text : SELECT /*+ ORDERED */*FROM employees e, locations l, departments dWHERE e.department_id = d.department_id AND l.location_id =d.location_id AND e.employee_id < :bnd-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------A potentially better execution plan was found for this statement.Recommendation (estimated benefit: 90.74%)------------------------------------------- Consider accepting the recommended SQL profile.execute dbms_sqltune.accept_sql_profile(task_name => 'hr_query', replace=> TRUE);2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------An expensive cartesian product operation was found at line ID 3 of theexecution plan.Recommendation--------------- Consider removing the "ORDERED" hint.Rationale---------The "ORDERED" hint might force the optimizer to generate a cartesianproduct. A cartesian product should be avoided whenever possible becauseit is an expensive operation and might produce a large amount of data.-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------1- Original With Adjusted Cost
------------------------------
Plan hash value: 3871948714-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 85 | 11645 | 103 (1)| 00:00:02 |
|* 1 | HASH JOIN | | 85 | 11645 | 103 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1973 | 225K| 99 (0)| 00:00:02 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 86 | 5848 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 86 | | 1 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 23 | 1127 | 96 (0)| 00:00:02 |
| 7 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND"L"."LOCATION_ID"="D"."LOCATION_ID")5 - access("E"."EMPLOYEE_ID"<:BND)2- Using SQL Profile
--------------------
Plan hash value: 2153960720--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 85 | 11645 | 10 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 85 | 11645 | 10 (20)| 00:00:01 |
| 2 | MERGE JOIN | | 27 | 1863 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_LOCATION_IX | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 23 | 1127 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 86 | 5848 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 86 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")5 - access("L"."LOCATION_ID"="D"."LOCATION_ID")filter("L"."LOCATION_ID"="D"."LOCATION_ID")8 - access("E"."EMPLOYEE_ID"<:BND)-------------------------------------------------------------------------------
-->上面的advisor report中得到了两个findings，一个是建议我们接受profile，一个建议我们移出ordered hint，因为它导致了笛卡尔集2、根据优化建议接受SQL profile
-->根据上面的advisor，接下来我们accept这个profile，唯一不同的增加了一个名字，如果不指定名字，系统会自动生成一个
hr@CNMMBO> execute dbms_sqltune.accept_sql_profile(task_name => 'hr_query',replace=> TRUE,name=>'hr_profile');PL/SQL procedure successfully completed.--所有生成的SQL profile的相关信息都存放在数据字典dba_sql_profiles中
hr@CNMMBO> select name,category,signature,sql_text,created,type,status,force_matching from dba_sql_profiles;NAME CATEGORY SIGNATURE SQL_TEXT CREATED TYPE STATUS FOR
------------ ---------- -------------------- ----------------------------------- ----------------- --------- -------- ---
hr_profile DEFAULT 8856746596263812636 SELECT /*+ ORDERED */ 20130607 14:27:33 MANUAL ENABLED NO*FROM employees e, locations l, departments dWHERE e.department_id = d.department_id AND l.location_id = d.location_id AND e.employee_id < :bnd------------------------------------------------------------------------------------------------------------
--下面是dba_sql_profiles数据字典中的列几个重要的说明
a、CATEGORY: Category of the SQL profile
-- 用于对SQL Profile实施分类管理，指明了当前的SQL profile属于哪一个分类，缺省情况下dafault类会被激活
-- 可以在接受SQL profile时指定分类，具体参考包dbms_sqltune.accept_sql_profile
-- 初始化参数sqltune_category用于控制系统和会话级别的category被激活，也就是说单个session仅仅支持单一的category激活
-- 如下查看当前设定的category及如何修改
-->查看系统的sqltune_category参数
hr@CNMMBO> show parameter sqltuneNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sqltune_category string DEFAULT-->通过下面的方式来修改缺省的catagory类别
alter session | system sqltune_category = category_name;-------------------------------------------------------------------------------------------------------------------
b、SIGNATURE: Unique identifier generated from normalized SQL text
-- 用于控制标准化SQL文本的到唯一签名ID并将该ID的profile的信息存储到数据字典
-- 在category激活以及profile被启用的情形下，后续的SQL语句运行时如果得到的签名ID与存储到数据字典的ID相同，则SQL profile被使用-------------------------------------------------------------------------------------------------------------------
c、TYPE: Type of the SQL profile (how it was created):MANUAL AUTO-TUNE
-- 该列表明对应的sql profile是自动还是手动产生的 -------------------------------------------------------------------------------------------------------------------
d、STATUS: Status of the SQL profile: ENABLED DISABLED VOID
-- 该列表明对于sql profile是否处于启用状态 -------------------------------------------------------------------------------------------------------------------
e、FORCE_MATCHING
-- If TRUE this causes SQL Profiles to target all SQL statements which have the same text after normalizing all literal values to bind variables.
-- (Note that if a combination of literal values and bind variables is used in the same SQL text, no transformation occurs).
-- This is analogous to the matching algorithm use by the FORCE option of the CURSOR_SHARING parameter.
-- If FALSE, literals are not transformed.
-- This is analogous to the matching algorithm used by the EXACT option of the CURSOR_SHARING parameter.--FORCE_MATCHING描述了SQL文本是模糊匹配还是精确匹配--为true时等同于CURSOR_SHARING参数的force，不区分空格，大小写及字面量。如果字面量与绑定变量混合则产生不同的签名ID--为false时等同于CURSOR_SHARING参数的exact，不区分空格和大小写--CURSOR_SHARING参数设置影响解析,字面量产生硬解析,绑定变量产生软解析(library cache中对应的sql存在时,可以参考Oracle硬解析与软解析--字面量与绑定变量select * from scott.emp where ename='SCOTT'; --SCOTT即是字面量select * from scott.emp where ename=:b1; --b1即是绑定变量--上面的查询结果为no，也就是说此时使用的是缺省值false.
--前面我们完成tuning后生成的report中提到了建议接受一个profile以及remove提示ordered
--那我们看看接受sql profile之后Oracle到底干了什么
--从下面的查询可知,sql profile告诉优化器忽略该SQL语句中的提示，这就是他做的事情
hr@CNMMBO> SELECT attr_val2 FROM sys.sqlprof$ p, sys.sqlprof$attr a3 WHERE p.sp_name = 'hr_profile' AND p.signature = a.signature AND p.category = a.category;ATTR_VAL
--------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
3、测试接受SQL profile后的情形
--对于OLTP的情形，大部分SQL语句使用了绑定变量，下面来测试一下绑定变量是否影响sql profile的运用
--以及测试SQL语句在使用大小写，添加或移除空格的情形时sql profile是否被使用到
hr@CNMMBO> variable bnd number; --定义绑定变量并赋值
hr@CNMMBO> exec :bnd:=166 PL/SQL procedure successfully completed.hr@CNMMBO> set autot trace exp;
hr@CNMMBO> SELECT /*+ ORDERED */2 *3 FROM employees e, locations l, departments d4 WHERE e.department_id = d.department_id AND l.location_id =5 d.location_id AND e.employee_id < :bnd;Execution Plan
----------------------------------------------------------
Plan hash value: 685643925------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 685 | 9 (23)| 00:00:01 |
|* 1 | HASH JOIN | | 5 | 685 | 9 (23)| 00:00:01 |
| 2 | MERGE JOIN | | 5 | 440 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 5 | 340 | 3 (34)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 340 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 2 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("L"."LOCATION_ID"="D"."LOCATION_ID")5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")7 - access("E"."EMPLOYEE_ID"<TO_NUMBER(:BND))Note
------ SQL profile "hr_profile" used for this statement --->这个提示表明sql profile已经被使用 --下面我们给绑定变量重新赋值，以及使用小写来替换原来的SQL语句，并且移除一些空格，看看profile是否依旧会被使用
hr@CNMMBO> exec :bnd:=133;PL/SQL procedure successfully completed.hr@CNMMBO> select /*+ ORDERED */ * from employees e, locations l, departments d2 where e.department_id = d.department_id AND l.location_id = d.location_id3 and e.employee_id < :bnd;Execution Plan
----------------------------------------------------------
Plan hash value: 685643925------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 685 | 9 (23)| 00:00:01 |
|* 1 | HASH JOIN | | 5 | 685 | 9 (23)| 00:00:01 |
| 2 | MERGE JOIN | | 5 | 440 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 5 | 340 | 3 (34)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 340 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 2 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access("L"."LOCATION_ID"="D"."LOCATION_ID")5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")7 - access("E"."EMPLOYEE_ID"<TO_NUMBER(:BND))Note
------ SQL profile "hr_profile" used for this statement --->这个提示表明sql profile已经被使用 --从上面的测试可知，大小写差异，空格多少以及绑定变量对上面的SQL语句并没有影响，之前的profile依旧有效
4、对比优化前后的I/O 开销
a、先测试优化后的I/O cost
hr@CNMMBO> set autot trace stat;
hr@CNMMBO> select /*+ ORDERED */ * from employees e, locations l, departments d2 where e.department_id = d.department_id AND l.location_id = d.location_id3 and e.employee_id < :bnd;33 rows selected.Statistics
----------------------------------------------------------0 recursive calls0 db block gets14 consistent gets10 physical reads0 redo size5362 bytes sent via SQL*Net to client514 bytes received via SQL*Net from client4 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)33 rows processed--上面的Statistics表明优化后的consistent gets为14，physical reads为10--将sql profile disable，则该profile不会生效
hr@CNMMBO> set autot off;
hr@CNMMBO> BEGIN2 DBMS_SQLTUNE.alter_sql_profile (name => 'hr_profile', attribute_name => 'STATUS', VALUE => 'DISABLED');3 END;4 /PL/SQL procedure successfully completed. --Author : Robinson
--Blog : http://blog.csdn.net/robinson_0612-->查看disable后的状态
hr@CNMMBO> select name,status from dba_sql_profiles where name='hr_profile';NAME STATUS
------------------------------ --------
hr_profile DISABLEDhr@CNMMBO> print bnd;BND
----------133--下面来查看disable后的统计信息，SQL语句会回到优化之前
hr@CNMMBO> set autot trace stat;
hr@CNMMBO> select /*+ ORDERED */ * from employees e, locations l, departments d2 where e.department_id = d.department_id AND l.location_id = d.location_id3 and e.employee_id < :bnd;33 rows selected.Statistics
----------------------------------------------------------9 recursive calls0 db block gets27 consistent gets5 physical reads0 redo size5403 bytes sent via SQL*Net to client514 bytes received via SQL*Net from client4 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)33 rows processed--从上面的统计信息可知，SQL优化之前的consistent gets为27，大于优化之后的consistent gets
--注，此处未对比physical reads,一般情形下我们考虑的是consistent gets，如果需要两个都对比，应每次执行SQL前flush buffer cache-->接下来可以移除sql profile
hr@CNMMBO> set autot off;
hr@CNMMBO> exec dbms_sqltune.drop_sql_profile('hr_profile');PL/SQL procedure successfully completed. 5、使用STA优化awr中SQL的脚本
robin@SZDB:~/dba_scripts/custom/sql> more tune_awr_sql.sql
SET ECHO OFF TERMOUT ON FEEDBACK OFF VERIFY OFF
SET SCAN ON PAGESIZE 9999
SET LONG 1000000 LINESIZE 180
COL recs FORMAT a145
--Author : Robinson
--Blog : http://blog.csdn.net/robinson_0612VARIABLE tuning_task VARCHAR2(30)DECLAREl_sql_id v$session.prev_sql_id%TYPE:='&input_sql_id';
BEGIN:tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id,begin_snap=>&input_begin_snap,end_snap=>&input_end_snap,task_name=>'&input_task_name');dbms_sqltune.execute_tuning_task(:tuning_task);
END;
/SELECT dbms_sqltune.report_tuning_task(:tuning_task) as recs
FROM dual;SET VERIFY ON FEEDBACK ON
6、小结
a、SQL优化器在tuning模式下不会对原始SQL语句文本做任何修改。
b、对于使用STA优化的SQL语句，SQL优化器会转换优化器的模式或移除添加hint(如本例是忽略hint)以实现优化。
c、SQL profile会纠正查询优化器对基数的错误评估。
d、对于统计信息缺失或过时或根本不存在统计信息的情形，SQL优化器会给出建议以获取真实的统计信息。
e、SQL profile能够被导入导出。导出之前，该profile必须先被accept。
f、SQL profile所依赖的对象被删除后，SQL profile并不会被删除。但如果使用drop user username cascade时是例外。
更多参考
DML Error Logging 特性
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划