Re: CTAS for tables with big lob segment (out of line) very slow

Hemant K Chitale wrote,on my timestamp of 6/08/2006 2:53 PM:
>
> Are you putting the LOB Segment in an ASSM Tablespace
> ("segment_space_management='AUTO' " ) ?
> In that case, use Manual Segment Space Management for the LOB Segment
> Tablespace.

 > snippage

>>
>> Is there any method to speed up the reorg(either move or ctas, and 
>> then exchange subpartition?), also it looks like the SQL is failing at 
>> LOB read consistency, is there any way to prove the LOB is causng the 
>> snapshot too old?
>> -- 

the LOB *will* be causing the snapshot too old message: in 9i, LOBs use their own tablespace to keep the undo blocks. No matter how much larger one makes the UNDO tablespace, it won't matter.

To change the amount of space dedicated in the LOB's tablespace to undo blocks, use:

alter table <tname> modify lob (<lob_column_name>) (pctversion <nn>);

where <nn> is by default 10 and I suggest the OP try 20. Note: Oracle doc specifically states that this is *NOT* a true "percentage".

The recommendation to not use ASSM is incredibly relevant as well: there is an unpublished bug in 9ir2 only fixed in 9.2.0.7 and 10r2, which causes LOBs in ASSM tablespaces to not only slow down on inserts but also in some cases result in data corruption.

DAMHIKT...

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l