Re: Archivelog mysteries
- From: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
- To: wbfergus@xxxxxxxxx
- Date: Sun, 18 Mar 2007 09:00:31 -0500
Bill,
You situation seems to be pretty "normal" for many batch programs
these days as they are working and updating the same data over and
over again.
There is lot's of overhead in archive logs and it depends how you
updated your record. Even if the tablespace is NOLOGGING, only direct
path inserts and LOB operations (depending on release maybe) are able
to use nologging operations and even they generate some redo.
There is also undo generated that you should account for.
If you are really keen on content of you redo - dump it and
investigate. You would want to have a look at Julian Dyke's
presentation then -
julian.dyke.users.btopenworld.com/com/Presentations/RedoInternals.ppt.
Good luck,
Alex
On 3/18/07, Bill Ferguson <wbfergus@xxxxxxxxx> wrote:
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
--
Best regards,
Alex Gorbachev
http://www.oracloid.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
