OraFAQ Forum: Performance Tuning » undo tbalespace full, which is rectified but now having big WAIT

Home » RDBMS Server » Performance Tuning » undo tbalespace full, which is rectified but now having big WAIT (ORACLE 11G R2 (11.2.0.1.0), ASM , SOLARIS 10, RAC of two Nodes)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

undo tbalespace full, which is rectified but now having big WAIT [message #546525]

Wed, 07 March 2012 05:04

janakors
Messages: 232
Registered: September 2009

Senior Member

hi,
we have a situation where both undo tablespaces were almost filled i.e UNDOTBS1 99% and UNDOTBS2 100% filled so i add data files to it and then i found a lot of blocking session and was just killing them through EM then i stop my front end listener and also down the service, now i don't have any blocking session but on EM a big WAIT is coming. alert log shows nothing serious, it was showing deadlock but now it is over as well.
please guide as don't know what is going wrong

Regards

JANAKORS

Report message to a moderator

Re: undo tbalespace full, which is rectified but now having big WAIT [message #546545 is a reply to message #546525]

Wed, 07 March 2012 05:53

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

we have a situation where both undo tablespaces were almost filled i.e UNDOTBS1 99% and UNDOTBS2 100% filled

What do you EXACTLY mean by this sentence?
Which query do you use to determine they are filled?

Quote:

then i found a lot of blocking session

There is NO relation between any lock and UNDO tablespace usage.

Quote:

on EM a big WAIT is coming

Which is? I bet you are waiting on locks taken by the sessions you killed and that are currently being rolled back.

Quote:

it was showing deadlock but now it is over as well

Which means the desing of your application is wrong.

Regards
Michel

Report message to a moderator

Re: undo tbalespace full, which is rectified but now having big WAIT [message #546551 is a reply to message #546545]

Wed, 07 March 2012 06:20

janakors
Messages: 232
Registered: September 2009

Senior Member

Quote:

What do you EXACTLY mean by this sentence?
Which query do you use to determine they are filled?

i checked it from EM when user were complaining about the slowness of application.
Quote:

Which is? I bet you are waiting on locks taken by the sessions you killed and that are currently being rolled back.

so you mean to say that when the session is killed and that session having some locks , so that wont be released?

kindly advice

Regards
Janakors

Report message to a moderator

Re: undo tbalespace full, which is rectified but now having big WAIT [message #546553 is a reply to message #546551]

Wed, 07 March 2012 06:23

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

The locks will be released once oracle has finished rolling back the sessions transaction(s). This may take a while.

Report message to a moderator

Re: undo tbalespace full, which is rectified but now having big WAIT [message #546554 is a reply to message #546553]

Wed, 07 March 2012 06:28

janakors
Messages: 232
Registered: September 2009

Senior Member

@cookiemonster, and this is why i am having big WAIT graph on home page of enterprise manager?

and what can be the reason of blocking session? bad application design or normal activity as we get this problem oftenly.

Regards
Janakors

Report message to a moderator