V$METRICGROUP

V$METRICGROUP displays information about the metric group for each of the four major Streams components: capture, propagation, apply, and queue.

Column	Datatype	Description
GROUP_ID	NUMBER	Internal ID associated with each group
NAME	VARCHAR2(64)	External name of the group
INTERVAL_SIZE	NUMBER	How often to collect statistics
MAX_INTERVAL	NUMBER	Total number of intervals over which statistics should be collected

Oracle data dictionary views

Oracle dynamic performance views

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.

Oracle Classes of Wait Events

Every wait event belongs to a class of wait event. The following list describes each of the wait classes.

· Administrative: Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)

· Application: Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)

· Cluster: Waits related to Real Application Clusters resources (for example, global cache resources such as 'gc cr block busy')

· Commit: This wait class only comprises one wait event - wait for redo log write confirmation after a commit (that is, 'log file sync')

· Concurrency: Waits for internal database resources (for example, latches)

· Configuration: Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)

· Idle: Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net message from client')

· Network: Waits related to network messaging (for example, 'SQL*Net more data to dblink')

· Other: Waits which should not typically occur on a system (for example, 'wait for EMON to spawn')

· Scheduler: Resource Manager related waits (for example, 'resmgr: become active')

· System I/O: Waits for background process I/O (for example, DBWR wait for 'db file parallel write')

· User I/O: Waits for user I/O (for example 'db file sequential read')

Note:

1. Information about wait events is displayed in three dynamic performance views:

· V$SESSION_WAIT displays the events for which sessions have just completed waiting or are currently waiting.

· V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view.

· V$SESSION_EVENT is similar to V$SYSTEM_EVENT, but displays all waits for each session.

2. Wait event classes in Oracle 10gR2 and its total events:

SELECT wait_class, count(*) FROM V$EVENT_NAME

GROUP BY wait_class ORDER BY 1;

WAIT_CLASS	COUNT(*)
Administrative	46
Application	12
Cluster	47
Commit	1
Concurrency	24
Configuration	23
Idle	62
Network	26
Other	591
Scheduler	2
System I/O	24
User I/O	17

3. For a complete listing of wait events, in alphabetical order, you can issue the following SQL statement:

SELECT name FROM V$EVENT_NAME ORDER BY name;