Oracle statspack is essential if you’re trying to track down a performance problem. However, one problem I came across is that the top summary report you get from spreport is only a partial query. This makes it tough for developers to track down exactly where in the code the bad query lives – Especially if they have a lot of similar queries with just a few changes in the where clause.
SQL> @?/rdbms/admin/spreport
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
42,659,654 31 1,376,117.9 27.0 743.40 1621.51 2018450324
Doesn’t leave you with the full query in most cases, just the first 5 lines or so.
Something like this
27,611,489 142 194,447.1 1.8 450.19 711.84 2018450324
Module: PIPE)@BaanCA (TNS V1-V3)
SELECT /*+FIRST_ROWS INDEX_ASC(a ttfgld418200$idx1)*/ a.t$cprj,a
.t$docn,a.t$fcom,a.t$fitr,a.t$line,a.t$lino,a.t$ocom,a.t$orno,a.
t$pono,a.t$prin,a.t$sern,a.t$sint,a.t$tmpk,a.t$trdt,a.t$tror,a.t
$trtm,a.t$ttyp,b.t$amnt,b.t$amth,b.t$btno,b.t$ccty,b.t$ccur,b.t$
cpcp,b.t$cprj,b.t$crdt,b.t$crtm,b.t$crus,b.t$cuni,b.t$cuno,b.t$c
So what you’d need to do to get the entire query is to first note the “Hash Value” from
your spreport
SQL> @?/rdbms/admin/sprepsql Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 28631 Begin Snapshot Id specified: 28631 Enter value for end_snap: 28633 End Snapshot Id specified: 28633 Specify the Hash Value ~~~~~~~~~~~~~~~~~~~~~~ Enter value for hash_value: 2018450324 Hash Value specified is: 2018450324
The result will all of the lines of the query and some nice info about it.
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: 27,611,489 194,447.1 1.83
Disk Reads: 4,007,081 28,218.9 6.01
Rows processed: 77,378 544.9
CPU Time(s/ms): 450 3,170.3
Elapsed Time(s/ms): 712 5,012.9
Sorts: 142 1.0
Parse Calls: 4 .0
Invalidations: 0
Version count: 1
Sharable Mem(K): 143
Executions: 142
SQL Text
~~~~~~~~
SELECT /*+FIRST_ROWS INDEX_ASC(a ttfgld418200$idx1)*/ a.t$cprj,a
.t$docn,a.t$fcom,a.t$fitr,a.t$line,a.t$lino,a.t$ocom,a.t$orno,a.
t$pono,a.t$prin,a.t$sern,a.t$sint,a.t$tmpk,a.t$trdt,a.t$tror,a.t
$trtm,a.t$ttyp,b.t$amnt,b.t$amth,b.t$btno,b.t$ccty,b.t$ccur,b.t$
cpcp,b.t$cprj,b.t$crdt,b.t$crtm,b.t$crus,b.t$cuni,b.t$cuno,b.t$c
vat,b.t$cwar,b.t$dbcr,b.t$dcdt,b.t$dim1,b.t$dim2,b.t$dim3,b.t$di
m4,b.t$dim5,b.t$docn,b.t$fact,b.t$fcom,b.t$fitr,b.t$fprd,b.t$fye
r,b.t$inpo,b.t$item,b.t$leac,b.t$line,b.t$lino,b.t$nper,b.t$nuni
,b.t$ocom,b.t$orno,b.t$podt,b.t$pono,b.t$potm,b.t$pous,b.t$qty1,
b.t$qty2,b.t$rate,b.t$refr,b.t$rprd,b.t$ryer,b.t$sern,b.t$sint,b
.t$srno,b.t$suno,b.t$tmpk,b.t$trdt,b.t$tror,b.t$trtm,b.t$tsls,b.
t$ttyp,b.t$vamh,b.t$vamt,b.t$vprd,b.t$vtyp,b.t$vyer,c.t$dsca,c.t
$dscb,c.t$psts,c.t$sdat,c.t$ddat,c.t$cuno,c.t$cprj,c.t$intp,d.t$
ttyp,d.t$desc,e.t$leac,f.t$cpcp,f.t$dsca,g.t$nama FROM baan.ttfg
ld418200 a,baan.ttfgld410200 b,baan.ttipcs020200 c,baan.ttfgld01
1200 d,baan.ttfgld008200 e,baan.tticpr010200 f,baan.ttccom010200
g WHERE ((b.t$podt >= :1) AND (b.t$podt <= :2) OR (a.t$sint = :6) A
ND (((b.t$fyer * :5) + b.t$fprd) <= :7)) AND (:8 = :9 OR (:10 =
:11 AND a.t$sint = :3) OR (:12 = :13 AND (a.t$sint = :16) AND (a.t$ttyp
= :18) AND (a.t$docn = :23 AND a.t$fitr >= :24 AND a.t$tror <= :25 AND
a.t$fitr = :28 AND a.t$
tror >= :29 AND a.t$orno >= :30 AND a.t$cprj <= :31 AND a.t$tror
<= :32 AND a.t$orno = :34) AND (b.t$btno
= :36) AND (b.t$cpcp = :38) AND (b.t$leac = :40 AND NOT(b.t
$crdt = :40 AND (b.t$crtm < :41))) AND (b.t$crdt :43))) AND (c.t$psts >= :44)
AND (c.t$psts <= :45) ORDER BY 7,15,4,14,16,11,5
0 Comments.