Chapter 21 SGA Architecture and Wait Event

1. Chapter 21 SGA Architecture and Wait Event 2008. 07. 25. Summarized & Presented by YeonJongHeum IDS Lab., Seoul National University

2. Primary Components of Oracle

3. 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

4. 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

5. 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

6. 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

7. 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

8. Diagnostic Tools for DB Buffer Cache

9. 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

10. 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

11. 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

12. 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

13. 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