Re: Critical Qs on materialized views - Undo Amt Needed ?
- From: Jared Still <jkstill@xxxxxxxxx>
- To: VIVEK_SHARMA@xxxxxxxxxxx
- Date: Mon, 29 Aug 2005 11:52:57 -0700
> 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.osuser
,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(+)
/
Other related posts:
