Re: SGA Tuning and The Memory Advisor

On Oct 26, 9:32 am, cptkirkh <kh..._at_scic.com> wrote:
> On Oct 26, 9:52 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Oct 26, 9:20 am, bert.sca..._at_verizon.net wrote:
>
> > > On Oct 25, 4:41 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > On Oct 25, 3:42 pm,cptkirkh<kh..._at_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 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.
>
> > > > You need to remember it isn't Oracle telling you these tales, it's
> > > > TOAD. Ask Quest about this behaviour.
>
> > > > David Fitzjarrell
>
> > > 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.- Hide quoted text -
>
> > > - Show quoted text -
>
> > Yes, I misread the post originally. My mistake. We make them.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - 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

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