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" <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
