查看执行计划的常用办法（一）

看执行计划的方法很多，例如有set autotrace、dbms_xplan、10046、10053等，而最真实的反应执行计划的只有10046 event，这个后面小鱼会列举出部分的案例来反应这个情况，这里我们主要针对上述几个查看执行计划的方法进行一点剖析 1 set autotrace Set autotr

看执行计划的方法很多，例如有set autotrace、dbms_xplan、10046、10053等，而最真实的反应执行计划的只有10046 event，这个后面小鱼会列举出部分的案例来反应这个情况，这里我们主要针对上述几个查看执行计划的方法进行一点剖析

1 set autotrace
Set autotrace这个可以查看执行计划和执行sql消耗的统计信息。
SQL> set autotrace traceoly
SQL> select * from t01 where object_id between 19900 and 20000;

101 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 253365841

--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 99 | 9207 | 4 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T01 | 99 | 9207 | 4 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T01_ID | 99 | | 2 (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">=19900 AND "OBJECT_ID"

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
6498 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
101 rows processed

除了显示执行计划外还显示了sql语句执行过程消耗的资源，具体的autotrace使用办法都是比较简单，可以参考官档的详细说明，这个就不再叙述。

Autotrace 其实转化为了两个sql语句，其中一个是这个session的执行sql语句，另一个是explain plan for statement_id=’’ for 原sql语句，我们来进行验证下
Session A：
SQL> alter system flush shared_pool;

System altered.

SQL> select * from t01;

50079 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50079 | 4548K| 159 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T01 | 50079 | 4548K| 159 (2)| 00:00:02 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1229 recursive calls
0 db block gets
4183 consistent gets
0 physical reads
0 redo size
2569820 bytes sent via SQL*Net to client
37210 bytes received via SQL*Net from client
3340 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
50079 rows processed

Session B：
SQL> select sql_id,sql_text from v$sql where sql_text like '%select * from t01%'
and sql_text not like '%v$sql%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------

5ts7a9dr622qv
select * from t01

ft6jyj0gxkkvs
EXPLAIN PLAN SET STATEMENT_ID='PLUS190008' FOR select * from t01

还有一个需要注意的地方是 set autotrace traceonly exp下，select的sql是不会执行的，只是进行解析，而如果是dml的sql语句，则除了解析还是实际执行，请看下面的示例：
SQL> alter system flush shared_pool;
SQL> set autotrace traceonly exp
SQL> select * from t01;

Execution Plan
----------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50079 | 4548K| 159 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T01 | 50079 | 4548K| 159 (2)| 00:00:02 |
--------------------------------------------------------------------------

查看v$sql中发现这个sql语句只是进行解析，并没有实际执行
SQL> select sql_id,sql_text,parse_calls,executions,fetches from v$sql where sql_
text like 'select * from t01%';

SQL_ID SQL_TEXT PARSE_CALLS EXECUTIONS FETCHES
------------- -------------------- ----------- ---------- ----------
5ts7a9dr622qv select * from t01 1 0 0

而如果是dml语句，则不仅会解析这个sql语句还会执行：
SQL> set autotrace traceonly exp
SQL> delete from t01 where rownum

9 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3885504243

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 9 | 63 | 158 (1)| 00:00:02 |
| 1 | DELETE | T01 | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T01 | 50079 | 342K| 158 (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM SQL> select sql_id,sql_text,parse_calls,executions,fetches from v$sql where sql_
text like 'delete from t01 where rownum

SQL_ID SQL_TEXT PARSE_CALLS EXECUTIONS FETCHES
------------- -------------------- ----------- ---------- ----------
62cp99p4bzkst delete from t01 wher 1 1 0
e rownum

2 dbms_xplan
Dbms_xplan包提供了一系列查看执行计划的函数，官档有一个大体的介绍：
The DBMS_XPLAN package supplies five table functions:
? DISPLAY - to format and display the contents of a plan table.
? DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
? DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
? DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
? DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
Display主要用于显示cbo预估的执行计划，此时这个sql不需要真正执行
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);

table_name默认是PLAN_TABLE，这个PLAN_TABLE其实很有意思，我们来对其进行简单的分析

SQL> select object_type from dba_objects where object_name='PLAN_TABLE';

OBJECT_TYPE
-------------------
SYNONYM
SQL> select table_name,owner from dba_synonyms where synonym_name='PLAN_TABLE';

TABLE_NAME OWNER
------------------------------ ------------------------------
PLAN_TABLE$ PUBLIC

PLAN_TABLE其实是PLAN_TABLE$的public synonym，那么来看看PLAN_TABLE$的定义
SQL> select dbms_metadata.get_ddl('TABLE','PLAN_TABLE$','SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','PLAN_TABLE$','SYS')
--------------------------------------------------------------------------------

CREATE GLOBAL TEMPORARY TABLE "SYS"."PLAN_TABLE$"
( "STATEMENT_ID" VARCHAR2(30),
"PLAN_ID" NUMBER,
"TIMESTAMP" DATE,
"REMARKS" VARCHAR2(4000),
"OPERATION" VARCHAR2(30),
"OPTIONS" VARCHAR2(255),
"OBJECT_NODE" VARCHAR2(128),
"OBJECT_OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"OBJECT_ALIAS" VARCHAR2(65),
"OBJECT_INSTANCE" NUMBER(*,0),
"OBJECT_TYPE" VARCHAR2(30),
"OPTIMIZER" VARCHAR2(255),
"SEARCH_C OLUMNS" NUMBER,
"ID" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
"DEPTH" NUMBER(*,0),
"POSITION" NUMBER(*,0),
"COST" NUMBER(*,0),
"CARDINALITY" NUMBER(*,0),
"BYTES" NUMBER(*,0),
"OTHER_TAG" VARCHAR2(255),
"PARTITION_START" VARCHAR2(255),
"PARTITION_STOP" VARCHAR2(255),
"PARTITION_ID" NUMBER(*,0),
"OTHER" LONG,
"OTHER_XML" CLOB,
"DISTRIBUTION" VARCHAR2(30),
"CPU_COST" NUMBER(*,0),
"IO_COST" NUMBER(*,0),
"TEMP_SPACE" NUMBER(*,0),
"ACCESS_PREDICATES" VARCHAR2(4000),
"FILTER_PREDICATES" VARCHAR2(4000),
"PROJECTION" VARCHAR2(4000),
"TIME" NUMBER(*,0),
"QBLOCK_NAME" VARCHAR2(30)
) ON COMMIT PRESERVE ROWS

这个看出plan_table$是个基于session级别的global temporary table，这里我们需要注意的是为什么我们很多会话查询plan_table确没有出现冲突了，其实oracle是这么处理的：
首先建立了plan_table$的session 级别的global temporary table了，然后创建一个public synonym的plan_table指向了临时表plan_table$，最后授予dml和select等权限给这个public synonym plan_table，也就实现了不同用户可以查询同一个表但是数据又不冲突

oracle的$ORACLE_HOME/rdbms/admin/catplan.sql的脚本，该脚本是用来创建临时表plan_table$的，截取部分sql语句如下：
drop table plan_table$;
create global temporary table plan_table$
(
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
other_xml clob,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30)
) on commit preserve rows
/
--注意这里需要授权给public同义词，不然别的用户无法做dml操作。
grant select, insert, update, delete on plan_table$ to public
/
create or replace public synonym plan_table for plan_table$
/

