Friday, May 25, 2012

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>

No comments: