Using Alert Log Information as an Aid in Oracle Performance Tuning

The information listed in the slide and additional information are written to the alert log. The
information written to the alert log changes somewhat with each version of the Oracle
database. Some values, such as the checkpoint start and end times, are written only when
requested. These values are written into the alert log file only if the
LOG_CHECKPOINTS_TO_ALERT parameter has been set to TRUE.
The alert log file can grow to an unmanageable size. You can safely delete the alert log while
the instance is started, although you should consider making an archived copy of it first. This
archived copy could prove valuable if you should have a future problem that requires
investigating the history of an instance. Note: Both versions of the alert log, text and XML,
should be trimmed periodically.
For example, suppose the DBA noticed a change in performance statistics. The DBA finds that
an instance parameter has changed since the last baseline. To confirm that the performance
change corresponds to the parameter change, the alert log can be searched. The alert log lists
all the non-default parameter settings on each startup, and records ALTER SYSTEM commands
with a time stamp.
The following sample of the alert log shows the startup parameters, the warning message for
FAST_START_MTTR_TARGET, and a sequence of log file switches showing the time for
each:
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 88080384
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 285212672
control_files =
/u01/app/oracle/oradata/orcl/control01.ctl,
/u01/app/oracle/oradata/orcl/control02.ctl,
/u01/app/oracle/oradata/orcl/control03.ctl
db_block_size = 8192
__db_cache_size = 184549376
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest =
/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain = oracle.com
dispatchers = (PROTOCOL=TCP)
(SERVICE=orclXDB)
job_queue_processes = 10
…
Tue Dec 20 01:12:16 2005
MTTR advisory is disabled because FAST_START_MTTR_TARGET is
not set
…
Tue Dec 20 01:13:42 2005
Thread 1 advanced to log sequence 6
Current log# 2 seq# 6 mem# 0:
/u01/app/oracle/oradata/orcl/redo02.log
Tue Dec 20 01:16:32 2005
Thread 1 advanced to log sequence 7
Current log# 3 seq# 7 mem# 0:
/u01/app/oracle/oradata/orcl/redo03.log
Tue Dec 20 01:43:46 2005
Thread 1 advanced to log sequence 8
Current log# 1 seq# 8 mem# 0:
/u01/app/oracle/oradata/orcl/redo01.log
…