Ho) to /etermine Memory #ettings for Oracle /ataase elease 11g

Tis section outlines o# to monitor and ie# Oracle system?related statistics& and tune Oracle data%ase memory parameters. Tere are many #ays to determine optimal memory settings& %ut te preferred #ay is to use memory adisors& including Memory 0disor& S,0 0disor& Sared Pool 0disor&

-uffer ace 0disor& and P,0 0disor. =ou must ae an Oracle login #it D-0 priileges to use tese adisors and to perform te follo#ing tasAs. 1otice tat most of te @ueries %elo# ae e@uialent grapical interfaces troug Oracle Enterprise Manager.

otal Memory #iBing MEMOKJA!E4

MEMO<=>T0<,ET specifies te Oracle system?#ide usa%le memory& including %ot S,0 and P,0. Prior to Oracle data%ase release 66g& S,0 and P,0 ad to %e tuned separately.

If a data%ase is upgraded from Oracle 6g to 66g& MEMO<=>T0<,ET can %e determined %y simply adding S,0>T0<,ET and P,0>0,,<E,0TE>T0<,ET from te Oracle 6g data%ase .

If a data%ase is upgraded from Oracle 5i to 66g& MEMO<=>T0<,ET can %e determined %y adding

P,0>0,,<E,0TE>T0<,ET and all S,0 components& including D->0HE>SIE& SH0<ED>POO:>SIE&

:0<,E>POO:>SIE& 0J0>POO:>SIE& etc.

If a data%ase as not %een upgraded from an earlier ersion& and as no istory references& #e

recommend tis parameter to %e initially set to 6 to ! ,-& depending on system resources and system limits. 0fter te data%ase as %een in use for some time& tis parameter can %e tuned as follo#s $Tis tuning also applies to te a%oe t#o upgrade scenarios*B

S>L? select @ <rom vmemory_tar!et_a.vice or.er &y memory_si9eB

MEMO$Y_SIE MEMO$Y_SIE_"ACTO$ EST6_6:_TIME EST6_6:_TIME_"ACTO$ DE$SION

    

;3' (8 +83 ;(,++ '

-7' (78 ,*7 ;('7*; '

,*' ; ,+; ; '

+8' ;(-8 ,,8 (%3;7 '

8+' ;(8 ,,8 (%3;7 '

*,' ;(78 ,,8 (%3;7 '

7-' - ,,8 (%3;7 '

Te ro# #it te MEMO<=>SIE>70TO< of 6 so#s te current si;e of memory& as set %y te

MEMO<=>T0<,ET initiali;ation parameter& and te amount of D- time re@uired to complete te current

#orAload. In preious and su%se@uent ro#s& te results so# a num%er of alternatie

MEMO<=>T0<,ET si;es. 7or eac alternatie si;e& te data%ase so#s te si;e factor $te multiple of te current si;e*& and te estimated D- time to complete te current #orAload if te MEMO<=>T0<,ET parameter #ere canged to te alternatie si;e. 1otice tat for a total memory si;e smaller tan te current MEMO<=>T0<,ET si;e $!) in tis e'ample*& estimated D- time $ESTD>D->TIME* increases.

1otice also tat in tis e'ample& tere is noting to %e gained %y increasing total memory si;e %eyond 9/M-& %ecause te ESTD>D->TIME alue is not decreasing. So& in tis e'ample& te suggested

MEMO<=>T0<,ET si;e is 9/M-.

#!A #iBing #!AJA!E4

1ormally& S,0 is tuned automatically %y Oracle data%ase if 0utomatic Memory Management is ena%led.

-ut a D-0 can still monitor te si;e of S,0 and find out if it is at te optimal si;e.

S>L? select @ <rom vs!a_tar!et_a.vice or.er &y s!a_si9eB

SFA_SIE SFA_SIE_"ACTO$ EST6_6:_TIME EST6_6:_TIME_"ACTO$ EST6_PHYSICAL_$EA6S

    

-%' (8 ++3;7* ;(*873 ;*,*;',

