Oracle的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_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 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，因为它导致了笛卡尔集

dbca删除数据库时选项灰色MySQL delete 千万数据操作相关资讯 SQL Tuning Advisor

Oracle调整顾问（SQL Tuning （12/01/2013 20:00:50）
SQL Tuning Advisor使用实例（02/20/2013 10:08:20）

使用SQL tuning advisor（STA）自动（05/31/2013 07:00:20）

本文评论查看全部评论（0）

首页 / 数据库 / MySQL / Oracle的SQL Tuning Advisor（STA） 到底做了什么？

首页 / 数据库 / MySQL / Oracle的SQL Tuning Advisor（STA）到底做了什么？