小鱼个人觉得这个办法很多应用程序都可以参考使用来实现数据隔离，$ORACLE_HOME/rdbms/admin下的很多脚本都值得细细摸索。

Statement_id为特定需要展示的执行计划的sql语句，默认为null，null表示展示最近执行explain的语句对应的执行计划

Format为展示执行计划的几种方式，oracle官档给出有下列四种：
Basic：displays the minimum information in the plan-the operation ID,the operation name and its option.
Typical : this is the default.Displays the most relevant information in the plan(operation id,name and option,#row,#bytes and optimizer cost).Pruning,parallel and prediace information are only display when applicable.Excludes only PROJECTION,ALIAS and REMOTE SQL information(see below)
SERIAL : Like TYPICAL except that the parallel information is not displayed,event if the plan executes in parallel.
ALL : Maximum user level. Includes information displayed with the TYPICAL level with addition information(PROJECTION,ALIAS and information about REMOTE SQL if the operation is distributed).

其实还有一个小鱼个人经常喜欢用advanced，下面展示下如何用dbms_xplan.display去查看cbo预估的执行计划

SQL> explain plan for select * from t01 where object_name='IND$';

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 158 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T01 | 8 | 1416 | 158 (1)| 00:00:02 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / T01@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T01"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='IND$')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "T01"."OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,128],
"T01"."SUBOBJECT_NAME"[VARCHAR2,30], "T01"."OBJECT_ID"[NUMBER,22],
"T01"."DATA_OBJECT_ID"[NUMBER,22], "T01"."OBJECT_TYPE"[VARCHAR2,19],
"T01"."CREATED"[DATE,7], "T01"."LAST_DDL_TIME"[DATE,7],
"T01"."TIMESTAMP"[VARCHAR2,19], "T01"."STATUS"[VARCHAR2,7],
"T01"."TEMPORARY"[VARCHAR2,1], "T01"."GENERATED"[VARCHAR2,1],
"T01"."SECONDARY"[VARCHAR2,1]

