Relations between server physical memory and the parameters sga_target and sga_max_size in Oracle 10g


  I want to know according to the physical memory in a windows server, what will the good parameters values
for sga_target and sga_max_size ? , if there is only one database in that server and nothing else running.

  Window 2008 SP2 - 16 core 2.67ghz intel. - 32GB Ram.

Any relations between physical memory, sga_target and sga_max_size ?

joe_echavarriaDatabase AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Sorry but there isn't enough information to provide an answer.

Every connection to Oracle uses additional memory so you'll need to account for maximum number of connections.

There is also the PGA stuff and any additional pools you might need.

Personally, I would let Oracle manage ALL memory and just set:

and not worry about the sga specific parameters.

I would probably start off by giving Oracle (not just the SGA) about 70-80% of available RAM after startup.  Then monitor usage over time.

The goal is to NEVER EVER NO MATTER WHAT have memory page to disk!!!!
joe_echavarriaDatabase AdministratorAuthor Commented:

  I am confuse with those 4 parameters: memory_max_target, memory_target, sga_target and sga_max_target.

  Do i have to set  the 4 parameters ?

  An example of the settings will be :

    memory_max_target to 32G   or to 23G( 70-80% of available RAM.
    memory_target to ????
    sga_target to ? ???
    max_sga_target to  ????
joe_echavarriaDatabase AdministratorAuthor Commented:
Can memory_target and memory_máx_target be use in 10g? ,  or are parameters for 11g?
slightwv (䄆 Netminder) Commented:
>>Can memory_target and memory_máx_target be use in 10g? ,  or are parameters for 11g?

Sorry.  I missed the 10g in the question.  I believe those are new to 11g.

>> Do i have to set  the 4 parameters ?

In 10g you also need to be aware of PGA_AGGREGATE_TARGET
This chapter explains how to allocate memory to Oracle memory caches, and how to use those caches. Proper sizing and effective use of the Oracle memory caches greatly improves database performance.

Oracle recommends automatic memory configuration for your system using the SGA_TARGET and PGA_AGGREGATE_TARGET initialization parameters. However, you can manually adjust the memory pools on your system and that process is provided in this chapter.

pga_aggregate_target, sga_target and sga_max_size are what should be set in 10g to use Automatic Memory Management.

>>or to 23G( 70-80% of available RAM.

Available RAM isn't what the server has installed.  It is what is free after everything is up and running.

The OS uses some.  Other services running use more.  Give Oracle 70-80% of what is left after you have everything possible running.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
I would start by setting  sga_max_size to 50% of physical RAM and  PGA_AGGREGATE_TARGET to 10 or maybe 15% of of physical RAM.  The sga_target value should be set a bit lower than sga_max_size, so maybey 45-485vof physical RAM.

Then monitor the server this way for a while (some hours at least, maybe some days) to make sure that there is no page file usage.  Then you can try slightly higher values for these.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.