continued
June 17, 2002 - 1pm Central time zone
Reviewer: Andre from Brazil
I am saying that because we used to size RBS extents according to some rules to make the best of
them, like using the 20-or-so-extents-per-RBS rule (which is statistically proven to enhance
performance). Using AUM we just have no control over this. I am affraid we may encounter some
side-effect. Just like AUTOALLOCATE isn't the best answer if you know what you are doing, I am
affraid UAM is not either.
Some idiosyncracies with AUM
June 17, 2002 - 4pm Central time zone
Reviewer: Connor from UK
I very much like the concept of AUM - still a couple of things maybe for Oracle to iron out (not
that I've tested them with 9.2 yet).
a) You always start with (is it 8?) undo segments. In a heavy system this automatically ramps up
to 'n', but as soon as you shutdown, you're back down to 8 and some associated contention as it
ramps it way back up to 'n'
b) seems to be 'aggressive' on enforcing the undo retention. As soon as some rb info expires, it
gets excised out, so (imho) you tend to get a fair few more shrinks that an equivalent manual
scheme.
That aside, its a lovely way to do rollback - any maybe 9.2 will reveal some better things.
Connor
continued -- To Connor
June 17, 2002 - 5pm Central time zone
Reviewer: Andre from Brazil
Yep, Connor. I think, after all, the new undo manageability makes up for any eventual overhead.
Automatic Undo Management
October 19, 2002 - 2am Central time zone
Reviewer: Natalia
I have two confusion relating to AUM.
1)UNDO_MANAGEMENT=AUTO, if UNDO_TABLESPACE parameter is omitted and there is also no undo
tablespace then the instance starts and use rollback segments from SYSTEM tablespace. Does the
instance will start in AUM and would the automatic undo will be active?
2) suppose UNDOTBS1 is switched to UNDOTBS2 and due to some pending transactions its status is
PENDING OFFLINE thenonly the existing transactions can continue to use it so what about the new
user transactions? will they use new undotbs2?
Thanks
Followup October 19, 2002 - 10am Central time zone:
1) i have confusion about the question. I cannot parse that last sentence...
2) do what I would do -- test it! Anyway, it would switch to the new tblspace, all new
transactions would use the new tablespace and as soon as the old transaction in undotbs1 was done,
it would finish offlining the entire old undo tablespace rollback segments.
correction
October 19, 2002 - 4pm Central time zone
Reviewer: Natalia from Pakistan
sorry for the ambiguity in my question (1).
actually I want to know that will the instance use SYSTEM tablespace for automatic undo management?
Thanks
Followup October 20, 2002 - 9am Central time zone:
it won't -- not for auto (that is set to an UNDO tablespace and system isn't an undo tablespace).
Now, that said, if the tablespace the undo is for is offlined or otherwise made unavailable, we
will as normal copy the undo information into the system rollback segments so we can rollback any
outstanding transactions upon re-onlining or recovering that tablespace.
set transaction use rollback segment
March 17, 2003 - 7pm Central time zone
Reviewer: Arun Gupta from Harrisburg, PA USA
If I use AUM, what should I do with SET TRANSACTION USE ROLLBACK SEGMENT statements in the code.
Thanks
Followup March 17, 2003 - 7pm Central time zone:
set undo_suppress_errors to true so that the statement is silently ignored by the database.
undo_suppress_error
March 17, 2003 - 10pm Central time zone
Reviewer: Chandru from Harlingen, TX
Tom,
Please see below.
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> show parameter undo_suppress_errors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_suppress_errors boolean FALSE
SQL> set transaction use rollback segment SYSSMU1$;
set transaction use rollback segment SYSSMU1$
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
SQL> alter session set undo_suppress_errors = true;
Session altered.
SQL> set transaction use rollback segment SYSSMU1$;
Transaction set.
***********************
It looks like the database does not "ignore" it but gives the feedback as transaction is set. Is it
misleading? I tried the same by setting optimal parameter as below and the behavior is same.
SQL> alter session set undo_suppress_errors = false;
Session altered.
SQL> alter rollback segment SYSSMU8$ storage (optimal 1M);
alter rollback segment SYSSMU8$ storage (optimal 1M)
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
SQL> alter session set undo_suppress_errors = true;
Session altered.
SQL> alter rollback segment SYSSMU8$ storage (optimal 1M);
Rollback segment altered.
Even though it suppresses the error, the feed back is like the database is doing what is asked.
Could you please clarify? However, if I checked the v$rollstat as shown below, optimal is not
actually set as it gives null value for optsize even though it said rollback segment altered.
SQL> select usn,optsize,status from v$rollstat;
USN OPTSIZE STATUS
---------- ---------- ---------------
0 ONLINE
1 ONLINE
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE
10 ONLINE
11 rows selected.
****************************************
Followup March 18, 2003 - 7am Central time zone:
that is what I meant by "ignores it". The database is acting like a teenager..
You say to the database:
"do this thing"
Database replies
"Yeah, whatever"
You ask:
"so did you do it"
Database replies
"Yeah, sure I did" (lying through its teeth)
That would be the very definition of ignoring you. It ignores the command.
Great Reply!
March 18, 2003 - 11am Central time zone
Reviewer: Chandru from Harlingen, TX
Thanks as always for your response. I am clear now.
Chandru
Ooo! Someone has a teenager!
March 19, 2003 - 12pm Central time zone
Reviewer: A reader
Very informative
April 23, 2003 - 10am Central time zone
Reviewer: Robert
Is the default undo tablespace installed when running dbca sufficient for a database or does it
still need to be checked for files/size/undo retention etc?
Basically, if you were not to go with the default what would you recommend changing?
Cheers
Robert
Followup April 23, 2003 - 7pm Central time zone:
well, for the GUI inclined you would fire up OEM during your peak processing times and ask OEM "if
I wanted an undo retention period of 1 hour, how much disk do I need to buy".
You can do the same by using the v$ views and such to size it out yourself.
The default is ok since it is done with autoextend datafiles -- so given you have enough freespace,
it'll grow as needed. Most implementations I would suspect might be inclined to size it a little
more "precisely" using simulations and load tests -- so they know how much to buy.
undo retention -- is personal. only you know what it needs be for your system.
file size -- is again, something you do need to know, but OEM will do it for you or you can derive
the information from the database itself under load.
questions on rollback segments
July 8, 2003 - 3pm Central time zone
Reviewer: A reader from ca
Hi Tom
Thanx for your continuing support to Oracle community!!
"I've always said (over and over I've
said) -- you gotta configure sufficient rollback for the job you do. undo tablespaces help enforce
that 100%."
Could you kindly explain how undo tablespaces help enforce the sufficient rollback configuration
automatically 100%?
Could you also comment on what happens if we have operations (say a Foreign Key cascade delete)
that deletes tables with multiple rows - each of which have clobs in them ( each clob could contain
4G of data) Won't this generate lots of undo - resulting in one needing to configure huge RBSs just
for this purpose?
Is this a disadvantage of using clobs?
Followup July 8, 2003 - 3pm Central time zone:
they use an retention period. assuming you've the space on disk physically to hold it, they grow
to accomidate you.
look at the amount of UNDO generated by a delete on a clob, you might be surprised ;) clobs are
undone a little differently (eg: the lob index changes are undoable -- the lob data just isn't
recycled until you commit - no undo for 4gig)
ops$tkyte@ORA920> create table t ( x int, y clob );
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_clob clob;
3 l_data long default rpad( '*', 32760, '*' );
4 begin
5 insert into t values ( 1, empty_clob() )
6 returning y into l_clob;
7
8 for i in 1 .. 1000
9 loop
10 dbms_lob.writeAppend( l_clob, length(l_data), l_data );
11 end loop;
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> select x, dbms_lob.getlength(y) from t;
X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
1 32760000
ops$tkyte@ORA920> select used_ublk from v$transaction;
no rows selected
ops$tkyte@ORA920> delete from t;
1 row deleted.
ops$tkyte@ORA920> select used_ublk from v$transaction;
USED_UBLK
----------
14
ops$tkyte@ORA920> commit;
Commit complete.
thank you!
July 8, 2003 - 8pm Central time zone
Reviewer: A reader
This is very interesting -
Could you kindly elaborate on "clobs are undone a little differently (eg: the lob index changes are
undoable -- the lob data just isn't recycled until you commit - no undo for 4gig)"
If there is no undo for 4gig - what are the implications
for the data value - I am sure it is retained for
rollback cases. Why is it that in the above example of
yours the undo generated is only 14 blocks for a clob
tha has approx 30mb of data.
Thanx!!!
Followup July 9, 2003 - 10am Central time zone:
we just unlink the pointers to the lob data -- it remains safely in the lob segment, if we need to
rollback -- we just point to it again.
great !! thank you so much!!!
July 9, 2003 - 12pm Central time zone
Reviewer: a fan
undo for insert of clob
August 17, 2003 - 5pm Central time zone
Reviewer: A reader
In the above experiment if I do a select for undo space usage before the commit - I get the undo
generated for the insert operation of clob - in my experiment I got a number
greater than that for the delete. Usually undo for a delete is the maximum. Why is this not true
for clobs?
Thanx!
Followup August 17, 2003 - 7pm Central time zone:
because of the way lobs are managed -- the insert has undo for the lob index, the delete likewise.
inserts and deletes from indexes are each "intense" and inserts can be much harder then deletes
(splits and such).
The lob data itself is versioned in the lob space, not in undo.
Re: undo for insert of clob
August 17, 2003 - 5pm Central time zone
Reviewer: A reader
Is it because in the above case, we did 1000 separate inserts that would perhaps generate more undo
as compared
to one insert. Since there was only one delete operation,
this may be a valid point?
thanx!
thank you Tom!
August 17, 2003 - 8pm Central time zone
Reviewer: A reader
"because of the way lobs are managed -- the insert has undo for the lob index,
the delete likewise. inserts and deletes from indexes are each "intense" and
inserts can be much harder then deletes (splits and such).
The lob data itself is versioned in the lob space, not in undo"
Could you elaborate on the following points:
1. this implies that each lob has an index associated with it?
2. deletes from indexes can be less intense than inserts
because inserts can result in splitting of blocks
whereas deletes need not. How does that affect the
RBS generated? Isn't RBS a function of data that changes
in the end - not the intermediate results such
as "splitting" of blocks?
3. Kindly elaborate a little more on what you meant by "The lob data itself is versioned in the lob
space, not in undo"? What is versioning here?
Thanx a million for your awesome site and unparalleled
generosity!
Followup August 18, 2003 - 7am Central time zone:
1) thats a fact
2) to undelete an index entry is pretty easy. to un-insert it, harder. The actual LOB data
itself, well, that doesn't need to go into rbs. it is sort of like an "insert append" operation.
only the DML that affects the management of space (the index mods) needs to be rolled back, the
actual data that was inserted (the lob) doesn't -- we just "unpoint" to it.
to rollback a lob delete -- we rollback the changes to the lob index, lob "reappears".
to rollback a lob insert -- we rollback the changes to the lob index, lob "unappears"
3) just like the versioning we do in rbs for "regular" data. we keep multiple-copies of lob data
in the lob area.
sorry stil not clear:(
August 18, 2003 - 12pm Central time zone
Reviewer: A reader
"to rollback a lob delete -- we rollback the changes to the lob index, lob
"reappears".
to rollback a lob insert -- we rollback the changes to the lob index, lob
"unappears"
"
Insert operations int indexes are more intense due to splitting of blocks, right? Does this mean
that un-inserting it involves rejoining of split blocks - which
makes it more "intense" - Hence RBS of insert has more
data?
Thanx!!
Followup August 18, 2003 - 3pm Central time zone:
ops$tkyte@ORA817DEV> create table t as select * from all_objects where rownum < 100;
Table created.
ops$tkyte@ORA817DEV> create index t_idx on t(object_name);
Index created.
ops$tkyte@ORA817DEV> analyze index t_idx validate structure;
Index analyzed.
ops$tkyte@ORA817DEV> @printtbl8 'select * from index_stats';
HEIGHT : 1
BLOCKS : 64
NAME : T_IDX
PARTITION_NAME :
LF_ROWS : 99
LF_BLKS : 1
LF_ROWS_LEN : 3821
LF_BLK_LEN : 7996
BR_ROWS : 0
BR_BLKS : 0
BR_ROWS_LEN : 0
BR_BLK_LEN : 0
DEL_LF_ROWS : 0
DEL_LF_ROWS_LEN : 0
DISTINCT_KEYS : 99
MOST_REPEATED_KEY : 1
BTREE_SPACE : 7996
USED_SPACE : 3821
PCT_USED : 48
ROWS_PER_KEY : 1
BLKS_GETS_PER_ACCESS : 2
PRE_ROWS : 0
PRE_ROWS_LEN : 0
-----------------
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> set verify on
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t select * from all_objects;
26833 rows created.
ops$tkyte@ORA817DEV> rollback;
Rollback complete.
ops$tkyte@ORA817DEV> analyze index t_idx validate structure;
Index analyzed.
ops$tkyte@ORA817DEV> @printtbl8 'select * from index_stats';
HEIGHT : 2
BLOCKS : 192
NAME : T_IDX
PARTITION_NAME :
LF_ROWS : 99
LF_BLKS : 176
LF_ROWS_LEN : 3821
LF_BLK_LEN : 7996
BR_ROWS : 175
BR_BLKS : 1
BR_ROWS_LEN : 2768
BR_BLK_LEN : 8028
DEL_LF_ROWS : 0
DEL_LF_ROWS_LEN : 0
DISTINCT_KEYS : 98
MOST_REPEATED_KEY : 1
BTREE_SPACE : 1415324
USED_SPACE : 6589
PCT_USED : 1
ROWS_PER_KEY : 1.01020408163265306122448979591836734694
BLKS_GETS_PER_ACCESS : 3.00510204081632653061224489795918367347
PRE_ROWS : 0
PRE_ROWS_LEN : 0
-----------------
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> set verify on
we didn't "unsplit" them, we in effect deleted them from the index. see the size increase...
hmmm..
August 18, 2003 - 3pm Central time zone
Reviewer: A reader
"we didn't "unsplit" them, we in effect deleted them from the index. see the
size increase... "
If we did not "unsplit" them, then the rollback of insert
should not be very intensive compared to delete rollback?
sorry not getting it...
thanx a lot for your tireless efforts!!
September 23, 2003 - 2pm Central time zone
Reviewer: A reader from orlando,FL
Tom,
Pls validate my understanding of an AUM environment:
a) there can be only one undo tablespace
b) None of the ASSM parameters are applicable to the undo tablespace.
Thanks as always...
Followup September 23, 2003 - 2pm Central time zone:
a) correct, just stripe it.
b) correct, it is a big circular buffer -- no segment space to manage really. it is not like a
table.
beginner question
November 11, 2003 - 12pm Central time zone
Reviewer: Fernando from Madrid, Spain
I'm a programmer, not a DBA but I've been told to find out how to solve a situation.
Testing a developing environment DB, accidentally a 3 million records transaction (that rarely
lasted around 12 hours when other times had only lasted around 3 hours) made our UNDO tablespace
datafile increase so much that the machine where our DB is became 100% full, when it used to be
only 92% (we share space with other applications).
As our application will never allow such "big" transactions to happen in the production
environment, I've been told to find a way to "rebuild" the undo tablespace in order to have a
datafile limit (something like a 25% increase of the initial size), so that if some "accident"
happens again the transaction will fail but the disk space will not increase so much.
(I don't know if what I've been asked to to is a good idea)
In the create database stament whe have the following undo tablespace:
UNDO TABLESPACE undo DATAFILE 'UNDO.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
Another problem, we have now, is that we need releasing disk space, if we want to go on working,
and we can´t resize the undo tablespace to its initial size, why?
Thanks in advance.
Followup November 11, 2003 - 1pm Central time zone:
to resize an undo tablespace:
a) create a new one of the desired size
b) alter system to switch to it
c) when the last active transaction finished in the old, drop it.
if you want the maxsize to be 125% of the orginal size, use a maxsize of 125% of the original, NOT
unlimited.
undo retention
December 24, 2003 - 8pm Central time zone
Reviewer: Reader
Is there any performance issue if I set undo_retention to a large value? My understanding is that I
should provide adequate undo space to support undo_retention. Other than the need for more disk
space for undo, would a larger value for the retention affect the database performance in any way?
Thanks.
Followup December 25, 2003 - 10am Central time zone:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=BykX9.29812%24jM5.77141%40newsfeeds
.bigpond.com&rnum=8&prev=/groups%3Fq%3D%2B%2522large%2Brollback%2Bsegments%2522%2Bgroup:comp.databas
es.oracle.*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3DBykX9.29812%2524jM5.77141%2540newsf
eeds.bigpond.com%26rnum%3D8
there can be some overhead, yes.
if the rollbacks are kept "small", we can reuse (new) a block of undo without ever having written
it -- that is, we could cycle through the RBS data in memory and not really write it out.
However, in order to not get other errors (1555) you need to have more then sufficient space
allocated.
read that thread, you'll see all of the viewpoints. For me, the ease -- and error free (no 1555's)
processing I gain from having more then adequately sized undo more than offsets anything else. We
can tune IO (and it happens in the background, dbwr is the guy waiting, not us in general)
Reader
December 26, 2003 - 7am Central time zone
Reviewer: A reader
Care to give us the subject title in comp.database.oracle
Please shed some light on multiple undo tablespaces
January 23, 2004 - 6pm Central time zone
Reviewer: Moorthy Rekapalli from Atlanta, GA USA
Tom,
First of all, thank you very much for sharing your knowledge with the Oracle community. For the
last 3 years, you became my virtual Guru.
Please explain the significance of undo_tablespace parameter when there is more than one tablespace
that shows UNDO in dba_tablespaces.contents column.
Thanks,
Moorthy.
Followup January 24, 2004 - 8am Central time zone:
just means you created more than one.
only one is being used, the others are offline and unavailable.
commit time in dba_undo_extents
February 19, 2004 - 6pm Central time zone
Reviewer: reader
Tom, please see below. Why don't I see the commit time? Thanks.
SQL> create table t (x int) tablespace users;
Table created.
SQL> insert into t values(1);
1 row created.
SQL> /
1 row created.
SQL> commit;
SQL> select SEGMENT_NAME,COMMIT_JTIME,COMMIT_WTIME from dba_undo_extents;
SEGMENT_NAME COMMIT_JTIME COMMIT_WTIME
------------------------------ ------------ --------------------
_SYSSMU1$
_SYSSMU1$
_SYSSMU1$
_SYSSMU1$
_SYSSMU1$
_SYSSMU2$
_SYSSMU2$
_SYSSMU2$
_SYSSMU3$
_SYSSMU3$
_SYSSMU4$
_SYSSMU4$
_SYSSMU5$
_SYSSMU5$
_SYSSMU5$
_SYSSMU6$
_SYSSMU6$
_SYSSMU6$
_SYSSMU7$
_SYSSMU7$
_SYSSMU7$
_SYSSMU8$
_SYSSMU8$
_SYSSMU8$
24 rows selected.
Followup February 19, 2004 - 7pm Central time zone:
create or replace view dba_undo_extents (
OWNER
,SEGMENT_NAME
,TABLESPACE_NAME
,EXTENT_ID
,FILE_ID
,BLOCK_ID
,BYTES
,BLOCKS
,RELATIVE_FNO
,COMMIT_JTIME
,COMMIT_WTIME
,STATUS
) as
select /*+ ordered use_nl(e) use_nl(f) */
'SYS', u.name, t.name,
e.ktfbueextno, f.file#, e.ktfbuebno,
e.ktfbueblks * t.BLOCKSIZE, e.ktfbueblks, e.ktfbuefno,
nullif(e.ktfbuectm, e.ktfbuectm),
nullif(e.ktfbuestt, e.ktfbuestt),
decode(e.ktfbuesta, 1, 'ACTIVE', 2, 'EXPIRED', 3, 'UNEXPIRED',
'UNDEFINED')
.....
they are always NULL -- they are deprecated and not filled in. They were far too expensive to
maintain.
Manual and Automatic Undo in the same instance?
February 23, 2004 - 2am Central time zone
Reviewer: Matt from Australia
Is it possible to have an instance use automatic undo and also manually allocate specific RBS for
large TX's?
We have a third party product that has specific RBS requirements that is currently in the same
instance as several application schemas. The preference is to use automatic undo, but without
impacting the third party product that required specific large named RBS for some of it's TX's.
Can automatic UNDO handle diverse undo requirements? ie: an OLTP type requirements alonsgside a
batch load/processing requiremnt?
Followup February 23, 2004 - 7am Central time zone:
AUTO is always binary.
You are either in AUTO or you are in MANUAL. You pick.
You can set undo_suppress_errors to let the 3rd party issue "set transaction use rollback..."
statements and have them silently succeed. It'll use auto undo mgmt.
Yes, you can use it for "diverse" requirements. If you don't like that they get "big" -- you can
always rotate them.
when "too big for your liking"
create a new undo tablespace
alter system to point to it
when all active transactions in old finish drop it
end when.
can undo tablespace be ASSM? Thanks.
February 24, 2004 - 7pm Central time zone
Reviewer: reader
Followup February 25, 2004 - 8am Central time zone:
no, and give the circular buffer effect (it is not like a data segment at all) it would not really
"make sense". the undo data is stored in a big circular list with the tail chasing the head.
table data isn't stored in that fashion. there are no freelists, no pctfree/pctused, etc on an
undo segment -- space isn't managed in a heap like normal stuff.
from the sql ref:
Restrictions on undo tablespaces:
....
The only clauses you can specify for an undo tablespace are the DATAFILE clause and the
extent_management_clause to specify local extent management....
rollback segments placement
February 25, 2004 - 3pm Central time zone
Reviewer: K U from Nashville, TN USA
Previously (<=8i), we could define rollback segment selection order using 'rollback_segments'
parameter in initSID.ora file to distribute io_load / reduce inode_lock contention.
With 9i With Automatic Undo Management, I find the rollback segments created in a single datafile
(when it had enough space for them all). I would rather see them distributed across multiple
datafiles.
Also, for an UNDO tablespace, I cannot specify the storage parameters. (Thought that by defining
minextents or minimum extent clause I could cheat the system into limiting rbs per datafile - But,
'default storage' clause errors out.)
Docs did not help me. Can you, please?
ALso, what, do you think, is the best practice in this regard?
Regards
KU
Followup February 25, 2004 - 5pm Central time zone:
add multiple datafiles to your undo tablespace then? they will naturally "stripe" themselves.
or better yet (much much better yet), much much better yet, use striping at the OS/raid level (or
asm in 10g)
and the init.ora just told Oracle what RBS's were available to that instance, it was not a load
balancing/'reduce inode lock contention (except with OPS but there it was cross instance contention
it was working on)
as for the extent sizing and so on -- AUTO is AUTO. It does what it does. AUTO means "fire and
forget, you don't specify stuff"
Manual is manual. Manual means you can specify away to your hearts content.
You choose - manual or auto. Like you do when you buy a car (I myself prefer manual transmissions
but automatic undo)...
ora-1555 in aum
April 14, 2004 - 7pm Central time zone
Reviewer: A reader
On a 9.2 database, I have
undo_management = AUTO
undo_retention = 86400 # 1 day
undo_tablespace = RBS
The rbs tablespace is sized at 2G. I know that this is more than sufficient to satisfy the
undo_retention requirement. But I still got a ORA-1555 on one of my materialized view refresh jobs
that reads data across a dblink. As soon as I got the error, I went in and manually did the
snapshot refresh and it worked fine. Funnily enough, the ORA-1555 said
ORA-12012: error on auto execute of job 101
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
1. Why is the rbs segment name ""?
2. I thought that if I have sufficient rbs tablespace and large undo_retention, that is the end of
ORA-1555? Why did I still get one?
Thanks
Followup April 15, 2004 - 8am Central time zone:
the 1555 is coming from the REMOTE database, not yours.
You need to talk to the DBA of the remote site, they have undersized RBS's.
How can you tell?
April 15, 2004 - 9am Central time zone
Reviewer: A reader
How can you tell that the ORA-1555 is coming from the remote database and not my database? I dont
see a 'preceding line from ...' that indicates this?
Thanks
Followup April 15, 2004 - 9am Central time zone:
because a 1555 is caused by a READ (only by READS) and the MV refresh is reading from -- well --
the remote site, not the local site.
Hence the 1555 comes from the remote (and yes, the fact that the rbs name is not visible indicates
that the 1555 happened on remote, else the name would be visible)
UNDO with batch jobs
May 12, 2004 - 1pm Central time zone
Reviewer: Arun Gupta from Harrisburg, PA USA
Tom,
Our databases require undo tablespace of 3GB when running batch jobs. During the day, an undo
tablespace of 100MB is enough. Should we create a 3GB undo tablespace and use it all the time or is
there a better scheme? We are using AUM, 9ir2. In the rollback segment days, the directive was to
create a large rollback segment for long transaction, assign the long transaction to this large RBS
and drop the large RBS once done.
Thanks
Followup May 13, 2004 - 8am Central time zone:
I would size it once and be done with it. why "create", "drop", "create", "drop" over and over.
It is not like you are saving space or anything.
the A in AUM stands for Auto. Auto should be "less manual".
transacions spanning in more than one undo segment
May 21, 2004 - 4pm Central time zone
Reviewer: Marvin
Hi
When we used to use RBS a transaction can only make use of one single rollback segment.
Using undo segments will transactions be able to use more than one undo segment?
Followup May 22, 2004 - 3pm Central time zone:
undo segments are rollback segments -- just automagically managed ones. Oracle decides how many
undo (rollback) segments and how big they should be.
The rules are still the same, your transaction will be in "a" rollback/undo segment.
Thanks for all you share!
May 27, 2004 - 11am Central time zone
Reviewer: Evan from Atlanta, Ga
Confusion Confusion
April 4, 2005 - 5pm Central time zone
Reviewer: aru from NZ
Hi Tom,
Nice thread this!
you said:-
"Now, that said, if the tablespace the undo is for is offlined or otherwise made unavailable, we
will as normal copy the undo information into the system rollback segments so we can rollback any
outstanding transactions upon
re-onlining or recovering that tablespace. "
I was under the impression that if there is any undo information in the undo segments then the undo
tablespace cannot be taken offline. Or is that ACTIVE (sorry! not shouting) information.
Suppose I am using a undo segment and eventually commit my transaction, then the retention policy
kicks in and keeps the transaction information for a stipulated amount of time.
Am I right? Now if I want to make the undo tablespace offline (before the retention policy
expires), the undo information is copied to the system tablespace.
Is my new understanding right? Sorry if it's not worded nicely but my mind is as clear as mud at
the moment.
Thanks Tom,you're GREAT
Regards,
ARU.
Followup April 4, 2005 - 9pm Central time zone:
you can kick things offline. tablespaces can be offlined "brutally"
Be wary of SMU prior to 9.2.0.5
April 25, 2005 - 11am Central time zone
Reviewer: Doug Burns from Edinburgh
Just a word of caution if you're planning to use SMU and you're not on 9.2.0.5 ...
I've just worked at a site where we ran into a very messy corruption problem on a very important
(aren't they all?!?!), extremely large, high transaction database. It turns out that there's a bug
prior to 9.2.0.5. This is from the related TAR -
"Oracle Development has found the root cause of the corruption problem which crippled your
database. They have reproduced the problem on an in-house database,
thereby confirming the problem and getting the identical results as you experienced on your
database.
The problem occurs when an undo segment is being transitioned from an offline status to an
online status by a foreground process while the SMON process is doing its regular undo segment
maintenance. A small timing window exists when the foreground process releases the latch covering
the undo segment and SMON picks up that undo segment and changes the undo segments status to
invalid and releases its space. The foreground process reacquires the latch without rechecking
the undo segments status field.
This problem is fixed in 9.2.0.5 as that piece of code was rewritten to eliminate the timing
window."
To be crystal clear, I'm not advocating avoiding SMU, but make sure that you're using an up to date
version of the software. I'm also trying to save others from the pain of the whole experience!
(There's more on this saga at http://doug.burns.tripod.com/oracle
HTH,
Doug
Better Link
September 9, 2005 - 12pm Central time zone
Reviewer: Doug from Edinburgh, UK
Tom,
I noticed a few visitors coming from here on Statcounter and thought I'd better add more
appropriate links because the original postings are hard to find.
http://doug.burns.tripod.com/oracle/index.blog?entry_id=1033359 http://doug.burns.tripod.com/oracle/index.blog?entry_id=1076404 http://doug.burns.tripod.com/oracle/index.blog?entry_id=1076486
Hope that's okay.
Cheers,
Doug
Followup September 9, 2005 - 12pm Central time zone:
No problem, always use the permalink in the blogs myself ;)
SYSTEM RBS in AUM?
October 4, 2005 - 12pm Central time zone
Reviewer: Rob H from Winnipeg :D
I know I'm going to get flamed over this, but why is there a SYSTEM RBS in a 9i database with AUM?
It is very small. Is it used only in the database creation?
Followup October 4, 2005 - 5pm Central time zone:
there can be times the undo tablespace isn't there. and system rbs is used in some in doubt
distributed transactions
why would you get flamed?
How to Check?
October 6, 2005 - 12pm Central time zone
Reviewer: Rob H from Winnipeg :D
I thought I might get flamed for asking a question you've already answered. I go out of my way to
try to review answers you've posted so that you're not asking it again, that and it seem like a
noobie questions.
Anyways, do you think the system rbs can/should be removed? I assume your answer is that its
small an why break something thats working. This is more of a curiousity of 'why' its there(if
there is UNDO tbs) and 'what' it does for the database(maybe try something to cause it into
action)? Just trying to learn and understand.
Followup October 6, 2005 - 1pm Central time zone:
no, it shouldn't be removed. it can grow if it needs to. consider it part of the "dictionary" if
you will.
DBA_UNDO_EXTENTS and v$rollname
October 15, 2005 - 2pm Central time zone
Reviewer: Sami
Dear Tom,
DBA_UNDO_EXTENTS has 1421 undo segments however v$rollname has only 11 and gv$rollstat has 33.
I got explanation for gv$rollstat=33 since we have 3 node RAC each instance has 11 undosegments.
Question:
Why v$rollname has only 11 segments while DBA_UNDO_EXTENTS has 1421? Why all the segments are not
listed under v$rollname or gv$rollstat?
SQL> select count(*) from v$rollname;
COUNT(*)
----------
11
SQL> select count(*) from gv$rollstat;
COUNT(*)
----------
33
SQL> select count(distinct segment_name) from DBA_UNDO_EXTENTS
COUNT(DISTINCTSEGMENT_NAME)
---------------------------
1421
SQL> select count(*) from DBA_UNDO_EXTENTS;
COUNT(*)
----------
6478
SQL> select status,count(*) from DBA_UNDO_EXTENTS group by status;
STATUS COUNT(*)
--------- ----------
EXPIRED 6433
UNEXPIRED 45
Followup October 15, 2005 - 3pm Central time zone:
undo_EXTENTS
^^^^^^^
v$rollname - names of online rollback segments...
a segment is comprised of 1 or MORE extents, a rollback segment in particular has at least 2
extents..
you cannot compare the number of extents to the number of segments.
distinct segment_name of DBA_UNDO_EXTENTS
October 15, 2005 - 3pm Central time zone
Reviewer: A reader
Tom,
I understood the segment to extend relationship (1-M).
But look at the query.
select count(distinct segment_name) from DBA_UNDO_EXTENTS;
That is why I did "distinct segment_name".
Followup October 16, 2005 - 7am Central time zone:
see below, Kirtikumar Deshpande beat me to it.
distinct segment_name of DBA_UNDO_EXTENTS
October 15, 2005 - 10pm Central time zone
Reviewer: Kirtikumar Deshpande from Carrollton, TX USA
According the Oracle9i Reference Guide, V$ROLLNAME lists all *online* undo segments.
What you see from distinct(segment_name)in DBA_UNDO_EXTENTS are the names of *all* undo segments
Oracle ever created, in AUM mode, to support concurrent transactions.
In AUM mode, Oracle never drops any undo segments. Those are just offlined when not needed. And,
brought online when required.
Hope this helps.
- Kirti
Thanks Kirti & Tom. That helps!!!
October 16, 2005 - 7pm Central time zone
Reviewer: Sami
undo 9i vs rollback 8i
December 30, 2005 - 10am Central time zone
Reviewer: daniel from mea
Hi Tom,
Our application which is extremely oltp in nature but follows certain online time, for example it
is online for users for a period of 4 hours in 24 hrs time. However db is up runnign 24x7.
We have an issue:
For about 20 secs our db literally hangs, as if nothing is moving, reporting to oracle and
uploading all files, they have identified that when ever we have this situation, the alert log
shows around 40 undo segments onlined, according to tar, this is a serialized process.
So the situation is:
Online activity starts at 10:00 am, at 10:03:05 sec, alert log shows 40 (sometimes 38) undo
segments onlined, this takes from 15 to 20 secs, and exactly at this time, i see from oem lock
manager that i have many sessions waiting, and when the undo segments are acuaired all trans went
fast as if there is no prob.
After that until 12:00 noon, alert log does not show any other undo created, online time of
application completes at 12:00.
Then at 3:00 pm (aft 3 hrs) the alert log again shows those undo as smon offlining ,,, all of
em.(except the first 5 undo which we see when we start the database).
Our undo retention is 3 hrs, offcourse we can set this to lower value, our undo space is 6.5 gb out
of which we see ever going 3 gb used. Undo tspace is not auto extend.
Oracle has informed about the undo 9i algorithm as:
In simple terms the 9.2 algorithm for using undo extents is as follows:
1. A new extent will be allocated from the undo tablespace.
2. If this fails because no free extent is available and we cannot autoextend,
try stealing an expired extent from another undo segment.
3. If this fails because there are no extents expired, try to reuse an unexpired extent from the
cur
rent undo segment.
4. If this fails, try stealing an unexpired extent from another undo segment.
5. If all fail, report an "Out-Of-Space" error.
Anyway they are saying to go back to 8i rollback so the rollback segments are always online.
But I am not convinced. So I rejected.
Then they came up with another solution that is put an event in the alert log (we ar eon 9205) so
that smon will never offline undos.
Fine we did that, but then yes we do not see the undos getting offlined, at 3:00 pm, but next
morning at 10:03 instead of oracle reusing the 40 undo it created another 40 (new ones), when we
see the undo view, we found old 40 undo were hving status of Expired.
Now what should we do, go back to 8i?
If only there is a way that i can make all 40 undo online before 10: am with some dummy load.
Cheers
Followup December 30, 2005 - 11am Central time zone:
This was a known issue with automatic undo management in the first go around - if you had a period
of "lots of logins right now" - there could be an issue.
Things that start with "A" follow the 80/20 rule - much of the time they work better than good
enough for most people. However, sometimes - they do not. You have a special circumstance that in
the release you are using - the workaround was to not use the 'A' feature, but the 'M' feature
(manual)
For the same reason that we still have manual transmissions in some vehicles - but not others.
Sometimes you need to override the 'A'
December 30, 2005 - 12pm Central time zone
Reviewer: daniel from mea
Hi Tom,
I did not get it when you said
"if you had a period of "lots of logins right now"
And then when you said it is specific to my release.
Questions:
Statspack shows logons = 1.11 per sec under the load profile. This was from statspack of 15 mints
on peak load.
Does that mean we have a connection being made to our
database from client ; 1 connection every sec??
How can i further investigate this logons, bec we have pre established connections to database from
oralce 10gas server.
Also please explain the link of logons to undo as u said?
Now coming back to our release, if 9205 has an issue, when it is solved, in 9207 or 10g? and how
did oracle solved it?
I mean i read all of ur articles in undo and you said to one person that this is auto undo, thats
when oracle creates undo and offline em on fly, so how the new releases solved this issue?
Cheers
Followup December 31, 2005 - 10am Central time zone:
you have a period of high "create me a session please" or "create me a new transaction" - lots of
people trying to login all at the same time. that is causing Oracle to want to create new undo
segments to satisfy this. That is causing the bottleneck *in the release of Oracle you are using*
(support has already told you "we know about this issue, the "A" for automatic algorithm is changed
in a later release - your workaround is to not use the "A" feature in your case).
You would be looking at 10g.
system rbs & undo_management=auto
January 13, 2006 - 10am Central time zone
Reviewer: Yogesh from Pune, India
There is already discussion about system rbs and undo_management=auto in this thread. But I could't
understand, if we are using undo_management=auto, do we require system rbs at all? or it is totally
optional?
I've upgraded my database from 8174 to 10201, changed undo_management=auto, But when I was trying
to get rid of system rbs, I was getting "ORA-01544: cannot drop system rollback segment" Can you
please explain this?
Followup January 13, 2006 - 11am Central time zone:
you'll find that it is there - leave it be, it is created normally as part of the database
creation.
Yes, it is used/needed.
SYSTEM RBS still used
January 14, 2006 - 1am Central time zone
Reviewer: A reader
Probably more detail than people need to know, but the system rollback segment still must be used
for some special recursive transactions. This includes changes to the status of the other
undo/rollback segments, and some tablespace level operations.
AUM Question...
July 13, 2006 - 2pm Central time zone
Reviewer: Maria Saavedra from Venezuela
Hi Tom!!
Thanks for all this information ... i read it and i agree, but i have to ask ..
We are migrating a DB from 8.1.7 to 9.2.0.7, is a very transactional db, the old db (8i) has 63
rollback segments, where 60 for daily use, 1 for SYSTEM, and 2 for a special activity (month end
reports & process).
For month end reports we have this RBS.
create public rollback segment CIERRE01 tablespace UNDOTBS storage (INITIAL 51200K NEXT 51200K
optimal 409600K minextents 4 maxextents 32765);
create public rollback segment CIERRE02 tablespace UNDOTBS storage (INITIAL 51200K NEXT 51200K
optimal 409600K minextents 4 maxextents 32765);
For daily RBS this:
create public rollback segment R01 tablespace UNDOTBS storage (INITIAL 51200K NEXT 51200K optimal
102400K minextents 2 maxextents 240);
So there is my question?
What do you recommend me to use ... Automatic o Manual Undo Management in the new DB 9i....???
Thanks!
Followup July 13, 2006 - 5pm Central time zone:
I'd go with automatic undo management in all likelyhood.
these two you create, they can just "go bigger" (not sure why you just didn't have the 60 you
already have do that, since you were using optimal anyway).
Oracle will online and offline segments as it needs them and if a big transaction needs more
extents, it'll allocate them or steal them from another segment it is managing.
Can partitioning the source table prevent ORA-1555?
September 22, 2006 - 2pm Central time zone
Reviewer: kenchendz from CA,USA
Thank you Tom,excellent explaination.
We have a huge table which is 12G and keep growing in speed 1G/month. Every 3 months we do a
refresh on another database to get the up-to-date data.Actually i drop the old table and re-create
a MV on demand using a DBLink to that production one.
It worked fine before since the table was small. Now, I am getting the ORA-1555 error as discussed
and know how to deal with it.
However, it does not look like a good idea to have the UNDO size to grow as the table grows.
I come up with an idea to partition the production table, so the size of the UNDO does not have to
be that huge to keep the reading consistency.
Just thoughts, am I correct or totally wrong?
Followup September 22, 2006 - 4pm Central time zone:
why doesn't SIZING UNDO TO BE AS BIG AS YOU NEED TO DO YOUR JOB seem like a good idea???
I'm very confused by that concept.
I cannot tell if partitioning will help you - not any more than you could help me with "my car
won't start" given the information provided.
September 22, 2006 - 6pm Central time zone
Reviewer: kenchendz from CA,USA
let me explain my idea here:
1)Current situation:
When person AA execute "select * from table_huge;", which will take about 20 minutes, then UNDO
will hold up all data in table_huge(12G) to keep read consistency as other persons do any DML on
the same table during this 20 minutes. So UNDO need to be sized more larger than the size of
table_huge, and need to be keep growing to get my job done as table_huge grows.
2)My thought:
To break table_huge to partitions(1G each), so UNDO doesn't have to hold up all data of the table
but only 1G data in specific partition to keep the read consistency. As the table grows, the new
partitions are all the similar size, then the UNDO does not have to grow.
Hope this is clearer. Thanks.
Followup September 24, 2006 - 1pm Central time zone:
1) no, you got it sort of wrong.
if someone did select * from tablehuge and it takes 20 minutes, then undo needs to be sized to hold
the amount of undo your system ACTUALLY GENERATES - which might be
a) much larger than this table
b) much smaller than this table
c) either a or b, depends
it is purely the duration of the query, has nothing to do with the table size or anything. you
need to have configured as much undo as you need to satisfy your longest running query.
2) that would accomplish, well, nothing. You still have a 20 minute query, you still need
sufficient undo space to hold at least 20 minutes worth of the undo your system generates. period.
Unexplainable UNDO usage...
July 5, 2007 - 2pm Central time zone
Reviewer: Kevin T. from Stevens Point, WI
Tom:
We've noticed some unusual UNDO tablespace behaviour when it comes to INSERT operations on an EMPTY or TRUNCATED table. For example:
Here's a simple test case:
STEP ONE - Make sure your system is in LOGGING mode. Archive_Logging seems to need to be
enabled for this to occur.
>select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
STEP TWO - Create a destination table
>create table test_table nologging as (select * from all_objects where 1=0);
>create index test_index on test_table (owner, object_id) nologging;
STEP THREE - Create a source table of sufficient size (about 2-4 million records)
>create source_table nologging as (select * from all_objects);
>insert /* Append */ into source_table (select * from source_table);
repeat the insert statement to continue doubling the size of the table until you have a sufficient record count.
STEP FOUR - Retrieve the sizes of your datafiles for before/after comparisons
select d.tablespace_name Tablespace,
TO_CHAR(NVL(d.bytes / 1024 / 1024, 0), '99999990D000') "AllocatedSize(MB)",
TO_CHAR(((NVL(d.bytes / 1024 / 1024, 0)) - (NVL((NVL(s.bytes, 0))/1024/1024, 0))), '99999990D000') "UsedSpace(MB)"
FROM sys.dba_data_files d,
v$datafile v,
(SELECT file_id, SUM(bytes) bytes
FROM sys.dba_free_space
GROUP BY file_id) s
WHERE (s.file_id (+)= d.file_id)
AND (d.file_name = v.name);
TABLESPACE AllocatedSize UsedSpace(MB)
------------------------------ ------------- -------------
SYSTEM 300.000 276.563
UNDOTBS1 10240.000 361.000
SYSAUX 1024.000 283.813
USERS 1368.750 152.188
*Notice how UNDOTBS1 is currently at 361MB used.
STEP FIVE - Run your insert from the source table into the test table
>insert /* Append */ into test_table (select * from source_table)
STEP SIX - repeat space listing and compare results.
TABLESPACE AllocatedSize UsedSpace(MB)
------------------------------ ------------- -------------
SYSTEM 300.000 276.563
UNDOTBS1 10240.000 544.000
SYSAUX 1024.000 283.813
USERS 1368.750 348.063
*Now UNDOTBS1 is at 544MB used.
I haven't been able to get an adequate explanation from Oracle via metalink as to *why*
1. Even though we have nologging and the fact that we're telling the system to utilize direct-path inserts, that we're chewing up so much UNDO just for the index.
2. We're using approximately 195MB of tablespace to store the index+table - yet it's taking approximate 183MB of UNDO, even though both the INDEX and TABLE were empty prior to the operation.
3. Even given consistent reads... since this information has not been comitted to the db, the datafile changes should only be accessabe to this session. Given the append operation which only changes the HWM during the final block write, it doesn't explain how it'd be using 183MB of UNDO for block-write changes which haven't been comitted....
I can't seem to find a clear explanation anywhere as to why this occurs. The amount of UNDO generated is approximately 50-100% *less* if any of the following changes:
A. Done on a system in NOLOGGING mode
B. The index is built seperately as opposed to during the INSERT
C. The operation is done *without* the APPEND hint.
I realize this may not be the best explanation of what we're seeing, we did about 10-15 tests which would take pages of text here, using just about every combination. This was the one odd-ball out of the bunch.
Could you offer any insight as to what exactly is happening here with UNDO?
Followup July 5, 2007 - 3pm Central time zone:
why not? you have 10240 units of undo space we can use for nothing other than....
undo
and we are. We won't overwrite undo until and unless we have to. undo is used in a big circular buffer - you are measuring undo generated in a very strange (eg: incorrect) fashion. You are measuring the size of an undo segment(s) in an undo tablespace. That is NOT the amount of undo you've generated (perhaps it is, perhaps it isn't - they are two different things here).
Your index will always generate undo - regardless of the "nologging" mode - in response to an index.
when you do an insert /*+ append */
a) undo for the table segment MIGHT be skipped (assuming the append hint was used and followed)
b) undo for the index(es) on that table will NEVER be skipped. We'll create a mini-index of the newly inserted data and then we bulk merge that index into the existing one - this merge - generates UNDO for sure.
when you do a create table as select and a create index - no undo is generated for the table or the index - redo is, but not undo.
use v$sesstat to monitor your undo generation and understand that
a) create table - doesn't need to generate undo for the table segement (just for the dictionary operations)
b) create index, see a)
c) insert /*+ APPEND */ can skip undo for the table segment
d) inserts of any sort against an index will generate undo for the index - period.
ops$tkyte%ORA10GR2> select count(*) from big_table.big_table;
COUNT(*)
----------
1000000
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "undo change vector size"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
----------------------- ----------
undo change vector size 96820
ops$tkyte%ORA10GR2> create table t as select * from big_table.big_table;
Table created.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
----------------------- ---------- ------------------
undo change vector size 173864 77,044
so, our create table generated a small bit of undo, 77k
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "undo change vector size"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
----------------------- ----------
undo change vector size 173864
ops$tkyte%ORA10GR2> insert /*+ append */ into t select * from t;
1000000 rows created.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
----------------------- ---------- ------------------
undo change vector size 184980 11,116
and the insert append, even less...
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "undo change vector size"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
----------------------- ----------
undo change vector size 193420
ops$tkyte%ORA10GR2> create table t as select * from big_table.big_table;
Table created.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
----------------------- ---------- ------------------
undo change vector size 273940 80,520
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "undo change vector size"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
----------------------- ----------
undo change vector size 273940
ops$tkyte%ORA10GR2> create index i on t(object_name);
Index created.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
----------------------- ---------- ------------------
undo change vector size 309636 35,696
Now, the create index - very little undo - but an insert OF ANY SORT:
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "undo change vector size"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
----------------------- ----------
undo change vector size 309636
ops$tkyte%ORA10GR2> insert /*+ append */ into t select * from t;
1000000 rows created.
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
----------------------- ---------- ------------------
undo change vector size 95575072 95,265,436
generates a lot - that is the undo for the index
Unexplainable UNDO usage...
July 5, 2007 - 4pm Central time zone
Reviewer: Kevin T. from Stevens Point, WI
Tom,
Well, I agree the retrievial of UNDO usage was a bit unusual, only because the system we were working on at the time is a single user environment which we could essentially assume no other operations were occuring.
In addition, I was unaware there was a particular parameter you could view to see the UNDO generation on an operation by operation basis as such. Knowing that it exists now at least gives us a more discrete means of measurement.
I see though, so it's special cases like the temporary index merge which is generating the UNDO during the INSERT then. I was assuming, since this wasn't generating any TEMP and being done in memory, that we wouldn't see UNDO operations even on the index. It just seemed like the outlier out of the bunch of different tests we were looking at...
So, would these temporary index merge type operations also explain why then, when doing the index build after the table is already generated, that the UNDO usage is so minimal, since these types of merges would not occur?
We see the operation generally as one statement, but behind the scenes Oracle is inserting/re-inserting into the index for every record in the table. It's these mid-operation changes that are being recorded into the UNDO then?
The reason we're investigating this, is that we have a number of external datawarehouse facilities that are truncating/re-inserting large quantities of data into table-sets for Datamart and Datawarehouse functions. However, we need to appropriately judge the amount of UNDO tablespace required to ensure these operations don't fail mid-stream (because they're third party tools, we don't necessarily have the option of disabling/rebuilding the indexes seperately).
Knowing how Oracle is generating UNDO for table/index inserts improves our sizing considerations. Often times these operations were overlooked and caused the dreaded UNDO tablespace to fill...
Followup July 5, 2007 - 4pm Central time zone:
it doesn't matter if nothing else occurs, UNDO wraps - big circular buffer - you cannot measure undo generation this way. Also, undo grows in extents, we might allocate an 8mb extent (or more, or less) and use 0.01% of it. It would show as being used (100% of it), but we use 0.01% of it really.
In short, you cannot measure undo usage in any circumstances as you did, it was not really unusual - it was invalid.
yes, create index => no undo for index, insert of anytype => undo for index, least amount of undo for direct path inserts (mini index, bulk merge) - most (typically) for conventional path, row by row inserts.
if you
a) truncate
b) set unusable (the indexes)
c) direct path load
d) rebuild unusable indexes
you'll generate least undo, virtually NO undo. make sure to do A and then B, not the other way (the truncate will reset the unusable bit)
else, you cannot avoid the index undo generation.
Create index online and undo
July 6, 2007 - 6am Central time zone
Reviewer: Andras Gabor from Hungary
Hi Tom,
What about online index creating? After reading above I was wandering how much undo is generated in that case (as opposed to offline index creation)?
I used your v$mystat technique to meausure this:
Case 1: create table, create index offline
Case 2: create table, create index online
Case 3: create table empty, create index, load data /*+ append */
The index creation in Case 2 generated only a very little bit more than Case 1. And much much less then index+data load in Case 3.
Although during the online idnex creation in case 2 there was no real parallel activity (no other sessions modifying the table hence the index) in my environment. Do you consider this relevant?
Any comments on this?
Full script (without measurement results):
-- setup
column statistic# new_value stat_num
select * from v$statname where name like 'undo change vector size';
create table big_table as select * from dba_objects where rownum <= 20000;
-- Case 1
select * from v$mystat where statistic# = &stat_num;
create table t as select * from big_table;
select * from v$mystat where statistic# = &stat_num;
create index i on t(object_name);
select * from v$mystat where statistic# = &stat_num;
drop table t purge;
-- Case 2
select * from v$mystat where statistic# = &stat_num;
create table t as select * from big_table;
select * from v$mystat where statistic# = &stat_num;
create index i on t(object_name) online;
select * from v$mystat where statistic# = &stat_num;
drop table t purge;
-- Case 3
select * from v$mystat where statistic# = &stat_num;
create table t as select * from big_table where 1=2;
select * from v$mystat where statistic# = &stat_num;
create index i on t(object_name) online;
select * from v$mystat where statistic# = &stat_num;
insert /*+ append */ into t select * from big_table;
commit;
select * from v$mystat where statistic# = &stat_num;
-- Clean up 10g
drop table t purge;
drop table big_table purge;
Followup July 6, 2007 - 12pm Central time zone:
why does it matter?
but an online create will generate undo based on the amount of activity going against the table...
and if there is no activity (no modifications) there is no need for online :)
offline undo segment
May 27, 2008 - 4am Central time zone
Reviewer: ck
Hi Tom,
I try to find the concept behind offline undo segment, but not able to get any information.
How and when oracle will offline its' undo segment? Does offline undo segments contains of
unexpired extent?
Thanks
Followup May 27, 2008 - 8am Central time zone:
Not sure what you mean exactly.
Are you asking about automatic undo management?
December 11, 2009 - 3am Central time zone
Reviewer: Keita
Hello Tom
1-)
Suppose undo tbs is 10gb.
There is no activity in the database and hence Oracle decided to take the rollback segments
offline.
Does it also drop the rollbacksegments?
I mean, is there a chance to reduce my undo tablespace 10gb to 8gb automatically?
2-)
I know that round robin is used in manuel undo, Is it same in AUM?
Suppose
transaction1 assigned to rbs1
transaction2 assigned to rbs2.
transaction3 assigned to rbs3
Assume, transaction2 commited. and undo retention period is expired.
Does transaction4 assigned to rbs2 or rbs1?
Followup December 11, 2009 - 7am Central time zone:
1) yes it will offline and even drop segments it no longer desires, however, it is unlikely that you'll be able to shrink from 10gb to 8gb - because we resize FILES, not tablespaces.
If your undo tablespace was allocated like this (1 = extent for undo segment #1, 2 = extent for undo segment #2 and so on....) F=free space
123333111222311122211133333331
and we decide to get rid of #3, we'd have:
12FFFF111222F111222111FFFFFFF1
with that last extent out there - for #1 - we cannot shrink anything.
2) it is likely to be round robin, but no assurances.
December 13, 2009 - 4pm Central time zone
Reviewer: A reader
Thanks for your prompt reply Tom.
Suppose it become:
12FFFF111222F111222111FFFFFFFF
Does oracle automatically shrink the size of the undo tablespace? (for example from 10gb to 7gb)
Followup December 14, 2009 - 8am Central time zone:
the datafiles will not shrink, no. We'll allocate and reallocate space inside of there - move extents around, release them - but the datafiles do not shrink on their own.
|