Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009

    Unanswered: Transaction rollbacks

    Hi all,

    I have observed certain 'transaction rollbacks' while running my app
    I collected these statistics from V$MYSTAT, V$STATNAME.

    1.user rollbacks
    2.transaction rollbacks
    3.rollback changes - undo records applied

    user rollbacks is always 0 (as far I understand, any rollbacks in app code or library(oci wrapper) would show up here). I don't understand why 'transaction rollback' happens! along with some value for 'rollback changes - undo records applied'. (e.g.: user rollbacks 0; transaction rollbacks 13; rollback changes - undo records applied 30) Further checks with the app has shown that this is happening during insertions. I commented out writing part of the app and then no 'transaction rollbacks' happen.

    Also I see that these statistics are of class (128, DEBUG). So is it something that application cannot avoid but should be done at oracle end?

    I would run this app in parallel batches.
    Could this statistic contribute to a high rollback per transaction ratio?
    Could this lead to performance issues?

    I tried generating 10046 trace on the session. But I couldn't find anything related to rollbacks in the trace file or tkprof output.

    Will putting a trigger on V$MYSTAT help to capture updates to the statistic value. In that case, is it possible to identify which insert(exact query) is causing the problem?

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >this is happening during insertions.
    Please elaborate GREATLY.

    On which system is INSERT actually running?
    What is the source of the data being inserted?

    Is data going into DB on a row by row basis?
    Is data going into DB multiple rows from a single INSERT?

    Please post actual INSERT & EXPLAIN PLAN from it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts