Database Reference
In-Depth Information
FROM t_one
WHERE n1=100;
N1 FNO BLOCK OBJ V1
---------- ---------- ---------- ---------- ----------
100 4 180 75742 250
■
the previous sQL statement has
length(v1)
phrase in the
SELECT
list. that column is needed to force reading
the data block of the table. Query execution will read the block from the disk into the buffer cache.
Note
Resource_name starts with a format [0xblock_id][0xfile_id],BL, where both block_id and file_id are converted to
hexadecimal format.
4
The query printed below constructs a resource_name protecting the block using dbms_rowid
package. This resource_name will be used to query GRD to view resources and locks.
SELECT DISTINCT '[0x'||trim(TO_CHAR(dbms_rowid.rowid_block_number(rowid), 'xxxxxxxx'))
||'][0x'|| trim(TO_CHAR(dbms_rowid.rowid_to_absolute_fno (rowid,user,'T_ONE'),'xxxx'))
||'],[BL]' res
FROM t_one
WHERE n1=100;
RES
---------------------------
[0xb4][0x4],[BL]
With the derived resource_name, I will query gv$ges_resource to print the GRD
resource
protecting the block. The
output of the query below shows that this resource is visible in inst_id=2 (I am connected to instance 2). Master node
of the resource is 1 (Master_node starts with 0, so master_node=1 is inst_id=2). This resource protects the block with
(file_id=4 and block_id=180). Column on_convert_q shows the count of processes waiting in the converting queue to
acquire locks, indicating the count of lock waiters. Column on_grant_q shows the count of processes holding locks,
essentially, a count of lock holders.
RS@ORCL2:2>
SELECT resource_name, ON_CONVERT_Q, ON_GRANT_Q, MASTER_NODE
FROM gv$ges_resource
WHERE resource_name LIKE '[0xb4][0x4],[BL]%'
/
RESOURCE_NAME ON_CONVERT_Q ON_GRANT_Q MASTER_NODE
------------------------------ ------------ ---------- -----------
[0xb4][0x4],[BL][ext 0x0,0x0] 0 1
1
gv$ges_enqueue externalizes the
locks
held in GRD. The following query accesses gv$ges_enqueue to print
lock details. We can see that owner_node column is set to 1 (column owner_node starts with 0, and so owner_node=1
is inst_id=2). Essentially, in this example, the resource is mastered by inst_id=2 as shown in the output of
gv$ges_resuorce and lock on that resource is also owned by the same instance. (Note that SQL statements in the
section are available in downloadable scripts Listing_10-1.sql).
4
Note that in Database version 12c, the pluggable databases concept is introduced, and multiple pluggable databases can share
the same buffer cache. Since (file_id , block_id ) combination is unique even when multiple databases are plugged in a container
database, so, BL resource_name format remains the same in 12c also.
