les_08-调整共享池(52).ppt

Tuning the Shared Pool Objectives After completing this lesson, you should be able to do the following: Diagnose shared pool problems Size the shared pool Size the reserved area Keep objects in the shared pool Shared Pool Architecture Major components of the shared pool are: Library cache Data dictionary cache User global area (UGA) for shared server sessions Shared Pool Operation The shared pool is managed by an LRU algorithm. New objects require memory allocations. Re-creatable objects are aged out of the cache. Objects are made up of chunks of memory. A memory allocation is a whole chunk. A chunk is contiguous. The Library Cache Stores complex object metadata associated with cursors Stores SQL statements and PL/SQL blocks that are to be shared by users Prevents statement reparsing SQL and PL/SQL Storage Cursor Usage and Parsing Parse procedure: 1. Find and execute an open cursor. 2. Find a closed cursor in the session cache. 3. Search the hash chains (soft parse). 4. Construct the cursor (hard parse). Important Shared Pool Latches latch: shared pool protects memory allocations in the shared pool. latch: library cache locates matching SQL in the shared pool. Mutex A mutual exclusion object allows: Sharing of a resource without corruption Shared access for reads Exclusive access for update Each object to have its own mutex Benefits of Mutex Performance is improved with mutexes. Mutexes: Are smaller and faster Have less potential for contention Replace latches and pins Mutex Views and Statistics Mutex views: V$MUTEX_SLEEP V$MUTEX_SLEEP_HISTORY Mutex wait events: cursor:mutex X cursor:mutex S cursor:pin X cursor:pin S cursor:pin S wait on X Mutex-Protected Operations From 10.2.0.2 onward, mutex protects: Selects from V$SQLSTAT Searches of child cursor lists Statspack/AWR Indicators Statspack and AWR reports include indicators: Load Profile Instance Efficiencies Top Wait Events Time Model Load Profile Instance Efficiencies Top Waits Time Model Library Cache Act