Databases Reference
In-Depth Information
In the following screenshot, we can see the results of the last three steps:
There's more...
The library cache stores parsed SQL statements, execution plans, PL/SQL blocks, and Java
classes, ready to be executed. The application code shared in the Library Cache can be easily
reused by different database sessions.
The reuse of a piece of code, already in the cache, is called a Library Cache Hit. A Library
Cache Miss occurs when the execution of a piece of code can't find the already parsed code
in the library cache.
The Library Cache Hit is also called soft parse, while
the Library Cache Miss is called hard parse.
The main reasons to tune the Library Cache are to minimize misses (reparsing) and avoid
invalidations. They last occur when there are cached statements in the Library Cache that
reference a database object which is modified, for example, a table altered to add a field.
The reload is executed when the corresponding parsed statement is not found in the
Library Cache, due to invalidation or aging of the statement. The LRU (least recently used)
algorithm determines which statements in the cache should be aged out to make room
for newly-executed statements.
