SGA Tuning and The Memory Advisor
cptkirkh
report that my SGA is not large enough. I have the sga_target to a
number higher than that but it keeps returning the following;
FINDING 1: 78% impact (16293 seconds)
-------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
RECOMMENDATION 1: DB Configuration, 78% benefit (16293 seconds)
ACTION: Increase the size of the SGA by setting the parameter
"sga_target" to 1140 M.
ADDITIONAL INFORMATION:
The value of parameter "sga_target" was "912 M" during the
analysis
period.
I thought that on a 10g, actually it is 10.2.0.3 on windows 2003, that
by setting the sga_target it turns on the SGA automated memory
management and will adjust the SGA if it needs more up to the
sga_max_size number. if that is the case then why do i keep recieving
these findings? Thanks for your help.
fitzj...@cox.net
You need to remember it isn't Oracle telling you these tales, it's
TOAD. Ask Quest about this behaviour.
David Fitzjarrell
Jerome Vitalis
On Oct 25, 3:42 pm, cptkirkh <kh...@scic.com> wrote:
>> Wehn I run the ADDM/AWR in TOAD it keeps returning the following report
>> that my SGA is not large enough. I have the sga_target to a number
>> higher than that
Did you really mean sga_target here? Or is it sga_max_size?
>> I thought that on a 10g, actually it is 10.2.0.3 on windows 2003, that
>> by setting the sga_target it turns on the SGA automated memory
>> management and will adjust the SGA if it needs more up to the
>> sga_max_size number.
No. The sizes of the *pools* inside the SGA are automatically adjusted
and their sum is sga_target.
You can increase sga_target dynamically up to sga_max_size (this is the
shared memory size allocated from the OS at instance startup and you
cannot change it dynamically.)
cptkirkh
<vitalismanN05...@gmail.com.invalid> wrote:
It states that my sga_target is not high enough and that i should set
it to the value listed. Also I get the saem resopnse from the ADDM
inside of the OEM so I know it is not just a TOAD thing. So that
makes sense why the ADDM keeps asking me to increase the sga_target.
The problem is that number varies dependign on the time fo day. Is
there a query that ican run to get a best pracitce average to find out
what to increase my sga_target or should I jsut tKe the advice of the
ADDM and set it to the highest value it tells me to for the past
week.
bert....@verizon.net
Not true. Toad simply calls the Oracle PL/SQL pre-canned packages to
generate the report in either text or html. Oracle creates all the
content.
fitzj...@cox.net
>
> - Show quoted text -
Yes, I misread the post originally. My mistake. We make them.
David Fitzjarrell
cptkirkh
> On Oct 26, 9:20 am, bert.sca...@verizon.net wrote:
>
>
>
>
>
> > On Oct 25, 4:41 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
>
> - Show quoted text -
Ok so i made those changes and in the first hour ADDM gave me back
this answer.
The SGA was inadequately sized, causing additional I/O or hard parses.
RECOMMENDATION 1: DB Configuration, 100% benefit (2317 seconds)
ACTION: Increase the size of the SGA by setting the parameter
"sga_target" to 1430 M.
ADDITIONAL INFORMATION:
The value of parameter "sga_target" was "1144 M" during the
analysis
period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant
database time.
(51% impact [1178 seconds])
SYMPTOM: Hard parsing of SQL statements was consuming
significant
database time. (4.7% impact [109 seconds])
Is this the case of once you give it a little it wants a lot or am I
going to get to a point where it is happy?
joel garry
You need to check what's getting hard parsed. If your app is sending
in a bunch of identical statements differening only by literals, it
should be changed to use bind variables, or no one will ever be
happy. Then again, 4.7% impact isn't all that much.
This requires more investigation than an advisor will do on its own.
There's plenty of stuff about on the web. Try searching for hard
parse on asktom.oracle.com to start.
More impactful is the Wait class "User I/O", which may or may not be
helped by increasing the SGA buffers. I've seen that one uselessly
keep asking for more and more. You might start with the performance
guide discussion about v$bh, then google about for more correct
information. This is where I've seen moving a couple of key tables
and indices into a recycle pool have magically delicious results.
Other investigation about what exactly is bottlenecking I/O may
provide useful information. Simply finding the most active
tablespaces and making sure they aren't conflicting with redo or
archiving can be useful here too. On the other hand, you're also
going to see this with poor optimizer choices - if something is
repeatedly full table scanning when it should be grabbing a few things
out of the SGA and sorting in memory, that might be an easy fix. On
the third leg, you might just have a busy system doing what it is
supposed to as best it can. Has anything changed, are people
complaining? This is where you have to watch for compulsive tuning
disorder.
jg
--
@home.com is bogus.
http://users.skynet.be/J.Beever/index.html