Release 8.1.5
A67790-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Reference Release 8.1.5 A67790-01 |
|
This chapter describes the dynamic performance views These views are typically referred to as V$ views.
The following topics are included in this chapter:
The Oracle server contains a set of underlying views that are maintained by the server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.
Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. These views can be selected from, but never updated or altered by the user.
The file CATALOG.SQL contains definitions of the views and public synonyms for the dynamic performance views. You must run CATALOG.SQL to create these views and synonyms.
Dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. Database administrators or users should only access the V$ objects, not the V_$ objects.
The dynamic performance views are used by Enterprise Manager and Oracle Trace, which is the primary interface for accessing information about system performance.
Suggestion: Once the instance is started, the V$ views that read from memory are accessible. Views that read data from disk require that the database be mounted.
Warning: Information about the dynamic performance views is presented for completeness only; this information does not imply a commitment to support these views in the future.
In Oracle, there is an additional class of fixed views, the GV$ (Global V$) fixed views. For each of the V$ views described in this chapter (with the exception of V$CACHE_LOCK, V$LOCK_ACTIVITY, V$LOCKS_WITH_COLLISIONS, and V$ROLLNAME), there is a GV$ view. In a parallel server environment, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view possesses an additional column named INST_ID with type integer. The INST_ID column displays the instance number from which the associated V$ view information was obtained. The INST_ID column can be used as a filter to retrieve V$ information from a subset of available instances. For example, the query:
SELECT * FROM GV$LOCK WHERE INST_ID = 2 OR INST_ID = 5
retrieves the information from the V$ views on instances 2 and 5.
The GV$ views can be used to return information on groups of instances defined with the OPS_ADMIN_GROUP parameter. For more information see "OPS_ADMIN_GROUP" and Oracle8i Parallel Server Concepts and Administration.
GV$ views have the following restrictions:
After installation, only username SYS or anyone with SYSDBA ROLE has access to the dynamic performance tables.
For more information, see Oracle Enterprise Manager Administrator's Guide.
This section lists the columns and public synonyms for the dynamic performance views.
This view displays objects in the database that are currently locked and the sessions that are accessing them.
This view maps instance names to instance numbers for all instances that have the database currently mounted.
| Column | Datatype | Description |
|---|---|---|
|
INST_NUMBER |
NUMBER |
The instance number |
|
INST_NAME |
VARCHAR2(60) |
The instance name |
This view describes statistics for the queues in the database.
This view contains information on redo log files in need of archiving. Each row provides information for one thread. This information is also available in V$LOG. Oracle recommends that you use V$LOG. For more information, see "V$LOG".
This view describes, for the current instance, all the archive log destinations, their current value, mode, and status.
For more information on archived log destinations, see "LOG_ARCHIVE_DEST", "LOG_ARCHIVE_DUPLEX_DEST", and "LOG_ARCHIVE_DEST_n" "LOG_ARCHIVE_DEST_STATE_n", "STANDBY_ARCHIVE_DEST", "LOG_ARCHIVE_MIN_SUCCEED_DEST".
This view displays archived log information from the controlfile including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy.
This view provides information about the state of the various ARCH processes for the instance.
This view displays the backup status of all online datafiles.
This view displays backup set information from the controlfile. A backup set record is inserted after the backup set is successfully completed.
This view displays information about corruptions in datafile backups from the controlfile. Note that corruptions are not tolerated in the controlfile and archived log backups.
This view displays backup datafile and backup controlfile information from the controlfile.
This view displays information about supported backup devices. If a device type does not support named devices, then one row with the device type and a null device name is returned for that device type. If a device type supports named devices then one row is returned for each available device of that type. The special device type DISK is not returned by this view because it is always available.
| Column | Datatype | Description |
|---|---|---|
|
DEVICE_TYPE |
VARCHAR2(17) |
Type of the backup device |
|
DEVICE_NAME |
VARCHAR2(512) |
Name of the backup device |
This view displays information about backup pieces from the controlfile. Each backup set consist of one or more backup pieces.
This view displays information about archived logs in backup sets from the controlfile. Note that online redo logs cannot be backed up directly; they must be archived first to disk and then backed up. An archive log backup set can contain one or more archived logs.
This view displays backup set information from the controlfile. A backup set record is inserted after the backup set is successfully completed.
This view displays backup set information from the controlfile. A backup set record is inserted after the backup set is successfully completed.
This view describes the background processes.
This is a Parallel Server view. This view gives the status and number of pings for every buffer in the SGA.
For more information, see Oracle8i Parallel Server Concepts and Administration.
This view displays information about all buffer pools available for the instance. The "sets" pertain to the number of LRU latch sets. For more information, see "DB_BLOCK_LRU_LATCHES".
This view displays information about all buffer pools available for the instance. The "sets" pertain to the number of LRU latch sets. For more information, see "DB_BLOCK_LRU_LATCHES".
This is a Parallel Server view. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects.
| Column | Datatype | Description |
|---|---|---|
|
FILE# |
NUMBER |
Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES) |
|
BLOCK# |
NUMBER |
Block number |
|
CLASS# |
NUMBER |
Class number |
|
STATUS |
VARCHAR2(1) |
Status of block: |
|
XNC |
NUMBER |
Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete but is retained for historical compatibility |
|
FORCED_READS |
NUMBER |
The forced reads |
|
FORCED_WRITES |
NUMBER |
The forced writes |
|
NAME |
VARCHAR2(30) |
Name of the database object containing the block |
|
PARTITION_NAME |
VARCHAR2(30) |
The name of the partition; NULL for non-partitioned objects |
|
KIND |
VARCHAR2(12) |
Type of database object. See Table 3-1 |
|
OWNER# |
NUMBER |
Owner number |
|
LOCK_ELEMENT_ADDR |
RAW(4) |
The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock |
|
LOCK_ELEMENT_ |
NUMBER |
The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock |
For more information, see Oracle8i Parallel Server Concepts and Administration.
| Type Number | KIND Value | Type Number | KIND Value |
|---|---|---|---|
| 1 | INDEX | 11 | PACKAGE BODY |
| 2 | TABLE | 12 | TRIGGER |
| 3 | CLUSTER | 13 | TYPE |
| 4 | VIEW | 14 | TYPE BODY |
| 5 | SYNONYM | 19 | TABLE PARTITION |
| 6 | SEQUENCE | 20 | INDEX PARTITION |
| 7 | PROCEDURE | 21 | LOB |
| 8 | FUNCTION | 22 | LIBRARY |
| 9 | PACKAGE | NULL | UNKNOWN |
| 10 | NON-EXISTENT | ------ | ------- |
This is a Parallel Server view.
| Column | Datatype | Description |
|---|---|---|
|
FILE# |
NUMBER |
Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES) |
|
BLOCK# |
NUMBER |
Block number |
|
STATUS |
VARCHAR2(4) |
Status of block: |
|
XNC |
NUMBER |
Number of parallel cache management (PCM) lock conversions due to contention with another instance |
|
NAME |
VARCHAR2(30) |
Name of the database object containing the block |
|
KIND |
VARCHAR2(12) |
Type of database object. See Table 3-1 |
|
OWNER# |
NUMBER |
Owner number |
|
LOCK_ELEMENT |
RAW(4) |
The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock |
|
LOCK_ELEMENT |
NUMBER |
The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock |
|
FORCED_READS |
NUMBER |
Number of times the block had to be made re-read from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on this block in lock mode |
|
FORCED_WRITES |
NUMBER |
Number of times DBWR had to write this block to disk because this instance had dirtied the block and another instance had requested the PCM lock on the block in conflicting mode |
|
INDX |
NUMBER |
Platform-specific lock manager identifier |
|
CLASS |
NUMBER |
Platform-specific lock manager identifier |
V$CACHE_LOCK is similar to V$CACHE, except for the platform-specific lock manager identifiers. This information may be useful if the platform- specific lock manager provides tools for monitoring the PCM lock operations that are occurring. For example, first query to find the lock element address using INDX and CLASS, then query V$BH to find the buffers that are covered by the lock. See also "V$CACHE".