ITPub博客

首页 > Linux操作系统 > Linux操作系统 > statspack 里sprepsql.sql 的用法和作用

statspack 里sprepsql.sql 的用法和作用

原创 Linux操作系统 作者:paulyibinyi 时间:2009-01-15 23:07:45 0 删除 编辑

  sprepsql.sql 用于根据给定的SQL Hash 值生成SQL 报告 

  里面包括完整的sql和对应的执行计划

 @sprepsql.sql  输入开始和结束snap_id

  然后再输入sql hash value值 在输入路径和文件名 生成的报告如下:
STATSPACK SQL report for Hash Value: 2466850192  Module: JDBC Thin Client

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORCL          1067237780 orcl                1 9.2.0.5.0   NO      p670

 Start Id     Start Time         End Id      End Time       Duration(mins)
--------- ------------------- --------- ------------------- --------------
       64 15-Jan-09 10:05:53         67 15-Jan-09 11:44:48           98.92

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
   milliseconds (ms) for Per Execute
                                                       % Snap
                     Statement Total      Per Execute   Total
                     ---------------  ---------------  ------
        Buffer Gets:          95,473         95,473.0     .10
         Disk Reads:          25,631         25,631.0     .40
     Rows processed:               4              4.0
     CPU Time(s/ms):               2          1,810.0
 Elapsed Time(s/ms):              88         87,701.6
              Sorts:               0               .0
        Parse Calls:               1              1.0
      Invalidations:               0
      Version count:               1
    Sharable Mem(K):              42
         Executions:               1

SQL Text
~~~~~~~~
select * from (select  rownum rn ,  T_RK_GMSFZBD.HKSZDPCS as "HK
SZDPCS",T_RK_GMSFZBD.HKSZDJWH as "HKSZDJWH",T_RK_GMSFZBD.XM as "
XM",T_RK_GMSFZBD.SFZHM as "SFZHM",T_RK_GMSFZBD.SLH as "SLH",T_RK
_GMSFZBD.LQ_FFR as "LQ_FFR",T_RK_GMSFZBD.LQ_LQRQ as "LQ_LQRQ" FR
OM T_RK_GMSFZBD  Where (  (T_RK_GMSFZBD.GMSFZ_SLZT   = '08') and
  (T_RK_GMSFZBD.HKSZDPCS   = '4401830016') and  (T_RK_GMSFZBD.LQ
_LQRQ   = '20090115') )   and rownum<11 ) where rn>=1

All Optimizer Plan(s) for this Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this Hash value, and the Snap Id's they
were first found in the shared pool
-> ordered by Snap Id

    Plan
 Hash Value  Snap Id        Cost Optimizer
------------ -------- ---------- --------------------
  3682949207       67         0  CHOOSE

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified.  The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 3682949207 ----|       |      |        |
|VIEW                            |                     |       |      |        |
| COUNT STOPKEY                  |                     |       |      |        |
|  TABLE ACCESS BY INDEX ROWID   |T_RK_GMSFZBD         |       |      |        |
|   AND-EQUAL                    |                     |       |      |        |
|    INDEX RANGE SCAN            |IDX_GMSFZB_GMSFZSLZT |       |      |        |
|    INDEX RANGE SCAN            |IDX_GMSFZBDNEW_HKXZD |       |      |        |
--------------------------------------------------------------------------------

End of Report

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-539769/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6596976