+,8 (78 ,,%,,* ;(-88- ;*,*;',

83' ; -';3** ; 8;,33;

7-8 ;(-8 -';3** ; 8;,33;

37' ;(8 -';3** ; 8;,33;

;';8 ;(78 -';3** ; 8;,33;

;;*' - -';3** ; 8;,33;

-ased on te similar analysis to a%oe& #e can see te current setting of S,0>T0<,ET is already optimal.

P!A #iBing P!AJA!!E!AEJA!E4

:iAe S,0& P,0 is also automatically tuned %y Oracle data%ase if 0utomatic Memory Management is ena%led. Te follo#ing @uery can %e used to monitor if P,0 si;e is properly si;ed. Te result #ill %e so#n similar to te @uery results of \memory>target>si;e and \sga>target>si;e.

SQL> select * from v$pga_target_advice order by pga_target_for_estimate;

'O!J>*FFE #iBing

In te system ie# \sysstat& te alue for redo %uffer allocation retries reflects te num%er of times a user process #aits for space in te redo log %uffer. Tis alue sould %e near ;ero for a properly si;ed data%ase. 7or e'ampleB

select name, value from v$sysstat

where name = 'redo buffer allocation retries'

 NAME VALUE

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%

redo buffer allocation retries 1021967

If te log %uffer as no room for updates& te data%ase must #ait and re try. In tis e'ample& te data%ase as retried a total of 6&26&5)4 times. To improe performance& increase te :O,>-U77E<

parameter alue. Tis alue is e'pressed in %ytes and must %e a multiple of te Llog %locA si;e alue&

#ic is te operating system %locA si;e. 7or a 7inancial Management application& set :O,>-U77E< to a minimum of (M-& and ten use te a%oe @uery to monitor te performance and increase as

necessary. If :O,>-U77E< needs to %e increased& #e recommend gro#ing it %y /N at a time.

Ho) to .alculate t+e %umer of Processes for Oracle /ataase elease 11g

Te num%er of user processes tat can simultaneously connect to Oracle data%ase is limited %y te P<OESSES initiali;ation parameter of te Oracle data%ase instance. 7or an 7inancial Management application& te alue of tis parameter depends on te connection pool si;e parameter of tat

application. Eac 7inancial Management application re@uires a minimum of 2 data%ase connections&

plus te 7inancial Management data%ase connection pool setting alue. 7or e'ample& if te 7inancial Management data%ase connection pool setting is 9& ten eac 7inancial Management application re@uires at least ) Oracle data%ase connections. 0s a result& te alue of P<OESSES sould %e set

iger tan ).

%ote Te total num%er of serers in an 7inancial Management cluster& and te total num%er of applications& affect te num%er of re@uired data%ase connections.

:et us use an e'ample to illustrate o# to calculate te num%er of processe s tat #ill connect to te Oracle data%ase. Suppose a cluster of t#o 7inancial Management application serers as t#o

applications on eac serer. Te 7inancial Management data%ase connection pool setting is 9. Te minimum num%er of Oracle data%ase connections is calculated as te sum of te connection pool setting plus 2& times te num%er of applications& times te num%er of application serersB $9  2*

Z 2 Z 2  29. 7or additional safety& multiply tis num%er %y a factor of 6.6 to allo# for ancillary connections and general usage of te data%ase. onsidering tat Oracle data%ase also as some

%acAground processes& #e add 2 to tis num%er to arrie at te alue of P<OESSES. So& in tis case& P<OESSES sould %e set to 2).

In general&

P<OESSES  $7inancial Management connection pool setting  2* Z $1um%er of 7inancial Management applications* Z $1um%er of 7inancial Management application serers* Z 6.6  2.

Ot+er .onsiderations

#+ared #er&er &s /edicated #er&er

7inancial Management re@uires all connections to Oracle data%ase to %e sered %y dedicated serer processes. In oter #ords& 7inancial Management does not #orA #it sared serer processes.

Dedicated serer processes consume more PU and memory resources %ut aciee %etter performance. To use dedicated serer& te net serice name alue sould include te

SE<JE<DEDI0TED clause in te connect descriptor. Here is an e'ample of net serice configured for dedicated serer processesB

HFMDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = hfm.oracle.com)(PORT = 1521)) (CONNECT_DATA =

(SERVER = DEDICATED) (SERVICE_NAME = HFMDB1)

) )