SEO

Hi All,

Can you please help me to understand this.
I have set created new undo tablespace of 4 mb on my test database.

I set undo_retention = 0 and was updateding a table with 1 records and
commiting each time.
But still my undo tablespace is growing.

here is the test case

SQL>
SQL> r
1 select file_name,round(bytes/1048576) size_mb , autoextensible
2* from dba_Data_files where tablespace_name = 'UNDO'

FILE_NAME SIZE_MB AUT

-------------------------------------------------------- ---------- ---

/TEST/datafile/o1_mf_undo_2lgqkhgv_.dbf 4 YES


SQL>
SQL> create table t as select rownum a from dual;

Table created.

SQL> get und_test
1 declare
2 v_time int;
3 v_undo int;
4 begin
5 select sum(bytes)/1048576 into v_undo from dba_segments where
segment_type like '%UNDO%';
6 ---
7 dbms_output.put_line(' -- Before Starting Undo Size : '||v_undo );
8 execute immediate ' alter system set undo_retention=0 ';
9 ----
10 v_time := dbms_utility.get_time ;
11 while ( ((dbms_utility.get_time - v_time)/100) < 300 )
12 loop
13 update t set a=a;
14 commit;
15 end loop;
16 ----
17 select sum(bytes)/1048576 into v_undo from dba_segments where
segment_type like '%UNDO%';
18 dbms_output.put_line(' -- Undo Size with undo_retention=0 :
'||v_undo );
19 ----
20 ----- second time with undo_retention = 9000
21 execute immediate ' alter system set undo_retention=9000 ';
22 v_time := dbms_utility.get_time ;
23 while ( ((dbms_utility.get_time - v_time)/100) < 300 )
24 loop
25 update t set a=a;
26 commit;
27 end loop;
28 ----
29 select sum(bytes)/1048576 into v_undo from dba_segments where
segment_type like '%UNDO%';
30 dbms_output.put_line(' -- Undo Size with undo_retention=9000 :
'||v_undo );
31 ----
32* end;
33 /
-- Before Starting Undo Size : 1

-- Undo Size with undo_retention=0 : 251

-- Undo Size with undo_retention=9000 : 476


PL/SQL procedure successfully completed.

Using 10g on linux 4.0
Undo is LMT manual segment space management.
No. of Transaction : I fixed the loop for 300 seconds.

This tablespace's size increased to 251 mb with undo_retention=0 and
with
unto_retention=9000 it reach to 476 mb.

Regards,
Jagjeet Singh

Jagjeet Singh wrote:
> Hi All,
>
> Can you please help me to understand this.
> I have set created new undo tablespace of 4 mb on my test database.
>
> I set undo_retention = 0 and was updateding a table with 1 records and
> commiting each time.
> But still my undo tablespace is growing.
>
> here is the test case
>
> Regards,
> Jagjeet Singh

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
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

> Undo is LMT manual segment space management.
> No. of Transaction : I fixed the loop for 300 seconds.
>
> This tablespace's size increased to 251 mb with undo_retention=0 and
> with
> unto_retention=9000 it reach to 476 mb.
>
> Regards,
> Jagjeet Singh
>

Even though your UNDO_RETENTION is set to 0 seconds, you will still
generate undo. And that undo will require space until that transaction
is committed or rolled back. At that point, Oracle *may* overwrite that
undo with the next transaction, provided UNDO_RETENTION seconds have passed.

In your PL/SQL block, you have an UPDATE statement. Obviously, this
update statement is generating undo.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

>> 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.

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.

Regards,
Jagjeet Singh

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...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.