1 / 50

The Library Cache

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

patsy
Download Presentation

The Library Cache

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. The Library Cache

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

  3. 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 shared cursors and information about other RDBMS objects.

  4. Library Cache Objects • The units of information that are stored in the library cache are called objects. • There are two classes of objects: • Stored objects • Created and dropped with explicit SQL or PL/SQL commands • Examples: Tables, views, packages, functions • Transient objects • Created at execution time and live only for the duration of the instance (or aged out) • Example: Shared and nonshared cursors

  5. Shared Cursors • In-memory representation of an executable object: • SQL statements • Anonymous PL/SQL block • PL/SQL stored procedures or functions • Java stored 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

  6. Shared Cursors Scott, Ted and Mark issueselect * from EMPLOYEES;(EMPLOYEES is a synonym for HQ.EMPLOYEES Mark has own copy of EMPLOYEES) John issuesselect * from employees;(John has his own copy of EMPLOYEES) Parent Cursors select * from employees select * from EMPLOYEES Child Cursors Base Obj: JOHN.EMPLOYEES Base Obj: HQ.EMPLOYEES Base Obj: MARK.EMPLOYEES Used by John Shared by Scott and Ted Used by Mark

  7. Library Cache Architecture • The library 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.

  8. Hash Buckets Hash Buckets Object Handles select * from employees 0 1 2 3 4 5 6 7

  9. Object Handles Hash Bucket Object Handles Name Namespace Lock owners Lock waiters Pin owners Pin waiters Flags Heap 0 (Object) Heap 0 (Object)

  10. Heap 0 (Object) • Internally, most of the object identity is represented by structures of type kglob. • These are the structures stored in heap 0. • Object structures have the following components: • Type • Name • Flags • Tables • Data blocks

  11. 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. • The most important namespace is called cursor (CRSR) and houses the shared SQL cursors.

  12. 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.NAME@DBLINK. • For example, HR.EMPLOYEES@ACME.COM

  13. Object Flags • 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 authorization

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

  15. Object Data Blocks • The remainder of an object’s data is stored in other independent data heaps. • 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.

  16. Library Cache Object Hash Buckets Object Handles 0 Heap 0 (Object) Object Type Object Name Flags Tables Data Blocks Dependency table Object Child table Source Heap 1 Translation table Diana Authorization table Pcode Access table Mcode R-O dependency table Errors Schema name table SQL Context Heap 6 . . .

  17. Object Heaps Heap 0 1 2 3 4 5 6 7 8-11 Usage Object Source Diana Pcode Mcode Errors SQL Context Free Subordinate Heaps

  18. 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 • Pinning an object will load object information into memory if it is not there.

  19. 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 transaction, then it is released when a commit or rollback occurs. • If associated with a call, then it is released when the call returns.

  20. 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 locked in any of the three modes. • Transient objects (cursors) can only be held in Null (N) mode.

  21. Null Shared Exclusive Null Shared Exclusive None Null Shared Exclusive 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:

  22. Pin Modes • There are two pin modes: • Share (S):to read 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.

  23. Library Cache Latches • Alibrary cache load lock latchis 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 common point of contention.

  24. 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 determine the number of child latches.

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

  26. Causes of Library 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 increase. • CURSOR_SPACE_FOR_TIME can greatly increase memory utilization.

  27. 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 match in name and data type. • The SQL statements must be optimized by using the same optimization approach.

  28. 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: • V$SQL • V$SQL_BIND_DATA • V$SQL_BIND_METADATA

  29. 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 5272 93 TABLE/PROCED 488128 .921 843318 .988 4302 0 TRIGGER 6531 .998 6531 .998 4 0 Diagnostics: V$LIBRARYCACHE SQL> select namespace, gets, gethitratio, pins, 2 pinhitratio, reloads, invalidations 3 from v$librarycache;

  30. Diagnostics: Library Cache Dump • Oracle9i Release 2 level values: • Level 1: Dump library cache statistics • Level 2: 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 The following command formats and dumps the contents of the library cache: SQL> alter session set events 'immediate trace 2 name library_cache level 4';

  31. 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. LIBRARY CACHE HASH TABLE: size=509 count=376 BUCKET 0: BUCKET 1:** BUCKET 2:* ...

  32. Library Cache Dump Interpretation Library object handle dump: 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=5f2e4f8[5f2e4f8,5f2e4f8] ltm=5f2e500[5f2e500,5f2e500] pwt=5f2e510[5f2e510,5f2e510] ptm=5f2e568[5f2e568,5f2e568] ref=5f2e4e8[5f2e4e8,5f2e4e8] 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 waiters, temporary locks, pin waiters, temporary pins and references 1 2 3 4 5 6

  33. LIBRARY OBJECT: object=5f26110 type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0 CHILDREN: size=16 child# table reference handle ------ -------- --------- -------- 0 5f262cc 5f26294 5f26048 DATA BLOCKS: data# heap pointer status pins change ----- -------- -------- ------ ---- ------ 0 5f2e468 5f26194 I/-/A 0 NONE 1 2 3 4 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 data structures (heap descriptors)

  34. Diagnostics: V$SGASTAT • Allocations of fixed and variable memory in the SGA: • Free memory • Library Cache • SQL area • Others POOL NAME BYTESshared pool free memory 2658568shared pool PL/SQL MPCODE 38404shared pool PL/SQL DIANA 245744shared pool library cache 770228shared pool sql area 1590252

  35. Diagnostics: V$SQLAREA • Estimate memory usage: • Locate similar SQL that may not be shared due to not using bind variables: 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 cursors that cannot be shared because of differences in object definitions: SQL> select substr(sql_text,1,50),version_count 2 from v$sqlarea 3 where version_count > 5; SQL> select sum(sharable_mem) 2 from v$sqlarea;

  36. Other Tuning Tips • You can use the DBMS_SHARED_POOL package to “keep” certain objects in the shared pool. • Pin large packages, 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

  37. Library Cache Contention Diagnosis SQL> select latch#, substr(name,1,25) "Latch", sleeps 2 from v$latch 3 where sleeps!=0 4 order by sleeps desc; LATCH# Latch SLEEPS 99 library cache 4326 62 cache buffers chains 260 67 cache buffers lru chain 233 2 session allocation 2 61 batching SCNs 2 11 messages 2

  38. Latch Contention Diagnostics SQL> select gets, misses, sleeps, spin_gets, 2 sleep1, sleep2, sleep3 3 from v$latch_children 4 where latch#=99 5 order by sleeps desc; GETS MISSES SLEEPSSPIN_GETSSLEEP1SLEEP2SLEEP3 1188104 209 240 107 32 53 17 1769415 207 226 101 27 67 12 1652124 185 220 107 20 37 21 1280490 177 211 98 19 42 18 1184178 136 124 70 23 28 15 1154608 107 93 34 39 24 10 1175960 95 85 53 7 27 8 1019071 92 77 48 18 19 7 738016 57 50 29 10 14 4

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

  40. References • Web IV Notes: 32895.1, 34433.1, 61623.1 • Source: kgl.h, kgl.c

More Related