Note
-----
- dynamic sampling used for this statement

46 rows selected.

Advanced除了显示了默认参数typical的执行计划和谓词过滤和note信息（如果有动态采样、sql profile、cardinalityfeedback还会有个note的信息），还会显示Query Block Name/object alias、outline data和column projection information。

Advanced展示了outline data，这个可以帮助我们展示一些复杂sql的hint写法。

最近case是因为生产环境10GR2有个列的直方图信息总是被删除导致sql执行计划总是出现了问题，而何种原因造成这个直方图信息被删除一直很难查找，小鱼利用这个advanced展示了这个复杂sql的outline data，这个outline data其实可以也可以当做一个hint的写法，特别对于复杂sql的hint写法基本基本很难完成，当然固化执行计划的方法很多，后面小鱼会专门整理一篇文章来进行分析

下面来看看dbms_xplan.display_awr
Dbms_xplan.display_awr是用来显示保存在awr报告中的执行计划，跟dbms_xplan.display不同点是这个sql是执行过的，并且被保存在awr中。

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

sql_id是sql语句的sql_id
plan_hash_value是sql_id相关联的plan_hash_value，如果不指定默认为null，此时展示的这个sql_id对应的所有执行计划
db_id是database db_id
format和dbms_xplan.display中的含义一样，不再详述

需要注意的是同一个sql_id可能有多个执行计划，比如一个父游标可能有多个子游标，一个子游标对应于一个执行计划，而这个执行计划（也就是plan_hash_value）可能相同，也可能不相同，我们往往对比sql_id都是直接查看的sql文本，也就是所说的父游标，父游标是不存储执行计划的，而执行计划则存储在子游标中heap 6中。

业界有个描述父游标、子游标和执行计划的关系：
HASH VALUE101-----------> 父游标Handle
|
Heap0
---------------------------------------------------
| |
子游标1 Handle 子游标2 Handle ………………
|
子游标Heap 0 ----->子游标Heap 6（也就是存储执行计划的地方）

HASH VALUE102 ------->…………

HASH VALUE103 ------->…………
………………………
先来张草图随便看看吧，简单描述一下，父游标Handle中有Heap 0地址，父游标的Heap 0中有它的所有子游标的Handle ， 每个子游标的Handle中，又有子游标各自Heap 0的地址，Heap 0中有Heap 6地址，Heap 6就是执行计划。每个Handle、Heap处，都会有Library cache lock/pin 和相关Latch，或者Mutex。而且在硬、软、软软解析时，这些地方的这些锁、Pin、Latch、Mutex都不一样，所以这一块是相当复杂的。

