Re: How to clear UNDOTBS- Slightly OT
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: ecandrietta@xxxxxxxxx, wellmetus@xxxxxxxxx
- Date: Mon, 18 Jan 2010 09:26:15 -0800 (PST)
OK, I'm just going to ask this of the DBA's here. I'm going to be perfectly
honest here- If I had a 100MB database with a 25GB undo tablespace, my ADHD,
slightly obsessive compulsive, little DBA self would be going it bit crazy with
that... :) I do have an APEX system that uses a ton of undo...and I have put
the challenge to the developer to correct the code that is utilizing that undo
inefficiently, printing out the fixes that are needed to stop doing so- but I
still have to look at a 10GB undo on a 35GB database, which annoys me, which
means I get to tease and annoy him... :)
I was "raised" as a DBA, that when I had a large temp and undo tablespace in
comparison to my database size, (and keep in mind, it's not actually size that
is the issue here, it's the size utilized per transaction, but when it's huge
overall, that's a pretty good indicator...) then I need to examine WHAT exactly
is using them both.
I was taught that a developer and user is going to see temp and undo just like
any other tablespace- it runs out of space, you add more, but a DBA should
NEVER see these two this way. Tune your temp usage, verify that your undo is
being utilized efficiently and you notice over all that your database is
running efficiently as well. Otherwise my database is just working hard and
not working smart.. It means that less is working in memory, less is fitting
in memory, less is using the right objects or taking the "long route" to what
it needs in my database and more I/O which makes Kellyn a grumpy DBA... :)
How many DBA's inspect what is utilizing temp and undo? How many DBA's see a
large temp and/or UNDO tablespace as a red flag in a database?
Kellyn Pedersen
Multi-Platform DBA
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 Mon, 1/18/10, Roger Xu <wellmetus@xxxxxxxxx> wrote:
From: Roger Xu <wellmetus@xxxxxxxxx>
Subject: Re: How to clear UNDOTBS
To: ecandrietta@xxxxxxxxx
Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Monday, January 18, 2010, 10:02 AM
I have an issue where the undo tablespace datafile is 25 GB in size with less
than 100 MB is actually used but we need to backup the 25 GB datafile on every
level 0 RMAN. My solution is to use RMAN compress on this datafle and it works
great. Instead of 25GB, I am seeing some MB only. Thanks.
On Mon, Jan 18, 2010 at 7:26 AM, Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
Hi,
Other related posts:
