Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)

A library cache dump will have the details about the objects in the library cache, including the dependency structures and the details about the cursor, such as hash value and timestamp. This event can be used either with the immediate option, which dumps the library cache once the command is issued, or it can be triggered when an error occurs.

Syntax

Following methods show how to dump the library cache contents.

Using ALTER SESSION

The following ALTER SESSION command can be used to dump the library cache at level 10.

alter session set events 'immediate trace name library_cache level 10'

Using oradebug

The following oradebug commands can be used to produce an immediate dump of the library cache at required level.

oradebug setmypid oradebug unlimit oradebug dump library_cache <level>

Controlling the Dump Information Using Levels

The following list shows the available levels and the information they produce:

• Level 1 Dumps library cache statistics

• Level 2 Dumps hash table summary

• Level 4 Dumps library cache objects with basic information

• Level 8 Dumps objects with detailed information (including child references, pin waiters, etc.)

• Level 16 Dumps heap sizes (can be latch intensive )

• Level 32 Dumps heap information

You can mix these levels to produce various pieces of information. For example, if you use level 11 (8 + 2 + 1), the dump will have the dumps of level 8, level 2, and level 1.