RE: STATSPACK - Rollback per transaction %

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: 2005-12-27 18:45:38
Message-id: 04DDF147ED3A0D42B48A48A18D574C4503D3FF7F@NT15.oneneck.corp

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On \ Behalf Of BN
Sent: Thursday, December 22, 2005 8:10 AM To: oracle-l_at_freelists.org
Subject: STATSPACK - Rollback per transaction %

Greetings,  

Sent to wrong address...  

• Forwarded message ---------- From: BN < bnsarma_at_gmail.com> Date: Dec 20, 2005 11:31 AM Subject: STATSPACK - Rollback per transaction % To: oracle-l-admins_at_freelists.org

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.

Received on Tue Dec 27 2005 - 18:45:38 CST