RE: 8i CLOB data needs a lobotomy

From: John Barron <jbarron_at_windriver.com>
Date: Tue, 27 Jun 2000 16:23:41 -0700
Message-Id: <10541.110602@fatcity.com>

--=====================_120365476==_.ALT

Content-Type: text/plain; charset="us-ascii"

Thanks for the ideas steve. The tablespace was empty before the load. This is our first test run so we re-creted the whole schema from scratch. This happens on all our perl data loads where we are importing into CLOB columns. I have about 12 tables where this is happening and in each case I shrink the data down to about 5% or less than the orginal space it was taking. Ive also checked the index storage and its not the problem. I have to rebuild the indexes after this as the move table command renders them invalid.

anyway its not a major problem just some weirdness either in the perl DBD loads or the way oracle stores CLOBS from an insert.

John Barron

At 02:54 PM 6/27/00 -0800, you wrote:
>
> What about the CRTS_QISI_LOB tablespace? What's the before/after on it? Did
> the clobs start out in the CRTS_DATA tablespace? No lobs in the system
> tablespace, right? Check the tablespace_name in dba_segments where
> segment_type in('LOBSEGMENT','LOBINDEX'). Just some ideas... happy lob
> hunting.
> HTH,
> Steve Orr
>>
>> -----Original Message-----
>> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of John Barron
>> Sent: Tuesday, June 27, 2000 2:28 PM
>> To: Multiple recipients of list ORACLE-L
>> Subject: 8i CLOB data needs a labotomy
>>
>> Hi
>>
>> I think my 8i CLOB data needs a labotomy. This is my problem and my work
>> around. We use perl DBD::Oracle to load our database from flat files. Part
>> of it is to load embedded text data into CLOB columns in our table
>> CR_HLD_QISI_CALLS. The CLOB cols are PROBLEMTEXT, ANALYSISTEXT, etc. When we
>> run the perl script the 100M text file mushrooms out to 900M in the
>> tablespace. Using the tablespace coalesce command has no affect on the
>> tablespaces. If we MOVE the tables and CLOB data to another tablespace (or
>> even in the same tablespace) and compress to one extent it shrinks the 800M
>> down to 40M.
>>
>> Why does it take so much tablespace? Is it the way perl loads data? We have
>> set default storage on the tablespace and tables to PCTFREE=0 as there will
>> be no updates to this table. The database is UTF8 with NLSLANG=ja16euc and
>> we do have some indexes on the tables but none of this accounts for the huge
>> space requirements for the initial upload.
>>
>> Any comments, pointers would be appreciated. Ive added our move sql and the
>> space used before and after the table move and coalesce.
>>
>> tia
>>
>> John Barron
>>
>>
>>
>> The SQL for moving the CLOBS and tables
>> ------------------------------------------------------
>> alter table CRTS.CR_HLD_QISI_CALLS move tablespace CRTS_DATA
>> lob (PROBLEMTEXT) store as (tablespace CRTS_QISI_LOB
>> storage(minextents 1));
>> alter table CRTS.CR_HLD_QISI_CALLS move
>> lob (ANALYSISTEXT) store as (tablespace CRTS_QISI_LOB
>> storage(minextents 1));
>> alter table CRTS.CR_HLD_QISI_CALLS move
>> lob (HISTORY) store as (tablespace CRTS_QISI_LOB
>> storage(minextents 1));
>>
>> alter tablespace CRTS_QISI_DATA coalesce;
>>
>> alter table CRTS.CR_HLD_QISI_CALLS move tablespace CRTS_QISI_DATA
>> lob (ESC_SUMMARY) store as (tablespace CRTS_QISI_LOB
>> storage(minextents 1));
>>
>>
>>
>> The BEFORE and AFTER space output
>> -------------------------------------------------
>> Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
>> With the Partitioning option
>> JServer Release 8.1.6.0.0 Production
>>
>> Table CLOB Col
>> CR_HLD_QISI_CALLS PROBLEMTEXT 64,430,080
>> CR_HLD_QISI_CALLS ANALYSISTEXT 469,114,880
>> CR_HLD_QISI_CALLS HISTORY 279,552,000
>> CR_HLD_QISI_CALLS ESC_SUMMARY 1,597,440
>> CR_HLD_QISI_CALLS Total 814,694,400
>>
>> Table altered.
>>
>>
>>
>> Table altered.
>>
>>
>>
>> Table altered.
>>
>>
>>
>> Tablespace altered.
>>
>>
>>
>> Table altered.
>>
>> CR_HLD_QISI_CALLS PROBLEMTEXT 39,936,000
>> CR_HLD_QISI_CALLS ANALYSISTEXT 532,480
>> CR_HLD_QISI_CALLS HISTORY 532,480
>> CR_HLD_QISI_CALLS ESC_SUMMARY 532,480
>> CR_HLD_QISI_CALLS Total 41,533,440
>
>

--=====================_120365476==_.ALT

Content-Type: text/html; charset="us-ascii"

