Oracle9i Performance Tuning

1
Oracle9i Performance Tuning

• Chapter 10
• Other Tuning Issues

2
Chapter Objectives

• Understand the role of Oracle background
  processes
• Detect background process contention
• Understand the concept of latches
• Tune and monitor an operating system

3
Oracle Instance
4
Oracles Background Processes
5
Background Processes
6
Checkpoints

• A CKPT process wakes up and makes an announcement
  when
• A log file switch occurs
• A commit is issued
• A checkpoint timeout occurs
• A checkpoint interval occurs
• A manual checkpoint is issued
• A manual log file switch is issued
• A database shutdown is issued
• A tablespace is put in OFFLINE or BEGIN BACKUP
  status

7
Regulating Checkpoints

• Use the following to regulate checkpoints
• LOG_CHECKPOINT_INTERVAL This parameter tells
  Oracle the number of OS blocks that need to be
  written by LGWR to wake up the CKPT
• For example, if this parameter was set to 10 KB
  and LGWR wrote 10 KB to log files since the last
  checkpoint, the CKPT process is triggered and a
  checkpoint is performed
• LOG_CHECKPOINT_TIMEOUT This parameter informs
  Oracle of the number of seconds the CKPT process
  has been idle
• For example, if this parameter is set to eight
  seconds, the checkpoint is performed eight
  seconds after the last checkpoint
• To disable this parameter, set it to zero

8
Regulating Checkpoints (continued)

• Oracle introduced two new initialization
  parameters that contribute to regulating
  checkpoints
• FAST_START_IO_TARGET This parameter specifies
  the target number of I/O operations to perform
  crash recovery
• This parameter is deprecated
• FAST_START_MTTR_TARGET This parameter specifies
  the number of seconds Oracle should take to
  perform crash recovery, and the default value is
  300 seconds
• MTTR stands for Mean Time To Recover

9
Regulating Checkpoints (continued)
10
DBWR Configuration
11
Redo Log Size Guidelines
12
Redo Log Multiplexing
13
Operating System Tuning
14
Windows Monitoring Tools
15
Windows Monitoring Tools
16
UNIX Monitoring Tools

• There are several UNIX monitoring tools
  available
• ps command
• mpstat utility
• iostat utility
• vmstat utility
• sar utility

17
Summary

• Background processes are the core of the Oracle
  engine, which makes Oracle one of the leading
  database management systems in the database arena
• Background processes have been written to be
  highly efficient because they are the backbone of
  Oracle systems
• Oracle instance architecture consists of memory
  and background processes as one integral part
• Of the Oracle background processes, only the
  performance of ARCH, CKPT, DBWR, and LGWR can be
  enhanced
• The checkpoint mechanism serves two purposes
  data consistency, whereby all data files are kept
  synchronized and data recovery, whereby Oracle
  determines whether it requires recovery or not
  based on the checkpoint stamp on each file
• System administrators spend most of their time
  and effort tuning and designing I/O related
  factors and less on tuning the CPU