Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: CTAS for tables with big lob segment (out of line) very slow

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

From: Hemant K Chitale <>
Date: Sun, 06 Aug 2006 12:53:54 +0800
Message-Id: <>

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

Hemant K Chitale

Received on Sat Aug 05 2006 - 23:53:54 CDT

Original text of this message