Oracle Performance Views

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 NameDescription
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.

  • RELOADS - number of object definitions aged out of the library cache due to lack of space.
  • PINS - executions of an item in the library cache.
  • GETHITRATION - should always show a value greater than 0.9 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_LOGS 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_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).

  • Shows total extents in the TEMP tablespace areas.
  • Can monitor sort segments in the TEMP tablespace.
  • The EXTENT_HITS column is incremented by 1 whenever an unused extent is found in the 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$SUBCACHE Subordinate cache memory allocations currently loaded into library cache memory
V$SYSSTAT Contains session system level statistics.

  • Memory sorts - how many times a particular type of operation has been performed since startup of the database. Sorting should be performed in memory whenever possible, especially for OLTP (online transaction processing) applications.
  • Monitors client/server traffic statistics - this view can be used in conjunction with the X$KCBRBH table to assess the effect in increasing or decreasing the database buffer cache size.
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$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.

  • High freelist contention.
  • VALUE column values greater than zero indicate possible contention for a resource. Note that any references to the term undo refer to rollback segments.