当采样awr报表中发现某个sql语句对系统影响较大时，而现在的shared pool中可能该子游标又已经被踢出shared pool时，此时可以用dbms_xplan.display_awr来查看awr中曾经的执行计划来进行分析
具体使用方法比较简单，不再列出。

继续看看dbms_xplan.display_cursor
Dbms_xplan.display_cursor展示的是存储在shared pool中的执行游标的执行计划。
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');

这里有个新的参数是child_number，这个表示的子游标的编号，上面小鱼就说过一个父游标可能对应多个子游标，子游标存储具体的执行计划，子游标都有其各自的child_number，这个可以查询v$sql视图来看看具体的某个父游标sql_id有多少个child_number

Sql_id如果不声明，默认展示最近的sql执行计划，child_number默认为0，设置null则展示该sql_id所有的子游标执行计划。

Format跟上面两个函数有一些变化，多了iostats、memstats、allstats参数
SQL> select /*+gather_plan_statistics*/* from t01 where object_id=1;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats
last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 9fxrn9aygh837, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from t01 where object_id=1

Plan hash value: 253365841

--------------------------------------------------------------------------------
----------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cos
t (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------
----------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T01 | 1 | 1 | 93 |
2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | IND_T01_ID | 1 | 1 | |
1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------
----------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / T01@SEL$1
2 - SEL$1 / T01@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T01"@"SEL$1" ("T01"."OBJECT_ID"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "T01"."OWNER"[VARCHAR2,30], "T01"."OBJECT_NAME"[VARCHAR2,128], "T01"."SUB
OBJECT_NAME"[VARCHAR2,30],
"OBJECT_ID"[NUMBER,22], "T01"."DATA_OBJECT_ID"[NUMBER,22], "T01"."OBJECT_
TYPE"[VARCHAR2,19], "T01"."CREATED"[DATE,7],
"T01"."LAST_DDL_TIME"[DATE,7], "T01"."TIMESTAMP"[VARCHAR2,19], "T01"."STA
TUS"[VARCHAR2,7], "T01"."TEMPORARY"[VARCHAR2,1],
"T01"."GENERATED"[VARCHAR2,1], "T01"."SECONDARY"[VARCHAR2,1]
2 - "T01".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

46 rows selected.

Iostats会显示sql语句中IO统计信息，要显示IO统计信息则需要设置statistics_level=all或者sql语句中增加gather_plan_statistics的hint，否则会出现上述的警告信息
Memstats会显示自动管理pga下消耗的内存资源
Allstats同时包含iostats和memstats信息
Last表示最后一次执行的统计信息，如果不加默认会显示所有执行次数的统计信息。

我们观察上面allstats last参数对应的执行计划，发觉执行计划中有一些改变：
Starts表示该执行步骤的循环次数（如果只看单次执行sql统计信息，这个次数一般都是1，但是对于nested loop，这个可能会变化）
E-Rows和E-Bytes分别表示执行cbo估算返回的rows和bytes
A-Rows和A-Bytes则分别表示实际执行sql语句时返回的rows和bytes
E-Time和A-Time则分别表示cbo估算执行该步骤执行计划消耗的时间和实际执行时消耗的时间
Buffer则表示该执行计划产生的逻辑读

可以对sql加上了hint gather_plan_statistics或者typical_statistics设置为all，结合dbms_xplan.display_cursor来抓取实际数据返回的行数和cbo估算返回的行数、观察哪一步执行计划最消耗时间等。

优化器统计信息不准造成cbo错误的估算了rows而选择了偏差的执行计划案例已经很多了，可以比较e_rows*start是否和a_rows有较大的偏差，buffer/a_rows读取每行平均消耗的逻辑读，对这些信息有一定了解后可以方便我们优化sql。

比如可以用hint gather_plan_statistics获得sql语句实际某个执行计划返回的cardinality后，直接加上hint cardinality来让cbo选择更合理的执行计划，不过由于业务数据一直在变化，这种加上cardinality hint的优化办法实用性并不高，更多是用于测试。

比如下列cardinality hint示例：
SQL> explain plan for select /*+gather_plan_statistics cardinality(t01 1)*/* fro
m t01 where object_id between 100 and 20000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 158 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T01 | 1 | 93 | 158 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=100)

