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)
undo tbalespace full, which is rectified but now having big WAIT [message #546525] Wed, 07 March 2012 05:04 Go to next message
janakors
Messages: 160
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58911
Registered: March 2007
Location: Nanterre, France, http://...
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



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 Go to previous messageGo to next message
janakors
Messages: 160
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
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 Go to previous messageGo to next message
cookiemonster
Messages: 10914
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.
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 Go to previous messageGo to next message
janakors
Messages: 160
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
Re: undo tbalespace full, which is rectified but now having big WAIT [message #546556 is a reply to message #546554] Wed, 07 March 2012 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and what can be the reason of blocking session?


Now or before you killed?

Quote:
bad application design or normal activity as we get this problem oftenly


As you also have dead locks the former option is the most likely.

Regards
Michel
Re: undo tbalespace full, which is rectified but now having big WAIT [message #546562 is a reply to message #546556] Wed, 07 March 2012 06:55 Go to previous messageGo to next message
janakors
Messages: 160
Registered: September 2009
Senior Member
No, before killing
Re: undo tbalespace full, which is rectified but now having big WAIT [message #546564 is a reply to message #546562] Wed, 07 March 2012 06:59 Go to previous messageGo to next message
Roachcoach
Messages: 1198
Registered: May 2010
Location: UK
Senior Member
What is the wait on EM? user i/o? cpu? concurrency? other?
Re: undo tbalespace full, which is rectified but now having big WAIT [message #546765 is a reply to message #546564] Thu, 08 March 2012 12:37 Go to previous message
janakors
Messages: 160
Registered: September 2009
Senior Member
well, we have restarted all application servers to end the issue
Previous Topic: Slow Query
Next Topic: Disk reads
Goto Forum:
  


Current Time: Wed Aug 27 14:28:55 CDT 2014

Total time taken to generate the page: 0.09446 seconds