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

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

----