oracle SGA_target, SGA_max_Size and server physical/virtual memory


  I am having these settings in my Windows Server :   a) Physical Memory of 32GBs  and b) One database with sga_target=2Gb and SGA_Max_size= 8Gb.

   I am confuse with a few things.

     a) If the server have 32gb, those the Oracle database reserves 2GB for it uses, if it needs more goes no more than 8Gb ?

     b) The server have 30Gb for others process because there are 2GB reserves for Oracle ?  ---  

     c)  If database requires more memory  , let say a batch process requires 12Gb , the database will only goes no more than  8Gb ?

     d)  If there some others process using RAM, and the database need it, it will take out those process to use virtual memory and the OS will prioritize the database and let it use RAM until 8Gb ( sga_max_taget).  ?

   I am confuse on how the memory managament works.
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:
I would start in the docs:  Memory Architecture.

The database uses other memory besides the SGA.  the PGA for example.

There are also database connections and programs that use memory outside of the database and in the OS itself.
Geert GOracle dbaCommented:
you got the general idea actually
a) yes
b) well yes, unless oracle has taken more
c) yes
d) yes and no, it all memory is used before starting the database, then the database will have a lot of difficulty starting

are you having performance problems or something alike ?
getting errors ?
slightwv (䄆 Netminder) Commented:
>>you got the general idea actually

The 'yes's above are true only of the SGA.  The SGA is bound by the  SGA_Max_size setting.

As I mentioned there are memory allocations that happen outside of the SGA that are not controlled by  SGA_Max_size.

So, with a lot of connections doing a lot of work, 'Oracle' can use a lot more than 8Gig.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

joe_echavarriaDatabase AdministratorAuthor Commented:
I am having some performance problems and wanted to have those things clear in mind.

Now i have a clear picture of all of this.

Geert GOracle dbaCommented:
in general memory ... is not the item to look at in case of performance
ITSS ... It's the stupid Sql

without any tools in place for monitoring like em

find the queries with most consistent gets or disk reads per execution in v$sql
and see if they can be changed to use less resources

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:
You may be able to get better Oracle performance by setting SGA_MAX_SIZE to 16Gb (if you have no non-Oracle programs on this server) and setting the SGA_TARGET close to 16Gb.  That would allow Oracle to cache a bigger portion of the database in memory (assuming that your database size is larger than 2Gb) so tghat may give you better Oracle performance.

But, I agree with Geert, that usually slow Oracle performance is caused by poor SQL queries or statements that do not use the existing indexes to allow Oracle to give you good performance.
joe_echavarriaDatabase AdministratorAuthor Commented:
Thanks a lot for you help.
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.