continued
Andre, June 17, 2002 - 1:27 pm UTC
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
Connor, June 17, 2002 - 4:37 pm UTC
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
Andre, June 17, 2002 - 5:53 pm UTC
Yep, Connor. I think, after all, the new undo manageability makes up for any eventual overhead.
Automatic Undo Management
Natalia, October 19, 2002 - 2:50 am UTC
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
October 19, 2002 - 10:45 am UTC
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
Natalia, October 19, 2002 - 4:48 pm UTC
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
October 20, 2002 - 9:29 am UTC
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
Arun Gupta, March 17, 2003 - 7:05 pm UTC
If I use AUM, what should I do with SET TRANSACTION USE ROLLBACK SEGMENT statements in the code.
Thanks
March 17, 2003 - 7:11 pm UTC
set undo_suppress_errors to true so that the statement is silently ignored by the database.
undo_suppress_error
Chandru, March 17, 2003 - 10:41 pm UTC
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.
****************************************
March 18, 2003 - 7:32 am UTC
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!
Chandru, March 18, 2003 - 11:51 am UTC
Thanks as always for your response. I am clear now.
Chandru
Ooo! Someone has a teenager!
A reader, March 19, 2003 - 12:36 pm UTC
Very informative
Robert, April 23, 2003 - 10:52 am UTC
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
April 23, 2003 - 7:20 pm UTC
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
A reader, July 08, 2003 - 3:18 pm UTC
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?
July 08, 2003 - 3:57 pm UTC
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!
A reader, July 08, 2003 - 8:05 pm UTC
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!!!
July 09, 2003 - 10:38 am UTC
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!!!
a fan, July 09, 2003 - 12:11 pm UTC
undo for insert of clob
A reader, August 17, 2003 - 5:18 pm UTC
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!
August 17, 2003 - 7:47 pm UTC
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
A reader, August 17, 2003 - 5:24 pm UTC
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!
A reader, August 17, 2003 - 8:30 pm UTC
"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!
August 18, 2003 - 7:14 am UTC
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:(
A reader, August 18, 2003 - 12:08 pm UTC
"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!!
August 18, 2003 - 3:05 pm UTC
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..
A reader, August 18, 2003 - 3:55 pm UTC
"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!!
A reader, September 23, 2003 - 2:05 pm UTC
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...
September 23, 2003 - 2:40 pm UTC
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
Fernando, November 11, 2003 - 12:14 pm UTC
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.
November 11, 2003 - 1:39 pm UTC
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
Reader, December 24, 2003 - 8:04 pm UTC
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.
December 25, 2003 - 10:09 am UTC
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.databases.oracle.*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3DBykX9.29812%2524jM5.77141%2540newsfeeds.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
A reader, December 26, 2003 - 7:30 am UTC
Care to give us the subject title in comp.database.oracle
December 26, 2003 - 10:31 am UTC
Please shed some light on multiple undo tablespaces
Moorthy Rekapalli, January 23, 2004 - 6:52 pm UTC
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.
January 24, 2004 - 8:36 am UTC
just means you created more than one.
only one is being used, the others are offline and unavailable.
commit time in dba_undo_extents
reader, February 19, 2004 - 6:23 pm UTC
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.
February 19, 2004 - 7:29 pm UTC
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?
Matt, February 23, 2004 - 2:03 am UTC
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?
February 23, 2004 - 7:49 am UTC
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.
reader, February 24, 2004 - 7:31 pm UTC
February 25, 2004 - 8:06 am UTC
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
K U, February 25, 2004 - 3:35 pm UTC
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
February 25, 2004 - 5:27 pm UTC
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
A reader, April 14, 2004 - 7:47 pm UTC
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
April 15, 2004 - 8:17 am UTC
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?
A reader, April 15, 2004 - 9:17 am UTC
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
April 15, 2004 - 9:37 am UTC
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
Arun Gupta, May 12, 2004 - 1:50 pm UTC
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
May 13, 2004 - 8:54 am UTC
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
Marvin, May 21, 2004 - 4:20 pm UTC
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?
May 22, 2004 - 3:59 pm UTC
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!
Evan, May 27, 2004 - 11:12 am UTC
Confusion Confusion
aru, April 04, 2005 - 5:52 pm UTC
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.
April 04, 2005 - 9:03 pm UTC
you can kick things offline. tablespaces can be offlined "brutally"
Be wary of SMU prior to 9.2.0.5
Doug Burns, April 25, 2005 - 11:12 am UTC
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 </code>
http://doug.burns.tripod.com/oracle <code>
HTH,
Doug
Better Link
Doug, September 09, 2005 - 12:45 pm UTC
September 09, 2005 - 12:50 pm UTC
No problem, always use the permalink in the blogs myself ;)
SYSTEM RBS in AUM?
Rob H, October 04, 2005 - 12:09 pm UTC
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?
October 04, 2005 - 5:14 pm UTC
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?
Rob H, October 06, 2005 - 12:55 pm UTC
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.
October 06, 2005 - 1:38 pm UTC
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
Sami, October 15, 2005 - 2:37 pm UTC
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
October 15, 2005 - 3:42 pm UTC
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
A reader, October 15, 2005 - 3:46 pm UTC
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".
October 16, 2005 - 7:52 am UTC
see below, Kirtikumar Deshpande beat me to it.
distinct segment_name of DBA_UNDO_EXTENTS
Kirtikumar Deshpande, October 15, 2005 - 10:41 pm UTC
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!!!
Sami, October 16, 2005 - 7:27 pm UTC
undo 9i vs rollback 8i
daniel, December 30, 2005 - 10:45 am UTC
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
December 30, 2005 - 11:15 am UTC
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'
daniel, December 30, 2005 - 12:01 pm UTC
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
December 31, 2005 - 10:48 am UTC
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
Yogesh, January 13, 2006 - 10:37 am UTC
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?
January 13, 2006 - 11:20 am UTC
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
A reader, January 14, 2006 - 1:01 am UTC
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...
Maria Saavedra, July 13, 2006 - 2:18 pm UTC
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!
July 13, 2006 - 5:02 pm UTC
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?
kenchendz, September 22, 2006 - 2:45 pm UTC
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?
September 22, 2006 - 4:03 pm UTC
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.
kenchendz, September 22, 2006 - 6:32 pm UTC
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.
September 24, 2006 - 1:24 pm UTC
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...
Kevin T., July 05, 2007 - 2:57 pm UTC
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?
July 05, 2007 - 3:17 pm UTC
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
<b>so, our create table generated a small bit of undo, 77k</b>
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
<b>and the insert append, even less...</b>
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
<b>Now, the create index - very little undo - but an insert OF ANY SORT:</b>
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
<b>generates a lot - that is the undo for the index</b>
Unexplainable UNDO usage...
Kevin T., July 05, 2007 - 4:03 pm UTC
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...
July 05, 2007 - 4:32 pm UTC
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
Andras Gabor, July 06, 2007 - 6:01 am UTC
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;
July 06, 2007 - 12:59 pm UTC
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
ck, May 27, 2008 - 4:18 am UTC
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
May 27, 2008 - 8:21 am UTC
Not sure what you mean exactly.
Are you asking about automatic undo management?
Keita, December 11, 2009 - 3:21 am UTC
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?
December 11, 2009 - 7:36 am UTC
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.
A reader, December 13, 2009 - 4:49 pm UTC
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)
December 14, 2009 - 8:42 am UTC
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.