Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Undo Tablespace growing even with retention = 0

Re: Undo Tablespace growing even with retention = 0

From: joel garry <joel-garry_at_home.com>
Date: 9 Oct 2006 15:00:59 -0700
Message-ID: <1160431259.889103.206590@k70g2000cwa.googlegroups.com>

Jagjeet Singh wrote:
> >> Why would you expect behaviour to be different? Setting retention to 0
> >> is more likely to guarantee an ORA-01555 than anything else. A 4MB UNDO
> >> tablespace is preposterously small. What is the point of what you are
> >> doing? Is there a business case hidden here somewhere or are you just
> >>playing around for purposes of personal education?
> >> --
> >> Daniel A. Morgan
>
>
> If you run the same example with 9i then the output would be different.
>
> 31 ----
> 32 end;
> 33 /
>
> -- Before Starting Undo Size : 1
> -- Undo Size with undo_retention=0 : 1 <<----- Here it is 1 mb
> -- Undo Size with undo_retention=9000 : 213
>
> PL/SQL procedure successfully completed.
>
> This is was the answer I was expecting because I am commiting after
> each single update and undo_retention is set to 0.
>
> There is no point for oracle to not to overwrite the existing space as
> record is commited and I do not
> ask to keep this undo information for other queries [
> undo_retention=0]
>
>
> In 10g, Result is different because of it's new feature "Automatic Undo
> Tuning" ..
> Oracle is ignoring my undo_retention, you can query tuned_undoretention
> column for what undo_retention was used at that time.

I suspect what you see is a side-effect of guaranteed retention. What makes you think it is ignoring? Maybe it is just seeing the 0 and saying, "Oh, well then I'll just take as much space as I need without worrying about what is there and whether I have to overwrite it?" The docs say it is ignored if you use a fixed size tablespace, which you are not. Setting it to 0 is saying use auto undo retention.

I think you may not be clear because of this fixed v. autoextend difference, see
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#ADMIN10180  "If the undo tablespace is configured with the AUTOEXTEND option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time. Again, this tuned retention period can be greater than the specified minimum retention period."

So the questions become, is there more overhead extending tablespaces or deciding to overwrite undo? When you fill up the disk or hit maxsize, would you prefer ORA-1555 or ORA-30036? Tell Oracle with guarantee retention when you create or alter the tablespace. If you use a non-zero retention with guarantee, you might get ORA-30036 when there is still space available, at least in 10gR1.

>
> And yes, It is for personal education because I feel comfort myself
> with these type of small test casese to understand the concept rather
> than to keep remember the stuff.

Actually, thanks, I wasn't too clear on this until now either. I'm part of the Just Generally Don't Use Autoextend crowd.

jg

-- 
@home.com is bogus.
Received on Mon Oct 09 2006 - 17:00:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US