Beware of Oracle LOG_ARCHIVE_MAX_PROCESSES Parameter!!

Author: Jason

Oracle LOG_ARCHIVE_MAX_PROCESSES parameter specifies the number of active ARCH processes (ARC0-ARCn) initially invoked by Oracle. The value of LOG_ARCHIVE_MAX_PROCESSES parameter is evaluated at instance startup if LOG_ARCHIVE_START initialization parameter is set to true. Achieving high availability in Oracle is the dream of everyone using Oracle as their enterprise database and it is often recommended to increase the Oracle LOG_ARCHIVE_MAX_PROCESSES parameter in order to achieve this target. In this article we will discuss how increasing the value of LOG_ARCHIVE_MAX_PROCESSES parameter should not be always the best solution for your database system and why you should be careful while setting the value of Oracle LOG_ARCHIVE_MAX_PROCESSES parameter.

Changing LOG_ARCHIVE_MAX_PROCESSES parameter:

While running ARCHIVELOG mode you can change the LOG_ARCHIVE_MAX_PROCESSES parameter by using below command

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3

The number of ARCn processes should be defined based on the number of log files and database workload. For example, if you are using an active database with 6 or more logfile groups then you can use 2 or more Archivers.

Oracle multiple log writer (LGWR) can also be used to automatically increase the number of ARCn processes but the better approach is to use LOG_ARCHIVE_MAX_PROCESSES parameter so that the overhead for dynamically starting new processes is avoided and ARCn can handle workload.

Changing LOG_ARCHIVE_MAX_PROCESSES for handling Network Traffic :

Sometimes it happens that we face application slow down due to heavy network traffic on the application and we tend to increase the LOG_ARCHIVE_MAX_PROCESSES parameter however this is not always the best solution. Whenever you face application slow down due to network traffic then instead of increasing the LOG _ARCHIVE_MAX_PROCESSES parameter one should first try to increase bandwidth or decrease network roundtrips or exchanged bytes.

Changing LOG_ARCHIVE_MAX_PROCESSES for resolving Archive Gaps :

Sometimes it happens that we face the problem of Archive Gaps whereby a range of archived redo log files is created. Archive gaps are created whenever the next archived redo log file generated by the primary database is not applied to the standby database. It is usually recommended to increase the LOG_ARCHIVE_MAX_PROCESSES parameter in order to resolve archive gaps by controlling the number of arc processes the instance uses.

Each server process including Oracle server background processes and if your monitoring system is warning about low free memory on the RAC nodes then you first need to determine the memory utilized for the PGA.

SQL> SELECT NAME, VALUE FROM V$PGASTAT;

          NAME                            VALUE
--------------------------------       ----------
aggregate PGA target parameter         182562773
aggregate PGA auto target              21252741
global memory bound                    49242818
total PGA inuse                        3468447232
total PGA allocated                    5197688320
maximum PGA allocated                  6782717317
total freeable PGA memory              382310731

Here we can see that about 5GB has been allocated to the PGA which is far greater than the AGGEGRATE PGA target. Now we need to determine that which processes are utilizing the most PGA by running below command.

SQL> SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM/(1024*1024) "ALLOCATION(MB)", PGA_FREEABLE_MEM FREEABLE, PGA_MAX_MEM FROM V$PROCESS ORDER BY PGA_ALLOC_MEM ASC;

      PROGRAM         PGA_USED_MEM ALLOCATION(MB)    FREEABLE   PGA_MAX_MEM
-------------------   ------------ --------------    ---------  ------------
oracle@PROD (ARC5)     25164349     54.2087615         0         48548778
oracle@PROD (ARC13)    25166029     54.2087615         0         48548778
oracle@PROD (ARC9)     25164205     54.2087615         0         48548778
oracle@PROD (ARC8)     25167965     54.2087615         0         48548778
oracle@PROD (ARC2)     24259573     54.2087615         0         48548778
oracle@PROD (ARC6)     25164389     54.2087615         0         48548778

Here we can see that every archive background process is utilizing about 55 MB of the memory and we should be careful in setting the value of log_archive_max_processes parameter in order to avoid over allocation of memory. Oracle allows us to dynamically change the value of LOG_ARCHIVE_MAX_PROCESSES parameter and hence we can solve this problem by just running the ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES command. This will remove the processes that are not required and we will get a lot more free memory. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Secrets of Truly Dropping a Table in Oracle 10g!!
   Beware of Oracle Automatic Memory Management Pitfalls!!
   Beware of DISTINCT Clause, Important Concerns!!
   Great Tips on solving Temporary Tablespace Problems!