Transcript {Title} {Sub-title}

Chapter 21 SGA Architecture and Wait Event
2008. 07. 25.
Summarized & Presented by Yeon JongHeum
IDS Lab., Seoul National University
Primary Components of Oracle
Copyright  2008 by CEBT
2
System Global Area(SGA)
 Memory Structure of Instance
 Shared Pool

Used to store the most recently executed SQL

Used to Most recently used data definitions
 Database Buffer Cache

Stores copies of data block that have been retrieved from the
data files
 Redo Log Buffer Cache

Records all changes made to the database data blocks
Copyright  2008 by CEBT
3
Shared Pool
 Library cache

Contains statement text, parsed code, and execution plan
 Data dictionary cache

Contains definitions for tables, columns, and privileges from the
data dictionary tables
 UGA

Session information for Oracle Shared Server user when large
pool is not configured
Copyright  2008 by CEBT
4
Library Cache
 Used to store SQL statements and PL/SQL blocks to be shared
by users
 Managed by an LRU algorithm
 Used to prevent statement reparsing
Copyright  2008 by CEBT
5
Tuning Shared Pool
 Shared Pool Size

SHARED_POOL_SIZE

SHARED_POOL_RESERVED_SIZE

V$SHARED_POOL_RESERVED
 Wait Event

V$SESSION_WAIT or Statspack

Latch Free Wait Event

Library Cache Load Lock Wait Event

Library Cache Lock Wait Event
Copyright  2008 by CEBT
6
Database Buffer Cache
 Stores copies of data block and rollback block for caching that
have been retrieved from the data files that server read
 Enables great performance gains when you obtain and update
data
 Managed through a least recently used(LRU) algorithm
Copyright  2008 by CEBT
7
Diagnostic Tools for DB Buffer Cache
Copyright  2008 by CEBT
8
Latch of Database Buffer Cache
 Cache Buffer Chain Latch

Guarantee buffer header serialize access(protect hash chain)

Processes need to acquire latch to get buffer header lock

Assign to each Hash Bucket
 Cache Buffer LRU Chain Latch

Guarantee LRU list serialize access

Assign to each LRU List
Copyright  2008 by CEBT
9
Tuning Database Buffer Cache


Sizing Database Buffer Cache

DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE
Wait Event

Buffer Busy Wait Event

Free Buffer Wait Event

Local Write Wait Event

Buffer Dead Lock Wait Event

Direct Path Write Wait Event

Direct Path Read Wait Event

Write Complete Wait Event
Copyright  2008 by CEBT
10
Redo Log Buffer

Processes copy redo entries from the user’s memory space to
the redo log buffer for each DML or DDL statement

The redo entries contain the information necessary to
reconstruct or redo changes made to the database by INSERT,
UPDATE , DELETE CREATE, ALTER, or DROP operations.

Used for database recovery

Take up continuous, sequential space in the buffer
Copyright  2008 by CEBT
11
Latch of Redo Log Buffer
 Redo Copy Latch

Write log data to redo log buffer

Two times of CPU number
 Redo Allocation Latch

Check and assign free spaces of redo log buffer to processes

Write log data order by time

Only one in the database
 Redo Writing Latch

Write data of redo log buffer to redo log file by LGWR

Guarantee that only one LGWR executes
Copyright  2008 by CEBT
12
Tuning Redo Log Buffer


Sizing the Redo Log Buffer

Adjust the LOG_BUFFER parameter

Default value : OS-specific, generally 500k
Wait Event

Log Buffer Space Wait Event

LGWR Wait For Redo Copy Wait Event

Log File Sequential Redo Wait Event

Log File Parallel Write Wait Event

Log File Single Write Wait Event

Log File Sync Wait Event

Log File Switch Completion Wait Event

Log File Switch(Check Incomplete) Wait Event

Log File Switch(Archive Needed) Wait Event

Log File Switch(Clear Log File) Wait Event

Switch Log File Command Wait Event

Redo Switch/Archive Wait Event
Copyright  2008 by CEBT
13