Enqueue wait

Hi once again at your doorsteps :o

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 :rolleyes:
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?

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

Hi

You can try restarting the database.

regards
Hrishy

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?

Quote:

---

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.

are they going down? I dont think it should be smon's details in there - it is the details of the session you killed

Quote:

---

Originally posted by hrishy
Hi

You can try restarting the database.

regards
Hrishy

---

Wont help..

Abhay.

Quote:

---

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:confused:

Quote:

---

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?:o

it's not rude, they are plain facts

OOPS! Sorry if that was taken as being rude - I did want to make the point forcefully, as this is not the first time I've seen this sort of thing (e.g. not bothering to backup RBS TS). I do have your best interests at heart!

Quote:

---

Originally posted by simply_dba
Heck no they are going up:confused:

---

then that transaction would be belonging to some other session.. join with v$session to see the details.


Abhay.

Quote:

---

Originally posted by abhaysk
then that transaction would be belonging to some other session.. join with v$session to see the details.


Abhay.

---

There's only one row in v$transaction

Quote:

---

Originally posted by DaPi
OOPS! Sorry if that was taken as being rude - I did want to make the point forcefully, as this is not the first time I've seen this sort of thing (e.g. not bothering to backup RBS TS). I do have your best interests at heart!

---

No hard feelings !!
You certainly made your point.
I was just being the nagging type:D

here is an output from v$enqueue_lock:

Code:

---

ADDR                            KADDR    SID        TYPE        ID1        ID2        LMODE        REQUEST        CTIME        BLOCK

00000003B5956368        00000003B5956388        5        TX        262290        3268756        6        0        36        1
00000003B59562E0        00000003B5956300        5        TS        3        1              3        0        2047129        0
00000003B59560C0        00000003B59560E0        5        PS        1        0              4        0        81164          0
00000003B595B588        00000003B595B5A8        19        TX        262290        3268756        0        4        36        0

---

sid 5 being the smon and 19 being the current delete sql

Quote:

---

Originally posted by abhaysk
Wont help..

Abhay.

---

I thought bouncing the Db would kick in fast start parallel rollback.
or am i missing something ?

regards
Hrishy

It's one of those "pay me now or pay me later" with SMON.
Either way SMON is going to do whatever he needs to do whether you let him clean up before shutdown or on startup.

Been there done that before and it sucks lol :p

Quote:

---

Originally posted by simply_dba
There's only one row in v$transaction

---

Yes but which session that transaction belongs to.. did u check?

are you sure that its the dead sessions transaction?


Abhay.

Quote:

---

Originally posted by hrishy
I thought bouncing the Db would kick in fast start parallel rollback.
or am i missing something ?

regards
Hrishy

---

That would any how have been invoked by smon process if that appropriate parameter is defined.. so why shutdown?

Abhay.