Session Wait Events for Oracle Parallel Server
This appendix describes session wait events which are relevant to a parallel server. It includes the following sections:
Introduction
Since Oracle7 release 7.0.12, a new wait interface keeps track of the waits of all sessions and processes in an instance. The views include V$SYSTEM_EVENT, V$SESSION_EVENT and V$SESSION_WAIT.
The V$SESSION_WAIT view shows the events for which sessions have just completed waiting or are currently waiting. The V$SYSTEM_EVENT view shows the total number of times all the sessions have waited for the events in that view.
This appendix documents the name, wait time, and parameters of each event, and may provide advice on how to tune it. The following SQL statement displays all the events in an Oracle7 release:
SELECT *
FROM V$EVENT_NAME;
Parameter Descriptions
This section documents a number of common event parameters.
name
The name or "type" of the lock can be determined by looking at the two high order bytes of P1 or P1RAW. The name will always be two characters. Use the following SQL statement to 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.
| Name
| Meaning
|
| BL
| Buffer Cache Management (PCM lock)
|
| CF
| Control File Transaction
|
| CI
| Cross Instance Call
|
| CU
| Bind Enqueue
|
| DF
| Data File
|
| DL
| Direct Loader
|
| DM
| Database Mount
|
| DR
| Distributed Recovery
|
| FS
| File Set
|
| IN
| Instance Number
|
| IR
| Instance Recovery
|
| IS
| Instance State
|
| IV
| Library Cache Invalidation
|
| 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 Change Number
|
| SM
| SMON synchronization
|
| SN
| Sequence Number
|
| SQ
| Sequence Enqueue
|
| SV
| Sequence Number Value
|
| ST
| Space Management Transaction
|
| TA
| Transaction Recovery
|
| TM
| DML Enqueue
|
| TS
| Table Space (aka 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
|
Table D - 1. Lock Names in Oracle
mode
The mode is stored in the low order bytes of P1 or P1RAW. It will have one of the following values:
| mode
| meaning
|
| 1
| Null mode
|
| 2
| Sub-Share
|
| 3
| Sub-Exclusive
|
| 4
| Share
|
| 5
| Share/Sub-Exclusive
|
| 6
| Exclusive
|
Table D - 2. Lock Modes
With the following SQL statement you 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, 65536) "Mode"
FROM V$SESSION_WAIT
WHERE EVENT = `DFS enqueue lock acquisition';
id1
P2 or P2RAW gives the first identifier of the enqueue. The meaning of the identifier depends on the name (P1).
id2
P3 or P3RAW gives the first identifier of the enqueue. The meaning of the identifier depends on the name (P1).
Oracle Parallel Server Events
This section documents wait events which are relevant to Oracle Parallel Server. These include:
The following table summarizes OPS wait events.
| Event Name
| P1
| P2
| P3
|
| batched allocate SCN lock request
| --
| --
| --
|
| buffer busy waits
| file#
| block#
| id
|
| DFS db file lock
| file#
| --
| --
|
| DFS enqueue lock acquisition
| name|mode
| id1
| id2
|
| DFS enqueue lock handle
| name|mode
| id1
| id2
|
| DFS enqueue request cancellation
| name|mode
| id1
| id2
|
| DFS lock acquisition
| type|mode
| id1
| id2
|
| DFS lock convert
| mode
| options
|
|
| DFS lock handle
| type|mode
| id1
| id2
|
| DFS lock release
| options
| --
| --
|
| DFS lock request cancellation
| options
| --
| --
|
| lock element cleanup
| file#
| block#
| lenum
|
| lock element waits
| file#
| block#
| lenum
|
| scginq AST call
| --
| --
| --
|
| sequence# cache entry
| entry#
| --
| --
|
| sequence# lock op
| entry#
| --
| --
|
Table D - 3. Wait Events for Oracle Parallel Server
batched allocate SCN lock request
The Oracle foreground process is waiting for another process to allocate a System Change Number (SCN). If the foreground process timed out waiting for a process to get the SCN, it will get the SCN itself.
Wait Time: The wait time is 1 second, on the assumption that an SCN allocate should normally take much less than that. (If the foreground process wakes up due to a post then this does not allow for the possibility of a dead process).
Parameters: None.
Advice: Check for a high number of timeouts on this event. Too many timeouts indicates that there may be too much congestion on the SC resource or DLM in general.
The following statistics from V$SYSSTAT are related:
- next SCNs gotten without going to server
- next SCNs gotten without going to DLM
- Unnecessary process cleanup for SCN batching
buffer busy waits
Wait until a buffer becomes available. This event occurs because a buffer is being read into the buffer cache by another session (and the session is waiting for that read to complete), or because the buffer is in the buffer cache in an incompatible mode.
Wait Time: Normal wait time is 1 second. If we waited for an exclusive buffer during the last wait, then we wait 3 seconds this wait.
Parameters:
file#
This is the file number of the data file that contains the block for which Oracle needs to wait. To find the name of this file, enter:
SELECT *
FROM V$DATAFILE
WHERE file# = file#;
block#
This is the block number of the block for which Oracle needs to wait. The block number is relative to the start of the file. To find the object to which this block belongs, enter:
SELECT name, kind
FROM ext_to_obj_view
WHERE file# = file#
AND lowb <= block#
AND highb >= block#;
To determine the type of the block (segment header, free list group block, and so on) enter:
SELECT 'segment header'
FROM DBA_SEGMENTS
WHERE dbafil = file#
AND dbablk = block#;
SELECT 'freelist group'
FROM DBA-SEGMENTS
WHERE dbafil = file#
AND block# between dbablk and dbablk + nfl;
...
id
The buffer busy wait event is called from different places in the Oracle kernel. Each place in the kernel indicates a different reason.
| Id
| Reason
|
| 0
| A block is being read.
|
| 1003
| Block is being read, probably with undo information for rollback.
|
| 1007
| Trying to get a new block.
|
| 1010
| Trying to get a buffer in Share mode, but a modification has started on the buffer that has not yet been completed.
|
| 1012
| A modification is occurring on an SCUR or XCUR buffer, but has not yet completed.
|
| 1012 (dup)
| Trying to access a CURRENT block, but a modification has started on the buffer and has not yet been completed.
|
| 1013
| Block is being read by another session, so we have to wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel cannot get a buffer in a certain amount of time and assumes a deadlock. It will therefore read the CR version of the block.
|
| 1014
| Block is being read by another session, so we have to wait until the read is completed.
|
| 1016
| The session wants the block in SCUR or XCUR mode. If we are in discrete TX mode, we wait for the first time and the second time escalate the block as a deadlock. In that case the "exchange deadlocks" system statistics is incremented and we yield the CPU for the event "buffer deadlock".
|
Table D - 4. Buffer Busy Waits IDs
Advice: The view V$WAITSTAT contains information on a per block class basis:
SELECT *
FROM V$WAITSTAT;
There is also an internal fixed view that shows the waits per file (X$KCBFWAIT):
SELECT NAME, COUNT
FROM X$KCBFWAIT, V$DATAFILE
WHERE indx + 1 = file#
DFS db file lock
This only shows up for the DBWR in parallel server. Each DBWR of every instance holds a global lock on each file in shared mode. The instance that is trying to put the file offline escalates the global lock from shared to exclusive in order to signal the other instances to synchronize their SGAs with the control file before it can be taken offline. The name of this lock is DF.
Wait Time: 1 second. The DBWR is waiting in a tight loop for the other instances to downgrade to NULL mode. During this time the DBWR cannot perform other tasks, such as writing buffers.
Parameters:
file#
Shows which file number is being brought offline. The following query shows the name of the database file:
SELECT *
FROM V$DATAFILE
WHERE file# = file#;
Advice: Do not try to offline files during a peak time in a database. This can cause the DBWR to fall behind and impact response times of transactions until it catches up with the workload.
DFS enqueue lock acquisition
The Oracle kernel is waiting for the acquisition of a global enqueue. The operation is a get on a lock that it currently does not have. If mode is not 0, it is a convert operation.
Wait Time: The wait is in a tight loop until acquisition is complete. The wait is 0.5 second every loop.
Parameters: name, mode, id1, id2
Advice: A lot 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 statistic is incremented when the get lock operation has finished and the operation was a get (mode is 0).
global lock get time
This statistic shows the time it took to complete the synchronous lock get.
global lock converts (non async)
This statistic is incremented when the synchronous convert lock operation has finished.
global lock convert time
This statistic shows the time it took to complete the synchronous lock convert.
Execute the following SQL statement to see the complete enqueue request for which sessions are waiting, or have just waited:
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';
Execute the following SQL statement on each instance to can find the current owner(s) of the enqueue for which this session is waiting:
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;
DFS enqueue lock handle
Oracle is waiting for the lock handle. The lock handle identifies a lock that is currently held. This lock handle is obtained from the lock state on the initial acquisition and may be used to reconstruct the state for conversion or release. This event is called if we are getting a lock and we want to know the lock handle (to identify the lock in future operations like conversions or release). The lock is maintained by the DLM.
Wait Time: This happens in a tight loop. Waits are 0.5 seconds every time.
Parameters: name, mode, id1, id2
Advice: Much depends on the on the name of the enqueue that you are trying to get. See "DFS enqueue lock acquisition" ![[*]](jump.gif)
for a description.
DFS enqueue request cancellation
Oracle is trying to cancel a lock asynchronously. The reason to cancel is that there is an user interrupt (CTRL-C).
Wait Time: The wait is 0.5 seconds in a tight loop, until the request is cancelled.
Parameters: name, mode, id1, id2
DFS lock acquisition
The locks that show up under this event are only gotten in Parallel Server mode. The locks gotten under the event "DFS enqueue lock acquisition" could also be gotten under normal (exclusive mode Oracle) operation, they would show up under the event "enqueues". This operation can either be synchronous or asynchronous, it depends on the type of the lock. The synchronous operations will show under this event.
Wait Time: Basically we keep on waiting until we have gotten the lock. There is a tight loop here we wait half a second (0.5 second) each time
Parameters: type, mode, id1, id2
Advice: The following statistics in the V$SYSSTAT table are related to this event:
global lock gets (non async)
This statistic is incremented when the synchronous get lock operation has finished and the operation was a get (mode is 0).
global lock get time
The time it took to complete this operation. This doesn't include the wait time for the asynchronous lock gets.
global lock gets (async)
This statistic is incremented for the async lock operations
DFS lock convert
Oracle needs to convert a lock. The convert is a synchronous operation if this event shows up in the V$SESSION_WAIT table, asynchronous if it does not. After a wait has completed there is a check for interrupts. When an interrupt occurs the lock is cancelled.
Wait Time: The wait is 0.5 second in a tight loop, until the convert completes.
Parameters: mode, options
options
These are internal options used by the Oracle kernel to DLM convert operations. These values are useful for debugging certain kind of operations. For example, if one DFS lock convert operation shows a SCGR_READ and the next DFS lock convert operation shows a SCGR_MOD, you can assume that this is a convert on the SC global lock.
Advice: Mostly useful in debugging the type of resource and type of operation that is being awaited. You can see converts by looking at the 0x10 and 0x20 flag.
DFS lock handle
The Oracle kernel needs to get the lock handle, so it is waiting for a currently pending lock operation to return a lock handle. There is a tight loop here until the lock handle is returned.
Wait Time: The wait is 0.5 seconds every time.
Parameters: type, mode, id1, id2
Advice: Much depends on the name of the enqueue you are trying to get. See "DFS enqueue lock acquisition" for a description.
DFS lock release
Oracle waits in this event while it is waiting for the DLM to release a lock. This operation is synchronous.
Wait Time: The wait is 0.5 second in a tight loop, until the lock is released.
Parameters: options
Advice: If a session is hanging in this operation, the problem is usually with the DLM. Check the DLM to see if it is still working.
DFS lock request cancellation
Oracle is trying to cancel a lock synchronously. This can happen for many reasons, commonly because Oracle received a BAST while an AAST is pending. This will most likely be on the PCM locks.
Wait Time: The wait is 0.5 second in a tight loop.
Parameters: options
This will most likely be 0.
Advice: Check DLM statistics on AASTs and BASTs.
lock element cleanup
A PCM lock upconvert was issued but has not finished yet. If the upconvert has finished, the lock element will be cleaned up. Before a lock element can be converted, a lock context (state object) is allocated by the foreground process. After the convert has completed (or is cancelled), the lock context will be cleaned up.
Wait Time: 2 seconds.
Parameters:
file#
Identifies the file in which resides the block for which the session is trying to get a PCM lock in a certain mode (either S or X). With the following SQL statement you can determine the name and the location of the datafile:
select *
from v$datafile
where file# = file#;
block#
This is the block for which the session wants a PCM lock. With the following SQL statement you can find out to which object this block belongs:
select name, kind
from ext_to_obj_view
where file# = file#
and lowb <= block#
and highb >= block#;
lenum
Is used as an index into the lock element table:
select addr
from x$le
where indx = lenum;
You can also join X$LE.ADDR to X$BH.LE_ADDR to find the buffer header in the cache, as follows:
select a.*
from x$bh a, x$le b
where a.le_addr = b.addr;
lock element waits
Basically we are waiting to make sure that there is no activity on the lock element, before we continue.
Wait Time: 1 second.
Parameters:
file#
Identifies the file in which the block resides for which the session is trying to get a PCM lock in a certain mode (either S or X). With the following SQL statement you can determine the name and the location of the datafile.
select *
from v$datafile
where file# = file#;
block#
This is the block for which the session wants a PCM lock. With the following SQL statement you can find out the object to which this block belongs:
select name, kind
from ext_to_obj_view
where file# = file#
and lowb <= block#
and highb >= block#;
lenum
Is used as an index into the lock element table:
select addr
from x$le
where indx = lenum;
We can also join X$LE.ADDR to X$BH.LE_ADDR to find the buffer header in the cache:
select a.*
from x$bh a, x$le b
where a.le_addr = b.addr
scginq AST call
Called by Oracle to find the highest lock mode that is held on a resource.
Wait Time: Wait up to one-fifth of a second, but keep on waiting until the null mode acquisition AST has fired.
Parameters: None.
sequence# cache entry
Only in parallel server: wait for a sequence cache entry to become available.
Wait Time: One hundredth of a second
Parameters: entry#
sequence# lock op
Only in parallel server: wait for the instance lock to be granted.
Wait Time:
Wait up to 5 seconds, but continue waiting for up to 15 minutes.
Parameters: entry#
