Re: huge amount of redo log switches

Mark,

The original poster does not seem to have any problems with the frequency of checkpoints. She mentioned the volume of archived logs as the source of her dissatisfaction.

Since she already tried to change the redo log buffer, she must have rebounced the database. That means that if there were any orphan connections, they have been cleared away. I would suggest to work with developers and to find out what is it that they are doing. The 'redo%' statistics in V$SESSTAT/SYSSTAT should help.

Regards
Karen

Mark wrote:

> From Oracle Docs:
>
> Setting Database Checkpoint Intervals
> When your database uses large online redo log files, you might want
> additional database checkpoints to take place automatically at predetermined
> intervals, between the checkpoints that automatically occur at log switches.
> When more database checkpoints are taken, the time necessary to recover from
> an instance failure decreases. However, the performance of the Oracle7
> Server can also decrease as a checkpoint proceeds due to the extra I/O
> necessary for the checkpoint to complete.
> Generally, unless your database consistently requires instance recovery on
> startup, set database checkpoint intervals so that checkpoints occur only at
> log switches. If you use small online redo log files, checkpoints already
> occur at frequent intervals (at each log switch).
>
> For more information about tuning Oracle7 regarding checkpoints, see the
> Oracle7 Server Tuning manual.
>
> The frequency of automatic database checkpoints is controlled by the values
> of the LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters.
>
> Setting LOG_CHECK-POINT_ INTERVAL
> To have database checkpoints only occur at log switches (the default), set
> the value for the LOG_CHECKPOINT_INTERVAL parameter higher than the size of
> the online redo log files in use. Alternatively, to force additional
> checkpoints to occur at intervals between two log switches, set the value
> for the LOG_CHECKPOINT_INTERVAL parameter lower than the size of the online
> redo log files in use.
> The value of the LOG_CHECKPOINT_INTERVAL is a number of operating system
> blocks, not Oracle7 data blocks. Therefore, you must know the size, in
> bytes, of your operating system's blocks. Once you know this, calculate the
> number of operating system blocks per online redo log file.
>
> Additional Information: See your operating system-specific Oracle
> documentation to determine the operating system block size.
>
> Setting LOG_CHECK-POINT_ INTERVAL: Example
> For example, assume the following conditions:
>
> All online redo log files of the database instance are 512K.
> The operating system block size is 512 bytes.
> Checkpoints should occur when an online redo log file is half full.
> Using this information, you can compute the number of blocks per redo log
> file as in the following equation:
>
> 512K/redo log file
> __________________ = approximately 1000 blocks/redo log file
>
> 512 bytes/OS block
> Now that the approximate number of blocks per online redo log file (1000) is
> known, the LOG_CHECKPOINT_INTERVAL parameter can be set accordingly in the
> instance's parameter file:
>
> LOG_CHECKPOINT_INTERVAL=500
>
> Setting LOG_CHECK-POINT_ TIMEOUT
> To have database checkpoints only occur at log switches (the default), set
> the value for the LOG_CHECKPOINT_TIMEOUT parameter to zero. Alternatively,
> to force additional checkpoints to occur at intervals between two log
> switches, set the value for the LOG_CHECKPOINT_TIMEOUT parameter to a time
> interval (in seconds) less than the average time it takes to fill an online
> redo log file. To determine the average time it takes to fill online redo
> log files, examine the LGWR trace file for messages that indicate the times
> of log switches.
> Note: See the Oracle7 Parallel Server manual for more information about the
> LOG_CHECKPOINT_TIMEOUT parameter when using the Oracle7 Parallel Server.
> Also, setting this parameter is particularly important when using Trusted
> Oracle7 in OS MAC mode. See the Trusted Oracle7 Server Administrator's
> Guide, Release 7.1 for more information.
>
> <susana73_at_hotmail.com> wrote in message news:8mmr4g$arp$1_at_nnrp1.deja.com...
> > Hello,
> >
> > I have a problem on my development database. It's generating huge
> > amount of redo log switches CONSISTENTLY which give me a large amount
> > of archive logs(2GB a day). Why I say consistent is because it starts
> > generating at 7am to 5pm on every 3 minutes basis EVERY DAY.
> >
> > It's a very small database(only 1GB) with multiple schemas and the only
> > users are developers. log_buffer=32768 and two sets of redo logs each
> > 4M. I've asked my developers and none of them say they have a job
> > running since 7am every day. And I don't think there is any large batch
> > job because it's a development db.
> >
> > I tried resizing(increasing and decreasing) log_buffer and redo log file
> > size but it doesn't help. I think I need to identify the db
> > transactions which are responsible for all those redo logs.
> >
> > 1GB database with 2GB archive logs doesn't make sence to me! Any help
> > is appreciated!
> >
> > Susana
> >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
Received on Mon Aug 07 2000 - 00:00:00 CDT