Enqueue wait
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Enqueue wait

  1. #1
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452

    Enqueue wait

    Hi once again at your doorsteps

    I was wondering is there any way to make smon obey you.
    Here's the problem I faced

    A delete statement was fired from a session, which ran for a long time (2 1/2 days) before it was killed.since there was some rollback being done, so the OS process was also killed.
    And then the developer felt the need to tune the statementand eventually came to me
    The tuned statement was fired and it was seen that it's waiting for for an enqueue held by SMON.Obviously SMON was doing the pending rollback from the first statement.Is there any way I can make SMON work faster or give up the rollback?? What could be the consequence of this?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    no you cant make smon stop the rollback - that would be frightening.

    You also cant make it go faster, if it went for over 2 and a half days chances are it will take longer to rollback

    you can monitor it via used_ublk and used_rec from v$transaction and see how long it will take to go

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You can try restarting the database.

    regards
    Hrishy

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    thanks
    My v$transaction shows only one entry where
    used_ublk=284
    used_urec=20528
    although the addr does not match with saddr,paddr or sql_addr of smon
    Am I viewing the right thing?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Enqueue wait

    Originally posted by simply_dba
    . . . or give up the rollback?? What could be the consequence of this?
    This as davey says is frightening. There seems to be an idea that rollback is a kind of "optional extra".

    Oracle can write "dirty" blocks to the data files before a transaction is complete. This means that in terms of transactional integrity, your data files contain CRAP.

    Rollback/Undo is Oracle's way of keeping track of the CRAP in your data files. "give up" the rollback and you have a CRAP database.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    are they going down? I dont think it should be smon's details in there - it is the details of the session you killed

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hrishy
    Hi

    You can try restarting the database.

    regards
    Hrishy
    Wont help..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Originally posted by davey23uk
    are they going down? I dont think it should be smon's details in there - it is the details of the session you killed
    Heck no they are going up
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  9. #9
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452

    Re: Re: Enqueue wait

    Originally posted by DaPi
    This as davey says is frightening. There seems to be an idea that rollback is a kind of "optional extra".

    Oracle can write "dirty" blocks to the data files before a transaction is complete. This means that in terms of transactional integrity, your data files contain CRAP.

    Rollback/Undo is Oracle's way of keeping track of the CRAP in your data files. "give up" the rollback and you have a CRAP database.
    Ok I asked a silly question.
    But do you have to be rude like that?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    it's not rude, they are plain facts

Posting Permissions

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



Click Here to Expand Forum to Full Width