DFS db file lock

This event occurs only for the DBWR in the Oracle Real Application Cluster. Each DBWR of every instance holds a global lock on each file in shared mode. The instance that is trying to offline the file will escalate the global lock from shared to exclusive. This signals the other instances to synchronize their SGAs with the control file before the file can be taken offline. The name of this lock is DF. Wait Time:

1 second in loop. The DBWR is waiting in a loop (sleep, check) for the other instances to downgrade to NULL mode. During this time, the DBWR cannot perform other tasks such as writing buffers.

Parameters

• name The name of the lock can be determined by looking at the 2 high order bytes of P1 or P1RAW. The name will always be 2 characters. The following SQL statement will retrieve the lock name:

  select chr(bitand(p1,-16777216)/16777215)||
         chr(bitand(p1,16711680)/65535) "Lock"
    from v$session_wait
   where event = 'DFS enqueue lock acquisition';

  The table below shows all the possible lock names.

  Enqueue names in Oracle.

  Name	Meaning
  BL	Buffer Cache Management (PCM lock)
  CF	Controlfile Transaction
  CI	Cross Instance Call
  CU	Bind Enqueue
  DF	Data File
  DL	Direct Loader
  DM	Database Mount
  DR	Distributed Recovery
  DX	Distributed Transaction
  FS	File Set
  IN	Instance Number
  IR	Instance Recovery
  IS	Instance State
  IV	Library Cache Invalidation
  JQ	Job Queue
  KK	Redo Log Kick
  L[A-P]	Library Cache Lock
  MM	Mount Definition
  MR	Media Recovery
  N[A-Z]	Library Cache Pin
  PF	Password File
  PI	Parallel Slaves
  PR	Process Startup
  PS	Parallel slave Synchronization
  Q[A-Z]	Row Cache Lock
  RT	Redo Thread
  SC	System Commit number
  SM	SMON synchronization
  SN	Sequence Number
  SQ	Sequence Enqueue
  SR	Synchronous Replication
  SS	Sort Segment
  ST	Space Management Transaction
  SV	Sequence Number Value
  TA	Transaction Recovery
  TM	DML Enqueue
  TS	Table Space (or Temporary Segment)
  TT	Temporary Table
  TX	Transaction
  UL	User-defined Locks
  UN	User Name
  US	Undo segment Serialization
  WL	Writing redo Log
  XA	Instance Attribute Lock
  XI	Instance Registration Lock

• mode The mode will be stored in the low order bytes of P1 or P1RAW. The mode will be one of the following values:

  Mode	Meaning
  1	Null mode
  2	Sub-Share
  3	Sub-Exclusive
  4	Share
  5	Share/Sub-Exclusive
  6	Exclusive

  With the following SQL statement one can find the name of the lock and the mode of the lock request:

  select chr(bitand(p1,-16777216)/16777215)||
         chr(bitand(p1, 16711680)/65535) "Lock",
         bitand(p1, 65535) "Mode"
    from v$session_wait
   where event = 'DFS enqueue lock acquisition';

• id1 P2 or P2RAW will give the first identifier of the enqueue.
• id2 P3 or P3RAW will give the second identifier of the enqueue. So much depends on the on the name of the enqueue that you are trying to get. The following statistics from v$sysstat are related:
  • global lock gets (non async) : This statistics gets incremented when the get lock operation has finished and the operation was a get (mode is 0).
  • global lock get time: The time it took to complete the synchronous lock get.
  • global lock converts (non async) :This statistics gets incremented when the synchronous convert lock operation has finished.
  • global lock convert time : The time it took to complete the synchronous lock convert.
  With the following SQL statement one can see the complete enqueue request that sessions are waiting for or have just waited for:

  select chr(bitand(p1,-16777216)/16777215)||
         chr(bitand(p1, 16711680)/65535) "Lock",
         bitand(p1,65535) "Mode", p2 id1, p3 id2
    from v$session_wait
   where event = 'DFS enqueue lock acquisition';

  With the following SQL statement that needs to be executed on each instance one can find the current owner(s) of the enqueue that this session is waiting for:

  select l.*
    from v$lock l, v$session_wait s
   where type = chr(bitand(p1, -16777216)/16777215)||
                chr(bitand(p1, 16711680)/65535)
     and event = 'DFS enqueue lock acquisition'
     and lmode > 0;

  There are also two internal tables that keep track of enqueues (local or global): x$ksqrs

  This fixed table shows all outstanding enqueues with an additional flag. It basically shows the same information as the v$lock table.

  ksqrsflg column of X$KSQRS fixed table.

  KSQRSFLG	Meaning
  0x01	Lock is global
  0x02	Not free
  0x04	User deadlock possible
  0x08	Global lock in first release group
  0x10	Global lock in last release group
  0x20	non global lock value is invalid

  x$ksqst

  This fixed table shows the number of gets and waits on each enqueue resource identified by type in v$lock and name in the "DFS enqueue lock ..." events.