Database Reference
In-Depth Information
REM Derive a resource_name string from object_id.
SELECT DISTINCT '[0x'
||trim(TO_CHAR(object_id, 'xxxxxxxx'))
||'][0x'
|| trim(TO_CHAR(0,'xxxx'))
|| '],[TM]' res
FROM dba_objects WHERE object_name=upper('&objname')
AND owner=upper('&owner') AND object_type LIKE 'TABLE%';
Enter value for objname: T1
Enter value for owner: RS
RES
---------------------------
[0xb6dff3][0x0],[TM]
In Listing 11-6, a SQL statement queries gv$ges_resource and searches for the resources with the coined resource
name. A global resource is visible in both nodes. Also, note that master_node of the resource is set to 0 (inst_id=1).
Querying gv$ges_enqueue, we see that two locks have been acquired on this resource, in KJUSERCW mode (Mode
KJUSERCW—Row Exclusive or Concurrent Write—is compatible with another KJUSERCW mode).
Listing 11-6.
TM Resources
REM Using the derived resource_name, identify all GES resources.
SELECT inst_id, resource_name, master_node
FROM gv$ges_resource WHERE resource_name LIKE '[0xb6dff3][0x0],[TM]%' ;
INST_ID RESOURCE_NAME MASTER_NODE
---------- ------------------------------ -----------
1 [0xb6dff3][0x0],[TM][ext 0x0,0 0
2 [0xb6dff3][0x0],[TM][ext 0x0,0 0
REM Identify all GES locks for that resource.
col state format a10
col inst_id format 99 head Inst
col owner_node format 99 head 'Owner|Node'
SQL> SELECT inst_id, resource_name1, pid, state,
owner_node , grant_level, request_level
FROM gv$ges_enqueue
WHERE resource_name1 LIKE '[0xb6dff3][0x0],[TM]%' ;
Owner
Inst RESOURCE_NAME1 PID STATE Node GRANT_LEV REQUEST_L
---- ------------------------------ ---------- ---------- ----- --------- ---------
2 [0xb6dff3][0x0],[TM][ext 0x0,0 20444 GRANTED 1 KJUSERCW KJUSERCW
1 [0xb6dff3][0x0],[TM][ext 0x0,0 21877 GRANTED 0 KJUSERCW KJUSERCW
In summary, in a RAC database, both TX and TM resources are used to implement the row-level locking scheme,
a scheme similar to the single-instance database locking scheme. While it is possible to understand row-level locks by
querying the gv$lock family of views, it is a better practice to review GRD-level locks also.
Table
11-2
provides a few common reasons for TX enqueue contention and possible causes that can help you to
resolve the contention.
