Re: Diagnose Slow System--OraPerf

Two groups of 20 members apiece? That must be a misprint! Is that even possible? Perhaps you mean 20 groups of 2 members apiece? That is still astonishing, but not as astonishing as the way it reads originally...

Just to make sure we have the right story, please post the results of the following query:

            select thread#, group#, members, bytes/1048575 mb from v$log;

Another query that might shed some light on the volume of redo that you are generating is:

            select thread#, trunc(first_time) dt,
                max(next_change#) - min(first_change#) changes,
                count(*) cnt
                from v$log_history
                group by thread#, trunc(first_time);

This will tell us how many redo log switches and how many individual redo changes you are recording day by day. This information, coupled with knowledge about the size of your online redo log files, should give us a decent idea of how big your redo log files should be. There is generally little reason for there to be more than 3-5 groups and there is generally little reason to have more than 2-3 members per group. If you are using Standby database or Quest SharePlex (both are redo logfile sniffing replication products, in essence), then there would be a reason for greater numbers of smaller-sized groups, but more than 2-3 members is still very difficult to justify...

If your poor old LGWR process is being forced to write to 20 members, then it's no wonder you're seeing information messages...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, May 25, 2002 6:28 PM

>
> After seeing the original post and replies, I checked out the analyzer at
> oraperf.com, as I am having performance problems for the first time since
> becoming a DBA for our student information system. Over time, we've gone
> from 7.3.4 to 8.1.7.3 64-bit, from sequent to Sun Solaris, from 800-1200
> users...underlying application changes (more database packages and
> procedures), and never really been tuned! I think our server upgrades
have
> masked the database tuning issues, until we get a busy period, then it
shows!
>
> Anyway, the analyzer had some recommendations I put in place this weekend
> (my only opportunity to bounce the db) and I will be evaluating the impact
> these changes on performance next week...however, one recommendation that
> puzzles me is to make my redo logs 9765 MB! I have two groups of 20.
Last
> weekend I doubled their size from 50 to 100 MB, but 9765 MB?! I am
getting
> errors in the alert log that indicate slow archiving of redo logs, "Failed
> to archive..." but them a minute or so later it is archived. This change
> made no difference in the number of Failures reported. I am hoping the
> log_buffer change recommended by the oraperf analyzer will help, but can
> anyone comment on that redo log size recommendation? I ran a variety of
> statspack reports through and all said the same thing!
>
> Thanks,
>
> Debi
>
>
>
>
> > short answer: yes
> >
> > oraperf is written/run by Anjo Kolk, father of the tuning by wait event
> > methodology. I would tend to trust his suggestions for tuning
> > opportunities. Would I not also investigate on my own? No. I know my
> > apps, he doesn't unless he's come onsite. But oraperf can give me some
> > generic guidelines and places to start looking
> >
> >
> > --- Greg Moore <sqlgreg_at_pacbell.net> wrote:
> > > > the YAPP analyzer at www.oraperf.com anyways..
> > >
> > > The last part of the oraperf report has suggestions for items to
> > > investigate
> > > and tune. Is oraperf really good at spotting the key tuning
> > > opportunities?
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Greg Moore
> > > INET: sqlgreg_at_pacbell.net
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing
> > > Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! - Official partner of 2002 FIFA World Cup
> > http://fifaworldcup.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: dlorraine_at_ucdavis.edu
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).