Tuesday, April 14, 2009

Oracle Common Wait Event Parameters

The following are descriptions of some of the more common wait event parameters.

1. 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 the following SQL statements:

select name, kind

from ext_to_obj

where file# = file#

and lowb <= block#

and highb >= block#;

2. blocks

The number of blocks that is being either read from or written to the file. The block size is dependent on the file type:

  • Database files have a block size of DB_BLOCK_SIZE
  • Logfiles and control files have a block size that is equivalent to the physical block size of the platform

3. break?

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.

4. class

The class of the block describes how the contents of the block are used. For example, class 1 represents data block, and class 4 represents segment header.

5. dba

The initials "dba" represents the data block address, which consists of a file number and a block number.

6. driver id

The address of the disconnect function of the driver that is currently being used.

7. file#

The following query returns the name of the database file:

select *

from v$datafile

where file# = file#;

8. id1

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).

9. id2

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).

10. le

The relative index number into V$GC_ELEMENT.

11. mode

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:

Mode Value

Description

1

Null mode

2

Sub-Share

3

Sub-Exclusive

4

Share

5

Share/Sub-Exclusive

6

Exclusive

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';

12. 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';

13. namespace

The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view.

14. requests

The number of I/Os that are "requested." This differs from the number of blocks in that one request could potentially contain multiple blocks.

15. session#

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#;

16. waited

This is the total amount of time the session has waited for this session to terminate.

No comments:

Post a Comment