Re: Diagnose Slow System--OraPerf

Aack! I don't know why, it makes more sense to me the way I said it, but I can see now my error. Here is the result of the first query...I have 20 groups with 2 members in each group! (That's not so astonishing I hope!)

  select thread#, group#, members, bytes/1048575 mb from v$log; THREAD# GROUP# MEMBERS MB
------- ------- ------- -------

      1       1       2     100
      1       2       2     100
      1       3       2     100
      1       4       2     100
      1       5       2     100
      1       6       2     100
      1       7       2     100
      1       8       2     100
      1       9       2     100
      1      10       2     100
      1      11       2     100
      1      12       2     100
      1      13       2     100
      1      14       2     100
      1      15       2     100
      1      16       2     100
      1      17       2     100
      1      18       2     100
      1      19       2     100
      1      20       2     100

And from the other query (this is great--thanks!):

THREAD# DT                 CHANGES     CNT
------- --------- ---------------- -------
      1 27-APR-02        8,293,428      11
      1 28-APR-02        6,180,502       4
      1 29-APR-02        2,151,943      42
      1 30-APR-02       10,732,708      31
      1 01-MAY-02       10,733,462      19
      1 02-MAY-02        6,402,380      12
      1 03-MAY-02        2,331,168      15
      1 04-MAY-02       14,845,034       6
      1 05-MAY-02        4,737,492       5
      1 06-MAY-02        5,791,174      15
      1 07-MAY-02        2,080,204      17
      1 08-MAY-02       11,791,301      16
      1 09-MAY-02        8,123,046      16
      1 10-MAY-02        2,375,798      13
      1 11-MAY-02       11,111,829       7
      1 12-MAY-02        3,330,510       4
      1 13-MAY-02        4,050,327      17
      1 14-MAY-02        8,238,873      26
      1 15-MAY-02        8,480,568      22
      1 16-MAY-02        5,711,059      15
      1 17-MAY-02        1,902,406      32
      1 18-MAY-02       13,463,213      19
      1 19-MAY-02          747,431       2
      1 20-MAY-02        9,780,205      12
      1 21-MAY-02        6,745,098      11
      1 22-MAY-02        5,984,587      10
      1 23-MAY-02        5,991,105      15
      1 24-MAY-02        1,847,971      12
      1 25-MAY-02        4,311,367       2

29 rows selected.  

I doubled the redo log size from 50 MB to 100 MB on May 18. Also, I plan to have a stand-by database by the end of summer...after we can upgrade to 9i release 2.

Thanks,

Debi

> 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
> >
> >
> >

-- 
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).