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

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.
I have done a few "migrations" of a Table with LONG RAW into another Table with the column as a BLOB
and have, unfortunately, suffered significantly with ASSM Tablespaces -- monitoring the growth of the
segment as I am running the INSERT I find that it keeps slowing down. In the database, where after some wisdom struck me, I created the Tablespace with Manual SSM,
the INSERT was much faster.

(using INSERT /*+ APPEND */ into new_table_with_BLOB as SELECT /*+ PARALLEL (ot 4) */
col1, col2, to_lob(longrawcol), col4 from old_table_with_LONGRAW ot; )

At 01:03 AM Friday, Zhu,Chao wrote:
>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
><http://www.cnoug.org>www.cnoug.org
>

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l