在Oracle 9i下的display_cursor脚本
转载：http://www.laoxiong.net/oracle9i_display_cursor.html
大家都知道Oracle 10g的dbms_stats包与Oracle 9i相比，功能增强了很多，比如增加了display_cursor这个过程，能够查看V$SQL_PLAN视图中的执行计划，如果在statistics_level参数设置为ALL，或者执行的sql使用了gather_plan_statistics hint，则在sql执行后，会在v$sql_plan_statistics_all视图中查到SQL的执行统计信息，例如逻辑读，物理读等等。这些数据对于性能诊断有着非常大的帮助。同时v$sql_plan中的执行计划，与通过EXPLAIN PLAN得到的执行计划相比，前者是oracle执行sql时真正使用的执行计划，而后者则可能不是真正的执行计划；同时有的时候，执行过的sql使用了绑定变量，而oracle在解析sql时通常会进行绑定变量窥探，这个时候我们不能使用EXPLAIN PLAN来得到那个sql的执行计划，就算得到的跟那个sql的真实的执行计划是不一样的，所以有时我们更愿意直接从v$sql_plan中得到执行计划。

但是在oracle 9i中的dbms_xplan包没有display_cursor这个过程。不过，本文根据一个开源软件SQLT中得到的一段脚本，经过修改后，能够显示 v$sql_plan和v$sql_plan_statistics中的执行计划和sql的执行统计数据。点击此处下载display_cursor_9i 代码

下面是使用这个代码的示例：

    SQL> select /*+ sqla */ count(*) from t1 where a<13;

      COUNT(*)
    ----------
         40000

在另一个会话中，得到这个SQL的hash_value , child_number以及在v$sql_plan中的执行计划。

    SQL> select hash_value,child_number from v$sql where sql_text like ‘%sqla%’ and sql_text not like ‘%v$sql%’;

    HASH_VALUE CHILD_NUMBER
    ---------- ------------
    1742773495            0

    SQL> @display_cursor_9i 1742773495 0
    原值  268:   s_hash_value := &1;
    新值  268:   s_hash_value := 1742773495;
    原值  269:   s_child_num := &2;
    新值  269:   s_child_num := 0;

    HASH_VALUE: 1742773495   CHILD_NUMBER: 0
    ---------------------------------------------------------
    select /*+ sqla */ count(*) from t1 where a<13

    Plan hash value: 3724264953

    ------------------------
    | Id   | Operation           | Name |  Rows | Bytes | Cost |
    ------------------------
    |    0 | SELECT STATEMENT    |      |       |       |   25 |
    |    1 |  SORT AGGREGATE     |      |     1 |     3 |      |
    | *  2 |   TABLE ACCESS FULL | T1   | 44444 |  133K |   25 |
    ------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    3 - filter(”A”<13)

    PL/SQL 过程已成功完成。

如果我们将statistics_level设置为ALL（注意：在oracle 9i中gather_plan_statistics这个hint无效），重新执行这个SQL：

    SQL> alter session set statistics_level=all;

    会话已更改。

    SQL> select /*+ sqla */ count(*) from t1 where a<13;

      COUNT(*)
    ----------
         40000

在会话2中重新进行之前的查询，只不过由于参数的参数，这个SQL有两个子游标，这次执行的游标其child_number为1：

    SQL> select hash_value,child_number from v$sql where sql_text like ‘%sqla%’ and sql_text not like ‘%v$sql%’;

    HASH_VALUE CHILD_NUMBER
    ---------- ------------
    1742773495            0
    1742773495            1

    SQL> @display_cursor_9i 1742773495 1
    原值  268:   s_hash_value := &1;
    新值  268:   s_hash_value := 1742773495;
    原值  269:   s_child_num := &2;
    新值  269:   s_child_num := 1;

    HASH_VALUE: 1742773495   CHILD_NUMBER: 1
    -------------------------------------------
    select /*+ sqla */ count(*) from t1 where a<13

    Plan hash value: 3724264953

    ----------------------------------------
    | Id   | Operation          | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers | OMem | 1Mem | Used-Mem |
    ----------------------------------------
    |    1 | SORT AGGREGATE     |      |      0 |      1 |      0 | 00:00:00.00 |       0 |    0 |    0 |    0 (0) |
    | *  2 |  TABLE ACCESS FULL | T1   |      0 |  44444 |      0 | 00:00:00.00 |       0 |    0 |    0 |    0 (0) |
    ----------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    1 - filter(”A”<13)

    PL/SQL 过程已成功完成。

不幸的是，在另一个会话中查询v$sql_plan_statistics_all的一些结果并不正确。只有在那个执行SQL的会话（就是例子中的会话 1）中，才能得到正确的结果：

----------------------------------------
| Id   | Operation          | Name