Library Cache Scripts

Marking objects for keeping in the library cache is vital to library cache and shared pool performance. This script is intended to be run regularly from cron to automate keeping. It follows our "keep everything that moves" philosophy. It keeps all in-use keep-able database objects. It also keeps some often reused cursors early in the life of the instance, while more than 20% of the shared pool body is still reserved in permanent memory. It is smart enough to avoid keeping cursors with high version counts (greater than 4) or any invalid versions. Working out the right keeping strategy for each site remains a complex task, but this script does most of what is needed at most sites.

Download script for: 7.3, 8.0 or 8.1

From Oracle 7.3, the sequence cache has been part of the library cache, and thus part of the shared pool. A side effect has been that cached sequence numbers can be aged out of the shared pool and thus lost. So even if you keep nothing else, cached sequences should be kept lest sequence numbers be lost. This script marks all cached sequences for keeping. It is intended to be used at instance startup.

Download script for: 7.3, 8.0 or 8.1

package_sql_executions.sql

We often see sites where very little has been done to mark important packages and cursors for keeping in the library cache. In part, the problem is that it is difficult to decide what to keep. Under version7, V$DB_OBJECT_CACHE had an execution count for packages, procedures and so on (although the statistics were known to be highly inaccurate under some circumstance). But Oracle8 no longer attempts to maintain executions counts for anything other than cursors. This has left many people in the dark as to which packages (and so on) to keep. The party line seems to be that customers should infer package execution counts from the cursor executions counts, but doing so is non-trivial because you have to navigate through a very obscure X$ table to do it - namely, X$KGLRD instead of the normal library cache dependency structure X$KGLDP. Although Ixora's recommendation remains that you should keep everything, this script at least allows you to attempt to follow the party line. Note that the results should be taken with a pinch of salt, because some packages do not execute any SQL directly, and some SQL statements may have been executed other than from the packages.

Download script for: 8.0 or 8.1

This script shows what has been marked for keeping in the library cache, if anything.

Download script for: 7.3, 8.0 or 8.1

session_cursor_cache.sql

The session cursor cache is an important facility for reducing load on the library cache. In our opinion, the session_cached_cursors parameter should always be set to at least 2. However, a larger value is normally beneficial.

The session cursor cache can be constrained by either the session_cached_cursors parameter, or the open_cursors parameter. This script reports the current maximum usage in any session with respect to these limits. If either of the usage figures approaches 100%, then the corresponding parameter should normally be increased.

This script also reports the percentage of parse calls satisfied by the session cursor cache, as well as the percentage breakdown of the remaining parse calls between hard and soft parse operations. The improvement in the percentage of parse calls satisfied by the session cursor cache can be used to assess benefit of increases in the size of the session cursor cache. (Because of inaccuracies in the statistics in V$SYSSTAT, the breakdown between hard and soft parse operations here should be regarded as merely indicative. More reliable information is available from V$LIBRARYCACHE which is queried by the library_stats.sql script below. Nevertheless, the session cursor cache hit rate is reliable.)

The 8.0 and 8.1 version of the script now also reports the maximum number of cache-able cursors that might be in use in any one session. If memory is abundant, this is the recommended value to use for the session_cached_cursors parameter.

Download script for: 7.3, 8.0 or 8.1

There are several bugs in all current production releases of Oracle that can result in very high version counts for sharable SQL statements. There are also several application development errors that can result in the same symptoms. High version (greater than 16) counts greatly increase the risk of library cache latch contention. This script is intended to perform a quick check on whether any such problems are present by displaying an exponential histogram of version counts.

View sample listing

Download script for: 7.3, 8.0 or 8.1

When the definition of a database object is changed (or might have been changed) all dependent SQL is invalidated. Invalidated SQL statements must be reparsed (reloaded) before they can be executed again. Invalidations are also one possible cause of high version counts. Therefore, invalidations should be eliminated if at all possible. To do so, it is necessary to determine which database objects were changed so as to trigger invalidations, how they were changed, and how that might be prevented in future. This script attempts to do the first part of the job, by attributing invalidations to changed database objects. Where an invalidated cursor is dependent on two or more objects for which the definition appears to have been changed, then the invalidations are distributed evenly between them, and shown in a second column (AND_MAYBE).

