Starting with Performance Tuning - Oracle Database 11gR2 Performance Tuning - page 22

Diagnosing performance issues using the
alert log

To diagnose certain performance issues, even the alert log can be used successfully.

Getting ready

There are some parameters to look at in the init.ora file of our database instance.

The parameter BACKGROUND_DUMP_DEST indicates the directory in which the alert log is
located. If the parameter LOG_CHECKPOINTS_TO_ALERT is set to TRUE , we will find even
checkpoint information in the alert log. By default this parameter is set to FALSE .

Before starting, we can issue the following command:

ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT = TRUE;
SHOW PARAMETER BACKGROUND_DUMP_DEST

This writes checkpoint information to the alert log and shows the directory in which we will
find the alert log file (named alert_<instance_name>.log ).

How to do it...

The following steps will demonstrate how to use the alert log:

1. In the alert log, we can find information like the following:
Sun Sep 19 12:25:26 2010
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 2 seq# 5 mem# 0: D:\APP\ORACLE\ORADATA\TESTDB\
REDO02.LOG

This informs us of a log-switch.

2. We can then verify the time between log switches.

If we have set the parameter LOG_CHECKPOINTS_TO_ALERT to TRUE , we will also
see lines like these in the alert log:
Sat Sep 25 20:18:01 2010
Beginning global checkpoint up to RBA [0x16.fd.10], SCN: 1296271
Completed checkpoint up to RBA [0x16.fd.10], SCN: 1296271

Then we can calculate checkpoint performance.