Application goes into wait due to UNDO SEGMENT extension.

[kumasudh]

Hi ,

I facing some issues with my application which goes into waiting state quite often because of the 'UNDO segment extension'. Here is the statspack result
Top 5 Timed Events
~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 1,261 3,110 19.21
CPU time 2,781 17.18
db file sequential read 598,545 2,626 16.22
buffer busy waits 5,309 2,408 14.87
rdbms ipc reply 1,044 1,952 12.06

Our UNDO tablespace was created like follows:

CREATE UNDO TABLESPACE UNDO DATAFILE '/u03/ofa/SMS/undo01.dbf' SIZE 1100M AUTOEXTEND ON NEXT 20M MAXSIZE 2001M ONLINE BLOCKSIZE 4K;

and at the moment when the Application is waiting in 'undo segment extension' the UNDO tablespace is used only 50%.
the value of undo_retention param is "1800".

Kindly help me to figure out the reason to resolve this problem.

Thank,
Best Regards

[John Brooks_1]

What version of Oracle are you running? Is it RAC? There is a known bug in 10g RAC.

Bug 3985182 - RAC cluster hang (waits for 'undo segment extension')

[kumasudh]

Hi John,

Thanks for the reply. I am using Oracle 9.2.0.7.0 server.

Best Regards
Sudhir