Re: huge amount of redo log switches

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