| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Small Redo Log File Size
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,
 |
 |