Home » RDBMS Server » Performance Tuning » A high value of Rollback per Transaction (97,74%) (10.2.0.4.0 RAC)
A high value of Rollback per Transaction (97,74%) [message #415855] Wed, 29 July 2009 09:43 Go to next message
alekons
Messages: 8
Registered: July 2009
Junior Member
Hello,

I have a production system and on this system, "rollback per transaction" ratio is always very high 97,74%

Where can i find more info about what is being rolling back ?

Thanks
Alex

AWR report :

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
********      4225517201 *********           1 10.2.0.4.0  YES ********

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      6485 21-Jul-09 01:00:05        88       1.9
  End Snap:      6665 28-Jul-09 13:00:35        75       2.3
   Elapsed:           10,800.51 (mins)
   DB Time:              328.39 (mins)

Cache Sizes
~~~~~~~~~~~                       Begin        End
                             ---------- ----------
               Buffer Cache:     1,008M     1,008M  Std Block Size:         8K
           Shared Pool Size:       352M       352M      Log Buffer:    14,384K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              1,046.44              1,003.84
              Logical reads:                250.65                240.44
              Block changes:                  5.49                  5.27
             Physical reads:                 11.59                 11.12
            Physical writes:                  0.24                  0.23
                 User calls:                 22.36                 21.45
                     Parses:                 13.31                 12.77
                Hard parses:                  0.02                  0.02
                      Sorts:                  2.07                  1.98
                     Logons:                  0.25                  0.24
                   Executes:                 14.45                 13.86
               Transactions:                  1.04

  % Blocks changed per Read:    2.19    Recursive Call %:    88.46
 Rollback per transaction %:   97.74       Rows per Sort:    41.39

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   95.38    In-memory Sort %:  100.00
            Library Hit   %:   99.58        Soft Parse %:   99.81
         Execute to Parse %:    7.89         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:  101.45     % Non-Parse CPU:   92.00

 Shared Pool Statistics        Begin    End
                              ------  ------
             Memory Usage %:   94.42   93.67
    % SQL with executions>1:   89.39   74.65
  % Memory for SQL w/exec>1:   88.25   62.61

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time                                         18,386          93.3
control file parallel write         218,583       1,967      9   10.0 System I/O
db file sequential read           7,511,780       1,162      0    5.9   User I/O
control file sequential read      2,225,252       1,002      0    5.1 System I/O
CGS wait for IPC msg              5,861,347         332      0    1.7      Other
          -------------------------------------------------------------

[Updated on: Wed, 29 July 2009 09:48]

Report message to a moderator

Re: A high value of Rollback per Transaction (97,74%) [message #415870 is a reply to message #415855] Wed, 29 July 2009 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Where can i find more info about what is being rolling back ?

This is the application.

Regards
Michel
Re: A high value of Rollback per Transaction (97,74%) [message #415875 is a reply to message #415855] Wed, 29 July 2009 10:42 Go to previous messageGo to next message
alekons
Messages: 8
Registered: July 2009
Junior Member
thank you for your reply. I have already address this to the developers but is there a way to find more info in DB side ?
Re: A high value of Rollback per Transaction (97,74%) [message #415886 is a reply to message #415875] Wed, 29 July 2009 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which kind of info?

Regards
Michel
Re: A high value of Rollback per Transaction (97,74%) [message #415889 is a reply to message #415855] Wed, 29 July 2009 11:33 Go to previous messageGo to next message
alekons
Messages: 8
Registered: July 2009
Junior Member
like which sql maybe ?
Re: A high value of Rollback per Transaction (97,74%) [message #415893 is a reply to message #415889] Wed, 29 July 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rollback;

Regards
Michel
Re: A high value of Rollback per Transaction (97,74%) [message #415926 is a reply to message #415855] Wed, 29 July 2009 15:54 Go to previous messageGo to next message
alekons
Messages: 8
Registered: July 2009
Junior Member
Whatever may help to find which statments are being rollback. The issue is that more tha one application (different schema) is running on the same instance so it is difficult to search only on application side.
Re: A high value of Rollback per Transaction (97,74%) [message #415938 is a reply to message #415855] Wed, 29 July 2009 19:42 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post results from following SQL

SELECT NAME, COUNT(*) FROM V$SYSSTAT WHERE NAME LIKE '%roll%' group by name order by 2 desc;
Re: A high value of Rollback per Transaction (97,74%) [message #415984 is a reply to message #415926] Thu, 30 July 2009 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so it is difficult to search only on application side.

If the application is unable to know if it rolls back then you really have a problem but this has nothing to do with Oracle.

Regards
Michel
Re: A high value of Rollback per Transaction (97,74%) [message #416023 is a reply to message #415855] Thu, 30 July 2009 03:36 Go to previous messageGo to next message
alekons
Messages: 8
Registered: July 2009
Junior Member
results from following SQL

SELECT NAME, COUNT(*) FROM V$SYSSTAT WHERE NAME LIKE '%roll%' group by name order by 2 desc;



NAME COUNT(*)
---------------------------------------------------------------- ----------------------
cleanouts and rollbacks - consistent read gets 1
transaction tables consistent read rollbacks 1
rollback changes - undo records applied 1
IMU CR rollbacks 1
transaction rollbacks 1
user rollbacks 1
rollbacks only - consistent read gets 1

7 rows selected
Re: A high value of Rollback per Transaction (97,74%) [message #416027 is a reply to message #416023] Thu, 30 July 2009 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The result is useless for a database that did not work.

Regards
Michel
Re: A high value of Rollback per Transaction (97,74%) [message #417048 is a reply to message #416027] Wed, 05 August 2009 16:39 Go to previous message
serenaender
Messages: 4
Registered: August 2009
Junior Member
Indeed, the number of transactions, physical and logical reads, per second shows that the database activity is minimal. In those conditions the ratio per transaction rollback can be high because there are few user commits.

How ever notice that the user commits metric is not good to estimate real rollbacks. It is recommended use the transaction rollback metric and recalculate ratio per transaction rollback:

new ratio per transaction rollbacks = (transaction rollbacks/(transaction rollbacks+user commits))*100
Previous Topic: FTS - Full Table Scan - (merged 3)
Next Topic: Temp Size
Goto Forum:
  


Current Time: Fri Dec 09 00:02:53 CST 2016

Total time taken to generate the page: 0.22014 seconds