Re: undo segments online causing performance problems.

The strategy with Automatic Undo is to have no more than one transaction per undo segment if possible, which is why Oracle keeps creating new ones if there is space in the undo tablespace.

Have a check on v$resource_limit for resource_name = 'transactions'. The max_utilization column will tell you the maximum number of concurrent transactions since the database started up. The number of online undo segments should not exceed. (Although there may be a short period after restarting the database where that is not true because of the number that are automatically brought online at startup).

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 29th Nov 2005

• Original Message ----- From: "The Human Fly" To: "oracle-l" Cc: "Hemant K Chitale" Sent: Thursday, December 29, 2005 2:03 PM Subject: Re: undo segments online causing performance problems.

I do have statspack report during this situation and what is mentioned in the metalink note, exactly has been occured to us. Like, enqueue is one of the top 5 timed events and under the enqueue activity, I see, TX (transaction Exclusive) and US (Unod Segments) entries. We have reported this to Oracle Support and they are still revieiwing the TAR. My question, why oracle doesn't reuse the already online undo in place of bring more undo segs. online. Still puzziling.

--
http://www.freelists.org/webpage/oracle-l