1 / 13

Chapter 21 SGA Architecture and Wait Event

Chapter 21 SGA Architecture and Wait Event. 2008. 07. 25. Summarized & Presented by Yeon JongHeum IDS Lab., Seoul National University. Primary Components of Oracle. System Global Area(SGA). Memory Structure of Instance Shared Pool Used to store the most recently executed SQL

ashtyn
Download Presentation

Chapter 21 SGA Architecture and Wait Event

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related