通过dbms_xplan.display_cursor识别低效的执行计划
dbms_xplan.display_cursor定义：
function display_cursor(sql_id varchar2 default null,
cursor_child_no integer default 0,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table
pipelined;
识别问题sql我们可以通过以下几种方式获得：
1. AWR
2. ASH
3. 根据当时占用CPU高的进程查找对应的sql
问题sql找到了，接着我们要看sql的执行计划，但问题是如果快速找到执行计划中哪个地方出了问题呢？
如果我们能得到sql执行时每一步实际返回的行数，就可以跟执行计划预计返回的行数做比较，两者数据相差不大，可以认为执行计划没有问题；反之，两者差距悬殊，就表示执行计划出了问题。
从10g开始oracle提供了dbms_xplan包的display_cursor函数，来同时显示执行计划预期返回的行数和实际返回的行数，于是我们可以利用这个包来快速找到执行计划中哪个地方出了问题，然后对症采取办法。
dbms_xplan包的display_cursor函数是从libary cache中获取执行计划，所以要想访问该函数，必须先授予权限：grant select any dictionary to scott;
使用dbms_xplan.display_cursor函数的步骤是：
1. 设置初始化参数statistics_level为ALL，如下：
SQL> alter session set statistics_level='ALL';
statistics_level控制数据库收集统计信息的级别，有三个值:
BASIC :收集基本的统计信息
TYPICAL：收集大部分统计信息(数据库的默认设置)
ALL：收集全部统计信息
2. 执行问题sql，如：
SQL> select ename,sal from emp,dept where emp.deptno=dept.deptno and dept.loc='CHICAGO';
3. 使用dbms_xplan.display_cursor包，查看带实际返回行的执行计划：
set lines 300
set pages 9000
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats last'));
dbms_xplan.display_cursor函数的定义是：
-- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL)
function display_cursor(sql_id varchar2 default null,
cursor_child_no integer default 0,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table
pipelined;
其中参数sql_id为父游标，如果为null，表示显示该会话之前的sql执行计划。cursor_child_no为子游标的序号，默认为0，如果设定为NULL，则所有该父游标下所有的子游标的执行计划都将返回。
参数format指定要显示哪些信息，常用的有：iostats（i/o信息显示）、allstats（i/o信息显示+pga信息）、advanced（显示所有统计信息）、iostats last或allstats last（只显示最后一次执行的统计信息）。默认值TYPICAL只能显示一个普通的执行计划，不能显示出实际返回的行。
第3步的输出为：
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cuq0vs99sctnm, child number 0
-------------------------------------
select ename,sal from emp,dept where emp.deptno=dept.deptno and
dept.loc='CHICAGO'
Plan hash value: 844388907
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 11 | | | |
| 1 | MERGE JOIN | | 1 | 4 | 6 |00:00:00.01 | 11 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 1 | 14 | 6 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT"."LOC"='CHICAGO')
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
25 rows selected.
解读以上输出：
Starts为该sql执行的次数。
E-Rows为执行计划预计的行数。
A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。
Buffers为每一步实际执行的逻辑读或一致性读。
Reads为物理读。
OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。
0/1/M 为最优/one-pass/multipass执行的次数。
查找低效执行计划：
1.比较A-Rows/Starts跟E-Rows，如果两值差别悬殊，则该行是低效执行计划。
2.查看Buffers/A-rows的比率，即返回一行平均消耗多少逻辑读
Buffers/A-rows<5 表示访问路径不错
Buffers/A-rows between 10 and 15，表示访问路径可以接受
Buffers/A-rows>15or20，表示路径不好，该行是低效执行计划，可以优化
另一种使用dbms_xplan.display_cursor函数的步骤是：
1. 在问题sql中加入提示：gather_plan_statistics
SQL> select /*+ gather_plan_statistics */ ename,sal from emp,dept where emp.deptno=dept.deptno and dept.loc='CHICAGO';
2. 使用dbms_xplan.display_cursor包，查看带实际返回行的执行计划：
set lines 300
set pages 9000
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats last'));
根据dbms_xplan.display_cursor函数可以很轻易地找到执行计划的哪个地方出了问题，接着我们就来分析CBO做出错误执行计划的原因。
如果出问题的那一步对应的是个索引，我们可以根据以下sql来判断是否统计信息出了问题：
0. 检查是否是索引碎片造成的
exec p_show_space('索引名', '索引属主', 'index')
1. 查看该索引的统计信息：
select index_name,num_rows,distinct_keys,num_rows/distinct_keys as avg_rows_per_key,last_analyzed from user_indexes where index_name='索引名'; 如下：
SQL> select index_name,num_rows,distinct_keys,num_rows/distinct_keys avg_rows_per_key,last_analyzed from user_indexes where index_name='PK_DEPT';
2. 查看实际的数据
select count(*) num_rows,count(nullif(col1,列值)) distinct_keys, count(distinct 列名) avg_rows_per_key from 表名; 如下：
SQL> select count(*) num_rows,count(nullif(loc,'CHICAGO')) distinct_keys,count(distinct 'CHICAGO') avg_rows_per_key from dept;
如果上面两步前两列数据差别悬殊，则说明统计信息不准确，需要重新收集统计信息；如果前两列数据相差不大，第三列数据是number，且相差很大，则可能索引所在列的直方图出了问题。
3. 查看索引列是否做了直方图统计
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select column_name,histogram,num_buckets,last_analyzed from user_tab_cols where table_name='EMP' and column_name in ('EMPNO','ENAME');
COLUMN_NAME HISTOGRAM NUM_BUCKETS LAST_ANALYZED
------------------------------ --------------- ----------- -------------------
EMPNO NONE 1 2012-10-23 22:48:28
ENAME HEIGHT BALANCED 16 2012-10-23 22:48:28
histogram列为NONE则表示未收集直方图。
直方图收集：
method_opt参数取值: size 1不搜集，size 2~255会搜集，size auto自动判断，size skewonly只要数据倾斜则收集。method_opt参数默认值为for all columns size auto，oracle自己决定采样比率。发现此默认值有时收集的直方图很有限，必要时可对所有索引列收集直方图 （不建议对所有列收集直方图，因为这样会导致存储直方图信息的表sys.histgrm$过大）
SQL> exec dbms_stats.gather_table_stats(user,'DEPT',method_opt =>'for all indexed columns',cascade=>true)
以下为个人牵强补充：
如果出问题的那一步对应的是个表，我们可以根据以下sql来判断是否统计信息出了问题：
1. 查看该表的统计信息及碎片率：
SQL> select num_rows from user_tables where table_name='EMP';
select num_rows,avg_row_len*num_rows/1024/blocks*8*100 碎片率 from user_tables where table_name='T'; --这种计算碎片率的方式仅适用于默认参数storage(initial 64K)建立的表
或用exec p_show_space('表名', '表属主', 'table')
2. 查看实际的数据
SQL> select count(*) num_rows from emp;
收集表的统计信息：exec dbms_stats.gather_table_stats(user,'表名',cascade=>true) --cascade=>true表示同时收集索引
http://pandarabbit.blog.163.com/blog/static/209284144201292910217427/
