Oracle RAC – Query The GRD – and object locking

Oracle RAC stores a Global Resource Directory in the shared pool to help facilitate Cache Fusion.
The following query displays much of it – and how much space is allocated.  Note: I queried a very inactive sandbox of mine below.

SELECT name, TRUNC( bytes/ 1024/ 1024, 2) size_MB FROM v$sgastat
WHERE name LIKE ‘ges resource%’
OR name LIKE ‘ges enqueues’
OR name LIKE ‘gcs resources’
OR name LIKE ‘gcs enqueues’
OR name LIKE ‘gcs shadows’
/
NAME SIZE_MB
————————– ———-
ges resource pools 0
ges resource dynamic 6.15
ges resource permanent 13.99
gcs resources 44.5
gcs shadows 27.39
ges resource quarantine q 0
ges resource hash table 1.56
ges enqueues 12.78
SQL>

Generally when a lock is requested on an object the Mastering instance is determined using a modified hashing algorithm.
Only one instance masters an object or buffer block.  At startup each instance in the cluster obtains a percentage of all of the objects (to be written to the buffer cache) to be mastered based on the number of nodes in the instance.  These are stored as ranges.  DRM (Dynamic Resource Mastering) supports movement of object blocks to a new master.
LMD – or Global Enqueue services is central to the locking of an object / buffer cache block or range of blocks.
When a lock request is processed, it is either granted and put into a grant queue, or queued for future conversion (to be granted) – to a convert queue.
With a tuning and diagnostics license needed to run AWRs you can see the growth and shrinkage of these GRD components over time (if there has been any) by issuing the following query.

select
to_date( to_char( trunc( begin_interval_time), ‘DD-MON-YYYY’), ‘DD-MON-YYYY’) DAY,
instance_number, trunc( max( bytes/ 1024/ 1024), 2) sz_MB
from (select begin_interval_time, s.instance_number,
sum( bytes) bytes from dba_hist_sgastat g,
dba_hist_snapshot s
where (name like ‘% ges%’ or name like ‘% gcs%’)
and trunc( begin_interval_time) > = sysdate -30
and s.snap_id = g.snap_id
and s.instance_number = g.instance_number
group by begin_interval_time, s.instance_number )
group by to_date( to_char( trunc( begin_interval_time), ‘DD-MON-YYYY’), ‘DD-MON-YYYY’), instance_number order by 1;

Scroll to Top