Re: SGA Tuning and The Memory Advisor

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? Received on Fri Oct 26 2007 - 11:32:11 CDT