Database Reference
In-Depth Information
In RAC, it is hard to debug the library cache lock and library cache pin contention using traditional methods.
It is easier to debug the contention by reviewing global locks. Script ges_blocking_locks.sql comes in handy to
understand holders and waiters of GRD locks.
Enqueue Statistics
There are a few enqueue statistics maintained by Oracle database related to global lock activity. AWR report also
calculates the average enqueue time using these statistics. A few lines from AWR report are given in the following
output, showing that the average global enqueue time is 0.1 ms.
Global Cache and Enqueue Services—Workload Characteristics
------------------------------------------------------------
Avg global enqueue get time (ms): 0.1
Three statistics are used in the calculation of this average: “
global enqueue get time
,” “
global enqueue
get async
,” and “
global enqueue gets sync
.” The
global enqueue get time statistic
maintains the total
time spent waiting for global locks;
global enqueue gets async
maintains the total number of async gets (mostly
by background processes); and
global enqueue gets sync
maintains the total number of sync gets (mostly by
foreground processes).
Sum of the values of
global enqueue get async
and
global enqueue get sync
is the total number of enqueue
gets. Average global enqueue time is calculated by dividing
global enqueue get time
by total number of enqueue gets.
It is very important to realize that
Avg global enqueue get time is
an average at an instance level. So, you
need to identify specific locks involved in the locking contention to troubleshoot elevated global enqueue get time.
The value of
Avg global enqueue get time
is merely an indicator about locking efficiency. Higher values for the
statistics imply that there was a locking contention during that AWR sample period.
Here is an example analysis from a production problem. As you can see, Avg global enqueue get time is very high.
Global Cache and Enqueue Services—Workload Characteristics
------------------------------------------------------------
Avg global enqueue get time (ms): 419.7
Reviewing the foreground wait events section, you will realize that there is no locking contention that can justify
elevated global enqueue get time. But, in RAC, library cache locks are globalized as global locks, and so waits for the
GRD locks are counted toward global enqueue get time statistics.
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
library cache lock 5,446 0 456,860 83889 0.0 111.3
library cache: mutex X 11,668 0 5,483 470 0.1 1.3
latch: ges resource hash l 5,915 0 2,462 416 0.0 .6
...
v$wait_chains
View v$wait_chains is another useful view that can be used to identify locking contention quickly. This view retrieves
rows from all instances, and so, there is no global counterpart for this view.
