Library cachepin (%)

Library cachepin (%)

Description

Library cache pins are used to managelibrary cache concurrency. Pinning an object causes the heaps to be loaded intomemory (if not already loaded). PINS can be acquired in NULL, SHARE orEXCLUSIVE modes and can be considered like a special form of lock. A wait for a"library cache pin" implies some other session holds that PIN in anincompatible mode.

Metric Summary

The rest of the information in thissection is only valid for this metric when it appears in either the EnterpriseManager Grid Control or the Enterprise Manager Database Control (ifapplicable).

The following table shows how often themetric's value is collected and compared against the default thresholds. The'Consecutive Number of Occurrences Preceding Notification' column indicates theconsecutive number of times the comparison against thresholds should hold TRUEbefore an alert is generated.

Target Version	Evaluation and Collection Frequency	Upload Frequency	Operator	Default Warning Threshold	Default Critical Threshold	Consecutive Number of Occurrences Preceding Notification	Alert Text
pre-10g	Every Minute	After Every Sample	>	20	Not Defined	3	%value%%% of service time is spent waiting on the 'library cache pin' event.

Data Source

(DeltaLibraryCachePinTime/DeltaServiceTime)*100where:

• DeltaLibraryCachePinTime: difference of 'sum of time waited for sessions of foreground processes on the 'library cache pin' event' between sample end and start
• DeltaServiceTime: difference of 'sum of time waited for sessions of foreground processes on events not in IdleEvents + sum of 'CPU used when call started' for sessions of foreground processes' between sample end and start

See Idle Events

User Action

What to do to reduce these waits dependsheavily on what blocking scenario is occurring. A common problem scenario isthe use of DYNAMIC SQL from within a PL/SQL procedure where the PL/SQL code isrecompiled and the DYNAMIC SQL calls something which depends on the callingprocedure.

• If there is general widespread waiting then the shared pool may need tuning.
• If there is a blocking scenario, collect evidence as described in the following query and contact Oracle support.

The following query will list the waitersand the session holding the pin, along with the wait event the holder iswaiting for.

column h_wait format A20

SELECT s.sid,

   waiter.p1raw w_p1r,

   holder.event h_wait,

   holder.p1raw h_p1r,

   holder.p2raw h_p2r,

   holder.p3raw h_p2r,

   count(s.sid) users_blocked,

   sql.hash_value

FROM

   v$sql sql,

    v$session s,

   x$kglpn p,

   v$session_wait waiter,

   v$session_wait holder

WHERE

   s.sql_hash_value = sql.hash_value and

   p.kglpnhdl=waiter.p1raw and

   s.saddr=p.kglpnuse and

   waiter.event like 'library cache pin' and

   holder.sid=s.sid

GROUP BY

   s.sid,

   waiter.p1raw ,

   holder.event ,

   holder.p1raw ,

   holder.p2raw ,

   holder.p3raw ,

   sql.hash_value

;

Related Topics

About Alerts

About the Metric Detail Page

Editing Thresholds

Understanding Line Charts

Copyright © 1996, 2009, Oracle and/or itsaffiliates. All rights reserved. 
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
Other names may be trademarks of their respective owners.