The V$ tables are used to show transitory performance information based on the underlying C structs known as the X$ tables. The DPT's are the only views in Oracle where data will change nearly every time they are selected from. The C structs are an integral part of the Oracle kernel are thus only available in a mounted but not an open database. The V$ tables overlay the X$ tables. The V$ views are loaded at instance startup and cleared out at shutdown. Additionally the contents of the V$ views change as the database changes in order to show a snapshot image of the database at a particular point in time, thus dynamic performance views.
The virtual views (V$ tables are called dynamic performance tables or views (DPT or DPV). Use of the V$ views can make a DBA's task much easier.
There are two X$ tables a DBA should be aware of. These tables are used for SGA database block buffer tuning.
The V$ views are the main Oracle structures. These main structures are actually C programming structs or record definitions. The details of these structures can be viewed by executing queries against SYS-owned virtual tables. The DPT's are created when the database is built. The information contained within the V$ and X$ structures is the information that would be used in database recovery. The only information needed for recovery concerns the physical data files and the redo logs.
The V$FIXED_TABLE view shows a list of all dynamic performance views in the database.
| View Name | Description |
|---|---|
| V$ACCESS | Currently locked objects in sessions |
| V$ACTIVE_INSTANCES | All instances mounted by a database |
| V$AQ | Messages in a specific database. |
| V$ARCHIVE | Logs in need of archiving (as V$LOG) |
| V$ARCHIVE_DEST | All archive destinations for the current instance. |
| V$ARCHIVED_LOG | Archive log history. |
| V$ARCHIVE_PROCESSES | State of each of multiple archive processes. |
| V$BACKUP | Online datafiles backup status (backup mode of datafile) |
| V$BACKUP_ASYNC_IO | Controlfile backup set information |
| V$BACKUP_CORRUPTION | Controlfile datafile backup corruptions |
| V$BACKUP_DATAFILE | Controlfile datafile backup information |
| V$BACKUP_DEVICE | Supported backup devices |
| V$BACKUP_PIECE | Controlfile backup pieces |
| V$BACKUP_REDOLOG | Non-archived backup redo logs |
| V$BACKUP_SET | Successful backup completion |
| V$BACKUP_SYNC_IO | Successful backup completion |
| V$BGPROCESS | Background processes |
| V$BH | Status and number of pings for each SGA buffer |
| V$BUFFER_POOL | Monitor various buffer pool settings currently in use. |
| V$BUFFER_POOL_STATISTICS | Instance available buffer pools |
| V$CACHE | Shows block headers of all SGA contaqined blocks, ie. currently cached blocks. Use to analyse the database buffer cache in both exclusive and shared server modes. This view shows only objects currently being cached. Parallel server installations only. |
| V$CACHE_LOCK | Locks in cache |
| V$CIRCUIT | User database connections through dispatchers (connection pooling) and servers, what Oracle calls virtual circuits |
| V$CLASS_PING | Number of blocks pinged in classes |
| V$COMPATIBILITY | Version compatibilities and incompatibilities |
| V$COMPATSEG | Permanent non-version retreatable items |
| V$CONTEXT | Current session attributes |
| V$CONTROLFILE | Use this view to verify the source of the control files for the current instance. |
| V$CONTROLFILE_RECORD_SECTION | Controlfile record sections |
| V$COPY_CORRUPTION | Datafile copying corruptions |
| V$DATABASE | Oracle database version and database name. |
| V$DATAFILE | Datafiles |
| V$DATAFILE_COPY | Datafile copy information |
| V$DATAFILE_HEADER | Datafile headers |
| V$DBFILE | Names and locations of all physical database data files. |
| V$DBLINK | Inter-database links |
| V$DB_OBJECT_CACHE | Contains the amount of shareable memory used by a cached PL/SQL object. |
| V$DB_PIPES | Database pipes |
| V$DELETED_OBJECT | Physically deleted item details, eg. logs, datafile copies, etc. |
| V$DISPATCHER | Dispatcher process |
| V$DISPATCHER_RATE | Dispatcher process rate statistics |
| V$DLM_ALL_LOCKS | Existing locks, particularly those causing contention |
| V$DLM_CONVERT_LOCAL | Lock conversion elapsed period of time |
| V$DLM_CONVERT_REMOTE | Lock conversion elapsed period of time |
| V$DLM_LATCH | Latch performance (obsolete) |
| V$DLM_LOCKS | Existing locks, particularly those causing contention |
| V$DLM_MISC | DLM statistics |
| V$DLM_RESS | Lock manager resources |
| V$ENABLEDPRIVS | Currently enabled privelages |
| V$ENQUEUE_LOCK | Enqueued object locks (same as V$LOCK) |
| V$EVENT_NAME | Wait events |
| V$EXECUTION | Parallel execution information |
| V$FALSE_PING | Unserviced pings as a result of lock collisions |
| V$FAST_START_SERVERS | Parallel transaction recovery slaves |
| V$FAST_START_TRANSACTIONS | Recovery transaction progress |
| V$FILE_PING | Number of blocks pinged per datafile |
| V$FILESTAT | All physical file object read/write statistics |
| V$FIXED_TABLE | Names of the main Oracle structures. |
| V$FIXED_VIEW_DEFINITION | Virtual performance view structures. |
| V$GLOBAL_BLOCKED_LOCKS | Globally blocked locks |
| V$GLOBAL_TRANSACTION | Active global transactions |
| V$HS_AGENT | HS agent processes |
| V$HS_SESSION | Open HS sessions |
| V$INDEXED_FIXED_COLUMN | Indexed dynamic performance table columns. Use to execute more efficient queries on V$tables |
| V$INSTANCE | Current instance state |
| V$INSTANCE_RECOVERY | Instance recovery information |
| V$LATCH | Latches and locks information. The SLEEPS column shows the number of times a process waited for a latch. The WILLING_TO_WAIT, MISSES and GETS columns are also useful. |
| V$LATCHHOLDER | Current latch holders |
| V$LATCHNAME | Decoded latch names (as in V$LATCH) |
| V$LATCH_CHILDREN | Child latch statistics |
| V$LATCH_MISSES | Missed latch requistion attempts |
| V$LATCH_PARENT | Parent latch statistics |
| V$LIBRARYCACHE | Activity in the shared pool library caches. The ratio of RELOADS to PINS for a specific type of cache must always be less than 1% in a properly tuned database.
|
| V$LICENSE | License limitations |
| V$LOADCSTAT | SQL*Loader direct load statistics |
| V$LOADTSTAT | SQL*Loader direct load statistics |
| V$LOCK | Currently held locks plus oustanding latch and locks requests |
| V$LOCK_ACTIVITY | Instance lock operations |
| V$LOCK_ELEMENT | Locks used by the buffer cache |
| V$LOCKED_OBJECT | All transaction locks |
| V$LOCKS_WITH_COLLISIONS | Buffers transaction with lock contentions |
| V$LOG | Log file details and status contained in the control file. |
| V$LOGFILE | Redo log file information. List of all redo log files and their locations. Use with the V$DBFILE to provide input to the CREATE CONTROLFILE command. |
| V$LOGHIST | Log file history details contained in the control file. |
| V$LOGMNR_CONTENTS | Log history |
| V$LOGMNR_DICTIONARY | Log history |
| V$LOGMNR_LOGS | Log information |
| V$LOGMNR_PARAMETERS | Log information |
| V$LOG_HISTORY | Archived logs plus log SCN (system change number) range. |
| V$MLS_PARAMETERS | Trusted Oracle information |
| V$MTS | Multithreaded server information |
| V$MYSTAT | Current session statistics |
| V$NLS_PARAMETERS | NLS (national language set) parameters |
| V$NLS_VALID_VALUES | NLS (national language set) parameters |
| V$OBJECT_DEPENDENCY | NLS (national language set) parameters |
| V$OBSOLETE_PARAMETER | Obsolete parameters |
| V$OFFLINE_RANGE | Datafile offline information |
| V$OPEN_CURSOR | User session currently open and parsed cursors |
| V$OPTION | All component installed or switched-on/off Oracle options |
| V$PARALLEL_DEGREE_LIMIT_MTH | Parallel dgree limitiations |
| V$PARAMETER | Displays the settings of initialisation parameters including default values. |
| V$PING | Block pings (as with V$CACHE) |
| V$PQ_SESSTAT | Parallel query statistics |
| V$PQ_SLAVE | Parallel server process statistics |
| V$PQ_SYSSTAT | Parallel query statistics |
| V$PQ_TQSTAT | Parallel query statistics |
| V$PROCESS | Currently active processes. |
| V$PROXY_ARCHIVEDLOG | Archive log backups |
| V$PROXY_DATAFILE | Datafile backups |
| V$PWFILE_USERS | Lists usernames granted the SYSDBA and SYSOPER roles. |
| V$PX_PROCESS | Parallel executing sessions |
| V$PX_PROCESS_SYSSTAT | Parallel executing session statistics |
| V$PX_SESSION | Parallel executing sessions |
| V$PX_SESSTAT | Parallel executing sessions |
| V$QUEUE | Multi-threaded message queues |
| V$RECOVER_FILE | The status of files needing media recovery |
| V$RECOVERY_FILE_STATUS | Tracks the status of media recovery (contains a row for each datafile for each RECOVER command). |
| V$RECOVERY_LOG | Archive logs required for media recovery |
| V$RECOVERY_PROGRESS | Database recovery progress |
| V$RECOVERY_STATUS | Tracks the status of media recovery (statistics). |
| V$REQDIST | Multi-threaded server dispatcher request times |
| V$RESERVED_WORDS | PL/SQL reserved words |
| V$RESOURCE | Resources |
| V$RESOURCE_LIMIT | System resource usage and limitations |
| V$ROLLNAME | Online rollback segments |
| V$ROLLSTAT | Online rollback segment statistics |
| V$ROWCACHE | Data dictionary activity and data dictionary cache statistics |
| V$ROWCACHE_PARENT | Data dictionary parent object statistics |
| V$ROWCACHE_SUBORDINATE | Data dictionary subordinate object statistics |
| V$RSRC_CONSUMER_GROUP | Resource consumer groups |
| V$RSRC_CONSUMER_GROUP_CPU_MTH | Resource consumer allocation methods |
| V$RSRC_PLAN | Resource plans |
| V$RSRC_PLAN_CPU_MTH | Resource consumer allocation methods |
| V$SESSION | Lists all sessions currently attached to the database plus their status. The row of a lock contention session can be obtained. A user session can be killed using the SERIAL# and the SID columns since these values are required by the ALTER SYSTEM KILL SESSION command. |
| V$SESSION_CONNECT_INFO | Session network connections |
| V$SESSION_CURSOR_CACHE | Session cursors |
| V$SESSION_EVENT | Session local events. Can show I/O contention for the redo log files such as system-wide waits per session. |
| V$SESSION_LONGOPS | Long running operations, currently anything over 6 seconds CPU time. |
| V$SESSION_OBJECT_CACHE | Session object cache statistics |
| V$SESSION_WAIT | Information on session waits. The WAIT_TIME column shows wait times recording a session's previous wait time. Resources for which active sessions are waiting. |
| V$SESSTAT | User session statistics |
| V$SESS_IO | User session specific I/O statistics |
| V$SGA | System global area (SGA) information |
| V$SGASTAT | Shows the settings of the shared global area. |
| V$SHARED_POOL_RESERVED | Reserved and shared pool statistics |
| V$SHARED_SERVER | Shared server processes |
| V$SORT_SEGMENT | This is a map to the sort extent pool (SEP).
|
| V$SORT_USAGE | Shows which processes are performing temporary data file sorting. |
| V$SQL | Shared SQL area statistics |
| V$SQL_BIND_DATA | Cursor variables |
| V$SQL_BIND_METADATA | Cursor variables |
| V$SQL_CURSOR | Session cursors |
| V$SQL_SHARED_MEMORY | Cursor shared memory |
| V$SQLAREA | V$SQLAREA and V$SQLTEXT are views for displaying high impact physical (disk I/O) and logical (database buffer cache) reads. V$SQLTEXT contains the entire query. |
| V$SQLTEXT | V$SQLTEXT can be joined to V$SQLAREA to display the complete text of a query. |
| V$SQLTEXT_WITH_NEWLINES | Legible form of V$SQLTEXT |
| V$STATNAME | V$SESSTAT and V$SYSSTAT combination |
| V$SUBCACHE | Subordinate cache memory allocations currently loaded into library cache memory |
| V$SYSSTAT | Contains session system level statistics.
|
| V$SYSTEM_CURSOR_CACHE | View of entire system cursor cache |
| V$SYSTEM_EVENT | Information on system-wide events and is used by the utlbstat and utlestat scripts as a statistics source. log buffer space and buffer busy wait (contention for the database buffer cache) are two events which should be watched for. |
| V$SYSTEM_PARAMETER | System parameters |
| V$TABLESPACE | Tablespaces |
| V$TEMPFILE | Temporary file details |
| V$TEMPORARY_LOBS | Temporary LOBS |
| V$TEMP_EXTENT_MAP | Temporary tablespace unit status |
| V$TEMP_EXTENT_POOL | State of cached temporary space |
| V$TEMP_PING | Number of blocks pinged per datafile |
| V$TEMP_SPACE_HEADER | Temporary tablespace header details |
| V$TEMPSTAT | File read/write statisitics |
| V$THREAD | Shows the status of the current thread. This view is useful for checking the instance name when executing a utility like the Server Manager against an Oracle installation with multiple database-instances. |
| V$TIMER | Time in 100ths of seconds |
| V$TRANSACTION | Information on all database transactions. Specific user transactions can be tracked using this and the V$SESSION views |
| V$TRANSACTION_ENQUEUE | Locks owned by transactions |
| V$TYPE_SIZE | Data block capacity estimation |
| V$VERSION | Oracle component versions |
| V$WAITSTAT | Information on wait statistics.
|