RE: STATSPACK - Rollback per transaction %

This probably isn't exactly what you want, but might be a good starting point:
 
select a.snap_time, round((100*(b.value/(c.value+d.value))),2) rb_per_tx from 
stats$snapshot a, stats$sysstat b, stats$sysstat c, stats$sysstat d where 
a.snap_id = b.snap_id and a.snap_id = c.snap_id and a.snap_id = d.snap_id and 
b.name = 'transaction rollbacks' and c.name = 'user commits' and d.name = 'user 
rollbacks' and a.snap_time > sysdate - 1 order by 1;
 
HTH
Brandon

 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On 
Behalf Of BN
Sent: Thursday, December 22, 2005 8:10 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: STATSPACK - Rollback per transaction %


Greetings,
 
Sent to wrong address...
 


---------- Forwarded message ----------
From: BN < bnsarma@xxxxxxxxx>
Date: Dec 20, 2005 11:31 AM
Subject: STATSPACK - Rollback per transaction %
To: oracle-l-admins@xxxxxxxxxxxxx

 
Greetings

Oracle 9i Rel2 HP-UX
 
We collect Hourly STATSPACK snaps for the Prod. DB.
 
 
I pulled out couple of  STATSPACK  reports from on of our Production Database, 
and see that 
"Rollback per transaction %" is upper 90%'s,  
 
I wanted to approach the DEV/App team to find out why are they rollingback more 
than 90% of their work,
 
Before that I wanted to pull out the info from all the STASPACK Snaps I have, 
I looked at the spreport.sql to see how they are arriving at this number, Not 
sure how I can make use of the following SQL to pull
Rollback information for all the snaps I have.

 
Select .....
 
....
 
      ,' Rollback per transaction %:' dscr, round(100*:urol/:tran,2) pctval
      ,'      Rows per Sort:'     , decode((:srtm+:srtd)
                                                   ,0,to_number(null)
                                            ,round(:srtr/(:srtm+:srtd),2)) 
bpctval 
 from sys.dual;

 
I apprecaite your suggestions/help to pull this info, or if somebody has 
already have a sql that they can share.

Regards & Thanks
 


-- 
Regards & Thanks
BN 


Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

Other related posts: