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

文章由LinuxBoy分享于2019-03-30 05:03:19热评（356）

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

推荐文章：

Oracle调整顾问(SQL Tuning Advisor 与 SQL Access Adv

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