ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在Oracle 9i下的display_cursor脚本

在Oracle 9i下的display_cursor脚本

原创 Linux操作系统 作者:aishu521 时间:2012-06-13 15:13:36 0 删除 编辑

來源地址:老熊文章

大家都知道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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle常用监控SQL
请登录后发表评论 登录
全部评论

注册时间:2012-05-26

  • 博文量
    139
  • 访问量
    677418