Question: I have a batch
job that runs nightly, and it suddenly starting taking 10x time to
execute. Log file switch (checkpoint incomplete)?.
I also see frequent log switches. Here is the output of a "ls
-al", showing frequent log switches:
As you see, as of 5:00 PM the log were
switching every 10 minutes. At 20:16
there is a pause in the redo logs, followed
by a blast of archived redo logs at large
sizes. How do I correct the "log file
switch checkpoint incomplete error?
Answer: During your
batch updates you should see high process activity from DBWR, LGWR and ARCH
background processes (using the "ps -ef " command.).
You can also repeat the "ls -alt|hread" commands in the
redo log directory and watch ARCH write the redo log files.
In general, you should
size your online
redo logs such that they switch no more frequently than 4 times
per hour at peak DML levels (the busiest update times).
Also see these notes on
tuning_online_redo_logs for RAC.
In
sum, the solution is to try these fixes (in order):
-
Increase the size of the
online redo logs. In this example, a redo log
switch every 10 minutes should be doubled, and change the redo
log file size from 1 gigabytes to two gigabytes. His
should reduce the work on the checkpoint process.
-
Increase the number of redo
log groups. More redo log groups can improve
concurrent throughput.
-
Change the updates to run in
NOLOGGING. This will not work immediately, but it
will prevent future problems. Beware of the danger of improper
usage
of the nologging option.
In some cases, this may also help improve the
performance of databases with too frequent redo log activity:
- Add additional log writer processes (LGWR).
- Ensure that the archived redo log filesystem resides on a
separate physical disk spindle.
- Put the archived redo log filesystem on super-fast
solid-state disks.
- If you also see high buffer busy waits, consider increasing
the number of freelists for the target tables and indexes. You can
also use bitmap freelists (segment space management auto).
- For inserts, consider using the APPEND hint.
- See the notes for
insert
tuning.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
