Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Critical Qs on materialized views - Undo Amt Needed ?
> What is the amount of Undo/RBS needed in the Target/Slave Database when > creating a MV on a approx 100 GB Table using Prebuilt table clause? > > Any thumb rules, docs, links? >
Rules of thumb can be dangerous.
I am making the following assumptions:
Your prebuilt table is created with nologging You are using DBMS_MVIEW.REFRESH to do a complete refresh of the table There is a primary key (and index) on the prebuilt table.
In this case the refresh will be done as an INSERT /*+ APPEND */.
Very little undo will be generated for inserts into the table blocks.
The tests I ran on 9.2.0.5 <http://9.2.0.5> show 1 block and 1 undo record for this.
This changes when you include the indexes.
Undo will be written for the index blocks. There is no rule of thumb, as it depends on the size of the index, whether or not it is unique, and probably a number of other things I haven't considered.
Here is what I plan to do to predict needed undo size for a large MV refresh
create a representative sample of my table ( 1 million rows)
create the primary key
insert the rows (do not commit or rollback)
>From another session, check the number of undo blocks as seen in
v$transaction.
Use this figure to calculate how big the rollback segment needs to be for the real production job.
This would probably be much easier with automatic undo, but this database is not setup that way, and I can't (nor do I want to) change it at this time.
Below are some test scripts that I used to play with this a bit.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist ------------------------------------------------------------------------------------ drop table t1; drop table t2; create table t1 nologging as select * from ( select rownum pk, a.* from dba_objects a, dba_objects b where rownum <=100000 ) / create table t2 nologging as select pk,owner,object_name, object_type from t1 where 1=2 / --create index t2_idx on t2(pk,owner,object_name, object_type); --create index t2_idx_2 on t2(object_type, object_name); --create unique index t2_idx on t2(pk,owner,object_name, object_type); --create unique index t2_idx on t2(pk); --create index t2_idx on t2(pk); --alter table t2 add constraint t2_pk primary key(owner,object_name, object_type); exit ------------------------------------------------------------------------------------ -- this is in a new session insert /*+ append */ into t2 select pk,owner,object_name, object_type from t1 / ------------------------------------------------------------------------------------ -- from another sessionselect s.osuserReceived on Mon Aug 29 2005 - 13:54:53 CDT
,s.username
,s.sid
,r.segment_name
,t.space
,t.recursive
,t.noundo
,t.used_ublk
,t.used_urec
,t.log_io
,t.phy_io
from v$session s, v$transaction t, dba_rollback_segs r where s.saddr=t.ses_addr and t.xidusn=r.segment_id(+) / -- http://www.freelists.org/webpage/oracle-l
![]() |
![]() |