Archivelog mysteries
- From: "Bill Ferguson" <wbfergus@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 18 Mar 2007 06:10:19 -0600
Hi all,
I've noticed a strange occurence on my database this weekend with archive
logs growing much, much larger than my database itself.
I'm running 10.2.0.1 on Windows Server 2003. I'm the only one on this
weekend running any processes, and the one I'm running is an update to a
table in it's own tablespace defined with "NOLOGGING". Once complete, the
table will be approximately 11.6 GB, the tablespace itself is 15 GB. The
tablespace has an 8k blocksize, and the average size of the XML field is
8.7k.
My program that I'm running takes the first master id and then loops through
approximately 30 child tables and populates a field in the table with XML
output. This is the only 'change' being made to any table. (Before running
the program, I re-created the table by truncating it and then inserting the
master id's for all my master records. At this point, the table was
approximately 1.7 GB.)
For some reason, this has generated approximately 70 GB of archive logs
already, and I'm only about 2/3 of the way through all the records.
Obviously there's a lot more being stored in the archive logs than just the
data change, but what? My PL/SQL program is only 12k, so even if it's
storing a before and after version of the table's record for each change,
and the entire PL/SQL program that changed it, the numbers don't add up.
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
Other related posts:
