SQL Tuning Advisor(STA)到底做了什么
本文主要是描述STA底层到底为我们作了什么使得SQL语句得以优化，同时演示绑定变量的情形下接受sql profile后，后续SQL是否采纳对应的sql profile的执行计划的情形。最后给出了awr中的SQL通过STA tuning的脚本。
1、使用STA优化library cache中的SQL
[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 | Row