1、The Library Cache,Objectives,At the end of this section, you should be able to: Explain the library cache architecture List locks, pins, and latches in the library cache Identify when library cache contention occurs Describe how to reduce library cache contention Detail how to diagnose library cache
2、 problems,The Library Cache,An area in the shared pool that manages information about: Shared cursors (SQL and PL/SQL objects) Database objects (tables, indexes, and so on) Initially created to manage PL/SQL programs and library units, therefore called library cache Scope was extended to include sha
3、red cursors and information about other RDBMS objects.,Library Cache Objects,The units of information that are stored in the library cache are called objects. There are two classes of objects: Stored objectsCreated and dropped with explicit SQL or PL/SQL commandsExamples: Tables, views, packages, fu
4、nctions Transient objectsCreated at execution time and live only for the duration of the instance (or aged out) Example: Shared and nonshared cursors,Shared Cursors,In-memory representation of an executable object: SQL statements Anonymous PL/SQL block PL/SQL stored procedures or functions Java stor
5、ed procedures Object Methods Represented by two or more objects: A parent cursor that has a name One or more child cursors containing the execution plan,Shared Cursors,select * from EMPLOYEES,Base Obj: MARK.EMPLOYEES,Base Obj: HQ.EMPLOYEES,Scott, Ted and Mark issue select * from EMPLOYEES; (EMPLOYEE
6、S is a synonym for HQ.EMPLOYEES Mark has own copy of EMPLOYEES),Shared by Scott and Ted,Used by Mark,John issues select * from employees; (John has his own copy of EMPLOYEES),select * from employees,Used by John,Parent Cursors,Child Cursors,Base Obj: JOHN.EMPLOYEES,Library Cache Architecture,The lib
7、rary cache is a hash table that is accessible through an array of hash buckets. The library cache manager (KGL) controls the access and usage of library cache objects. Memory for the library cache is allocated from the shared pool.,Hash Buckets,0,2,3,1,4,6,7,5,select * from employees,Hash Buckets,Ob
8、ject Handles,Object Handles,Name Namespace Lock owners Lock waiters Pin owners Pin waiters Flags Heap 0 (Object),Hash Bucket,Object Handles,Heap 0 (Object),Heap 0 (Object),Internally, most of the object identity is represented by structures of type kglob. These are the structures stored in heap 0. O
9、bject structures have the following components: Type Name Flags Tables Data blocks,Object Types,Objects are grouped in namespaces according to their type. Each object can only be of one type. All the objects of the same type are in the same namespace. A namespace may be used by more than one type. T
10、he most important namespace is called cursor (CRSR) and houses the shared SQL cursors.,Object Names,Library cache object names have three parts: Name of schema Name of object Name of database link (remote objects only) The format used is SCHEMA.NAMEDBLINK. For example, HR.EMPLOYEESACME.COM,Object Fl
11、ags,Public flags: Are not protected by pins or latches Indicate in detail the type of the object Status flags: Are protected by pins Indicate whether the object is being created/dropped/altered/updated Special status flags: Are protected by the library cache latch Are related to object validity and
12、authorization,Object Tables,The following tables are maintained for each object: Dependency table Child table Translation table Authorization table Access table Read-only dependency table Schema name table,Object Data Blocks,The remainder of an objects data is stored in other independent data heaps.
13、 The object structure contains an array of data block structures. The data block structures have a pointer to a different data heap. An object structure has room for 16 data block structures but not all of them are in use.,Library Cache Object,Source,Diana,Pcode,Mcode,Errors,SQL Context,0,Hash Bucke
14、ts,Object Handles,Heap 0 (Object),Object Type Object Name FlagsTablesData Blocks,Dependency table,Child table,Translation table,Authorization table,Access table,R-O dependency table,Object,. . .,Heap 1,Heap 6,Schema name table,Object Heaps,Heap 0 1 2 3 4 5 6 7 8-11,Usage Object Source Diana Pcode Mc
15、ode Errors SQL Context Free Subordinate Heaps,Locks and Pins,Locks and pins are used to control access to the library cache objects. Locks manage concurrency. Pins ensure cache coherence. When an object is accessed: First the lock is acquired on the handle Then the necessary object heaps are pinned
16、Pinning an object will load object information into memory if it is not there.,Lock and Pin Persistence,Every lock and pin is associated with a state object: Session Transaction Call If associated with a session, then the lock, or pin, persists until the session ends. If associated with a transactio
17、n, then it is released when a commit or rollback occurs. If associated with a call, then it is released when the call returns.,Lock Modes,There are three lock modes: Share (S): to read an object Exclusive (X): to modify/create objects Null (N): special for session persistency Stored objects can be l
18、ocked in any of the three modes. Transient objects (cursors) can only be held in Null (N) mode.,Lock Compatibility,Process A is holding a lock on an object of mode:,Process A tries to get another lock of mode:,Process B tries to get a lock of mode:,Pin Modes,There are two pin modes: Share (S): to re
19、ad an object heap Exclusive (X): to modify an object heap Both stored and transient object heaps can be pinned either in Share or Exclusive mode. When an object heap is pinned, it is also loaded into memory at the same time if it is not already there.,Library Cache Latches,A library cache load lock
20、latch is needed to load a library cache object. Multiple symmetric library cache latches are needed before getting a lock on an object handle. An object handle is protected by a latch that is determined by the bucket that it hashes into:latch# = mod(bucket#, #latches) Library cache latches are a com
21、mon point of contention.,Library Cache Latches,The number of child library cache latches depends on the number of CPUs. The default is the smallest prime number greater than or equal to the number of CPUs. The maximum number is limited to 67. You can use the hidden parameter _KGL_LATCH_COUNT to dete
22、rmine the number of child latches.,Causes of Library Cache Contention,Excessive parsing that can be caused by: Not sharing SQL Making unnecessary parse calls (soft) Not using bind variables Shared SQL being can be aged out when: Shared pool not sized properly Large pool not configured,Causes of Libr
23、ary Cache Contention,Not pinning large PL/SQL objects Invalidating dependent objects in the library cache by altering, recompiling, and so on Not setting parameters appropriately: HOLD_CURSORS=TRUE with precompilers When SESSION_CACHED_CURSORS are set too high, fragmentation in the shared pool may i
24、ncrease. CURSOR_SPACE_FOR_TIME can greatly increase memory utilization.,Sharing Cursors,Text of SQL statements must be identical. The only tolerated differences are literals, if CURSOR_SHARING is set to FORCE. SQL statements must reference the same objects. Bind variables in the SQL statements must
25、match in name and data type. The SQL statements must be optimized by using the same optimization approach.,Checking for Cursor Sharing,CURSOR_SHARING is an initialization parameter. It is session- or system-modifiable. Possible values are: FORCE EXACT V$ views showing system-generated bind variables
26、: V$SQL V$SQL_BIND_DATA V$SQL_BIND_METADATA,Diagnostics: V$LIBRARYCACHE,NAMESPACE GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI - - - - - - - BODY 8359 1 8366 998 17 0 CLUSTER 0 1 0 1 0 0 INDEX 15 .467 15 .067 7 0 OBJECT 0 1 0 1 0 0 PIPE 69 .899 93 .925 0 0 SQL AREA 337138 .879 2749467 .968 527
27、2 93 TABLE/PROCED 488128 .921 843318 .988 4302 0 TRIGGER 6531 .998 6531 .998 4 0,SQL select namespace, gets, gethitratio, pins,2 pinhitratio, reloads, invalidations3 from v$librarycache;,Diagnostics: Library Cache Dump,Oracle9i Release 2 level values: Level 1: Dump library cache statistics Level 2:
28、Dump hash table summary Level 4: Dump library cache objects, basic information Level 8: Dump objects with detailed information Level 16: Dump heap sizes (can be latch intensive) Level 32: Dump heap information,SQL alter session set events immediate trace2 name library_cache level 4;,The following co
29、mmand formats and dumps the contents of the library cache:,Library Cache Dump Interpretation,The statistics are the same as those in the V$LIBRARYCACHE view:The size indicates the number of buckets. The count indicates the number of object handles. The asterisks represent the handles in the bucket.,
30、LIBRARY CACHE HASH TABLE: size=509 count=376 BUCKET 0: BUCKET 1:* BUCKET 2:* .,Library Cache Dump Interpretation,1. Memory address of the object handle 2. Object name 3. Resulting hash value, timestamp 4. Namespace, flags 5. Heaps loaded and kept, lock, pin, and latch modes 6. Link lists of lock wai
31、ters, temporary locks, pin waiters, temporary pins and references,LIBRARY OBJECT HANDLE: handle=5f2e4e0 name=select * from emp hash=cde2b05 timestamp=05-21-2000 12:47:13 namespace=CRSR flags=RON/TIM/PN0/SML/12010000 kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch=0 lwt=5f2e4f85f2e4f8,5f2e4f8 ltm=5f
32、2e5005f2e500,5f2e500 pwt=5f2e5105f2e510,5f2e510 ptm=5f2e5685f2e568,5f2e568 ref=5f2e4e85f2e4e8,5f2e4e8,Library object handle dump:,1,2,3,4,5,6,Library Cache Dump Interpretation,Library object dump: 1. Memory address of the object (heap 0) 2. Object type, flags, and status 3. Object tables 4. Object d
33、ata structures (heap descriptors),Diagnostics: V$SGASTAT,Allocations of fixed and variable memory in the SGA: Free memory Library Cache SQL area Others,POOL NAME BYTES shared pool free memory 2658568 shared pool PL/SQL MPCODE 38404 shared pool PL/SQL DIANA 245744 shared pool library cache 770228 sha
34、red pool sql area 1590252,Diagnostics: V$SQLAREA,Estimate memory usage:,SQL select substr(sql_text,1,40) SQL, count(*)2 from v$sqlarea group by substr(sql_text,1,40)3 having count(*) 5;,Locate similar SQL that may not be shared due to not using bind variables:,SQL select substr(sql_text,1,50),versio
35、n_count2 from v$sqlarea 3 where version_count 5;,Locate cursors that cannot be shared because of differences in object definitions:,SQL select sum(sharable_mem) 2 from v$sqlarea;,Other Tuning Tips,You can use the DBMS_SHARED_POOL package to “keep” certain objects in the shared pool. Pin large packag
36、es, procedures, functions, and triggers in the shared pool. Run $ORACLE_HOME/rdbms/admin/dbmspool.sql to install the DBMS_SHARED_POOL package. Use an effective combination of the following initialization parameters parameters: LARGE_POOL_SIZE SHARED_POOL_RESERVED_SIZE,Library Cache Contention Diagno
37、sis,LATCH# Latch SLEEPS99 library cache 432662 cache buffers chains 26067 cache buffers lru chain 2332 session allocation 261 batching SCNs 211 messages 2,SQL select latch#, substr(name,1,25) “Latch“, sleeps 2 from v$latch3 where sleeps!=0 4 order by sleeps desc;,Latch Contention Diagnostics,GETS MI
38、SSES SLEEPS SPIN_GETS SLEEP1 SLEEP2 SLEEP31188104 209 240 107 32 53 171769415 207 226 101 27 67 121652124 185 220 107 20 37 211280490 177 211 98 19 42 181184178 136 124 70 23 28 151154608 107 93 34 39 24 101175960 95 85 53 7 27 81019071 92 77 48 18 19 7738016 57 50 29 10 14 4,SQL select gets, misses
39、, sleeps, spin_gets,2 sleep1, sleep2, sleep33 from v$latch_children 4 where latch#=995 order by sleeps desc;,Summary,In this lesson, you should have learned about: The architecture of the library cache The structure of library cache objects Locks, pins, and latches in the library cache Diagnosing and resolving contention in the library cache,References,Web IV Notes: 32895.1, 34433.1, 61623.1 Source: kgl.h, kgl.c,
