Wednesday, January 19, 2011

RAC: Library Cache locks and Pins

Scripts presented here and here are not very helpful for RAC environments, because x$ tables are local to instances and if the blocker is in a different instance than the blocked, you will get no results.
SELECT INST_ID,
       HANDLE,
       GRANT_LEVEL,
       REQUEST_LEVEL,
       RESOURCE_NAME1,
       RESOURCE_NAME2,
       PID,
       TRANSACTION_ID0,
       TRANSACTION_ID1,
       OWNER_NODE,
       BLOCKED,
       BLOCKER,
       STATE
  FROM GV$GES_BLOCKING_ENQUEUE;

Querying GV$GES_BLOCKING_ENQUEUE you will see in RESOURCE_NAME1 column, in a library cache lock entries like "[0x1b370d][0x81b0000],[LA]" and in library cache pins "[0x1b370d][0x81b0000],[NA]".
The range for library cache locks is LA to LZ and for library cache pins NA to NZ.

The blocker is the session with value "1" in the BLOCKER column.

No comments:

Post a Comment