Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Tuesday, December 20, 2011

SAP note 830576 - PGA_AGGREGATE_TARGET on Oracle 10gR2

Print Friendly and PDF
SAP note 830576 "Parameter Recommendations for Oracle 10g" is quite a popular one for me.
It lists all the SAP recommended Oracle 10g parameter settings for Oracle 10.2.0.4 and 10.2.0.5.
It's a good point of reference and I'd recommend you implement it as a baseline before tuning the system further.
It has a buddy note, 1289199 "Information About Oracle Parameters" which describes some of the parameters in more detail.

Unfortunately, there is a major flaw on note 830576.  When setting PGA_AGGREGATE_TARGET the SAP note says 20% of available memory.  It fails to mention that this should be 20% of the SGA size, not O/S memory.
The Oracle docs (see MYOS note 153367.1) say that the value should be:

Syntax                PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value      10 MB or 20% of the size of the SGA, whichever is greater
Modifiable         ALTER SYSTEM
Range of values Minimum: 10 MB
                         Maximum: 4096 GB - 1

The Oracle note goes on to say that when sizing the Oracle database memory areas, you should consider the SGA size first, then assign any spare memory to PGA.
Now in an SAP landscape with a single Central Instance + Dialog Instance on the same server as the database, you may wish to use the SAP 70/30 rule (70% to SAP, 30% to Oracle).

My order of sizing would look something like this:
1, Determine number of users of SAP system.
2, Determine number of DIALOG work processes + Background work processes + Update processes (~= Oracle "processes").
3, Determine leftover memory for Oracle SGA (split between pools, SAP doesn't support automatic memory management).
4, Determine leftover memory for PGA + overheads.

If you get to step 4 and you have diddly squat RAM left (hardly any), then consider adding more RAM to your server.  Remember, we don't like pageing.

SAP note 789011 "FAQ: Oracle Memory Areas", provides a range of SQL statements for checking the actual size of the PGA.  Since PGA_AGGREGATE_TARGET is only telling Oracle what you would like the maximum PGA allocation to be.

When you set PGA_AGGREGATE_TARGET, you also allow Oracle to release PGA memory back to the O/S.  Using the *_AREA_SIZE parameters and setting PGA_AGGREGATE_TARGET to 0, forces a specific size of PGA which does not release the memory to the O/S.

/* Actual PGA consumption */
SELECT VALUE FROM V$PGASTAT WHERE NAME = 'total PGA allocated';

/* Chronological PGA allocation (needs AWR license) */
SELECT SUBSTR(S.END_INTERVAL_TIME, 1, 40) TIME,
               P.VALUE PGA_ALLOCATION
  FROM DBA_HIST_SNAPSHOT S, DBA_HIST_PGASTAT P
WHERE P.NAME = 'total PGA allocated'
    AND S.SNAP_ID = P.SNAP_ID
ORDER BY P.SNAP_ID;


Oracle states:
Memory Area                                                             Dedicated Server     Shared Server
Nature of session memory                                                     Private           Shared
Location of the persistent area                                               PGA              SGA
Location of part of the runtime area for SELECT statements  PGA              PGA
Location of the runtime area for DML/DDL statements          PGA              PGA

When installing Oracle for SAP, by default it uses DEDICATED server mode (see note 70197).

No comments: