大家都知道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临时表空间使用等数据都能够显示。有兴趣的朋友可以自己试试。
performance