<html>
Thanks for the ideas steve. The tablespace was empty before the load. This is our first test run so we re-creted the whole schema from scratch. This happens on all our perl data loads where we are importing into CLOB columns. I have about 12 tables where this is happening and in each case I shrink the data down to about 5% or less than the orginal space it was taking. Ive also checked the index storage and its not the problem. I have to rebuild the indexes after this as the move table command renders them invalid. 
 
anyway its not a major problem just some weirdness either in the perl DBD loads or the way oracle stores CLOBS from an insert. 
John Barron 
 
At 02:54 PM 6/27/00 -0800, you wrote: <blockquote type=cite cite>What about the CRTS_QISI_LOB tablespace? What's the before/after on it? Did the clobs start out in the CRTS_DATA tablespace? No lobs in the system tablespace, right? Check the tablespace_name in dba_segments where segment_type in('LOBSEGMENT','LOBINDEX'). Just some ideas... happy lob hunting. 
HTH, 
Steve Orr<blockquote>

<dl>
<dd>-----Original Message-----
<dd>From:</b> root_at_fatcity.com

[<a href="mailto:root_at_fatcity.com%5DOn" eudora="autourl">mailto:root_at_fatcity.com]</a><a href="mailto:root_at_fatcity.com%5DOn" eudora="autourl">On</a> Behalf Of John Barron

<dd>Sent:</b> Tuesday, June 27, 2000 2:28 PM
<dd>To:</b> Multiple recipients of list ORACLE-L
<dd>Subject:</b> 8i CLOB data needs a labotomy<br>
<br>
</font>
<dd>Hi <br>
<br>

<dd>I think my 8i CLOB data needs a labotomy. This is my problem and my work around. We use perl DBD::Oracle to load our database from flat files. Part of it is to load embedded  text data into CLOB columns in our table CR_HLD_QISI_CALLS. The CLOB cols are PROBLEMTEXT, ANALYSISTEXT, etc. When we run the perl script the 100M text file mushrooms out to 900M in the tablespace. Using the tablespace coalesce command has no affect on the tablespaces. If we  MOVE the tables and CLOB data to another tablespace (or even in the same tablespace) and compress to one extent it shrinks the 800M down to 40M.

<dd>Why does it take so much tablespace? Is it the way perl loads data? We have set default storage on the tablespace and tables to PCTFREE=0 as there will be no updates to this table. The database is UTF8 with NLSLANG=ja16euc and we do have some indexes on the tables but none of this accounts for the huge space requirements for the initial upload.

<dd>Any comments, pointers would be appreciated. Ive added our move sql and the space used before and after the table move and coalesce.

<dd>tia

<dd>John Barron<br>
<br>
<br>
<br>

<dd>The SQL for moving the CLOBS and tables
<dd>------------------------------------------------------<font face="r_ansi">
<dd>alter table CRTS.CR_HLD_QISI_CALLS move tablespace CRTS_DATA
<dd>lob (PROBLEMTEXT) store as (tablespace CRTS_QISI_LOB
<dd>storage(minextents 1));
<dd>alter table CRTS.CR_HLD_QISI_CALLS move
<dd>lob (ANALYSISTEXT) store as (tablespace CRTS_QISI_LOB
<dd>storage(minextents 1));
<dd>alter table CRTS.CR_HLD_QISI_CALLS move
<dd>lob (HISTORY) store as (tablespace CRTS_QISI_LOB

<dd>storage(minextents 1));

<dd>alter tablespace CRTS_QISI_DATA coalesce;

<dd>alter table CRTS.CR_HLD_QISI_CALLS move tablespace CRTS_QISI_DATA
<dd>lob (ESC_SUMMARY) store as (tablespace CRTS_QISI_LOB
<dd>storage(minextents 1));<br>
<br>
<br>
<br>
</font>
<dd>The BEFORE and AFTER space output
<dd>-------------------------------------------------<font face="r_ansi">
<dd>Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
<dd>With the Partitioning option
<dd>JServer Release 8.1.6.0.0&nbsp; Production<br>
<br>

<dd>Table                CLOB Col
<dd>CR_HLD_QISI_CALLS   
PROBLEMTEXT                64,430,080
<dd>CR_HLD_QISI_CALLS   
ANALYSISTEXT              469,114,880
<dd>CR_HLD_QISI_CALLS   
HISTORY                   279,552,000
<dd>CR_HLD_QISI_CALLS   
ESC_SUMMARY                 1,597,440
<dd>CR_HLD_QISI_CALLS   
Total                     814,694,400

<dd>Table altered.<br>
<br>
<br>
<br>

<dd>Table altered.<br>
<br>

<dd>Table altered.<br>
<br>
<br>
<br>

<dd>Tablespace altered.<br>
<br>

<dd>Table altered.

<dd>CR_HLD_QISI_CALLS   
PROBLEMTEXT                39,936,000
<dd>CR_HLD_QISI_CALLS   
ANALYSISTEXT                  532,480
<dd>CR_HLD_QISI_CALLS   
HISTORY                       532,480
<dd>CR_HLD_QISI_CALLS   
ESC_SUMMARY                   532,480 Received on Tue Jun 27 2000 - 18:23:41 CDT