12 Replies Latest reply: Jan 12, 2012 7:50 AM by albertone RSS

    Oracle 9i - Is there any way to trace the amount of rollback statements ?

    albertone
      Hi,

      we're running Oracle 9.2.0.5.0 and we would need to monitor the amount of rollback statements executed on a specific table.
      Turning on traces for "set events '10046 trace name context forever, level 1'" would be too much invasive given the current load of the system so that was wondering if there was any quicker way to do that.

      Thanks in advance!

      Mike
        • 1. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
          Helios-GunesEROL
          Hi;

          Please check below note which could be helpful for your issue

          SMON is Generating a Trace File With UNDO SEG (BEFORE RECOVERY) Information [ID 754743.1]

          Regard
          Helios
          • 2. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
            Girish Sharma
            If you are interested to find the active transactions using undo extents then you can get the amount of undo

            SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
            FROM v$session s, v$transaction t
            WHERE s.saddr = t.ses_addr;

            dba_rollback_segs and v$rollstat views are for more information. V$TRANSACTION.USED_UBLK will tell you Number of undo blocks used.

            http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch3232.htm#REFRN30008

            There are great scripts at Sir Steve Adam's site : http://www.ixora.com.au/scripts/rbs.htm

            Regards
            Girish Sharma
            • 3. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
              albertone
              Thanks!

              Useful information indeed, thanks.

              I was specifically more interested in finding a way to get statistics related to the amount of times a "rollback" statement is issued by a user or applications. Reason being we wanted to monitor some inconsistencies related to trigger execution.

              Thanks again,

              Mike
              • 4. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                Girish Sharma
                some inconsistencies related to trigger execution.
                What exactly it mean? Which type of inconsistencies are you talking about/addressing? And you are going to something audit the commit / rollback? Oracle don't provides audit on commit and rollback. Just post the code of trigger; so that if possible, we may try to help you.

                Regards
                Girish Sharma
                • 5. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                  albertone
                  Did not want to raise another issue :)

                  We have a few huge tables which we need to monitor in terms of activities so that we're using triggers to update some oracle sequences keeping track of the action performed on them.

                  basically the trigger only increase the sequence value.

                  CREATE OR REPLACE TRIGGER xxx_Summary_Trigger
                  AFTER INSERT OR UPDATE or delete ON xxx
                  FOR EACH ROW
                  declare dummy number(10);
                  BEGIN
                       IF :New.xxx_Status = 'used' THEN
                            SELECT used_plus.nextval into dummy from dual;
                       ELSIF :New.Card_Status = 'activated' THEN
                            SELECT activated_plus.nextval into dummy from dual;
                  ...
                       END IF;

                       IF :Old.xxx_Status = 'used' THEN
                            SELECT used_minus.nextval into dummy from dual;
                       ELSIF :Old.xxx_Status = 'activated' THEN
                            SELECT activated_minus.nextval into dummy from dual;
                  ...
                       END IF;
                  END;

                  So that each time the xxx_Status is updated we have an updated sequence (nocache) which allows us to get the overall distribution without a long scan on the table (hosting more than 200M records).

                  I know triggers are not the best way to approach, not to mention they fire before a commit, so that in case of a rollback the resulting sequence would be showing a wrong value.

                  But this was only a quick and dirt approach, which works fine for the time being. I just wanted to confirm the inconsistencies we are encountering are due to some rollbacks which mislead the trigger, that's why I was interested in getting a stat on the rollback statement execution.

                  We get discrepancies every now and then, so that leaving traces on for a long time may be disruptive.

                  Thanks!

                  Mike
                  • 6. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                    Girish Sharma
                    Aha... so, you are trying to rollback the sequence by rollback segment? Mike, there is a big flaw in your design. You are using sequence (an object which only used to generate unique numbers) in trigger and wish that sequence should be rollback on rollbacking the transaction, which is not possible, because commit and and rollback are used for transaction, rolling back of sequence is no more concern/connected. If you wish to rollback the sequence (which is really a horrible idea though), you would have to increment with -1. Either you will have to use another logic (something by using Select .... For update Nowait) or try to read this (Re: Question on Sequence.. thread. There is no rollback trigger command in Oracle like Sybase.

                    Regards
                    Girish Sharma
                    • 7. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                      Girish Sharma
                      ..Removed Duplicated..

                      Edited by: Girish Sharma on Jan 10, 2012 9:29 PM
                      • 8. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                        albertone
                        Thanks,

                        I see what you mean, but we were not trying to rollback the sequence value. We know the limit of the trigger in not being able to fire after the commit, but we wanted to see if the issues we're having are related to rollback statements issued by the app, or by users connected directly to DB for instance.

                        Thanks for your time, will check anyway for alternatives among the info you shared.

                        Thanks,

                        Mike
                        • 9. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                          P.Forstmann
                          Maybe you have some triggers restarts. This is briefly document in http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#i1006211:

                          >
                          If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETE statement that conflicts with an UPDATE statement that is running, then the database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package variables that the trigger references. To detect this situation, include a counter variable in the package.
                          >

                          You can read more on this in :
                          http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html
                          http://tkyte.blogspot.com/2005/08/part-ii-seeing-restart.html
                          http://tkyte.blogspot.com/2005/09/part-iii-why-is-restart-important-to.html

                          It is possible that this issue occurs with Oracle 9i and AFTER TRIGGER.
                          • 10. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                            albertone
                            Really Interesting documentation...

                            Actually we are using the trigger only to increment the sequence, which as you may have seen we're using basically as a counter.
                            Not sure if the parallel access to sequence objects causes locks which may cause the restart,but it's worth investigating this side as well.

                            Will check, appreciated!

                            Mike
                            • 11. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                              jgarry
                              Maybe you can use log miner to look for ROLLBACK in the OPERATION column of V$LOGMNR_CONTENTS?
                              • 12. Re: Oracle 9i - Is there any way to trace the amount of rollback statements ?
                                albertone
                                Hey thank You!

                                Think this is quite useful and think may close this thread for the time being.

                                Really appreciated the support of all of you!

                                Mike