techblog 

Anyone interested in high-availability in an Oracle setting would do well to read the MAA pages on the Oracle website, there are many good papers and guidelines for optimising your environment contained there. For dataguard, I highly recommend the best practices for Redo Transport & Redo Apply papers. One recommendation contained there is increasing the log_archive_max_processes parameter, which controls the number of arc processes the instance uses, which in turn can help resolve any archive gaps. Unfortunately, I think I may have over egged this one, and as they say nothing is for free. Each server process takes some memory for it’s PGA and Oracle server background processes are no exception.

Lately, our monitoring system has been warning about low free memory on one of our RAC nodes, which seemed to be correlated to the number of oracle processes running. Hence I started looking at how much memory we were using for our pga:

SQL> select * from V$PGASTAT;

NAME		                        VALUE     UNIT
--------------------------------    ---------- ------------
aggregate PGA target parameter        199229440   bytes
aggregate PGA auto target              12451840   bytes
global memory bound                    39845888   bytes
total PGA inuse                      3658247168   bytes
total PGA allocated                  5270902784   bytes
maximum PGA allocated                6450857984   bytes
total freeable PGA memory             320667648   bytes
.
.

So this Instance had allocated 5GB to the pga, it’s interesting to note that the oracle documentation state that the total pga allocated will try to be kept under the aggegrate pga target, but we are an order of magnitude over here. I had thought the pga target parameter was maximum amount that could be allocated to each session, rather than a global total. Certainly my 190MB pga_aggregate_target is being overallocated by a long way.

I then looked at V$process to check what processes were using the most pga:

SQL> SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM/(1024*1024) "ALLOC(MB)",
PGA_FREEABLE_MEM FREEABLE, PGA_MAX_MEM
FROM V$PROCESS
order by pga_alloc_mem asc;

PROGRAM 	        PGA_USED_MEM  ALLOC(MB)FREEABLE PGA_MAX_MEM
----------------------  ------------  --------- ----- -------------
.
.
oracle@PROD (ARCg)	24274349     48.0507708   0    50384885
oracle@PROD (ARCf)	24276029     48.0507708   0    50384885
oracle@PROD (ARCe)	24274205     48.0507708   0    50384885
oracle@PROD (ARCd)	24277965     48.0507708   0    50384885
oracle@PROD (ARCc)	24259573     48.0507708   0    50384885
oracle@PROD (ARC5)	24274389     48.0507708   0    50384885
.
.

Basically, every archive background process was consuming nearly 50MB of memory. All 30 of them. That is 1.5GB of RAM. Thankfully, the log_archive_max_processes parameter is dynamic and I could remedy the situation with a simple alter system command, and after a little wait the unrequired arc processes were removed and the system had a lot more free memory.