Database Reference
In-Depth Information
table is a recommended approach. So, to resolve DFS lock handle contention, you must understand the root cause
before resolving the contention. Also, it is important to realize that every instance will have some DFS lock handle
contention and only when the time spent waiting for DFS lock handle contention is a major contributor of wait time
should you worry about the contention.
DFS Lock Handle Summary
Table
11-5
lists few common enqueue types associated with DFS lock handle mechanism. It is out of scope to discuss
every DFS lock handle enqueue type.
Table 11-5.
DFS Lock Request—Enqueue Types
Enqueue
Comment
Possible reason
SS
Sort Segment
Drop of sort segment or temporary tablespace configuration issues.
IV
Invalidation
Object invalidation due to a DDL statement.
CI
Cross Invocation
Many possible reasons. Check note 34631.1.
Library Cache Locks/Pins
Library cache locks protect library cache objects from concurrent modifications, and library cache pins protect
a library cache object while that library object is busy in execution. In a single-instance database, parse locks on a
SQL statement (cursors) implicitly acquire library cache locks on dependent objects. For example, when a table is
altered then the library cache object for that table will be invalidated. That invalidation would recursively invalidate
dependent cursors, requiring cursors to be reparsed on subsequent execution.
In a RAC database, dependency between the cursors and objects must be maintained globally. Local library
cache locks are not sufficient to maintain the global dependency. For example, if you alter a table in instance 1, then
all packages and cursors dependent upon the table must be invalidated in all instances. This invalidation is needed to
trigger an automatic recompilation of library cache objects in the subsequent execution of cursors.
Global dependency for library cache object is maintained through GRD resources and locks. Library cache
locks are globalized as resource types in the range of LA through LP, and library cache pins are globalized as NA
through NZ types of resources. I will explain the global locks with an example. In Listing 11-11, a PL/SQL block
is creating a dynamic SQL statement joining table
T_LIBTEST
1024 times, and then parses the statement calling
dbms_sql package. This example is artificial and serves no real-world purpose other than to show the use of GRD
resources and locks.
8
Listing 11-11.
Library Cache Lock and Pin
REM create a table
CREATE TABLE t_libtest (n1 NUMBER );
REM dynamically populate a CLOB variable and try to parse it.
DECLARE
v_sqltext CLOB;
c1 NUMBER ;
8
MOS note 122793.1 is also a good document to read about library cache lock contention.
