statspack 裡sprepsql.sql 的用法和作用

  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