Thread: SGA advice
12-20-10, 02:02 #1Registered User
- Join Date
- May 2009
Unanswered: SGA advice
I've been asked to comment on whether the following SGA memory allocations are sized correctly for our various ORACLE production databases...
Buffer Cache, Shared Pool, Java Pool
I've retrieved the sizes of these memory sections by running the following query for each database:
name, bytes/1024/1024 "MB", resizeable
This tells me what they're currently set at.
In order to determine what they should be set to i've resorted to looking at the
"Resource Advisors" present in ORACLE Enterprise Manager Console.
(i used the "Buffer Cache Size Advisor" and the "Shared Pool Size Advisor")
These advisors plot a graph showing the effects of more and less memory - compared to what its currently set to - and indicates how that affects I/O.
This indicates whether one should add more memory or take away some.
As far as i know, these "advisors" are dependant on the ORACLE statistics being up to date?
In any event, i was wondering how else i could go about evaluating the current settings for these memory allocations without using these advisors?
I'm assuming there's a "real DBA" alternative?
I'm a newbie by the way, so this is part of a learning experience for me, which probably explains why i've been told to not use the advisors for this.
So how can i otherwise evaluate these settings??
The next thing i need to know is:
What factors should one take into account when providing new estimates?
Obviously if one simply used the advisors then the accuracy of those estimates are dependant on how up to date the database statistics are?
Any advice here would be most welcome.
Next question i have is:
What is the cost of over allocation?
To me it is obvious - it just means that there will be less memory left to distribute to other areas such as the PGA, and other parts of the SGA.
Anything else i'm missing here?
Next question is:
Can this [over allocation] be reversed easily?
i'd say YES - just change the settings back to whatever you like.
Granted, any changes made will only be to the spfile and one would have to bounce the database to have the changes taken up.
Any comments appreciated.
12-20-10, 13:04 #2Registered User
Provided Answers: 1Code:
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
SQL> select name, bytes/1024/1024 "MB", resizeable from v$sgainfo 2 3 4 ; NAME MB RES -------------------------------- ---------- --- Fixed SGA Size 2.11110687 No Redo Buffers 7.234375 No Buffer Cache Size 272 Yes Shared Pool Size 496 Yes Large Pool Size 16 Yes Java Pool Size 128 Yes Streams Pool Size 32 Yes Shared IO Pool Size 0 Yes Granule Size 16 No Maximum SGA Size 1481.34766 No Startup overhead in Shared Pool 80 No NAME MB RES -------------------------------- ---------- --- Free SGA Memory Available 528 12 rows selected.
To what new values & why?You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.