Database Reference
In-Depth Information
Listing 11-5. TX Enqueue Contention
REM Reviewing the output of ges_blocking_locks.sql script, session 12229 is
REM waitingfor TX lock. Session 6445 is holding a TX enqueue.
REM Script is printed in Listing 11-4.
SQL> @ges_blocking_locks.sql
INST_ID : 2
RESOURCE_NAME1 : [0x615000b][0x2417],[TX][ext 0
GRANT_LEVEL : KJUSEREX
REQUEST_LEVEL : KJUSEREX
STATE : GRANTED
SID : 6445
EVENT : ges remote message
SEC : 0
-----------------
INST_ID : 1
RESOURCE_NAME1 : [0x615000b][0x2417],[TX][ext 0
GRANT_LEVEL : KJUSERNL
REQUEST_LEVEL : KJUSEREX
STATE : OPENING
SID : 12229
EVENT : enq: TX - row lock contention
SEC : 462
Transaction_id is used to coin a unique resource_name [0x615000b][0x2417],[TX] in GRD. The first part of
string [0x615000b] is the concatenation of strings 615 and 000b. 0x615 is a hexadecimal representation of decimal
1557, and 0x000b is the hexadecimal representation of 11. The second part of string 0x2417 is the hexadecimal
representation of 9239. These three parts, 1557.11.9239, combined together constitute the transaction_id we queried
using dbms_transaction package call.
gv$ges_blocking_enqueue encompasses both Ges and GCs layer locks. If your buffer cache is huge, in the
order of hundreds of gigabytes, accessing this gv$ view can be slower.
Note
Oracle Development introduces code optimization in new releases, and some of those features are quietly
introduced. For example, from version 11g, TX resource visibility stays local until a session in another instance tries
to acquire a lock on that TX resource. This optimistic strategy improves RDBMS efficiency, as most transactions
will complete without inducing or suffering from locking waits. Only a few percent of transactions will suffer from
locking contention and hence it is efficient to defer maintenance of GES structures until it is absolutely necessary to
do so. With this optimization, code path is reduced as the maintenance of global structures is completely avoided for
most transactions. Further, this optimization is very useful in reducing global cache workload in the database using
application affinity.
In addition to TX resources, the row-level locking scheme must protect the base table from any incompatible
change also. So, lock on a TM resource protecting the table is acquired in a single-instance database. In RAC, TM locks
are globalized as GRD resources, and locks are acquired in row share mode.
I will a fabricate TM resource_name by converting object_id of T1 table to a string of format [0x obj_id ]
[0x0],[TM] . This resource_name will be used to search in gv$ges_resource in Listing 11-6. The following code uses
built-in functions to coin the resource_name.
 
 
Search WWH ::




Custom Search