13 rows selected.

这里重点还是要对e_rows和a_rows来分析，看看何种原因造成优化器估算返回的rows和实际返回rows有较大的偏差。

再来看看dbms_xplan.display_sqlset函数：
Dbms_xplan.display_sqlset display the execution plan of a given statement stored in a SQL tuning set.
DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := 'TYPICAL',
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

Dbms_xplan.display_sqlset函数用于显示存储在sql tuning中的执行计划

Sqlset_name和sql_id都没有缺省值
plan_hash_value默认为null，为显示其所有的执行计划
format跟上面的相同
sql_set_owner默认为当前用户下的sql tunning的执行计划

这里小鱼先花点时间补充下oracle 10g推出的sql tuning advisor(STA)
12.2 SQL Tuning Advisor
The Automatic SQL Tuning capabilities are exposed through a server utility called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile. A user can choose to accept the recommendation to complete the tuning of the SQL statements.
Sql tuning advisor会根据sql语句来自动给出相应的优化建议，比如包括创建索引、统计信息，改写sql或者创建sql profile，用户可以接受这个建议来对sql语句进行优化。
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a multi-step process:
1. Create a SQL Tuning Set (if tuning multiple SQL statements)
2. Create a SQL tuning task
3. Execute a SQL tuning task
4. Display the results of a SQL tuning task
5. Implement recommendations as appropriate

有一个生产环境的sql执行计划异常，造成了显著的cache buffer chains latch等待，我们拿到这个sql ID来用sql tuning advisor来进行优化：

DECLARE
my_task_name VARCHAR2 (100);
BEGIN
my_task_name :=
DBMS_SQLTUNE.create_tuning_task (sql_id => '2x138d7fx7sbv',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task');
END;
/

execute dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task')

select dbms_sqltune.report_tuning_task(task_name=>’my_sql_tuning_task’) from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task
Tuning Task Owner : TEST
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : INTERRUPTED
Started at : 05/22/2014 14:20:34
Completed at : 05/22/2014 14:21:38

-------------------------------------------------------------------------------
Error: ORA-13639: The current operation was interrupted because it timed out.
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Schema Name: GH_CC
SQL ID : 2x138d7fx7sbv
SQL Text : select count(*) as listCount from
gh_cc.light_workflow_abstractticket tbl1 where
tbl1.sub_customer_no||tbl1.sub_service_name||tbl1.sub_address
in(select l.callnumber callnumber from (select
t.sub_customer_no||t.sub_service_name||t.sub_address
callnumber,count(t.sub_customer_no||t.sub_service_name||t.sub_add
ress) count1, 0 count2 from gh_cc.light_workflow_abstractticket
t where t.control_state
'2'and t.sub_service_name like '%报修%'
and t.sub_section_id = '19' and t.sub_service_id =
'szywsxshbxfgs' and t.create_time between '2014-05-01 14:04:07'
and '2014-05-22 00:00:00'group by
t.sub_customer_no||t.sub_service_name||t.sub_address) l group by
l.callnumber having sum(l.count1 + l.count2)>=2) and
tbl1.control_state
'2'and tbl1.sub_service_name like '%报修%'and
tbl1.sub_section_id = '19' and tbl1.sub_service_id =
'szywsxshbxfgs' and tbl1.create_time

原文地址：查看执行计划的常用办法（一）, 感谢原作者分享。