Shared Pool   «Prev 

Library Cache Section

Hit ratio, pin hit ratio, reloads

hit ratio should be > 70 , and pin ratio > 70 ...

  1. Indicates whether the measurement is for the SQL area, a table or procedure, a package body, or a trigger.
  2. Pin hit ratio column Text: The pin hit ratio--the number of times an SQL statement of PL/SQL block was accessed for execution in the library cache-is the most important part of the script. The higher the pin hit ratio, the more frequently Oracle found the SQL from the prior execution and did not need to re-parse the statement.
  3. This counts the number of times the parsed representation did not exist in the library cache, forcing Oracle to allocate the private SQL areas in order to parse and execute the statement.

Terms 1) library cache pin and 2) library cache lock

  1. library cache pin: This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock.
  2. library cache lock: This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:
    1. One client can prevent other clients from accessing the same object
    2. The client can maintain a dependency for a long time which does not allow another client to change the object
    This lock is also obtained to locate an object in the library cache.