玉面飞龙的BLOG

同事Daniel今天介绍这个dbms_xplan.display_cursor() 来着,其中的ALLSTATS LAST能够产生类似Wolfgang Breitling’s Tuning by Cardinality Feedback的效果。

SQL> set pagesize 100 linesize 200 serveroutput off
SQL> select /*+ gather_plan_statistics */ count(*) from TEST where rownum<=10 order by object_id;
COUNT(*)
———-
10

SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
—————————————————-
SQL_ID  0xj5kxtj0n59k, child number 0
————————————-
select /*+ gather_plan_statistics */ count(*) from TEST where rownum<=10 order by object_id;

Plan hash value: 4139816009
———————————————————————————————————
| Id  | Operation               | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
———————————————————————————————————
|   1 |  SORT AGGREGATE         |            |      1 |      1 |      1 |00:00:00.01 |       5 |     22 |
|*  2 |   COUNT STOPKEY         |            |      1 |        |     10 |00:00:00.01 |       5 |     22 |
|   3 |    PARTITION RANGE ALL  |            |      1 |    179K|     10 |00:00:00.01 |       5 |     22 |
|   4 |     INDEX FAST FULL SCAN| TEST_IDX   |      1 |    179K|     10 |00:00:00.01 |       5 |     22 |
———————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 - filter(ROWNUM<=10)

比sql_trace更加方便一些,和cardinality feedback一样是诊断SQL的最终方法。

dbms_xplan还是很有用的,又能够显示explain的计划，还能够读取sql area显示真正的执行计划；而且展现方式也多样。从$ORACLE_HOME/rdbms/admin/dbmsxpln.sql中可以了解更多相关信息。