SQL Tuning Advisor(STA) 到底做了什么?

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_query

RECS
---------------------------------------------------------------------------------------
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 d
              WHERE 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 the
  execution plan.

  Recommendation
  --------------
  - Consider removing the "ORDERED" hint.

  Rationale
  ---------
    The "ORDERED" hint might force the optimizer to generate a cartesian
    product. A cartesian product should be avoided whenever possible because
    it 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, de
                                             partments d
                                              WHERE e.department_id = d.departme
                                             nt_id AND l.location_id = d.locatio
                                             n_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 sqltune

NAME                                 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_val
  2    FROM sys.sqlprof$ p, sys.sqlprof$attr a
  3   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 d
  4  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 d
  2  where e.department_id = d.department_id AND l.location_id = d.location_id
  3  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 d
  2  where e.department_id = d.department_id AND l.location_id = d.location_id
  3  and e.employee_id < :bnd;

33 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
         10  physical reads
          0  redo size
       5362  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  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> BEGIN
  2     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                     DISABLED

hr@CNMMBO> print bnd;

       BND
----------
       133

--下面来查看disable后的统计信息，SQL语句会回到优化之前       
hr@CNMMBO> set autot trace stat;
hr@CNMMBO> select /*+ ORDERED */ * from employees e, locations l, departments d
  2  where e.department_id = d.department_id AND l.location_id = d.location_id
  3  and e.employee_id < :bnd;

33 rows selected.

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         27  consistent gets
          5  physical reads
          0  redo size
       5403  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  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_0612

VARIABLE tuning_task VARCHAR2(30)

DECLARE
  l_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时是例外。