Re: Archivelog mysteries

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_at_gmail.com> 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
--
http://www.freelists.org/webpage/oracle-l