This section describes some of the common wait event parameters.
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, issue the following SQL statement:
select segment_name, segment_type, owner, tablespace_name from dba_extents where file_id = file# and block# between block_id and block_id + blocks - 1;
The number of blocks that is being either read from or written to the file. The block size depends on the file type:
Database files have a block size of
Logfiles and control files have a block size that is equivalent to the physical block size of the platform
If the value for this parameter equals 0, a reset was sent to the client. A nonzero value indicates that a break was sent to the client.
The class of the block describes how the contents of the block are used. For example, class 1 represents data block, and class 2 represents sort block. Use this SQL query to see the block classes:
SELECT view_definition FROM V_$FIXED_VIEW_DEFINITION WHERE view_name='GV$WAITSTAT';
The initials "dba" represents the data block address, which consists of a file number and a block number.
The address of the disconnect function of the driver that is currently being used.
The following query returns the name of the database file:
select * from v$datafile where file# = file#;
The first identifier (id1) of the enqueue or global lock takes its value from P2 or P2RAW. The meaning of the identifier depends on the name (P1).
The second identifier (id2) of the enqueue or global lock takes its value from P3 or P3RAW. The meaning of the identifier depends on the name (P1).
The relative index number into
The mode is usually stored in the low order bytes of P1 or P1RAW and indicates the mode of the enqueue or global lock request. This parameter has one of the following values:
Table C-1 Lock Mode Values
Use the following SQL statement to retrieve 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';
name and type
The name or "type" of the enqueue or global lock can be determined by looking at the two high order bytes of P1 or P1RAW. The name is always 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 name of the object namespace as it is displayed in
The number of I/Os that are "requested." This differs from the number of blocks in that one request could potentially contain multiple blocks.
The number of the inactive session. Use the following SQL statement to find more information about the session:
select * from v$session where sid = session#;
This is the total amount of time the session has waited for this session to terminate.