Re: Archivelog mysteries

  • From: Michael Haddon <m.haddon@xxxxxxxxx>
  • To: Bill Ferguson <wbfergus@xxxxxxxxx>
  • Date: Sun, 18 Mar 2007 11:45:55 -0600

Bill -

What do the archive logs have in them as far as the number of updates to the LOB index? I believe the numbers will surprise you. You can see this using logminer.

Check your CLOB column - whats the ratio of records with XML greater than 4000 bytes?, If most records are less than that I would not store the CLOB externally.

Still would look into an XML table rather than a CLOB - or even one or more VARCHAR2 columns to store the complete record.

As an example - Look into how oracle stores the source for it's functions and procedures, which stores alot of text data. The synonym is USER_SOURCE, check out how the lines of text are stored as VARCHAR2 columns. This may give you some options.

Let me know how it goes.

Mike


Bill Ferguson wrote:
Alex,
 
Thanks for the link. I'm browsing through the presenation now, and while it shows there's lots of other information written into the redo, it really isn't all that much.
 
Mike,
 
The table itself is pretty simple:
  CREATE TABLE "USGS"."SEARCH_TABLE"
   ( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
 "XML_CLOB" CLOB NOT NULL ENABLE,
 "UPDATE_DATE" DATE DEFAULT sysdate,
  CONSTRAINT "SEARCH_TABLE_PK" PRIMARY KEY ("DEP_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USGS_LOB_DATA"  ENABLE,
  CONSTRAINT "SEARCH_TABLE_FK" FOREIGN KEY ("DEP_ID")
   REFERENCES "USGS"."DEPOSITS_BASE" ("DEP_ID") ON DELETE CASCADE ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USGS_LOB_DATA"
 LOB ("XML_CLOB") STORE AS (
  TABLESPACE "USGS_LOB_DATA" DISABLE STORAGE IN ROW CHUNK 32768 PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2007