在Oracle 9i下的display_cursor指令碼

來源地址：老熊文章

大家都知道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 | Starts | E-Rows | A-Rows | A-Time      | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|    1 | SORT AGGREGATE     |      |      1 |      1 |      1 | 00:00:00.39 |     155 |    0 |    0 |    0 (0) |
| *  2 |  TABLE ACCESS FULL | T1   |      1 |  44444 |  40000 | 00:00:00.21 |     155 |    0 |    0 |    0 (0) |
----------------------------------------------------------------------------------------------------------------

如果v$sql_plan_statistics_all有資料，則這個指令碼會生成上面的第2個示例的結果，否則，會得到示例1的結果。從輸出的結果來看，朋友們，是不是與dbms_xplan的輸出驚人地相似啊！

在這裡只是測試了最簡單的SQL，實際上這個指令碼對於並行，CPU成本，TEMP臨時表空間使用等資料都能夠顯示。有興趣的朋友可以自己試試。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15187685/viewspace-732681/，如需轉載，請註明出處，否則將追究法律責任。