oracle SGA_target, SGA_max_Size and server physical/virtual memory

Hi,

  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.
LVL 1
joe_echavarriaDatabase AdministratorAsked:
Who is Participating?
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.
http://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#CNCPT007

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.
0
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 ?
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.

Thanks.
0
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
0

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.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
Thanks a lot for you help.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.