Re: Archivelog mysteries

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)) ; I'm doing this so, with Oracle text, all of the data fields (about 250) in all of the child tables are in one place for a search. I also have an (Application Express) screen designed so users can either do a "anywhere in the record" type search, similar to Google, or specify which table(s)/field(s) to search for which text by using the XML tags. It works pretty well, but building the initial CLOB field takes several days to run, and I need to constantly keep checking my server to ensure the flashback area doesn't get filled up with the archive logs. I also have a call to the program to update the CLOB whenever a user makes a change to any data through an INSTEAD_OF trigger on each view (the users only access).

Since I'm the only one on this weekend, I just delete the archive logs periodically and then re-run the RMAN commands to delete the references within Oracle. Probably not the best situation, but it won't hurt anything to revert back to a previous valid backup from Friday, other than the searching won't work until the field is rebuilt. I've also dropped the Oracle Text index before running this, as performance was terrible with it syncing on commit. It only takes about 2 hours to build the index after the field is populated, but it takes over 2 days to populate the field. With the index active, I was having a throughput of about 1 record per minute, without the index the throughput is about 112 records per minute.

Thanks for the feedback guys.

-- 
-- Bill Ferguson

On 3/18/07, Michael Haddon <m.haddon_at_tx.rr.com> wrote:


>

> Bill -

>

> What is the table definition? - I have seen updates to a table that

> stores xml in a CLOB generate this kind of archive when there are alot

> of upates to the CLOB column. There are several updates that take place.

> If you can - create a test instance somewhere, run the test, then mine

> the archive logs and look at how many updates to the LOB index are

> actually taking place.

>

> We have this issue but I have finally convinced the dev team to use XML

> tables and varchar2 datatypes wherever possible instead of CLOB column

> for high activity tables.

>

> Hope this helps

>

> Mike

> >

> > Any idea why a table that's only 11.6 GB (when complete) would have

> > generated approximately 100 GB of archive logs? It doesn't seem

> > logical to me (at this point), that the archive log data would be

> > approximately 10 times the size of the data.

> >

> > --

> > -- Bill Ferguson

>

>

> --

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

>

>

>


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