Re: Archivelog mysteries
- From: "Bill Ferguson" <wbfergus@xxxxxxxxx>
- To: m.haddon@xxxxxxxxx
- Date: Sun, 18 Mar 2007 08:25:30 -0600
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
