Fw: RE: Okay to set undo to auto extend
Date: Tue, 6 Apr 2010 20:11:34 -0700 (PDT)
Message-ID: <225923.76335.qm_at_web114503.mail.gq1.yahoo.com>
I'm sure Mark will read this and say- "Thanks Kellyn for caring, but could you send it to the REST of the GROUP and not just me!?" :)
- On Tue, 4/6/10, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:
From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Subject: RE: Okay to set undo to auto extend
To: mark.powell2_at_hp.com
Date: Tuesday, April 6, 2010, 9:09 PM
We all know that Oracle states that we should no longer receive 1555 when we all moved from standard RBS to automatic undo, but there are still times, either due to poor coding practices, a bug or an OS process that has continued to consume undo behind the scenes.
I happen to monitor both my undo and temp space usage, if a threshold is reached for any process for either, it notifies me- my developers refer to it as "Kellyn's wall of shame..." and yes, it keeps them working hard to produce quality code. They know if they are eating up temp or undo that does not correlate to the data set they are working with, they are probably working hard and not working smart. It's simply a DBA's job to ensure we are using resources efficiently and undo can be abused, just like temp space.
Before I started monitoring our undo usage, a process, due to poor coding practice, failed and rolled back for almost two weeks before someone noticed that they'd filled up 900GB of undo set to auto-extend! If undo had been set correctly, this would never have occurred. Temp and Undo shouldn't be treated like standard tablespaces. They are not final destinations for any data- Due to this, I prefer to treat them as gages for how efficiently my folks are working with that data... :)
Kellyn Pedersen
Sr. Database Administrator
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
- On Tue, 4/6/10, Powell, Mark <mark.powell2_at_hp.com> wrote:
From: Powell, Mark <mark.powell2_at_hp.com> Subject: RE: Okay to set undo to auto extend To: "Oracle L" <oracle-l_at_freelists.org> Date: Tuesday, April 6, 2010, 1:14 PM
#yiv1463705870 #yiv86905054 DIV {
MARGIN:0px;}
I am against using auto-allocate for undo tablespaces. You should determine how much undo you need and how much you can afford to allocate, then allocate it. I do not see a point in allocating undo with extendable data files then wating it extend to max bytes. Just allocate the space to begin with.
When the undo tablespace is fixed Oracle ignores undo_retention and uses the entire tablespace as best it can before overwriting. See the documentation.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell
Sent: Monday, April 05, 2010 11:29 AM
To: po04541_at_yahoo.com; Oracle L
Subject: Re: Okay to set undo to auto extend
I'm curious as to why you'd set autoextend on and not set a maxsize? You'll still need to monitor how much disk space is being allocated but you'll have a stopping point to keep the UNDO from taking all of the available space if the odd transaction hits that consumes unusually large amounts of UNDO.
David Fitzjarrell
From: patrick obrien <po04541_at_yahoo.com>
To: Oracle L <oracle-l_at_freelists.org>
Sent: Mon, April 5, 2010 11:08:23 AM
Subject: Okay to set undo to auto extend
Oracle Admins,
My Oracle 10.2 Undo tablespace Auto Extend feature is not turned on.
That seems old school me.
Any body know of a good reason, other than to watch available disk space, to no utilize auto extend for my undo table space?
Thank you,
POB.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 06 2010 - 22:11:34 CDT