Re: Small Redo Log File Size

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1155689699.794760.252290_at_i42g2000cwa.googlegroups.com...
> Dereck L. Dietz wrote:
> > I'm working with an Oracle 10g Version 1.0 datawarehouse database
upgraded
> > from an Oracle 9i version.
> >
> > While running the Enterprice Manager and browsing through the
performance
> > recommendations I came across one which stated the Redo Log Files were
too
> > small. The size of the current Redo Log Files (3 groups of only 1 file
> > each) are only 10MB. Oracle recommended the size of 2GB for the Redo
Log
> > Files.
> >
> > Since we've experienced very slow response times the more activity there
is
> > against the database I think this should be taken seriously. I'm
expecting
> > resistance - or apathy - when I suggest following this recommendation.
> >
> > Since I'm not the "official" DBA there (and a contractor to boot) I'd
like
> > to have as much ammunition as possible to back up my suggestion.
> >
> > Would it be possible for the more knowledgeable on this group to post
about
> > the benefits of properly sized Redo Log Files and the pitfalls of
improperly
> > sized Redo Log Files? I'd like to be able to print them out to use if I
> > have resistance to this recommendation.
> >
> > Thanks.
>
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211.pdf
> Page 69-70 (4-3 through 4-4):
> "The size of the redo log files can influence performance, because the
> behavior of the
> database writer and archiver processes depend on the redo log sizes.
> Generally, larger
> redo log files provide better performance. Undersized log files
> increase checkpoint
> activity and reduce performance.
>
> Although the size of the redo log files does not affect LGWR
> performance, it can affect
> DBWR and checkpoint behavior. Checkpoint frequency is affected by
> several factors,
> including log file size and the setting of the FAST_START_MTTR_TARGET
> initialization
> parameter. If the FAST_START_MTTR_TARGET parameter is set to limit the
> instance
> recovery time, Oracle automatically tries to checkpoint as frequently
> as necessary.
> Under this condition, the size of the log files should be large enough
> to avoid
> additional checkpointing due to under sized log files. The optimal size
> can be obtained
> by querying the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_
> RECOVERY view. You can also obtain sizing advice on the Redo Log Groups
> page of
> Oracle Enterprise Manager Database Control.
>
> It may not always be possible to provide a specific size recommendation
> for redo log
> files, but redo log files in the range of a hundred megabytes to a few
> gigabytes are
> considered reasonable. Size your online redo log files according to the
> amount of redo
> your system generates. A rough guide is to switch logs at most once
> every twenty
> minutes."
>
> Check the redo log file switch frequency:
> SELECT
> LH2.RECID,
> LH2.STAMP,
> LH2.THREAD#,
> LH2.SEQUENCE#,
> LH2.FIRST_CHANGE#,
> LH2.NEXT_CHANGE#,
> LH1.FIRST_TIME PREV_FIRST_TIME,
> LH2.FIRST_TIME,
> ROUND((LH2.FIRST_TIME-LH1.FIRST_TIME)*24,2) HOURS
> FROM
> V$LOG_HISTORY LH1,
> V$LOG_HISTORY LH2
> WHERE
> LH2.RECID=LH1.RECID+1
> AND LH1.FIRST_TIME>TRUNC(SYSDATE-180)
> ORDER BY
> LH2.RECID DESC;
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>

Thanks. I know from monitoring the database for the past week that there are days when the LGWR, DBWR and CHKPT were constantly running for hours - at times being the top processes. Received on Tue Aug 15 2006 - 20:09:22 CDT