library cache latch等待事件

This latch serializes access to the objects in the library cache. Every time a SQL statement, a PL/SQL block or a stored object (procedure, package, function, trigger) is parsed or executed, this latch is acquired.

Problem – Fragmentation of the shared pool

Excessive execution of a statement can cause library cache contention.

Many versions of one SQL statement in the shared pool

Excessive parsing of SQL statements (even soft parsing) because of non-reusable queries or large objects being loaded in the shared pool forcing out the smaller heavily used queries

Solutions

Use bind variables whenever possible. Reuse of code means less parsing and less use of shared pool space.

Pin packages and procedures that are heavily executed in the shared pool. This prevents the heavily used code from being flushed out and thus needing to be parsed in.

Increase SESSION_CACHED_CURSORS parameter. This helps if the user repeatedly parses the same statements.

Use fully qualified table names. Example: “select * from owner.table” instead of “select * from table”

If using EXECUTE IMMEDIATE with bind variables, try using DBMS_SQL instead.

If the statement is complex and heavily executed, the load on the library cache could be reduced by breaking it up into multiple smaller statements.

Reducing the versions of SQL statements reduces the load on library cache as well. Oracle deals with various bind variable sizes by creating multiple versions of the statement. One possible solution would be writing more smaller queries rather than one large one.

Specify objects over a certain size be loaded into the large pool instead of the shared pool.

It’s possible that the shared pool is too small. If the above remedies have been attempted and contention is still evident, look into increasing the shared pool gradually.

Please also refer to Oracle’s MetaLink’s Note:146599.1 for information on this latch.

Library Cache Hit Ratio

The library cache (a component of the shared pool) stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. Oracle tries to reuse this code.

  • If the code has been executed previously and can be shared, Oracle will report a library cache hit.
  • If Oracle is unable to use existing code, then a new executable version of the code must be built, which is known as a library cache miss.

Latches and Tuning:The Library Cache

1. The shared pool is determined by the value of SHARED_POOL_SIZE as indicated in the init.ora file.  The library
cache is part of the shared pool.

a.  Determine the size of the shared poool by executing the following query:> select name,value from v$system_parameter where name = ‘shared_pool_size’;

The shared pool size is part of the VARIABLE SIZE value returned by querying V$SGA.

2.  The hidden parameter _KGL_BUCKET_COUNT determines the initial size of the hash table.  Typically, you
will accept the default value and not change this parameter.  The maximum value is 8.

a.  Determine the initial size of the hash table by executing the following query:> select ksppinm, ksppity from x$ksppi where ksppinm  = ‘_kgl_bucket_count’;

3. Object types are stored in a namespace.  While there can be 32 different namespaces, objects of the same
type will always be stored in the same namespace.

a.  Determine the namespaces allocated in the library cache by executing the following query:> select namespace from v$librarycache;

The number of namespaces are subject to increase at any time.  Common namespaces are:

CRSR: stores library cache objects of type cursor (shared SQL statements)
TABL/PRCD/TYPE: stores tables, views, sequences, synonyms, and procedure specifications
BODY/TYBD: stores procedure, function, package, and type bodies
INDX: stores librarcy cache objects of type index
TRGR: stores librarcy cache objects of type trigger
CLST: stores librarcy cache objects of type cluster

4.  Object tables are maintained for each object.  While the contents of each X$ table are fairly obscure, you can
query them to derive information about objects.

a.  Describe and/or query one of the following tables.X$KGLDP: (Dependency Table) One entry for each object that this object depends on.  For example, a view would depend on underlying tables or views.
X$KGLTR: (Translation Table) Contains records explaining how names referenced by this object were resolved to base objects.
X$KGLAU: (Authorization Table) Contains entries for each privilege defined on the object.
X$KGLXS: (Access Table) One or more entries for each entry in the dependency table.
X$KGLRD: (Read-only dependency table) Like the dependency table but for read only objects.
X$KGLSN: (Schema Name table) Only cursors have schema name tables and they store the schema names for the objects in the authorization table.
(Child Table) One entry for each object that is a child of this object.

5.  Remember that there are 32 namespaces in the library cache.  Also, each object has three types of flags; public,
status, and special status.
a.  Determine the number of namespaces by querying the OBJ$ table:

> select distinct(namespace) from obj$;

b. You can see the name of objects, their namespace, and flags by executing the following query.  Since there are many objects the query is limited using the psuedo column rownum.

> select name,namespace,flags from obj$ where flags > 1 and rownum < 10;

6. Locks and pins are used to control acccess to library cache objects. The X$KGLLK table, as indicated by the
letters LK, records locks on library cache objects.

a.  Connect as SYS and query the X$KGLLK table using the following query:> select user_name from x$kgllk where user_name = ‘SCOTT’;

Provided SCOTT is not logged in, this query should return no rows.:

b.  Create a second SQL*PLUS session as SCOTT/TIGER.

c.  Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

The user SCOTT acquired object handles on the objects DBMS_APPLICATION_INFO and DBMS_OUTPUT.

d. Switch to SCOTT’s SQL*PLUS session and execute the following update statement:

> update dept set dname = ‘TEST’ where deptno = ’10’;

e. Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

You will see that SCOTT has acquired additional locks as a result of the update statement.

7.  The X$KGLLPN table, as indicated by the letters PN, records pins on library cache objects.  The contents of the
X$KGLPN table are obscure but you may want to take a look at the data.
a.  Describe the X$KGLN table:

> desc X$KGLN

8. Library cache contention can be caused by excessive parsing of SQL statements.

a.  Determine the parse count in the library cache by executing the following query:b.  Create a second SQL*PLUS session as SCOTT/TIGER.c.  Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

9.  One diagnostic you can use for determing library cache performance is querying the V$LIBRARYCACHE
view.

a.  Execute the following query:> select namespace, gets, gethitratio, pins, pinhitratio, reloads, invalidations
from v$librarycache;

  • NAMESPACE: the different library cache namespaces
  • GETS: the total number of calls to locate and lock an object in the library cache
  • PINS: total number of calls to pin an object heap (to examine and possibly change)
  • GET/PINHITRATIO: ratio of overall requests to successful acquisitions for the GET and PIN calls in the cache
  • RELOADS: object reloads due to being aged out of the library cache
  • INVALIDATIONS: number of times the object was invalidated
  • Tuning Recommendations:

  • Keep the HITRATIOS above 90%
  • Keep the RELOADS to a minimum, ideally close to zero
  • Avoid DDL and minimize user role changes in a busy production environmentto prevent INVALIDATIONS
  • Size the shared pool appropriately so as to avoid objects getting aged out of the library cache
  • Similar SQL statements must be identical to be shared – use bind variables instead of literals
  • 10.  By performing a library cache dump you can gather extensive information about the library cache.  The dump will
    show you all of the namespaces, buckets, librarcy cache statistics, and content of the librarcy cache.  Beware, if you have
    a large database this dump file can be quite large and take a long time to generate.  You may want to select the
    appropriate level 1 – 4, depending upon the information you want to see.

    a.  As user SYS, execute the following query:> alter session set events ‘immediate trace name library_cache level 4’;

    The output will be generated in the USER_DUMP_DEST directory.

  • Level 1: dump libracy cache statistics
  • Level 2: include a hash table histogram; each bucket has one asterisk for each included handle
  • Level 3: include the dump of the object handles
  • Level 4: include the dump of the object structures (heap 0 only)
  • 11.  The X$KSMLRU fixed table tracks allocations in the shared pool that cause other objects to be aged out.  This
    table can be used to identify what is causing the large allocation.

    a.  Query the X$KSMLRU fixed table:> select * from x$ksmlru where ksmlru > 4000;

    The table contents are deleted after a SELECT.  This is done because the table stores only the largest allocations that have occurred.

    b.  Describe the X$KSMLRU table:

    > desc X$KSMLRU

    KSMLRSIZ: amount of contiguous memory being allocated.  Values over 5KB start to be a problem.
    KSMLRNUM: number of objects that were flushed from the shared pool in order to allocate the memory.
    KSMLRHON: the name of the object being loaded into the shared pool if the object is a PL/SQL object or a cursor.
    KSMLROHV: hash value of object being loaded.
    KSMLRSES: SADDR of the session that loaded the object.

    12.  One way to decrease the load on the library cache latch is to reduce the number of parse calls that are coming into
    the system.

    a.  To identify statements that are receiving a lot of parse calls, execute the following statement:> select sql_text, parse_calls, executions
    from v$sqlarea
    where parse_calls > 100
    and executions< 2 * parse_calls;

    13.  An additional method to tune the library cache is to convert anonymous blocks into packages if possible.
    a.  Find anonymous  blocks by executing the following query (47 is the command for an anonymous PL/SQL block):

    > select sql_text
    from v$sqlarea
    where command_type = 47;

    Oracle中SQL解析的流程

    Oracle中SQL解析的主要流程:
    sql_parse_digram

    我们说的游标概念比较复杂,它可以是客户端程序中的游标,服务进程中的私有游标,以及服务器端共享池里的共享游标。假设一个游标被打开了,一般来说它的共享游标信息(包括执行计划,优化树等)总是会在SQL AREA里,无需再次软/硬解析。

    SESSION_CACHED_CURSORS是Oracle中的一个初始化参数(修改必须重启实例),指定了每个会话缓存的游标上限(保留在PGA中);客户端程序中open cursor的要求仍会被传递给服务进程,服务进程首先扫描自身缓存的游标信息,如果命中则可以避免软解析,也有人称它为“软软解析”。

    HOLD_CURSOR是预编译程序中的一个参数,它指定了私有游标是否因该被缓存,这里不做展开。

    在分析工具tkprof中hard parse与soft parse被同等对待,都被认为是parse;软解析即会造成parse总数上升。

    软解析避免了硬解析过程中的几个步骤,但仍包括了初始化的语法,语义解析并计算语句HASH值与SQL AREA中已有语句进行对比;若匹配则查询优化等昂贵的操作得以避免。

    另请注意,10053事件仅在硬解析过程中被触发。

    Most of library cache latches have gone in 11g

    10g

    SELECT t1.ksllasnam "parent_name",
    t2.ksllwnam "location"
    FROM x$ksllw t2, x$kslwsc t1
    WHERE t2.indx = t1.indx
    AND ksllasnam like '%library cache%'

    parent_name location
    -------------------------------------------------- ----------------------------------------------------------------
    library cache kqlftc
    library cache kqlmbfre: parent
    library cache kqlmbpil: parent
    library cache kqlmbinv: parent
    library cache kqlmbfre: child: in loop
    library cache kqlmbfre: child: no obj to free
    library cache pin kqlmbpil: child
    library cache pin kqlmbpil: child: free and continue
    library cache pin kqlmbp: child
    library cache pin kqlmbpil: child: post parent after timeout
    library cache kgluka: parent
    library cache kglScanDependency
    library cache kglscn: parent
    library cache kglsca: parent
    library cache kglLockCursor
    library cache kgldrp: child
    library cache kgldrx: child
    library cache kgldmc: parent
    library cache kglhdiv: child
    library cache kglivl: parent
    library cache kglidp: child
    library cache kgldtld: parent
    library cache kgldte: parent
    library cache kglilf: parent
    library cache kglmvsca: parent
    library cache kgldte: child 0
    library cache kglget: KGLDSBYA
    library cache kglget: set reserved lock
    library cache kglget: hash collision
    library cache kglget: unpin heap 0
    library cache kglpin: child: KGLMX
    library cache kglupi: child: outside loop
    library cache kglkep: child
    library cache kglukp: child
    library cache kglupc: child
    library cache kglgfc: child
    library cache kglalt: child
    library cache kglupd: child
    library cache kglsts: child
    library cache kglpur: child
    library cache kglprg: child
    library cache kglini: child
    library cache kglver: child
    library cache kglivl: child
    library cache kglivr: child
    library cache kgldte: child
    library cache kglhdbrnl: child
    library cache kglobr: child
    library cache kglpnc: child
    library cache kglati
    library cache kglpim
    library cache kglupkp
    library cache kgllfr
    library cache kglnti
    library cache kglic
    library cache kglpnp: child
    library cache kglhfr: child
    library cache kglrtl
    library cache kglpin
    library cache kglswp
    library cache kgldti: 2child
    library cache kgldtld: 2child
    library cache kgl 2child pad56
    library cache kgllkal: parent
    library cache kglhdal: parent
    library cache kglhdunp: parent: flush
    library cache kglhdiv0: parent: invalidate
    library cache kglhdbr0: parent: invalidate
    library cache kgllldl: child:
    library cache kglhdbr: child
    library cache kgl parent pad2132
    library cache kglhdgn: child:
    library cache kglhdgc: child:
    library cache kglhdgh: child:
    library cache kglobpn: child:
    library cache kglpndl: child: before processing
    library cache kglhdiv0: child
    library cache kglpndl: child: after processing
    library cache kglobld
    library cache kglpin: child: heap processing
    library cache kglpnal: 2child
    library cache load lock kglllal: llatch
    library cache lock kglget: un-set reserved lock
    library cache lock kgllkal: child: multiinstance
    library cache lock kgllkal: child: in loop
    library cache lock kgllkal: child: deadlk det: error
    library cache lock kgllkdl: free heap 0
    library cache lock kgllkdl: child: cleanup
    library cache lock kgllkdl: child: multiinstance
    library cache lock kgllkdl: child: no lock handle
    library cache lock kgllkc: child
    library cache lock kglpsl: child
    library cache lock kglhdbrnl: child
    library cache lock kglhdcb: child
    library cache lock ksucallcbksafely: kqllhng
    library cache pin ksucallcbksafely: kqlphng
    library cache load lock ksucallcbksafely: kqldlhn
    library cache pin kglobld: child:
    library cache pin kgllkdl: child: free pin
    library cache pin kglpnal: child: alloc space
    library cache pin kglpnal: child: check granted
    library cache pin kglpndl: child: multiinstance
    library cache pin kglpnck
    library cache pin kglupc
    library cache pin kglpin: child: KGLMS
    library cache pin kglupi: child: within loop
    library cache pin kgldnp: child
    library cache pin kglpnc: child
    library cache pin kglpnp: child
    library cache pin kglobf0
    library cache pin kglpndl
    library cache pin dummy
    library cache pin kglivl0
    library cache hash chains kglpnrel
    library cache hash chains kglpnget
    library cache hash chains spare
    library cache pin allocation kglpnrel
    library cache pin allocation kglpnget
    library cache pin allocation spare1
    library cache pin allocation spare2
    library cache lock allocation kgllkrel
    library cache lock allocation kgllkget
    library cache lock allocation spare1
    library cache lock allocation spare2
    library cache load lock kglllal: llatch: in loop
    library cache load lock kgllldl: llatch
    library cache kgl 2child pad2137
    library cache kgl 2child pad2138
    library cache kksftc

    11g

    SELECT t1.ksllasnam "parent_name",
    t2.ksllwnam "location"
    FROM x$ksllw t2, x$kslwsc t1
    WHERE t2.indx = t1.indx
    AND ksllasnam like '%library cache%'

    parent_name location
    --------------------------------------- ---------------------------------------------
    library cache load lock kglllal: llatch
    library cache load lock ksucallcbksafely: kqldlhn
    library cache load lock kglllal: llatch: in loop
    library cache load lock kglllal: llatch: in loop 2
    library cache load lock kgllldl: llatch

    latch:
    library cache lock
    library cache lock allocation
    library cache pin
    library cache pin allocation
    library cache

    ALL above latches have gone(latch library cache load lock left), library cache lock/pin don't need acquire library cache any more in 11g ;

    library cache lock/pin structure now are protected by Library cache type mutex also.
    But We can not find out how kglpin/kgllkdl and else function are utlized with mutex from any V$/X$ dynamic views.
    Mutex is more black-box than Latch.

    SQL> select mutex_type ,location from v$mutex_sleep_history group by mutex_type,location;

    MUTEX_TYPE LOCATION
    -------------------------------- ---------------------------------------------
    Library Cache kglhdgn2 106
    Library Cache kglget1 1
    Cursor Pin kksfbc [KKSCHLCREA]
    Cursor Parent kksfbc [KKSPRTLOC1]
    Library Cache kglrfcl1 79
    Library Cache kgllkal1 80
    Library Cache kgllkdl1 85
    Cursor Pin kkslce [KKSCHLPIN2]
    Library Cache kglhdgh1 64
    Library Cache kglpin1 4
    Library Cache kglpndl1 95
    Cursor Pin kksfbc [KKSCHLPIN1]
    Library Cache kglhdgn1 62
    Library Cache kglpur1 28
    Library Cache kglpnal1 90
    Library Cache kglobpn1 71
    Library Cache kglpnal2 91

    Know More About Libarary Cache and Latches

    Stored objects And Transient objects are stored in the library cache, neither Temporary objects nor Permanent objects.

    The library cache is structured as a Hash table .But Library cache objects are composed of data heaps.
    Oracle access hash tables through hash buckets.

    SHARED_POOL_SIZE does the Oracle server determine the number of hash tables. When the the shared pool is larger, it can accommodate more object handles.

    _KGL_BUCKET_CNT can be used to set the number of hash buckets and deprecated.
    The minimum size of the hash table is 509 buckets in ORACLE 7.

    For performance reasons, when the linked list has an average depth of 2 the Oracle server doubles the size of the hash table.

    Locks manage concurrency whereas pins manage cache coherency.

    There are two valid library cache pin modes: Share and Exclusive

    An object handle is protected by a latch determined by the bucket it hashes into using the formula latch# = mod(bucket#, #latches) .

    The hidden parameter _KGL_LATCH_COUNT is used to determine the number of child latches.”The default value should be adequate, but if contention for the library cache latch cant be resolved, it may be advisable to increase this value. The default value for _KGL_LATCH_COUNT is the next prime number after CPU_COUNT. This value cannot exceed 66 (See: <>). ”

    Begin 10.2.0.2, mutex take place cursor pin latch.To avoid using Mutex latches, you can set _kks_use_mutex_pin=false .

    CURSOR_SPACE_FOR_TIME has been deprecated in 10.2.0.5 and 11.1.0.7.

    CURSOR_SPACE_FOR_TIME was originally introduced to try and help reduce latch contention by keeping cursors in memory in the SGA rather than allowing their data to be flushed from the shared pool. Such latch contention is avoided in current releases by the use of cursor mutexes and so this parameter is no longer relevant.

    v$open_cursor lists kinds of library cache lock,x$kgllk – Details about Object locks

    
    v$open_cursor
    
    select inst_id,
           kgllkuse,
           kgllksnm,
           user_name,
           kglhdpar,
           kglnahsh,
           kgllksqlid,
           kglnaobj,
           kgllkest,
           decode(kgllkexc, 0, to_number(NULL), kgllkexc),
           kgllkctp
      from x$kgllk
     where kglhdnsp = 0
       and kglhdpar != kgllkhdl
    
    
    SQL> select distinct kgllkctp from x$kgllk ;
    
    KGLLKCTP
    --------------------------------------------
    SESSION CURSOR CACHED
    PL/SQL CURSOR CACHED
    OPEN
    OPEN-RECURSIVE
    DICTIONARY LOOKUP CURSOR CACHED
    
    

    沪ICP备14014813号-2

    沪公网安备 31010802001379号