X-list: oracle-l Return-Path: Subject: RE: STATSPACK - Rollback per transaction % From: "Allen, Brandon" Message-id: 04DDF147ED3A0D42B48A48A18D574C4503D3FF7F@NT15.oneneck.corp Date: 2005-12-27 18:45:38 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On \ Behalf Of BN Sent: Thursday, December 22, 2005 8:10 AM To: oracle-l@freelists.org Subject: STATSPACK - Rollback per transaction % Greetings, Sent to wrong address... ---------- Forwarded message ---------- From: BN < bnsarma@gmail.com> Date: Dec 20, 2005 11:31 AM Subject: STATSPACK - Rollback per transaction % To: oracle-l-admins@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.