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

SQL Tuing Advisor(STA) 是Automatic Tuning Optimizer(自动优化调整器)的一部分。在前面的文章使用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

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

-------------------------------------------------------------------------------
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):
---------------------------------------------------