To diagnose certain performance issues, even the alert log can be used successfully.
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
).
The following steps will demonstrate how to use the alert log:
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.
We can then verify the time between log switches.
If we have set the parameter
LOG_CHECKPOINTS_TO_ALERT
toTRUE
, 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.
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.
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;
We will see the use of
SQL_TRACE
andTKPROF
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