Database Reference
In-Depth Information
(SELECT resource_name, master_node FROM v$ges_resource
WHERE resource_name LIKE '%'||c1.res||'%'
)
LOOP
dbms_output.put_line ('Resource name '|| c2.resource_name ||
', Master '||c2.master_node );
END LOOP;
dbms_output.put_line ('-------------------');
dbms_output.put_line ('Lock details...');
dbms_output.put_line ('-------------------');
FOR c3 IN
(SELECT resource_name1, transaction_id0, pid,
state, owner_node, grant_level
FROM v$ges_enqueue WHERE resource_name1 LIKE '%'||c1.res ||'%'
)
LOOP
dbms_output.put_line ('Res name '|| c3.resource_name1 || ', owner '||c3.owner_node );
dbms_output.put_line ('...Transaction_id0 '|| c3.transaction_id0 ||
',Level '||c3.grant_level|| ' State ' || c3.state );
END LOOP;
END LOOP;
END;
/
Refer to the output of Listing 11-12 printed in the following section: There are two types of resources—LB and NB
types—for the library cache object of T1_LIBTEST table. Output from gv$ges_enqueue shows that locks are acquired
by instance 1 in KJUSERPR mode while parsing the statement. LB resource type represents the global resource for
library cache lock on table T1_LIBTEST, and NB resource type represents the global resource for library cache pin on
table T1_LIBTEST.
Searches gv$ges_enqueue to identify locks associated with the resource_name.
-------------------
Object Details...APPS.T_LIBTEST
-------------------
Resource details...
-------------------
Resource name [0xd9bcbc52][0xb0a18d29],[LB][, Master 2
Resource name [0xd9bcbc52][0xb0a18d29],[NB][, Master 2
-------------------
Lock details...
-------------------
Res name [0xd9bcbc52][0xb0a18d29],[LB][, owner 1
...Transaction_id0 131479,Level KJUSERPR ,State GRANTED
Res name [0xd9bcbc52][0xb0a18d29],[NB][, owner 1
...Transaction_id0 131479,Level KJUSERPR ,State GRANTED
Troubleshooting Library Cache Lock Contention
It is crucial to understand how library cache locks and resources are globalized in a RAC database, and the preceding
section provided a foundation. In this section, I will create a library cache lock and library cache pin contention to
show a method to troubleshoot the contention.