Download script for: 8.0 or 8.1

This is a very simple script to show the basic library cache statistics for each namespace.

Download script for: 7.3, 8.0 or 8.1

active_cursors.sql

This script shows all the active cursors for each session, including top level cursors, such as anonymous PL/SQL blocks.

Download script for: 7.3, 8.0 or 8.1

Public synonyms should be entirely avoided in high concurrency environments. The memory wasted is proportional to the number of public synonyms in use, times the number of users. The performance cost is mainly in terms of increased latching in the shared pool and library cache. This script shows the number of users for each public synonym in use. This indicates whether there is any risk of potential performance problems due to public synonyms.

This script tends to understate the severity of the problem, because it looks for the library cache structures used to represent non-existent objects while resolving public synonyms, and these structures can be freed from the cache very quickly under some circumstances.

Download script for: 8.0 or 8.1

Logically equivalent SQL statements are those which differ only in capitalization, white space, comments or literals. If there are logically equivalent SQL statements in the library cache, this script prints out a shareable form of each such statement, preceded by a count of the number of distinct forms of the statement.

Download script for: 7.3, 8.0 or 8.1

This script counts the number of named objects in the library cache, and suggests an index into the bucket count array for the initial sizing of the library cache hash table at instance startup. This value should be set using the _kgl_bucket_count parameter. This prevents the hash table from needing to grow dynamically by making it start at the required size. This prevents the performance problems associated with hash table growth, and helps to control the mapping a library cache object hash values to child latches.

Download script for: 7.3, 8.0 or 8.1

This script is intended for cases in which the contention for the child library cache latches, as shown by V$LATCH_CHILDREN.SLEEPS, is not evenly distributed. That is, when at least one of the latches has seen far more sleeps than the others. Such latches are said to be "hot". The script regards any latch that has seen more than twice the average number of sleeps as hot.

Hot latches are due, at least in part, to intensive access to particular library cache objects. These intensively accessed objects are called the "hot" objects. To resolve the contention, it is necessary to identify the hot objects protected by the hot latches, and to control the mapping of hot objects to latches. There are two cases to consider. In some cases, the contention is due to the fact that multiple hot objects have coincidentally been mapped to the same latch. In other cases, the contention is due to a single extremely hot object, normally an SQL statement.

The objects_on_hot_latches.sql script identifies the objects protected by the hot latches. However, there is a significant complicating factor - namely, that the mapping of library cache objects to latches is not static. It is therefore possible for sleeps against a particular child latch to be due to a coincidence of hot objects on that latch for some period earlier in the life of the instance, but for there no longer to be any such coincidence. This is due to the dynamic resizing of the library cache hash table. Therefore, before objects_on_hot_latches.sql can be used effectively, it is best to stabilize the size of the library cache hash table, possibly using kgl_bucket_count.sql, and thus to prevent the dynamic remapping of objects to latches.

If the problem continues to be a coincidence of hot objects on particular child latches, then it can be addressed by renaming some of those objects carefully, so that their new hash values map onto cold latches. However, it is normally just as effective, and much less intrusive, to permute the effect of the hashing algorithm by adjusting the number of child latches using the _kgl_latch_count parameter.

The more difficult problem to resolve is that of a single extremely hot object. In this case it is necessary to modify the application to use a set of differently named equivalent objects that map to different latches. In the case of an extremely hot SQL statement, it is simply a matter of pseudo randomly introducing one of a set of different comments into the statement text so that each variant of the statement will hash to a different latch. In this way the load for the statement can be borne by multiple latches. We have applied the same principle to a hot sequence, and it could conceivably have other applications as well.

Download script for: 7.3, 8.0 or 8.1

This script unloads all cached sequence numbers from the library cache, by temporarily marking them as NOCACHE. This script is intended to be used prior to shutdown in single-instance Oracle, to prevent the loss of cached sequence numbers, should a SHUTDOWN ABORT become necessary. Download script for: 7.3, 8.0 or 8.1[@more@]