Re: how to shrink undo tablespace on 10GR2?
From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Wed, 20 Oct 2010 07:19:36 -0700 (PDT)
Message-ID: <301613.95692.qm_at_web120213.mail.ne1.yahoo.com>
Date: Wed, 20 Oct 2010 07:19:36 -0700 (PDT)
Message-ID: <301613.95692.qm_at_web120213.mail.ne1.yahoo.com>
I know we've discussed this in the past on here, with a bit of humor, but I am..oh what was the phrase Brandon Allen used to describe the type.... "A Temp and Undo Nazi", I believe was the term... :) A quick indicator in my book of a poor performing process is the amount of temp and/or undo that is required to complete the task- It's the "work hard instead of work smart" scenario, so someone better justify the ratio. If it has to "swap to disk" vs. performing the task in memory or if the database has to extensvely "track" where you've been as you've "trodded" through the database, again, you must justify the need to work this hard. I have a shell script that goes out and checks temp and undo usage with thresholds set in the script's env. If the threshold is breached for that database at any given run, an email goes out to the DBA's and the developers who work in that environment with a report of who is using what and who will be the winner of "Kellyn's Wall of Shame" for the day. It has been a great deterent and method to get the developers to recognize when they need to revisit logic in their code and find more efficient ways to complete a task. I'm a little concerned that a couple of the developers appear proud that they've hit the threshold. There is still one I know of that believes using large amounts of temp and undo means he's just doing a lot of complicated "cool" aggregations with the data. He's also the one most often to have huge processes go "awry" though and has not yet grasped that the two go hand in hand. I need to convince him that although we are talking "size matters", it's the geek gadget type of size matters- when it's 100's of GB's of data in your result set, you had better be going for the smallest and sleekest possible, especially when you are dealing with the largest amount of data! Kellyn Pedersen Sr. Database Administrator http://www.linkedin.com/in/kellynpedersen www.dbakevlar.blogspot.com "Go away before I replace you with a very small and efficient shell script..." ________________________________ From: Niall Litchfield <niall.litchfield_at_gmail.com> To: daniel.fink_at_optimaldba.com Cc: mccdba1_at_yahoo.com; oracle-l_at_freelists.org Sent: Wed, October 20, 2010 1:26:36 AM Subject: Re: how to shrink undo tablespace on 10GR2? It's one of the reasons why undo tablespaces should have datafiles set at a fixed size and some sort of sensible undo_retention configured. Still no-one seems to know how much undo they generate per time period anyway so perhaps that's just wishful thinking on my part. On 20 Oct 2010 01:52, "Daniel W. Fink" <daniel.fink_at_optimaldba.com> wrote: > >This is one of the 'nice features' of automatic undo. Since you are no longer >able to manually drop undo segments (in fact, you can't drop undo segments >without dropping the whole undo tablespace), all it takes is for a database to >experience high undo activity and a segment to be automatically created with a >block near the 'tail end' of the file. Since you can't drop the segment and >deallocation is spotty, you get a file with a lot of free space, but none >reclaimable because of the segment w/the high water mark block. Oracle really >should allow for the dropping of undo segments in this case. > >As others have said, your only choice is to create a new undo tablespace and >drop the old one. > > > >On 10/19/2010 08:43 AM, dba1 mcc wrote: >> >> We have 10GR2 on Linux server. I found one database "...
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 20 2010 - 09:19:36 CDT