dbms_xplan.display_cursor的一点小问题分享

工具包dbms_xplan.display_cursor在诊断SQL性能方面是非常有用的，特别是与Statistics_level=all;一起使用同时制定Format为allstats last是会看到很多有用的信息，但是今天遇到一个问题，和大家分享一下。就是Statistics_level=all加dbms_xplan.display_cursor(null,null,’allstats last’)显示的信息不完整。让我们看下如下的测试案例，问题与答案都一目了然了。

sqlplus / as sysdba

SQL> create table test as select * from dba_objects;

Table created.

SQL>  alter session set Statistics_level=all;

SQL> set linesize 500

SQL>  select count(*) from test;<<<<normal SQL
     49981

SQL>  select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));
SQL_ID  2fxk53j4a5r6d, child number 0
————————————-
 select count(*) from test

Plan hash value: 1950795681

————————————————————————————-
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  <<<buffers information is displayed
————————————————————————————-
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.21 |     692 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |  53270 |  49981 |00:00:00.10 |     692 |
————————————————————————————-

SQL> select count(*) from test where object_id<100 and object_id>200;  <<<<<with illogical predicate
         0

SQL>  select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));
SQL_ID  5xv7jfk3ryntq, child number 0
————————————-
select count(*) from test where object_id<100 and object_id>200

Plan hash value: 1617223730

—————————————————————————-
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |  <<<<<<<<<no buffers information
—————————————————————————-
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |
|*  2 |   FILTER            |      |      1 |        |      0 |00:00:00.01 |
|*  3 |    TABLE ACCESS FULL| TEST |      0 |      8 |      0 |00:00:00.01 |
—————————————————————————-