Tuesday, February 24, 2009

latch free

The latch free wait occurs when the process waits to acquire a latch that is currently held by other process.
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

No comments:

Post a Comment