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).
