Oracle 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