CTAS for tables with big lob segment (out of line) very slow
- From: "Zhu,Chao" <zhuchao@xxxxxxxxx>
- To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Aug 2006 01:03:49 +0800
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
Other related posts:
