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;

    Script:List OBJECT DEPENDENT

    以下脚本用以列出数据库中对象的依赖性:

    REM OBJECT DEPENDENT 
    
    select D_OBJ#,
           do.object_name,
           do.object_type dtyp,
           do.status      dsta,
           D_TIMESTAMP,
           ORDER#,
           P_OBJ#,
           po.object_name,
           po.object_type ptyp,
           po.status      psta,
           P_TIMESTAMP
      from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
     where D_OBJ# = do.object_ID
       and P_OBJ# = po.object_ID
       and do.object_ID in
           (select object_id from dba_objects where OBJECT_NAME = '&OBJNAME')
    /
    
    
    Select object_id, referenced_object_id, level
     from public_dependency
    start with object_id = (Select object_id
    from sys.DBA_OBJECTS
    WHERE owner        = upper('&owner')
    AND   object_name  = upper('&name')
    AND   object_type  = upper('&type'))
    connect by prior referenced_object_id = object_id
    /
    
    Select to_char(object_id) object_id, to_char(referenced_object_id) referenced_object_id, to_char(level) "LEVEL"
     from public_dependency
    connect by prior object_id = referenced_object_id
    start with referenced_object_id = (
       Select object_id from sys.DBA_OBJECTS
    WHERE owner        = upper('&owner')
    AND   object_name  = upper('&name')
    AND   object_type  = upper('&type'))
    /
    
    set feedback off
    set ver off
    set pages 10000
    column Owner format "A10"
    column Obj#  format "9999999999"
    column Object format "A35"
    rem
    ACCEPT OWN   CHAR PROMPT "Enter OWNER pattern: "
    ACCEPT NAM   CHAR PROMPT "Enter OBJECT NAME pattern: "
    prompt
    prompt Objects matching &&OWN..&&NAM
    prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    select o.obj# "Obj#",
           decode(o.linkname, null, u.name||'.'||o.name,
            o.remoteowner||'.'||o.name||'@'||o.linkname) "Object",
           decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                           4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                           7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                          10, '*Not Exist*',
                          11, 'PKG BODY', 12, 'TRIGGER',
                          13, 'TYPE', 14, 'TYPE BODY',
                          19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                          22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                          28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                          32, 'INDEXTYPE', 33, 'OPERATOR',
                          34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                          40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                          42, 'MATERIALIZED VIEW',
                          43, 'DIMENSION',
                          44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                          48, 'CONSUMER GROUP',
                          51, 'SUBSCRIPTION', 52, 'LOCATION',
                          55, 'XML SCHEMA', 56, 'JAVA DATA',
                          57, 'SECURITY PROFILE', 59, 'RULE',
                          62, 'EVALUATION CONTEXT', 66, 'JOB', 67, 'PROGRAM',
                          68, 'JOB CLASS', 69, 'WINDOW', 72, 'WINDOW GROUP',
                          74, 'SCHEDULE', 'UNDEFINED') "Type",
           decode(o.status,0,'N/A',1,'VALID', 'INVALID') "Status"
      from sys.obj$ o, sys.user$ u
     where owner#=user#
       and u.name like upper('&&OWN') and o.name like upper('&&NAM') ;
    prompt
    ACCEPT OBJID CHAR PROMPT "Enter Object ID required: "
    prompt
    prompt
    prompt Object &&OBJID is:
    prompt ~~~~~~~~~~~~~~~~~~~
    select o.obj# "Obj#",
           decode(o.linkname, null, u.name||'.'||o.name,
            o.remoteowner||'.'||o.name||'@'||o.linkname) "Object",
           decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                           4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                           7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                          10, '*Not Exist*',
                          11, 'PKG BODY', 12, 'TRIGGER',
                          13, 'TYPE', 14, 'TYPE BODY',
                          19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                          22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                          28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                          32, 'INDEXTYPE', 33, 'OPERATOR',
                          34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                          40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                          42, 'MATERIALIZED VIEW',
                          43, 'DIMENSION',
                          44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                          48, 'CONSUMER GROUP',
                          51, 'SUBSCRIPTION', 52, 'LOCATION',
                          55, 'XML SCHEMA', 56, 'JAVA DATA',
                          57, 'SECURITY PROFILE', 59, 'RULE',
                          62, 'EVALUATION CONTEXT', 66, 'JOB', 67, 'PROGRAM',
                          68, 'JOB CLASS', 69, 'WINDOW', 72, 'WINDOW GROUP',
                          74, 'SCHEDULE', 'UNDEFINED') "Type",
           decode(o.status,0,'N/A',1,'VALID', 'INVALID') "Status",
           substr(to_char(stime,'DD-MON-YYYY HH24:MI:SS'),1,20) "S-Time"
      from sys.obj$ o, sys.user$ u
     where owner#=user# and o.obj#='&&OBJID' ;
    prompt
    prompt Depends on:
    prompt ~~~~~~~~~~~~
    select o.obj# "Obj#",
           decode(o.linkname, null,
            nvl(u.name,'Unknown')||'.'||nvl(o.name,'Dropped?'),
            o.remoteowner||'.'||nvl(o.name,'Dropped?')||'@'||o.linkname) "Object",
           decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                           4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                           7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                          10, '*Not Exist*',
                          11, 'PKG BODY', 12, 'TRIGGER',
                          13, 'TYPE', 14, 'TYPE BODY',
                          19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                          22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                          28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                          32, 'INDEXTYPE', 33, 'OPERATOR',
                          34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                          40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                          42, 'MATERIALIZED VIEW',
                          43, 'DIMENSION',
                          44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                          48, 'CONSUMER GROUP',
                          51, 'SUBSCRIPTION', 52, 'LOCATION',
                          55, 'XML SCHEMA', 56, 'JAVA DATA',
                          57, 'SECURITY PROFILE', 59, 'RULE',
                          62, 'EVALUATION CONTEXT', 66, 'JOB', 67, 'PROGRAM',
                          68, 'JOB CLASS', 69, 'WINDOW', 72, 'WINDOW GROUP',
                          74, 'SCHEDULE', 'UNDEFINED') "Type",
            decode(sign(stime-P_TIMESTAMP),
                      1,'*NEWER*',-1,'*?OLDER?*',null,'-','-SAME-')
    "TimeStamp",
    decode(o.status,0,'N/A',1,'VALID','INVALID') "Status"
      from sys.dependency$ d,  sys.obj$ o, sys.user$ u
     where P_OBJ#=obj#(+) and o.owner#=u.user#(+) and D_OBJ#='&&OBJID' ;

    沪ICP备14014813号-2

    沪公网安备 31010802001379号