undo_retention and undo reuse (zt from asktom)

Reviewer: hk from Finland

Original questioner asked also:
"How do I know if I actually do need 3.5G undo space
or Oracle is not reusing the undo space where it should
be?"

That is good question anyhow and it's still open here?

If undo_tablespace size is 100Megs and we run some batch job, which take 95megs
undo space. Now, batch job is done and undo_tablespace is 95% full. Then
immediately starts another transaction and it needs over 5 megs undo space. Is
undo_retention ignored and transaction reuses undo_space?

If undo_retention is not ignored and we have autoextend on, we may want decrease
undo_retention when we run many batch jobs. Or we have to configure dedicated
undo_tablespace for batch jobs.

Followup:
it is not open -- they used 3.5 gig of undo space, they need 3.5 gig of undo
space. sorry, thought that was clear.

As for the 95% question -- IF you said "retain undo for 5 minutes" and 5 minutes
hasn't gone by -- well, we won't reuse it yet.

Or third option: you could size undo to satisfy your needs year round and be
done with it?

GOTO a page to Bookmark Review | Bottom | Top
December 21, 2002
Reviewer: hk from Finland

OK, it's not open - but if you handle it like new question: how do we know if
undo_tablespace is used for "actual" undo or if it is because of retention time
hasn't gone yet?

Thank's for the answering to "95% question". Why I asked this is because i have
heard undo_retention parameter is only "directive" and undo space is reused if
undo_tablespace is full regardless of value of undo_retention parameter.

Followup:
it doesn't matter if it is used for "actual" undo or because of the retention
period -- it is all "actual" undo.

But anyway, select sum(used_ublk) from v$transaction will tell you how much undo
is being used for current, right now, transactions.

And -- allow me to clarify. IF the undo tablespace can grow to accomidate the
undo retention period -- it will. If it cannot -- it will not. So consider
this example:

ops$tkyte@ORA920> @test

shows my undo tablespace is 1m right now.

The biggest it can autoextent to is 2gig and it'll grow in 1m increments (i know
that cause I created it that way, this report doesn't show that 1m increment)

MaxPoss Max
Tablespace Name KBytes Used Free Used Kbytes Used
---------------- ------------ ------------ ------------ ------ ---------- ------
...
*UNDOTBS 1,024 960 64 93.8 2,088,960 .0
.....
------------ ------------ ------------
sum 2,001,920 1,551,936 449,984

13 rows selected.

ops$tkyte@ORA920>
ops$tkyte@ORA920> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS
ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t;

Table dropped.

my undo retention is 3 hours -- 10,800 seconds...

ops$tkyte@ORA920> create table t ( x char(2000), y char(2000), z char(2000) );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 'x', 'x', 'x' );

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
2 for i in 1 .. 500
3 loop
4 update t set x = i, y = i, z = i;
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

now each of those transactions is 6+ kbytes of undo -- 3 * 2000 byte "before
images" to save off... That should generate well over 3meg of undo by the time
it is done BUT in 500 tiny transactions.

If the undo retention period is 3hours and I have 1meg of undo and that 1meg of
undo can grow to 2gig -- Oracle will grow it and we can see that:

ops$tkyte@ORA920> set echo off

MaxPoss Max
Tablespace Name KBytes Used Free Used Kbytes Used
---------------- ------------ ------------ ------------ ------ ---------- ------
...
*UNDOTBS 5,120 4,608 512 90.0 2,088,960 .2
.....

13 rows selected.

the RBS is now 5m with 4.6 meg "used" (well, none of the undo is really used
right now, it is just going to sit there for 3 hours waiting to be reused).

Now I do this:

ops$tkyte@ORA920> create undo tablespace undotbl_new datafile size 1m;
Tablespace created.

ops$tkyte@ORA920> alter system set undo_tablespace = undotbl_new scope=both;
System altered.

ops$tkyte@ORA920> drop tablespace undotbs;
Tablespace dropped.

ops$tkyte@ORA920> exec print_table( 'select * from dba_data_files where
tablespace_name = ''UNDOTBL_NEW'' ' );
FILE_NAME :
/usr/oracle/ora920/OraHome1/oradata/ora920/o1_mf_undotbl__z0936pcx_.dbf
FILE_ID : 2
TABLESPACE_NAME : UNDOTBL_NEW
BYTES : 1048576
BLOCKS : 128
STATUS : AVAILABLE
RELATIVE_FNO : 2
AUTOEXTENSIBLE : NO
MAXBYTES : 0
MAXBLOCKS : 0
INCREMENT_BY : 0
USER_BYTES : 983040
USER_BLOCKS : 120
-----------------

PL/SQL procedure successfully completed.

And I rerun the test:

ops$tkyte@ORA920> drop table t;
Table dropped.

ops$tkyte@ORA920> create table t ( x char(2000), y char(2000), z char(2000) );
Table created.

ops$tkyte@ORA920> insert into t values ( 'x', 'x', 'x' );
1 row created.

ops$tkyte@ORA920> begin
2 for i in 1 .. 500
3 loop
4 update t set x = i, y = i, z = i;
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> set echo off
old 29: order by &1
new 29: order by 1

% MaxPoss Max
Tablespace Name KBytes Used Free Used Kbytes Used
---------------- ------------ ------------ ------------ ------ ------- ------
*UNDOTBL_NEW 1,024 1,024 0 100.0 0 .0

13 rows selected.

and here, we can see that the undo tablespace is still 1m. Oracle could not
grow the undo -- but it did not fail the transactions.

So, in that respect, yes, the undo retention can be thought of as a "desire" --
if there is no way to get the undo space AND the undo space can be reused - it
will reuse it. If the datafiles are autoextend or the undo tablespace is big
enough all by itself, it will not reuse it

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-795139/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-795139/