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.

How it works...

The database writes information on the alert log about log switches and checkpoints. We can inspect the alert log to diagnose a possible problem with log files.

There's more...

We can force a log switch by using the following command:

ALTER SYSTEM SWITCH LOGFILE;

A checkpoint can be forced by using the following statement:

ALTER SYSTEM CHECKPOINT;

See also

• We will see the use of SQL_TRACE and TKPROF to generate trace files and the corresponding report over SQL activity of a particular session in Tracing SQL activity with SQL Trace and TKPROF in Chapter 4, Optimizing SQL Code