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

hi, guys,

    We have a case , where we need to reorg some of our partitions to reclaim disk space due to inproper pctused and delete. case like;
oracle 9205/solaris8.
subpartition part1_01 with 18 columns , 1LOB column. nocache/nologging/out of line/default pctversion;
subpartition table segment 2gb, lob segment about 6gb.

do a CTAS for table without lob segment can be done pretty quick (less than 10 minutes, detailed data at office computer), but with LOB column, it alwasy fails with snapshot too old after 1-2 hours. (we have undo_retention to 3 hours). We used parallel 4 for create and parallel 4 for select. with nologging and multiblock_count=128;

it is quite sure LOB is slowing down the SQL. Is there any way to speed up the CTAS? Or we can use alter table move subpartition to move (did not test it yet as can't play with prod), but suspect it should allso be very slow.

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?
--

Regards
Zhu Chao
www.cnoug.org

--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 03 2006 - 12:03:49 CDT