Like enqueue, Oracle uses latches to protect data structures.
One process at a time can either modify or inspect the data structure after acquiring the latch.
Other processes needing access to the data structure must wait till they acquire the latch. Unlike enqueue, processes requesting latch do not have to wait in a queue.
If the request to acquire a latch fails, the process simply waits for a short time and requests the latch again.
The short wait time is called “spin”. If the latch is not acquired after one or more spin iterations, the process sleeps for a short time and tries to acquire the latch again,
sleeping for successively longer periods until the latch is obtained.
You can determine which latch is waited, using this query:
SELECT NAME, ADDR, 'Child ' || CHILD#, GETS, MISSES, SLEEPS FROM GV$LATCH_CHILDREN WHERE ADDR = '&P1RAW' UNION SELECT NAME, ADDR, NULL, GETS, MISSES, SLEEPS FROM GV$LATCH WHERE ADDR = '&P1RAW';
The types are described here.
Use the script posted in "Identify waiting sessions".
1. Using the SQL hash value from the CURRENT_EVENT cursor, you can capture the SQL textfrom the GV$SQLTEXT view as shown next and store it in the appropriate history table.
If you are also interested in the SQL statistics, you can get the information from the V$SQLAREA using the same SQL hash value from the cursor:
SELECT INST_ID, HASH_VALUE, ADDRESS, PIECE, SQL_TEXT FROM GV$SQLTEXT WHERE HASH_VALUE = 4190307765 --sql_hash_value AND INST_ID = 1 --RAC Instance ORDER BY INST_ID, PIECE;
2. Find the hot block:
SELECT TS#, FILE#, DBARFIL, DBABLK, OBJ, CLASS, STATE, TCH FROM X$BH WHERE HLADDR = 'C0000006FE846AD8' --P1RAW or ADDR from gv$latch_children/gv$latch ORDER BY TCH DESC;
Blocks with a TCH (touch count) value that go into double figures or bigger are possible targets for hot blocks.
SELECT DISTINCT A.OBJECT_NAME, A.SUBOBJECT_NAME FROM DBA_OBJECTS A, SYS.X$BH B WHERE (A.OBJECT_ID = B.OBJ OR A.DATA_OBJECT_ID = B.OBJ) AND B.FILE# = 604 --DBARFIL AND B.DBABLK = 2003; --DBABLK