V$EVENT_NAME

This was run in an Oracle 11.2 database. What is in V$EVENT_NAME?

SQL> desc v$event_name

Name                       Null?    Type

-------------------------- -------- ------------------

EVENT#                              NUMBER

EVENT_ID                            NUMBER

NAME                                VARCHAR2(64)

PARAMETER1                          VARCHAR2(64)

PARAMETER2                          VARCHAR2(64)

PARAMETER3                          VARCHAR2(64)

WAIT_CLASS_ID                       NUMBER

WAIT_CLASS#                         NUMBER

WAIT_CLASS                          VARCHAR2(64)

 

SQL>

 

Despite its name, this is not a dynamic view like V$SESSION, for example. It contains details of all the wait events being recorded by your database. In Oracle 11.2 there are over 1000 wait events:

 

SQL> select count(*) from v$event_name

  2  /

 

  COUNT(*)

----------

      1118

 

SQL>

 

Each event has a number and name. These can change between Oracle versions:

 

SQL> l

  1* select event#, name from v$event_name

SQL> /

 

    EVENT# NAME

---------- ----------------------------------------

         0 null event

         1 pmon timer

         2 logout restrictor

         3 VKTM Logical Idle Wait

         4 VKTM Init Wait for GSGA

         5 IORM Scheduler Slave Idle Wait

         6 Parameter File I/O

         7 rdbms ipc message

         8 remote db operation

         9 remote db file read

        10 remote db file write

        11 Disk file operations I/O

        12 Disk file I/O Calibration

        13 Disk file Mirror Read

        14 Disk file Mirror/Media Repair Write

        15 direct path sync

        16 Datapump dump file I/O

Etc.

 

Each event also has a hash value based on the name. This will not change between Oracle versions if the name remains the same. It is stored in the EVENT_ID column:

 

  1* select event_id, name from v$event_name

SQL> /

 

  EVENT_ID NAME

---------- ----------------------------------------

2516578839 null event

3539483025 pmon timer

3934444552 logout restrictor

939791390 VKTM Logical Idle Wait

1513856046 VKTM Init Wait for GSGA

1421578053 IORM Scheduler Slave Idle Wait

1179235204 Parameter File I/O

866018717 rdbms ipc message

3222461937 remote db operation

2171045634 remote db file read

538064841 remote db file write

166678035 Disk file operations I/O

2215689832 Disk file I/O Calibration

  13102552 Disk file Mirror Read

2577606720 Disk file Mirror/Media Repair Write

2093619153 direct path sync

4155572307 Datapump dump file I/O

etc

 

Each event has a WAIT_CLASS. This allows you to exclude Idle events, for example. Each WAIT_CLASS has a hash value based on its name. This is stored in the WAIT_CLASS_ID column:

 

SQL> l

  1  select wait_class, wait_class_id, count(*)

  2  from v$event_name

  3* group by wait_class, wait_class_id

SQL> /

 

WAIT_CLASS           WAIT_CLASS_ID   COUNT(*)

-------------------- ------------- ----------

Network                 2000153315         35

Configuration           3290255840         24

Queueing                 644977587          9

Other                   1893977003        719

Concurrency             3875070507         32

Idle                    2723168908         94

Scheduler               2396326234          7

User I/O                1740759767         45

System I/O              4108307767         30

Commit                  3386400367          2

Cluster                 3871361733         50

Administrative          4166625743         54

Application             4217450380         17

 

13 rows selected.

 

SQL>