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=":16)" a="" nd="" (((b.t$fyer="" *="" :5)="" +="" b.t$fprd)="" <=":7))" and="" (:8=":9" (:10=":11" a.t$sint=":3)" (:12=":13" (a.t$ttyp=":18)" (a.t$docn=":23" a.t$fitr="">= :24 AND a.t$tror <= :25="" and="" a.t$fitr=":28" a.t$="" tror="">= :29 AND a.t$orno >= :30 AND a.t$cprj <= :31="" and="" a.t$tror="" <="" a.t$orno=":34)" (b.t$btno=":36)" (b.t$cpcp=":38)" (b.t$leac=":40" not(b.t="" $crdt=":40" (b.t$crtm="" :41)))="" (b.t$crdt="" :43)))="" (c.t$psts="">= :44)
AND (c.t$psts <= :45)="" order="" by="" 7,15,4,14,16,11,5=""
