--=====================_69839553==_.ALT
Content-Type: text/plain; charset="us-ascii"
Steve
We created the database from scratch and this was our first data load into a
new database. Just to recap the initial datafiles was 245M and it ended up
consuming 900M in the CLOB tablespace.
Ive just looked at our table create for the CLOB tables and noticed that there
is no storage clause in the LOB parameters section. When we created this we set
the table PCTFREE to 0 and I also asked the devlopers to do the same with LOBS.
Anyway it has not been done. At present we use the defaults CHUNK=8K (our db
block size) and PCTVERSION=10. I also assume that PCTFREE is set to default of
50 which could account for some of the space used. Im still not quite sure how
the PCTVERSION would affect the data storage (the manual is not all that
clear).
Our process for uploading the data (all done through a perl program) is:
1 rename the HOLD table to TEMP
2 rename the PREV table (which has been truncated as it holds the previous load
data) to HOLD
3 rename TEMP table to PREV table
The data is then uploaded from the perl program. The perl program dynamically
constructs sql statements to do the upload and has some specific sub routines
to handle CLOB cols so you cant actually see the sql. When I get round to it I
may modify the program to show the SQL. There is one perl program which does
the whole shabang and from a high level design perspective Im not 100%
convinced it is the best method. These HOLD tables are just holding data prior
to a data transformation that runs.
BTW Im juts the DBA the design of all this is by our perl/oracle developers. I
just run the uploads and sort out the problems plus do some perl debugging.
Going back to the LOB storage parameters I will try setting them to CHUNK=8K
(our db block size) and PCTVERSION=0 as we never change the data and set
PCTFREE=0.
Do you have any suggestions on these parameter settings?
Below is the largest of one of our HOLD tables.
Thanks for any help you can give.
John Barron
ps we have a small bug in the above process of renaming tables. because we
explicitly name the PK it travels with the table renames which can be a bit
confusing. We intend to go back to system generated ones.
CREATE TABLE CR_HLD_QISI_calls (
pkey Integer NOT NULL,
defectcall Integer NULL,
callcalldup INTEGER NULL,
agentcall Integer NULL,
customercall Integer NULL,
workgroupcall INTEGER NULL,
ownercall Integer NULL,
solutioncall Integer NULL,
CALLSTATUS VARCHAR2(20) NOT NULL,
ORIGIN VARCHAR2(20) NULL,
PRIORITY VARCHAR2(20) NULL,
problem VARCHAR2(240) NOT NULL,
action VARCHAR2(240) NULL,
datereported DATE NULL,
dateassigned DATE NULL,
dateowned DATE NULL,
dateresolved DATE NULL,
datemodified DATE NULL,
modifiedby VARCHAR2(20) NULL,
nextcontactdate DATE NULL,
datepending DATE NULL,
WHYPENDING VARCHAR2(30),
problemtext CLOB NULL,
analysistext CLOB NULL,
history CLOB NULL,
rdproductcall INTEGER NULL,
TECHNICAL_IMPACT VARCHAR2(20) NOT NULL,
PSOS_CLASS VARCHAR2(30) NULL,
MX_CLASS VARCHAR2(30) NULL,
target VARCHAR2(80) NULL,
prod_ver VARCHAR2(80) NULL,
comp_ver VARCHAR2(80) NULL,
tools_ver VARCHAR2(80) NULL,
host VARCHAR2(80) NULL,
host_os_ver VARCHAR2(80) NULL,
PRODUCT_LINE VARCHAR2(35) NOT NULL,
external_id VARCHAR2(80) NULL,
esc_status INTEGER NULL,
esc_summary CLOB NULL,
WHYCLOSED VARCHAR2(30) NULL,
board VARCHAR2(80) NULL,
TOOLS VARCHAR2(20) NULL,
datedefectlink DATE NULL,
DATE_EXTRACTED DATE NULL,
DATE_UPLOADED DATE NULL,
SOURCE_DATA_FILE VARCHAR2(50) NULL,
SOURCE_PARTITION VARCHAR2(30) NULL,
XFORM_PROCESSED_FLAG VARCHAR2(10) NULL,
Changed_Flag Varchar2(10) NULL,
CONSTRAINT CR_HLD_QISI_calls_PK
PRIMARY KEY (pkey)
USING INDEX
TABLESPACE &&idxsp
) TABLESPACE &&tblsp
LOB (analysistext) store as (tablespace &&lobsp)
LOB (esc_summary) store as (tablespace &&lobsp)
LOB (history) store as (tablespace &&lobsp)
LOB (problemtext) store as (tablespace &&lobsp)
storage(initial 128m next 128m pctincrease 0)
PCTFREE 0 ;
At 09:56 AM 6/28/00 -0800, you wrote:
>Can you check the before and after storage definitions for the tables/CLOBS?
>Maybe the tables were created one way but the move altered the way things
>are stored. Inline to out-of-line, shouldn't matter. Pctversion defaults to
>10, what's the before/after. Chunksize? I know the storage definitions for
>LOBS are important as someone here managed to "CLOBber" the system
>tablespace which was on autoextend. Fragmentation maximo! (This was on a
>development database.)
>
>Can you share the query that produced the before/after storage results? I'm
>just getting started in the LOB scene. I'm anticipating increased LOB usage
>so I need to nail down all the storage and operational issues.
>
>We'll be curious as to what you discover on this one. Please keep us posted.
>
>
>Thanks,
>Steve Orr
>
>
>-----Original Message-----
>Sent: Tuesday, June 27, 2000 5:26 PM
>To: Multiple recipients of list ORACLE-L
>
>
>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-----
>Sent: Tuesday, June 27, 2000 2:28 PM
>To: Multiple recipients of list ORACLE-L
>
>
>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
>
>--
>Author: Steve Orr
> INET: sorr_at_arzoo.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
--=====================_69839553==_.ALT
Content-Type: text/html; charset="us-ascii"
<html>
Steve<br>
<br>
We created the database from scratch and this was our first data load
into a new database. Just to recap the initial datafiles was 245M and it
ended up consuming 900M in the CLOB tablespace.<br>
<br>
Ive just looked at our table create for the CLOB tables and noticed that
there is no storage clause in the LOB parameters section. When we created
this we set the table PCTFREE to 0 and I also asked the devlopers to do
the same with LOBS. Anyway it has not been done. At present we use the
defaults CHUNK=8K (our db block size) and PCTVERSION=10. I also assume
that PCTFREE is set to default of 50 which could account for some of the
space used. Im still not quite sure how the PCTVERSION would affect the
data storage (the manual is not all that clear).<br>
<br>
Our process for uploading the data (all done through a perl program)
is:<br>
<br>
1 rename the HOLD table to TEMP<br>
2 rename the PREV table (which has been truncated as it holds the
previous load data) to HOLD<br>
3 rename TEMP table to PREV table<br>
<br>
The data is then uploaded from the perl program. The perl program
dynamically constructs sql statements to do the upload and has some
specific sub routines to handle CLOB cols so you cant actually see the
sql. When I get round to it I may modify the program to show the SQL.
There is one perl program which does the whole shabang and from a high
level design perspective Im not 100% convinced it is the best method.
These HOLD tables are just holding data prior to a data transformation
that runs.<br>
<br>
BTW Im juts the DBA the design of all this is by our perl/oracle
developers. I just run the uploads and sort out the problems plus do some
perl debugging.<br>
<br>
Going back to the LOB storage parameters I will try setting them to
CHUNK=8K (our db block size) and PCTVERSION=0 as we never change the data
and set PCTFREE=0.<br>
<br>
Do you have any suggestions on these parameter settings?<br>
<br>
Below is the largest of one of our HOLD tables.<br>
<br>
Thanks for any help you can give.<br>
<br>
John Barron<br>
<br>
ps we have a small bug in the above process of renaming tables. because
we explicitly name the PK it travels with the table renames which can be
a bit confusing. We intend to go back to system generated ones. <br>
<br>
<br>
<font face="r_ansi">CREATE TABLE CR_HLD_QISI_calls ( <br>
pkey
Integer NOT NULL, <br>
defectcall
Integer NULL, <br>
callcalldup INTEGER
NULL, <br>
agentcall
Integer NULL, <br>
customercall Integer
NULL, <br>
workgroupcall INTEGER NULL,
<br>
ownercall
Integer NULL, <br>
solutioncall Integer
NULL, <br>
CALLSTATUS
VARCHAR2(20) NOT NULL, <br>
ORIGIN
VARCHAR2(20) NULL, <br>
PRIORITY
VARCHAR2(20) NULL, <br>
problem
VARCHAR2(240) NOT NULL, <br>
action
VARCHAR2(240) NULL, <br>
datereported DATE NULL,
<br>
dateassigned DATE NULL,
<br>
dateowned
DATE NULL, <br>
dateresolved DATE NULL,
<br>
datemodified DATE NULL,
<br>
modifiedby
VARCHAR2(20) NULL, <br>
nextcontactdate DATE NULL, <br>
datepending DATE
NULL, <br>
WHYPENDING
VARCHAR2(30),<br>
problemtext CLOB
NULL, <br>
analysistext CLOB NULL,
<br>
history
CLOB NULL, <br>
rdproductcall INTEGER NULL,
<br>
TECHNICAL_IMPACT VARCHAR2(20) NOT NULL, <br>
PSOS_CLASS
VARCHAR2(30) NULL, <br>
MX_CLASS
VARCHAR2(30) NULL, <br>
target
VARCHAR2(80) NULL, <br>
prod_ver
VARCHAR2(80) NULL, <br>
comp_ver
VARCHAR2(80) NULL, <br>
tools_ver
VARCHAR2(80) NULL, <br>
host
VARCHAR2(80) NULL, <br>
host_os_ver
VARCHAR2(80) NULL, <br>
PRODUCT_LINE VARCHAR2(35)
NOT NULL, <br>
external_id
VARCHAR2(80) NULL, <br>
esc_status
INTEGER NULL, <br>
esc_summary CLOB
NULL, <br>
WHYCLOSED
VARCHAR2(30) NULL, <br>
board
VARCHAR2(80) NULL, <br>
TOOLS
VARCHAR2(20) NULL, <br>
datedefectlink DATE NULL, <br>
DATE_EXTRACTED DATE NULL, <br>
DATE_UPLOADED DATE NULL, <br>
SOURCE_DATA_FILE VARCHAR2(50) NULL, <br>
SOURCE_PARTITION VARCHAR2(30) NULL, <br>
XFORM_PROCESSED_FLAG VARCHAR2(10)
NULL, <br>
Changed_Flag Varchar2(10)
NULL, <br>
CONSTRAINT
CR_HLD_QISI_calls_PK<br>
PRIMARY KEY (pkey) <br>
USING INDEX <br>
TABLESPACE &&idxsp <br>
) TABLESPACE &&tblsp <br>
LOB (analysistext) store as (tablespace &&lobsp)<br>
LOB (esc_summary) store as (tablespace &&lobsp)<br>
LOB (history) store as (tablespace &&lobsp)<br>
LOB (problemtext) store as (tablespace &&lobsp)<br>
storage(initial 128m next 128m pctincrease 0)<br>
PCTFREE 0 ; <br>
<br>
<br>
</font>At 09:56 AM 6/28/00 -0800, you wrote:<br>
>Can you check the before and after storage definitions for the
tables/CLOBS?<br>
>Maybe the tables were created one way but the move altered the way
things<br>
>are stored. Inline to out-of-line, shouldn't matter. Pctversion
defaults to<br>
>10, what's the before/after. Chunksize? I know the storage
definitions for<br>
>LOBS are important as someone here managed to "CLOBber" the
system<br>
>tablespace which was on autoextend. Fragmentation maximo! (This was
on a<br>
>development database.)<br>
><br>
>Can you share the query that produced the before/after storage
results? I'm<br>
>just getting started in the LOB scene. I'm anticipating increased LOB
usage<br>
>so I need to nail down all the storage and operational issues.<br>
><br>
>We'll be curious as to what you discover on this one. Please keep us
posted.<br>
><br>
><br>
>Thanks,<br>
>Steve Orr<br>
><br>
><br>
>-----Original Message-----<br>
>Sent: Tuesday, June 27, 2000 5:26 PM<br>
>To: Multiple recipients of list ORACLE-L<br>
><br>
><br>
>Thanks for the ideas steve. The tablespace was empty before the load.
This<br>
>is our first test run so we re-creted the whole schema from scratch.
This<br>
>happens on all our perl data loads where we are importing into CLOB
columns.<br>
>I have about 12 tables where this is happening and in each case I
shrink the<br>
>data down to about 5% or less than the orginal space it was taking.
Ive also<br>
>checked the index storage and its not the problem. I have to rebuild
the<br>
>indexes after this as the move table command renders them
invalid.<br>
><br>
>anyway its not a major problem just some weirdness either in the perl
DBD<br>
>loads or the way oracle stores CLOBS from an insert.<br>
><br>
>John Barron<br>
><br>
>At 02:54 PM 6/27/00 -0800, you wrote:<br>
><br>
>What about the CRTS_QISI_LOB tablespace? What's the before/after on
it? Did<br>
>the clobs start out in the CRTS_DATA tablespace? No lobs in the
system<br>
>tablespace, right? Check the tablespace_name in dba_segments
where<br>
>segment_type in('LOBSEGMENT','LOBINDEX'). Just some ideas... happy
lob<br>
>hunting.<br>
>HTH,<br>
>Steve Orr<br>
>-----Original Message-----<br>
>Sent: Tuesday, June 27, 2000 2:28 PM<br>
>To: Multiple recipients of list ORACLE-L<br>
><br>
><br>
>Hi<br>
><br>
><br>
>I think my 8i CLOB data needs a labotomy. This is my problem and my
work<br>
>around. We use perl DBD::Oracle to load our database from flat files.
Part<br>
>of it is to load embedded text data into CLOB columns in our
table<br>
>CR_HLD_QISI_CALLS. The CLOB cols are PROBLEMTEXT, ANALYSISTEXT, etc.
When we<br>
>run the perl script the 100M text file mushrooms out to 900M in
the<br>
>tablespace. Using the tablespace coalesce command has no affect on
the<br>
>tablespaces. If we MOVE the tables and CLOB data to another
tablespace (or<br>
>even in the same tablespace) and compress to one extent it shrinks
the 800M<br>
>down to 40M.<br>
><br>
><br>
>Why does it take so much tablespace? Is it the way perl loads data?
We have<br>
>set default storage on the tablespace and tables to PCTFREE=0 as
there will<br>
>be no updates to this table. The database is UTF8 with
NLSLANG=ja16euc and<br>
>we do have some indexes on the tables but none of this accounts for
the huge<br>
>space requirements for the initial upload.<br>
><br>
><br>
>Any comments, pointers would be appreciated. Ive added our move sql
and the<br>
>space used before and after the table move and coalesce.<br>
><br>
><br>
>tia<br>
><br>
><br>
>John Barron<br>
><br>
><br>
><br>
><br>
>The SQL for moving the CLOBS and tables<br>
>------------------------------------------------------<br>
>alter table CRTS.CR_HLD_QISI_CALLS move tablespace CRTS_DATA<br>
>lob (PROBLEMTEXT) store as (tablespace CRTS_QISI_LOB<br>
>storage(minextents 1));<br>
>alter table CRTS.CR_HLD_QISI_CALLS move<br>
>lob (ANALYSISTEXT) store as (tablespace CRTS_QISI_LOB<br>
>storage(minextents 1));<br>
>alter table CRTS.CR_HLD_QISI_CALLS move<br>
>lob (HISTORY) store as (tablespace CRTS_QISI_LOB<br>
>storage(minextents 1));<br>
><br>
><br>
>alter tablespace CRTS_QISI_DATA coalesce;<br>
><br>
><br>
>alter table CRTS.CR_HLD_QISI_CALLS move tablespace
CRTS_QISI_DATA<br>
>lob (ESC_SUMMARY) store as (tablespace CRTS_QISI_LOB<br>
>storage(minextents 1));<br>
><br>
><br>
><br>
><br>
>The BEFORE and AFTER space output<br>
>-------------------------------------------------<br>
>Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production<br>
>With the Partitioning option<br>
>JServer Release 8.1.6.0.0 Production<br>
><br>
><br>
>Table
CLOB Col<br>
>CR_HLD_QISI_CALLS
PROBLEMTEXT
64,430,080<br>
>CR_HLD_QISI_CALLS
ANALYSISTEXT
469,114,880<br>
>CR_HLD_QISI_CALLS
HISTORY
279,552,000<br>
>CR_HLD_QISI_CALLS
ESC_SUMMARY
1,597,440<br>
>CR_HLD_QISI_CALLS
Total
814,694,400<br>
><br>
><br>
>Table altered.<br>
><br>
><br>
><br>
><br>
>Table altered.<br>
><br>
><br>
><br>
><br>
>Table altered.<br>
><br>
><br>
><br>
><br>
>Tablespace altered.<br>
><br>
><br>
><br>
><br>
>Table altered.<br>
><br>
><br>
>CR_HLD_QISI_CALLS
PROBLEMTEXT
39,936,000<br>
>CR_HLD_QISI_CALLS
ANALYSISTEXT
532,480<br>
>CR_HLD_QISI_CALLS
HISTORY
532,480<br>
>CR_HLD_QISI_CALLS
ESC_SUMMARY
532,480<br>
>CR_HLD_QISI_CALLS
Total
41,533,440<br>
><br>
>-- <br>
>Author: Steve Orr<br>
> INET: sorr_at_arzoo.com<br>
><br>
>Fat City Network Services -- (858) 538-5051
Received on Thu Jun 29 2000 - 11:59:16 CDT
