SGA Parameter Setting

[former_member182034]

hi all,

I want to set the value of below parameters against ECC6, Oracle 10205, Window server 2008 with( Processor Xeon 8C E7-2820 & 32GB Ram).

sga_max_size=

db_cache_size=

shared_pool_size=

pga_aggregate_target=

would you suggest me the value of these parameters.

and

which other parameter i have to set with respect to above parameter?

Regards,

[Former Member · Accepted solution]

Hi Majmali,

Please go through below link for oracle parameter recommendations.

[http://venkata-pagolu.blogspot.com/2011/07/server-parameters-for-oracle-9i-and-10g.html]

Regards,

Venkata S Pagolu

[nicholas_chang]

Hi Majamil,

If your server a CI/DB instance? If yes, SAP rules of thumb recommends either you assign 60(SAP) / 40 (DB) or 70 (SAP) / 30 (DB), but remember, there's no hard rules to follow. You can tune further after weeks of monitoring.

Generally, i'll assign as below:

40% of 32GB = 12GB~

32GB - 12GB = 20GB. You can assign 17-18GB to SAP, reserve 3GB for OS, Backup, and external applications.

db_cache_size= 9GB

shared_pool_size= [ (#CPU<4 in your case> on DB server / 4)] * 350MB + 300MB , = 650MB

pga_aggregate_target= 20% of DB memory, therefore = 2.4GB

Don't assign any value to sga_max_size. You can refer to Note 830576 - Parameter recommendations for Oracle 10g for more info.

Hope it helps,

Thanks,

Nicholas Chang

[former_member182034]

hi Nicholas,

I am asking about only DB instance while you sent me information about DB/CI instance.so, what is SAP rules of thumb rule for only DB instance.

now tell me how much memory i have to assign these parameter and which parameter will be effected due these one.

Regards,

[nicholas_chang]

Hi Majamil

For standalone DB instance, you can assign as below::

db_cache_size= 22GB

shared_pool_size= 1050MB

pga_aggregate_target= 6,4GB

Just take it as reference where you can fine tune it based on your requirement.

Cheers,

nicholas Chang

[former_member182034]

hi Nicholas,

thx for your expert suggestion.

sorry , i would not explain properly,

in our environment, PRD server consists on PRD DB and PRD APP separately on different machine and machine specs is:

SAP Database Server:

2 Processor x3690 X5, Xeon 8C E7-2820 105W 2.00GHz

Cache 18MB L3

Memory (Installed) 32 GB PC3L-10600 CL9 ECC DDR3 1333MHz

SAP Application Server:

IBM e-Server x-3650M3

2 Processor x3650 M3, Xeon 6C E5645 80W 2.40GHz/1333MHz

Cache 12M

Memory (Installed) 24 GB PC3L-10600 CL9 ECC DDR3 1333MHz

now what will be the SAP rules of thumb recommends for each server?

Thanks and Best Regards,

[nicholas_chang]

For standalone Application and DB server, there's no rules of thumb. You can assign memory accordingly, however, you should fully optimize memory available to ensure better performance and value for hardware cost invested.

[former_member182034]

hi Nicholas,

ok, there is no rules of thumb,

you just send me your expert suggestion for each server.

waiting you reply.

Regards