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".
For more information, see Oracle8i Parallel Server Concepts and Administration.
This view contains information about virtual circuits, which are user connections to the database through dispatchers and servers.
V$CLASS_PING displays the number of blocks pinged per block class. Use this view to compare contentions for blocks in different classes.
This view displays features in use by the database instance that may prevent downgrading to a previous release. This is the dynamic (SGA) version of this information, and may not reflect features that other instances have used, and may include temporary incompatibilities (like UNDO segments) that will not exist after the database is shut down cleanly.
| Column | Datatype | Description |
|---|---|---|
|
TYPE_ID |
VARCHAR2(8 |
Internal feature identifier |
|
RELEASE |
VARCHAR2(60) |
Release in which that feature appeared |
|
DESCRIPTION |
VARCHAR2(64) |
Description of the feature |
This view lists the permanent features in use by the database that will prevent moving back to an earlier release.
This view lists set attributes in the current session.
| Column | Datatype | Description |
|---|---|---|
|
NAMESPACE |
VARCHAR2(30) |
The name of namespace |
|
ATTRIBUTE |
VARCHAR2(30) |
The name of attribute |
|
VALUE |
VARCHAR2(64) |
The value of attribute |
This view lists the names of the control files.
| Column | Datatype | Description |
|---|---|---|
|
STATUS |
VARCHAR2(7) |
INVALID if the name cannot be determined, which should not occur. NULL if the name can be determined |
|
NAME |
VARCHAR2(257) |
The name of the control file |
This view displays information about the controlfile record sections.
This view displays information about datafile copy corruptions from the controlfile.
This view contains database information from the control file.
This view contains datafile information from the control file. See also the "V$DATAFILE_HEADER" view which displays information from datafile headers.
| Column | Datatype | Description |
|---|---|---|
|
FILE# |
NUMBER |
File identification number |
|
STATUS |
VARCHAR2 |
Type of file (system or user) and its status. Values: OFFLINE, ONLINE, SYSTEM, RECOVER, SYSOFF (an offline file from the SYSTEM tablespace) |
|
ENABLED |
VARCHAR2(10) |
Describes how accessible the file is from SQL. It is one of the values in Table 3-1 |
|
CHECKPOINT |
NUMBER |
SCN at last checkpoint |
|
CHECKPOINT_TIME |
DATE |
Time stamp of the checkpoint# |
|
UNRECOVERABLE _CHANGE# |
NUMBER |
Last unrecoverable change# made to this datafile. This column is always updated when an unrecoverable operation completes |
|
UNRECOVERABLE |
DATE |
Time stamp of the last unrecoverable change |
|
BYTES |
NUMBER |
Current size in bytes; 0 if inaccessible |
|
CREATE_BYTES |
NUMBER |
Size when created, in bytes |
|
NAME |
VARCHAR2 |
Name of the file |
|
CREATION_CHANGE# |
NUMBER |
Change number at which the datafile was created |
|
CREATION_TIME |
DATE |
Timestamp of the datafile creation |
|
TS# |
NUMBER |
Tablespace number |
|
RFILE# |
NUMBER |
Tablespace relative datafile number |
|
LAST_CHANGE# |
NUMBER |
Last change# made to this datafile. Set to NULL if the datafile is being changed |
|
LAST_TIME |
DATE |
Timestamp of the last change |
|
OFFLINE_CHANGE# |
NUMBER |
Offline change# of the last offline range. This column is updated only when the datafile is brought online |
|
ONLINE_CHANGE# |
NUMBER |
Online change# of the last offline range |
|
ONLINE_TIME |
DATE |
Online timestamp of the last offline range |
|
BLOCKS |
NUMBER |
Current datafile size in blocks; 0 if inaccessible |
|
BLOCK_SIZE |
NUMBER |
Block size of the datafile |
|
NAME |
VARCHAR2(512) |
Datafile name |
|
PLUGGED_IN |
NUMBER |
Describes whether the tablespace is plugged in. The value is 1 if the tablespace is plugged in and has not been made read-write, 0 if not. |
Table 3-2 describes values that can be entered in the ENABLED column.
| ENABLED Column Value | Description |
|---|---|
|
DISABLED |
No SQL access allowed |
|
READ ONLY |
No SQL updates allowed |
|
READ WRITE |
Full access allowed |
|
UNKNOWN |
Should not occur unless the control file is corrupted |
This view displays datafile copy information from the controlfile.
This view displays datafile information from the datafile headers.
This view lists all datafiles making up the database. This view is retained for historical compatibility. Use of V$DATAFILE is recommended instead. For more information, see "V$DATAFILE".
| Column | Datatype | Description |
|---|---|---|
|
FILE# |
NUMBER |
File identifier |
|
NAME |
VARCHAR2 |
Name of file |
This view describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. These database links must be committed or rolled back before being closed.
This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.
| Column | Datatype | Description |
|---|---|---|
|
OWNER |
VARCHAR2 |
Owner of the object |
|
NAME |
VARCHAR2 |
Name of the object |
|
DB_LINK |
VARCHAR2 |
Database link name, if any |
|
NAMESPACE |
VARCHAR2 |
Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT |
|
TYPE |
VARCHAR2 |
Type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK |
|
SHARABLE_MEM |
NUMBER |
Amount of sharable memory in the shared pool consumed by the object |
|
LOADS |
NUMBER |
Number of times the object has been loaded. This count also increases when an object has been invalidated |
|
EXECUTIONS |
NUMBER |
Not used. To see actual execution counts, see "V$SQLAREA". |
|
LOCKS |
NUMBER |
Number of users currently locking this object |
|
PINS |
NUMBER |
Number of users currently pinning this object |
|
KEPT |
VARCHAR2(3) |
YES or NO, depending on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP |
This view displays the pipes that are currently in this database.
This view displays information about deleted archived logs, datafile copies and backup pieces from the controlfile. The only purpose of this view is to optimize the recovery catalog resync operation. When an archived log, datafile copy, or backup piece is deleted, the corresponding record is marked deleted.
This view provides information on the dispatcher processes.
This view provides rate statistics for the dispatcher processes.
This is a Parallel Server view. V$DLM_ALL_LOCKS lists information of all locks currently known to lock manager that are being blocked or blocking others.
V$DLM_CONVERT_LOCAL displays the elapsed time for the local lock conversion operation.
V$DLM_CONVERT_REMOTE displays the elapsed time for the remote lock conversion operation.
| Conversion Type | Description |
|---|---|
| NULL -> SS | NULL mode to sub shared mode |
| NULL -> SX | NULL mode to shared exclusive mode |
| NULL -> S | NULL mode to shared mode |
| NULL -> SSX | NULL mode to sub-shared exclusive mode |
| NULL -> X | NULL mode to exclusive mode |
| SS -> SX | sub shared mode to shared exclusive mode |
| SS -> S | sub shared mode to shared mode |
| SS -> SSX | sub shared mode to sub-shared exclusive mode |
| SS -> X | sub shared mode to exclusive mode |
| SX -> S | shared exclusive mode to shared mode |
| SX -> SSX | shared exclusive mode to sub-shared exclusive mode |
| SX -> X | shared exclusive mode to exclusive mode |
| S -> SX | shared mode to shared exclusive mode |
| S -> SSX | shared mode to sub-shared exclusive mode |
| S -> X | shared mode to exclusive mode |
| SSX -> X | sub-shared exclusive mode to exclusive mode |
V$DLM_LATCH is obsolete. See "V$LATCH" for statistics about DLM latch performance.
This is a Parallel Server view. V$DLM_LOCKS lists information of all locks currently known to lock manager that are being blocked or blocking others.
V$DLM_MISC displays miscellaneous DLM statistics.
| Column | Datatype | Description |
|---|---|---|
|
STATISTIC# |
NUMBER |
Statistic number |
|
NAME |
VARCHAR2(64) |
Name of the statistic |
|
VALUE |
NUMBER |
Value associated with the statistic |
V$DLM_RESS is a Parallel Server view. It displays information of all resources currently known to the lock manager.
This view displays which privileges are enabled. These privileges can be found in the table SYS.SYSTEM_PRIVILEGES_MAP.
| Column | Datatype | Description |
|---|---|---|
|
PRIV_NUMBER |
NUMBER |
Numeric identifier of enabled privileges |
This view displays all locks owned by enqueue state objects. The columns in this view are identical to the columns in V$LOCK. For more information, see "V$LOCK".
This view contains information about wait events.
This view displays information on parallel execution.
V$FALSE_PING is a Parallel Server view. This view displays buffers that may be getting false pings. That is, buffers pinged more than 10 times that are protected by the same lock as another buffer that pinged more than 10 times. Buffers identified as getting false pings can be remapped in "GC_FILES_TO_LOCKS" to reduce lock collisions.
| Column | Datatype | Description |
|---|---|---|
|
FILE# |
NUMBER |
Datafile identifier number (to find filename, query DBA_DATA_FILES or V$DBFILES) |
|
BLOCK# |
NUMBER |
Block number |
|
STATUS |
VARCHAR2(1) |
Status of block: |
|
XNC |
NUMBER |
Number of PCM lock conversions from Exclusive mode due to contention with another instance. This column is obsolete but is retained for historical compatibility |
|
FORCED_READS |
NUMBER |
Number of times the block had to be reread from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on the block in exclusive mode |
|
FORCED_WRITES |
NUMBER |
Number of times DBWR had to write this block to disk because this instance had used the block and another instance had requested the lock on the block in a conflicting mode |
|
NAME |
VARCHAR2(30) |
Name of the database object containing the block |
|
PARTITION_NAME |
VARCHAR2 |
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_NAME |
NUMBER |
The name of the lock that contains the PCM lock that is covering the buffer |
|
LOCK_ELEMENT_CLASS |
NUMBER |
The lock element class |
For more information, see "GC_FILES_TO_LOCKS" and also Oracle8i Parallel Server Concepts and Administration.
V$FAST_START_SERVERS provides information about all the recovery slaves performing parallel transaction recovery.
| Column | Datatype | Description |
|---|---|---|
|
STATE |
VARCHAR2(11) |
State of the server; IDLE or RECOVERING |
|
UNDOBLOCKSDONE |
NUMBER |
The percentage of the assigned work done so far |
|
PID |
NUMBER |
The process ID |
For further information, see Oracle8i Backup and Recovery Guide.
V$FAST_START_TRANSACTIONS contains information about the progress of the transactions that Oracle is recovering.
For further information, see Oracle8i Backup and Recovery Guide.
The view V$FILE_PING displays the number of blocks pinged per datafile. This information in turn can be used to determine access patterns to existing datafiles and deciding new mappings from datafile blocks to PCM locks.
This view contains information about file read/write statistics.
This view displays all dynamic performance tables, views, and derived tables in the database. Some V$tables (for example, V$ROLLNAME) refer to real tables and are therefore not listed.
This view contains the definitions of all the fixed views (views beginning with V$). Use this table with caution. Oracle tries to keep the behavior of fixed views the same from release to release, but the definitions of the fixed views can change without notice. Use these definitions to optimize your queries by using indexed columns of the dynamic performance tables.
| Column | Datatype | Description |
|---|---|---|
|
VIEW_NAME |
VARCHAR2(30) |
The name of the fixed view |
|
VIEW_DEFINITION |
VARCHAR2(2000) |
The definition of the fixed view |
This view displays global blocked locks.
This view displays information on the currently active global transactions.
This view identifies the set of HS agents currently running on a given host, using one row per agent process.
This view identifies the set of HS sessions currently open for the Oracle Server.
This view displays the columns in dynamic performance tables that are indexed (X$ tables). The X$ tables can change without notice. Use this view only to write queries against fixed views (V$ views) more efficiently.
This view displays the state of the current instance. This version of V$INSTANCE is not compatible with earlier versions of V$INSTANCE.
This view is used to monitor the mechanisms that implement the user-specifiable limit on recovery reads.
This view lists statistics for non-parent latches and summary statistics for parent latches. That is, the statistics for a parent latch include counts from each of its children.
Note: Columns SLEEP5, SLEEP6,... SLEEP11 are present for compatibility with previous versions of Oracle. No data are accumulated for these columns.
This view contains information about the current latch holders.
This view contains information about decoded latch names for the latches shown in V$LATCH. The rows of V$LATCHNAME have a one-to-one correspondence to the rows of V$LATCH. For more information, see "V$LATCH".
| Column | Datatype | Description |
|---|---|---|
|
LATCH# |
NUMBER |
Latch number |
|
NAME |
VARCHAR2(64) |
Latch name |
This view contains statistics about child latches. This view includes all columns of V$LATCH plus the CHILD# column. Note that child latches have the same parent if their LATCH# columns match each other. For more information, see "V$LATCH".
This view contains statistics about missed attempts to acquire a latch.
This view contains statistics about the parent latch. The columns of V$LATCH_PARENT are identical to those in V$LATCH. For more information, see "V$LATCH".
This view contains statistics about library cache performance and activity.
This view contains information about license limits.
This view contains SQL*Loader statistics compiled during the execution of a direct load. These statistics apply to the whole load. Any SELECT against this table results in "no rows returned" since you cannot load data and do a query at the same time.
SQL*Loader statistics compiled during the execution of a direct load. These statistics apply to the current table. Any SELECT against this table results in "no rows returned" since you cannot load data and do a query at the same time.
This view lists the locks currently held by the Oracle server and outstanding requests for a lock or latch.
| Column | Datatype | Description |
|---|---|---|
|
ADDR |
RAW(4) |
Address of lock state object |
|
KADDR |
RAW(4) |
Address of lock |
|
SID |
NUMBER |
Identifier for session holding or acquiring the lock |
|
TYPE |
VARCHAR2(2) |
Type of lock. For a list of user and system types that can have locks, see Table 3-4, "Values for the TYPE column: User Types" and Table 3-5, "Values for the TYPE column: System Types" |
|
ID1 |
NUMBER |
Lock identifier #1 (depends on type) |
|
ID2 |
NUMBER |
Lock identifier #2 (depends on type) |
|
LMODE |
NUMBER |
Lock mode in which the session holds the lock: |
|
REQUEST |
NUMBER |
Lock mode in which the process requests the lock: |
|
CTIME |
NUMBER |
Time since current mode was granted |
|
BLOCK |
NUMBER |
The lock is blocking another lock |
The locks on the user types in Table 3-4 are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks.
| User Type | Description |
|---|---|
|
TM |
DML enqueue |
|
TX |
Transaction enqueue |
|
UL |
User supplied |
The locks on the system types in Table 3-5 are held for extremely short periods of time.
This is a Parallel Server view. V$LOCK_ACTIVITY displays the DLM lock operation activity of the current instance. Each row corresponds to a type of lock operation.
For more information, see Oracle8i Parallel Server Concepts and Administration.
This is a Parallel Server view. There is one entry in v$LOCK_ELEMENT for each PCM lock that is used by the buffer cache. The name of the PCM lock that corresponds to a lock element is {'BL', indx, class}.
For more information, see Oracle8i Parallel Server Concepts and Administration.
This view lists all locks acquired by every transaction on the system.
This is a Parallel Server view. Use this view to find the locks that protect multiple buffers, each of which has been either force-written or force-read at least 10 times. It is very likely that those buffers are experiencing false pings due to being mapped to the same lock.
For more information, see Oracle8i Parallel Server Concepts and Administration.
This view contains log file information from the control files.
| Column | Datatype | Description |
|---|---|---|
|
GROUP# |
NUMBER |
Log group number |
|
THREAD# |
NUMBER |
Log thread number |
|
SEQUENCE# |
NUMBER |
Log sequence number |
|
BYTES |
NUMBER |
Size of the log in bytes |
|
MEMBERS |
NUMBER |
Number of members in the log group |
|
ARCHIVED |
VARCHAR2 |
Archive status: YES, NO |
|
STATUS |
VARCHAR2(16) |
Log status. The STATUS column can have the values in Table 3-6. |
|
FIRST_CHANGE# |
NUMBER |
Lowest SCN in the log |
|
FIRST_TIME |
DATE |
Time of first SCN in the log |
Table 3-6 describes values in the log STATUS column.
This view contains information about redo log files.
This view contains log history information from the control file. This view is retained for historical compatibility. Use of V$LOG_HISTORY is recommended instead. For more information, see "V$LOG_HISTORY".
This view contains log history information.
This view contains log history information.
This view contains log information.
This view contains log information.
This view contains log history information from the control file.
This is a Trusted Oracle Server view that lists Trusted Oracle Server-specific initialization parameters. For more information, see your Trusted Oracle documentation.
This view contains information for tuning the multi-threaded server.
| Column | Datatype | Description |
|---|---|---|
|
MAXIMUM |
NUMBER |
The maximum number of connections each dispatcher can support. This value is determined at startup time using Net8 constants and other port-specific information, or can be lowered using the MTS_DISPATCHERS parameter |
|
SERVERS |
NUMBER |
The total number of multi-threaded servers started since the instance started (but not including those started during startup) |
|
SERVERS |
NUMBER |
The total number of multi-threaded servers stopped by Oracle since the instance started |
|
SERVERS |
NUMBER |
The highest number of servers running at one time since the instance started. If this value reaches the value set for the MTS_MAX_SERVERS initialization parameter, consider raising the value of MTS_SERVERS. For more information, see "MTS_SERVERS" |
This view contains statistics on the current session.
| Column | Datatype | Description |
|---|---|---|
|
SID |
NUMBER |
The ID of the current session |
|
STATISTIC# |
NUMBER |
The number of the statistic |
|
VALUE |
NUMBER |
The value of the statistic |
This view contains current values of NLS parameters.
This view lists all valid values for NLS parameters.
| Column | Datatype | Description |
|---|---|---|
|
PARAMETER |
VARCHAR2(64) |
Parameter name: |
|
VALUE |
VARCHAR2(64) |
NLS parameter value |
This view can be used to determine what objects are depended on by a package, procedure, or cursor that is currently loaded in the shared pool. For example, together with V$SESSION and V$SQL, it can be used to determine which tables are used in the SQL statement that a user is currently executing. For more information, see "V$SESSION" and "V$SQL".
This view lists obsolete parameters. If any value is true, you should examine why.
| Column | Datatype | Description |
|---|---|---|
|
NAME |
VARCHAR2(64) |
The name of the parameter |
|
ISSPECIFIED |
VARCHAR2(5) |
Whether the parameter was specified in the config file |
This view displays datafile offline information from the controlfile. Note that the last offline range of each datafile is kept in the DATAFILE record. For more information, see "V$DATAFILE".
An offline range is created for a datafile when its tablespace is first ALTERed to be OFFLINE NORMAL or READ ONLY, and then subsequently ALTERed to be ONLINE or read-write. Note that no offline range is created if the datafile itself is ALTERed to be OFFLINE or if the tablespace is ALTERed to be OFFLINE IMMEDIATE.
This view lists cursors that each user session currently has opened and parsed.
This view lists options that are installed with the Oracle Server.
| Column | Datatype | Description |
|---|---|---|
|
PARAMETER |
VARCHAR2(64) |
The name of the option |
|
VALUE |
VARCHAR2(64) |
TRUE if the option is installed |
This view displays all available parallel degree limit resource allocation methods.
| Column | Datatype | Description |
|---|---|---|
|
NAME |
VARCHAR2(40) |
The name of the parallel degree limit resource allocation method |
This view lists information about initialization parameters.
This is a Parallel Server view. The V$PING view is identical to the V$CACHE view but only displays blocks that have been pinged at least once. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects. For more information, see "V$CACHE".
| Column | Datatype | Description |
|---|---|---|
|
FILE# |
NUMBER |
Datafile identifier number (to find filename, query "DBA_DATA_FILES" or "V$DBFILE") |
|
BLOCK# |
NUMBER |
Block number |
|
CLASS# |
NUMBER |
Class number |
|
STATUS |
VARCHAR2(4) |
Status of block: |
|
XNC |
NUMBER |
Number of PCM lock conversions due to contention with another instance. This column is obsolete but is retained for historical compatibility |
|
FORCED_READS |
NUMBER |
Number of times the block had to be reread from disk because another instance had forced it out of this instance's cache by requesting the PCM lock on the block in exclusive mode |
|
FORCED_WRITES |
NUMBER |
Number of times DBWR had to write this block to disk because this instance had used the block and another instance had requested the lock on the block in a conflicting mode |
|
NAME |
VARCHAR2(30) |
Name of the database object containing the block |
|
PARTITION_NAME |
VARCHAR2(30) |
NULL for non-partitioned objects |
|
KIND |
VARCHAR2(15) |
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 name of the lock that contains the PCM lock that is covering the buffer |
For more information, see Oracle8i Parallel Server Concepts and Administration.
This view lists session statistics for parallel queries.
Note: This view will be obsoleted in a future release.
| Column | Datatype | Description |
|---|---|---|
|
STATISTIC |
VARCHAR2(30) |
Name of the statistic. See Table 3-7 |
|
LAST_QUERY |
NUMBER |
The value of the statistic for the last operation |
|
SESSION_TOTAL |
NUMBER |
The value of the statistic for the entire session to this point in time |
The statistics (fixed rows) in Table 3-7 have been defined for this view. After you have run a query or DML operation, you can use the information derived from V$PQ_SESSTAT to view the number of slave processes used, and other information for the session and system.
This view lists statistics for each of the active parallel execution servers on an instance.
Note: This view will be replaced/obsoleted in a future release by a new view called V$PX_PROCESS.
This view lists system statistics for parallel queries.
Note: This view will be replaced/obsoleted in a future release by a new view called V$PX_PROCESS_SYSSTAT.
| Column | Datatype | Description |
|---|---|---|
|
STATISTIC |
VARCHAR2(30) |
Name of the statistic. See Table 3-8 |
|
VALUE |
NUMBER |
The value of the statistic |
The statistics (fixed rows) in Table 3-8 have been defined for this view. After you have run a query or DML operation, you can use the information derived from V$PQ_SYSSTAT to view the number of slave processes used, and other information for the system.
This view contains statistics on parallel execution operations. The statistics are compiled after the query completes and only remain for the duration of the session. It displays the number of rows processed through each parallel execution server at each stage of the execution tree. This view can help determine skew problems in a query's execution.
Note: This view will be renamed V$PX_TQSTAT in a future release.
This view contains information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.
This view contains descriptions of archived log backups which are taken with a new feature called Proxy Copy. Each row represents a backup of one archived log.
This view contains descriptions of datafile and controlfile backups which are taken with a new feature called Proxy Copy. Each row represents a backup of one database file.
This view lists users who have been granted SYSDBA and SYSOPER privileges as derived from the password file.
This view contains information about the sessions running parallel execution.
This view contains information about the sessions running parallel execution.
| Column | Datatype | Description |
|---|---|---|
|
STATISTIC |
VARCHAR2(30) |
The name of the statistic |
|
VALUE |
NUMBER |
The value of the statistic |
The STATISTIC column's values are:
This view contains information about the sessions running parallel execution.
This view contains information about the sessions running parallel execution.
This view contains information on the multi-thread message queues.
This view displays the status of files needing media recovery.
V$RECOVERY_FILE_STATUS contains one row for each datafile for each RECOVER command. This view contains useful information only for the Oracle process doing the recovery. When Recovery Manager directs a server process to perform recovery, only Recovery Manager is able to view the relevant information in this view. V$RECOVERY_FILE_STATUS will be empty to all other Oracle users.
For further information, see Oracle8i Backup and Recovery Guide.
This view lists information about archived logs that are needed to complete media recovery. This information is derived from the log history view, V$LOG_HISTORY. For more information, see "V$LOG_HISTORY".
V$RECOVERY_LOG contains useful information only for the Oracle process doing the recovery. When Recovery Manager directs a server process to perform recovery, only Recovery Manager is able to view the relevant information in this view. V$RECOVERY_LOG will be empty to all other Oracle users.
| Column | Datatype | Description |
|---|---|---|
|
THREAD# |
NUMBER |
Thread number of the archived log |
|
SEQUENCE# |
NUMBER |
Sequence number of the archived log |
|
TIME |
VARCHAR2 |
Time of first entry (lowest SCN) in the log |
|
ARCHIVE_NAME |
VARCHAR2 |
Name of the file when archived, using the naming convention specified by "LOG_ARCHIVE_FORMAT" |
For further information, see Oracle8i Backup and Recovery Guide.
V$RECOVERY_PROGRESS can be used to track database recovery operations to ensure that they are not stalled, and also to estimate the time required to complete the operation in progress.
V$RECOVERY_PROGRESS is a subview of V$SESSION_LONGOPS.
For further information, see Oracle8i Backup and Recovery Guide.
V$RECOVERY_STATUS contains statistics of the current recovery process. This view contains useful information only for the Oracle process doing the recovery. When Recovery Manager directs a server process to perform recovery, only Recovery Manager is able to view the relevant information in this view. V$RECOVERY_STATUS will be empty to all other Oracle users.
For further information, see Oracle8i Backup and Recovery Guide.
This view lists statistics for the histogram of MTS dispatcher request times, divided into 12 buckets, or ranges of time. The time ranges grow exponentially as a function of the bucket number.
This view gives a list of all the keywords that are used by the PL/SQL compiler. This view helps developers to determine whether a word is already being used as a keyword in the language.
| Column | Datatype | Description |
|---|---|---|
|
KEYWORD |
VARCHAR2(64) |
The name of the keyword |
|
LENGTH |
NUMBER |
The length of the keyword |
This view contains resource name and address information.
| Column | Datatype | Description |
|---|---|---|
|
ADDR |
RAW(4) |
Address of resource object |
|
TYPE |
VARCHAR2 |
Resource type. The resource types are listed in Table 3-3. |
|
ID1 |
NUMBER |
Resource identifier #1 |
|
ID2 |
NUMBER |
Resource identifier #2 |
This view displays information about global resource use for some of the system resources. Use this view to monitor the consumption of resources so that you can take corrective action, if necessary. Many of the resources correspond to initialization parameters listed in Table 3-10.
Some resources, those used by DLM for example, have an initial allocation (soft limit), and the hard limit, which is theoretically infinite (although in practice it is limited by SGA size). During SGA reservation/initialization, a place is reserved in SGA for the INITIAL_ALLOCATION of resources, but if this allocation is exceeded, additional resources are allocated up to the value indicated by LIMIT_VALUE. The CURRENT_UTILIZATION column indicates whether the initial allocation has been exceeded. When the initial allocation value is exceeded, the additional required resources are allocated from the shared pool, where they must compete for space with other resources.
A good choice for the value of INITIAL_ALLOCATION will avoid the contention for space. For most resources, the value for INITIAL_ALLOCATION is the same as the LIMIT_VALUE. Exceeding LIMIT_VALUE results in an error.
| Column | Datatype | Description |
|---|---|---|
|
RESOURCE_NAME |
VARCHAR2(30) |
Name of the resource (see Table 3-10) |
|
CURRENT_UTILIZATION |
NUMBER |
Number of (resources, locks, or processes) currently being used |
|
MAX_UTILIZATION |
NUMBER |
Maximum consumption of this resource since the last instance start-up |
|
INITIAL_ALLOCATION |
VARCHAR2(10) |
Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file. (UNLIMITED for infinite allocation) |
|
LIMIT_VALUE |
VARCHAR2(10) |
Unlimited for resources and locks. This can be greater than the initial allocation value. (UNLIMITED for infinite limit) |
| Resource Name | Corresponds to this Initialization Parameter |
|---|---|
| DISTRIBUTED_TRANSACTIONS | DISTRIBUTED_TRANSACTIONS For more information on this parameter, see "DISTRIBUTED_TRANSACTIONS". |
| DML_LOCKS | DML_LOCKS For more information on this parameter see "DML_LOCKS". |
| ENQUEUE_LOCKS | This value is computed by Oracle. Use the V$ENQUEUE_LOCK view (described on ) to obtain more information about the enqueue locks. |
| ENQUEUE_RESOURCES | ENQUEUE_RESOURCES For more information on this parameter see "ENQUEUE_RESOURCES". |
| LM_PROCESSES | LM_PROCS For more information on this parameter see "LM_PROCS". |
| LM_RESOURCES | LM_RESS For more information on this parameter see "LM_RESS". |
| LM_LOCKS | LM_LOCKS For more information on this parameter see "LM_LOCKS". |
| MTS_MAX_SERVERS | MTS_MAX_SERVERS For more information on this parameter see "MTS_MAX_SERVERS". |
| PARALLEL_SLAVES | PARALLEL_MAX_SERVERS For more information on this parameter, see "PARALLEL_MAX_SERVERS". |
| PROCESSES | PROCESSES For more information on this parameter, see "PROCESSES". |
| ROLLBACK_SEGMENTS | MAX_ROLLBACK_SEGMENTS For more information on this parameter, see "MAX_ROLLBACK_SEGMENTS". |
| SESSIONS | SESSIONS For more information on this parameter, see "SESSIONS" |
| SORT_SEGMENT_LOCKS | This value is computed by Oracle |
| TEMPORARY_LOCKS | This value is computed by Oracle |
| TRANSACTIONS | TRANSACTIONS For more information on this parameter, see "TRANSACTIONS" |
This view lists the names of all online rollback segments. It can only be accessed when the database is open.
| Column | Datatype | Description |
|---|---|---|
|
USN |
NUMBER |
Rollback (undo) segment number |
|
NAME |
VARCHAR2 |
Rollback segment name |
This view contains rollback segment statistics.
This view displays statistics for data dictionary activity. Each row contains statistics for one data dictionary cache.
This view displays information for parent objects in the data dictionary. There is one row per lock owner, and one waiter for each object. This row shows the mode held or requested. For objects with no owners or waiters, a single row is displayed.
This view displays information for subordinate objects in the data dictionary.
This view displays data related to the currently active resource consumer groups.
This view shows all available resource allocation methods for resource consumer groups.
| Column | Datatype | Description |
|---|---|---|
|
NAME |
VARCHAR2(40) |
The name of the CPU resource allocation method |
This view displays the names of all currently active resource plans.
| Column | Datatype | Description |
|---|---|---|
|
NAME |
VARCHAR2(32) |
The name of the resource plan |
This view shows all available CPU resource allocation methods for resource plans.
| Column | Datatype | Description |
|---|---|---|
|
NAME |
VARCHAR2(32) |
The name of the resource allocation method |
This view lists session information for each current session.
| Column | Datatype | Description |
|---|---|---|
|
SADDR |
RAW(4) |
Session address |
|
SID |
NUMBER |
Session identifier |
|
SERIAL# |
NUMBER |
Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID |
|
AUDSID |
NUMBER |
Auditing session ID |
|
PADDR |
RAW(4) |
Address of the process that owns this session |
|
USER# |
NUMBER |
Oracle user identifier |
|
USERNAME |
VARCHAR2(30) |
Oracle username |
|
COMMAND |
NUMBER |
Command in progress (last statement parsed); for a list of values, see Table 3-7 |
|
OWNERID |
NUMBER |
The column contents are invalid if the value is 2147483644. Otherwise, this column contains the identifier of the user who owns the migratable session For operations using Parallel Slaves, interpret this value as a 4Byte value. The low-order 2Bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator |
|
TADDR |
VARCHAR2(8) |
Address of transaction state object |
|
LOCKWAIT |
VARCHAR2(8) |
Address of lock waiting for; NULL if none |
|
STATUS |
VARCHAR2(8) |
Status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily cached for use by Oracle*XA), SNIPED (session inactive, waiting on the client) |
|
SERVER |
VARCHAR2(9) |
Server type: DEDICATED, SHARED, PSEUDO, NONE |
|
SCHEMA# |
NUMBER |
Schema user identifier |
|
SCHEMANAME |
VARCHAR2(30) |
Schema user name |
|
OSUSER |
VARCHAR2(15) |
Operating system client user name |
|
PROCESS |
VARCHAR2(9) |
Operating system client process ID |
|
MACHINE |
VARCHAR2(64) |
Operating system machine name |
|
TERMINAL |
VARCHAR2(10) |
Operating system terminal name |
|
PROGRAM |
VARCHAR2(48) |
Operating system program name |
|
TYPE |
VARCHAR2(10) |
Session type |
|
SQL_ADDRESS |
RAW(4) |
Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed |
|
SQL_HASH |
NUMBER |
Used with SQL_ADDRESS to identify the SQL statement that is currently being executed |
|
MODULE |
VARCHAR2(48) |
Contains the name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure |
|
MODULE_HASH |
NUMBER |
The hash value of the above MODULE |
|
ACTION |
VARCHAR2(32) |
Contains the name of the currently executing action as set by calling the |
|
ACTION_HASH |
NUMBER |
The hash value of the above action name |
|
CLIENT_INFO |
VARCHAR2(64) |
Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure |
|
FIXED_TABLE |
NUMBER |
This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database |
|
ROW_WAIT_OBJ# |
NUMBER |
Object ID for the table containing the ROWID specified in ROW_WAIT_ROW# |
|
ROW_WAIT_FILE# |
NUMBER |
Identifier for the datafile containing the ROWID specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1 |
|
ROW_WAIT |
NUMBER |
Identifier for the block containing the ROWID specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1 |
|
ROW_WAIT_ROW# |
NUMBER |
The current ROWID being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1 |
|
LOGON_TIME |
DATE |
Time of logon |
|
LAST_CALL_ET |
NUMBER |
The last call |
|
PDML_STATUS |
VARCHAR2(8) |
If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED, PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML. |
|
PDDL_STATUS |
VARCHAR2(8) |
If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED, PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL. |
|
PDML_ENABLED |
VARCHAR2(3) |
This column has been replaced by PDML_ENABLED and PDML_STATUS. See above. |
|
FAILOVER_TYPE |
VARCHAR2(10) |
NONE if failover is disabled for this session, SESSION if client is able to failover its session following a disconnect, and SELECT if client is able to fail over selects in progress as well |
|
FAILOVER |
VARCHAR2(3) |
NONE if failover is disabled for this session, BASIC if client reconnects following a disconnect, PRECONNECT if the backup instance is able to support all connections from every instance that it is backup for |
|
FAILED_OVER |
VARCHAR2(13) |
TRUE if running in failover mode and have failed over, otherwise FALSE |
|
RESOURCE_CONSUMER_GROUP |
VARCHAR2(32) |
Name of the session's current resource consumer group |
Table 3-11 lists numeric values corresponding to commands that may be in progress during a session. These values can appear in the V$SESSION COMMAND column. They also appear in the data dictionary view SYS.AUDIT_ACTIONS.
This view displays information about network connections for the current session.
This view displays information on cursor usage for the current session. Note: the V$SESSION_CURSOR_CACHE view is not a measure of the effectiveness of the SESSION_CACHED_CURSORS initialization parameter.
This view lists information on waits for an event by a session. Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you want this column to reflect true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file. Please remember that doing this will have a small negative effect on system performance. For more information, see "TIMED_STATISTICS".
| Column | Datatype | Description |
|---|---|---|
|
SID |
NUMBER |
The ID of the session |
|
EVENT |
VARCHAR2(64) |
The name of the wait event. For more information, see Appendix A, "Oracle Wait Events" |
|
TOTAL_WAITS |
NUMBER |
The total number of waits for this event by this session |
|
TOTAL_TIMEOUTS |
NUMBER |
The total number of timeouts for this event by this session |
|
TIME_WAITED |
NUMBER |
The total amount of time waited for this event by this session, in hundredths of a second |
|
AVERAGE_WAIT |
NUMBER |
The average amount of time waited for this event by this session, in hundredths of a second |
|
MAX_WAIT |
NUMBER |
The maximum time (in hundredths of a second) waited for this event by this session |
This view displays the status of certain long-running operations. It provides progression reports on operations using the columns SOFAR and TOTALWORK. For example, the operational status for the following components can be monitored:
This view displays object cache statistics for the current user session on the local server (instance).
This view lists the resources or events for which active sessions are waiting.
The following are tuning considerations:
In previous releases the WAIT_TIME column contained an arbitrarily large value instead of a negative value to indicate the platform did not have a fast timing mechanism.
For more information on session waits, Appendix A, "Oracle Wait Events".
| Column | Datatype | Description |
|---|---|---|
|
SID |
NUMBER |
Session identifier |
|
SEQ# |
NUMBER |
Sequence number that uniquely identifies this wait. Incremented for each wait. |
|
EVENT |
VARCHAR2(64) |
Resource or event for which the session is waiting. For more information, see Appendix A, "Oracle Wait Events" |
|
P1TEXT |
VARCHAR2 |
Description of first additional parameter |
|
P1 |
NUMBER |
First additional parameter |
|
P1RAW |
RAW(4) |
First additional parameter |
|
P2TEXT |
VARCHAR2 |
Description of second parameter |
|
P2 |
NUMBER |
Second additional parameter |
|
P2RAW |
RAW(4) |
Second additional parameter |
|
P3TEXT |
VARCHAR2 |
Description of third parameter |
|
P3 |
NUMBER |
Third additional parameter |
|
P3RAW |
RAW(4) |
Third additional parameter |
|
WAIT_TIME |
NUMBER |
A non-zero value is the session's last wait time. A zero value means the session is currently waiting |
|
SECONDS_IN |
NUMBER |
The seconds in wait |
|
STATE |
VARCHAR2 |
Wait state (see Table 3-12) |
Table 3-12 defines values in the V$SESSION_WAIT STATE column.
This view lists user session statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), see "V$STATNAME".
| Column | Datatype | Description |
|---|---|---|
|
SID |
NUMBER |
Session identifier |
|
STATISTIC# |
NUMBER |
Statistic number (identifier) |
|
VALUE |
NUMBER |
Statistic value |
This view lists I/O statistics for each user session.
This view contains summary information on the System Global Area.
| Column | Datatype | Description |
|---|---|---|
|
NAME |
VARCHAR2 |
SGA component group |
|
VALUE |
NUMBER |
Memory size in bytes |
This view contains detailed information on the System Global Area.
This fixed view lists statistics that help you tune the reserved pool and space within the shared pool.
The following columns of V$SHARED_POOL_RESERVED are valid only if the initialization parameter shared_pool_reserved_size is set to a valid value. For more information, see "SHARED_POOL_RESERVED_SIZE".
The following columns of V$SHARED_POOL_RESERVED contain values which are valid even if shared_pool_reserved_size is not set.
This view contains information on the shared server processes.
This view contains information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.
This view describes sort usage.
This view lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.
This view displays the actual bind data sent by the client for each distinct bind variable in each cursor owned by the session querying this view if the data is available in the server.
This view displays bind metadata provided by the client for each distinct bind variable in each cursor owned by the session querying this view.
This view displays debugging information for each cursor associated with the session querying this view.
This view displays information about the cursor shared memory snapshot. Each SQL statement stored in the shared pool has one or more child objects associated with it. Each child object has a number of parts, one of which is the context heap, which holds, among other things, the query plan.
This view lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.
This view is identical to the V$SQLTEXT view except that, to improve legibility, V$SQLTEXT_WITH_NEWLINES does not replace newlines and tabs in the SQL statement with spaces. For more information, see "V$SQLTEXT".
This view displays decoded statistic names for the statistics shown in the V$SESSTAT and V$SYSSTAT tables. For more information, see "V$SESSTAT" and "V$SYSSTAT".
| Column | Datatype | Description |
|---|---|---|
|
STATISTIC# |
NUMBER |
Statistic number |
|
NAME |
VARCHAR2 |
Statistic name. See also Table 3-13 |
|
CLASS |
NUMBER |
Statistic class: |
Table 3-13 lists the generic Oracle Server statistics returned by V$STATNAME. For a complete description of each statistic, see Appendix C, "Statistics Descriptions".
Additional Information: On some platforms, the NAME and CLASS columns will contain additional operating system-specific statistics. See your operating system-specific Oracle documentation for more information about these statistics.
This view displays information about the subordinate caches currently loaded into library cache memory. The view walks through the library cache, printing out a row for each loaded subordinate cache per library cache object.
This view lists system statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), see "V$STATNAME".
This view displays similar information to the V$SESSION_CURSOR_CACHE view except that this information is system wide. For more information, see "V$SESSION_CURSOR_CACHE".
This view contains information on total waits for an event. Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you want this column to reflect true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file. Please remember that doing this will have a small negative effect on system performance. For more information, see"TIMED_STATISTICS".
This view contains information on system parameters.
This view displays tablespace information from the controlfile.
| Column | Datatype | Description |
|---|---|---|
|
TS# |
NUMBER |
Tablespace number |
|
NAME |
VARCHAR2 (30) |
Tablespace name |
This view displays tempfile information.
This view displays temporary lobs.
| Column | Datatype | Description |
|---|---|---|
|
SID |
NUMBER |
Session ID |
|
CACHE_LOBS |
NUMBER |
Number cache temp lobs |
|
NOCACHE_LOBS |
NUMBER |
Number of nocache temp lobs |
This view displays the status of each unit for all temporary tablespaces.
This view displays the state of temporary space cached and used for a given instance. Note that loading of the temporary space cache is lazy, and that instances can be dormant. Use GV$TEMP_EXTENT_POOL for information about all instances.
The view V$TEMP_PING displays the number of blocks pinged per datafile. This information in turn can be used to determine access patterns to existing datafiles and deciding new mappings from datafile blocks to PCM locks.
This view displays aggregate information per file per temporary tablespace regarding how much space is currently being used and how much is free as per the space header.
This view contains information about file read/write statistics.
This view contains thread information from the control file.
This view lists the elapsed time in hundredths of seconds. Time is measured since the beginning of the epoch, which is operating system specific, and wraps around to 0 again whenever the value overflows four bytes (roughly 497 days).
| Column | Datatype | Description |
|---|---|---|
|
HSECS |
NUMBER |
Elapsed time in hundredths of a second |
This view lists the active transactions in the system.
V$TRANSACTION_ENQUEUE displays locks owned by transaction state objects.
This view lists the sizes of various database components for use in estimating data block capacity.
Version numbers of core library components in the Oracle server. There is one row for each component.
| Column | Datatype | Description |
|---|---|---|
|
BANNER |
VARCHAR2 |
Component name and version number |
This view lists block contention statistics. This table is only updated when timed statistics are enabled.
