Re: Archive Log affects performance?

Archiver is not, in and of itself, a performance bug-bear, but since it tends to copy online logs about a third of the speed that LGWR can fill them up, it can most certainly cause problems (namely, a system hang as LGWR loops back to re-use an online log which ARCH hasn't managed to archive yet).

However -you have 8.1.7, and provided your DBA has configured to allow multiple archiver processes to be spawned (log_archive_max_processes=x), Oracle will notice the backlog of online logs awaiting archiving, and start additional ARCH processes to deal with it -so that particular potential bottleneck should not be too much of a drama (though since the maximum possible number of ARCH processes is merely 10, it might still be).

But after all that's said and done, I'd suggest you're looking in the wrong place in any case. The real thing that's going to slow the insert down is not what ARCH is doing (assuming all the above and a properly tuned redo sub-system to start with), but the mere fact that inserts create redo to start with. Flooding your redo buffer with a great wadge of data is not particularly pleasant, because poor LGWR will be having to flush like crazy. You can switch that off without having to disable archivelog mode itself merely by altering the relavant table to be 'NOLOGGING' -and then making sure that this bulk insert is being done by SQL Loader. If it's just a conventional 'insert' statement you're doing then NOLOGGING won't do anything for you at all. Otherwise, it will switch off the generation of all redo -and since nothing is getting into the online logs, you worries about its impact on ARCH will be sorted at the same time.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Noodles" <noodles_at_aol.com> wrote in message
news:20020119073024.13323.00002577_at_mb-cp.aol.com...


> OS:  HP Unix 11.0.x - (K and N class)

> ORA:  8.1.7

>

> I'm getting ready to do a multi-million row update on 40+ databases around

the


> world.  Before the update, I'll get a cold backup  and no users will be

logged


> in during the update.

>

> Do you think I can tweak a little more performance if I place the database

in


> non-archive log mode?  I've got lots of disk space and no IO contention.

>

> Some collegeaues think the archiver process puts a minimal system load but

> others disagree.  I suppose I could just test it but the senior DBA is the

one


> who thinks the archive process is not a performance factor. ( and I can't

> convince him to test his theory)

>

> Does anybody have any benchmark data/anecdotes to prove one way or the

other ?


>

> TIA,

> Cliff