通过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/

http://blog.csdn.net/dbanote/article/details/24516037

通过dbms_xplan.display_cursor识别低效的执行计划,布布扣,bubuko.com

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

上一篇:newLISP访问MySQL中文乱码


下一篇:mysql-5.6.17-win32免安装版配置