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;