Enqueue wait

[simply_dba]

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?

[davey23uk]

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

[hrishy]

Hi

You can try restarting the database.

regards
Hrishy

[simply_dba]

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?

[DaPi]

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.

[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

[abhaysk]

Originally posted by hrishy
Hi

You can try restarting the database.

regards
Hrishy

Wont help..

Abhay.

[simply_dba]

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

[simply_dba]

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?

[davey23uk]

it's not rude, they are plain facts