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