通过dbms_xplan.display_cursor识别低效的执行计划

识别问题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为一次通过模式。
O/1/M 为模式执行的次数。

查找低效执行计划：
1.比较E-Rows跟A-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 num_rows,distinct_keys,num_rows/distinct_keys as avg_rows_per_key from user_indexes where index_name='索引名';  如下：
SQL> select num_rows,distinct_keys,num_rows/distinct_keys as avg_rows_per_key from user_indexes where index_name='PK_DEPT';

2. 查看实际的数据
select count(*) num_rows,count(distinct 列名) distinct_keys,count(nullif(col1,列值)) avg_rows_per_key from 表名;  如下：
SQL> select count(*) num_rows,count(distinct 'CHICAGO') distinct_keys,count(nullif(loc,'CHICAGO')) avg_rows_per_key from dept;

如果上面两步前两列数据差别悬殊，则说明统计信息不准确，需要重新收集统计信息；如果前两列数据相差不大，第三列数据是number，且相差很大，则可能索引所在列的直方图出了问题。

3. 查看索引列是否做了直方图统计
两种判断方式：
①查看user_tab_col_statistics，如果histogram为none，就表示直方图收集不够。
select histogram,num_buckets from user_tab_col_statistics where table_name='表名' and column_name='列名';  如下：
SQL> select histogram,num_buckets from user_tab_col_statistics where table_name='DEPT' and column_name='LOC';
HISTOGRAM       NUM_BUCKETS
--------------- -----------
NONE                      1

②查user_histograms，如果列只有只有最大值、最小值两条记录分别对应端点号（endpoint_number）0和1，这种显示说明列LOC没有直方图信息。
select column_name,endpoint_number,endpoint_value from user_histograms where table_name='表名' and column_name='列名';
SQL> select column_name,endpoint_number,endpoint_value from user_histograms where table_name='EMP' and column_name='EMPNO';
COLUMN_NAME                                                                      ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------------------------------------------------------------------- --------------- --------------
EMPNO                                                                                          0           7369
EMPNO                                                                                          1           7934
如果列LOC上只有最大值、最小值两条记录分别对应端点号（endpoint_number）0和1，这种显示说明列LOC没有直方图信息。

直方图收集：
method_opt参数取值: size 1不搜集，size 2~255会搜集，size auto自动判断，size skewonly只要数据倾斜则搜集。
method_opt参数默认值为for all columns size auto，oracle自己决定采样比率。发现此默认值往往收集的直方图有时很有限，必要时可使用以下两种方式来收集：
1.对索引列收集直方图
SQL> exec dbms_stats.gather_table_stats(user,'DEPT',method_opt =>'for all indexed columns',cascade=>true)

2.对所有列收集直方图
exec dbms_stats.gather_table_stats(user,'EMP',method_opt =>'for all 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/