Oracle: V$ dynamic performance views by topic
Memo grouping the main Oracle V$ views by topic (sessions, SQL, memory, backup, RAC, Data Guard, ASM...).
Oracle has several hundred V$ dynamic performance views. This memo groups them by topic so you can quickly find the right view for a need. The list is not exhaustive: the names come from an Oracle 10g era inventory, but most still exist in current releases. To list every fixed view on your version: SELECT name FROM v$fixed_table WHERE name LIKE 'V$%' ORDER BY 1;.
Overview by topic
| Topic | Main views |
|---|---|
| Instance and database | V$INSTANCE, V$DATABASE, V$VERSION, V$OPTION, V$BGPROCESS, V$PROCESS |
| Parameters | V$PARAMETER, V$PARAMETER2, V$SPPARAMETER, V$SYSTEM_PARAMETER, V$OBSOLETE_PARAMETER, V$NLS_PARAMETERS |
| Sessions | V$SESSION, V$SESSION_WAIT, V$SESSION_EVENT, V$SESSION_LONGOPS, V$SESSTAT, V$MYSTAT, V$ACTIVE_SESSION_HISTORY |
| SQL and cursors | V$SQL, V$SQLAREA, V$SQLTEXT, V$SQL_PLAN, V$SQL_PLAN_STATISTICS, V$SQL_BIND_CAPTURE, V$OPEN_CURSOR, V$SQL_WORKAREA, V$SQLSTATS (10.2) |
| System performance | V$SYSSTAT, V$SYSTEM_EVENT, V$SYS_TIME_MODEL, V$STATNAME, V$EVENT_NAME, V$OSSTAT, V$EVENT_HISTOGRAM |
| Locks and latches | V$LOCK, V$LOCKED_OBJECT, V$ENQUEUE_STAT, V$LATCH, V$LATCHHOLDER, V$LATCHNAME, V$RESOURCE |
| Memory (SGA/PGA) | V$SGA, V$SGASTAT, V$SGAINFO, V$SGA_DYNAMIC_COMPONENTS, V$PGASTAT, V$BUFFER_POOL_STATISTICS, V$LIBRARYCACHE, V$ROWCACHE, V$SHARED_POOL_RESERVED |
| Advisors | V$DB_CACHE_ADVICE, V$SHARED_POOL_ADVICE, V$PGA_TARGET_ADVICE, V$MTTR_TARGET_ADVICE, V$SGA_TARGET_ADVICE (10.2) |
| Files and I/O | V$DATAFILE, V$FILESTAT, V$TEMPFILE, V$TEMPSTAT, V$TABLESPACE, V$WAITSTAT, V$FILE_HISTOGRAM (10.1) |
| Control files | V$CONTROLFILE, V$CONTROLFILE_RECORD_SECTION |
| Redo logs | V$LOG, V$LOGFILE, V$LOG_HISTORY, V$THREAD |
| Undo / rollback | V$UNDOSTAT, V$ROLLSTAT, V$ROLLNAME, V$TRANSACTION |
| Sorting and temporary space | V$SORT_SEGMENT, V$TEMPSEG_USAGE, V$TEMP_SPACE_HEADER, V$TEMP_EXTENT_MAP, V$TEMP_EXTENT_POOL |
| Backup and recovery | V$BACKUP_SET, V$BACKUP_PIECE, V$BACKUP_DATAFILE, V$RMAN_STATUS (10.1), V$RMAN_BACKUP_JOB_DETAILS (10.2), V$RMAN_CONFIGURATION, V$ARCHIVED_LOG, V$ARCHIVE_DEST, V$ARCHIVE_DEST_STATUS, V$RECOVER_FILE, V$RECOVERY_FILE_DEST (10.1), V$FLASH_RECOVERY_AREA_USAGE (10.2), V$RESTORE_POINT (10.2), V$DATABASE_BLOCK_CORRUPTION, V$DATABASE_INCARNATION |
| Data Guard (standby) | V$DATAGUARD_STATUS, V$MANAGED_STANDBY, V$STANDBY_LOG, V$ARCHIVE_GAP, V$DATAGUARD_CONFIG (10.1), V$DATAGUARD_STATS (10.2), V$LOGSTDBY |
| ASM | V$ASM_DISKGROUP, V$ASM_DISK, V$ASM_CLIENT, V$ASM_FILE, V$ASM_OPERATION, V$ASM_TEMPLATE, V$ASM_ALIAS (10.1), _STAT variants of the disk and disk group views (10.2) |
| RAC / Cache Fusion | V$ACTIVE_INSTANCES, V$BH, V$CLUSTER_INTERCONNECTS (10.2), V$CR_BLOCK_SERVER, V$CURRENT_BLOCK_SERVER (10.1), V$GC_ELEMENT, V$GES_ENQUEUE, V$GES_BLOCKING_ENQUEUE, V$INSTANCE_CACHE_TRANSFER |
| Metrics (10g) | V$METRICNAME, V$SYSMETRIC, V$SYSMETRIC_HISTORY, V$SESSMETRIC, V$SERVICEMETRIC, V$EVENTMETRIC, V$FILEMETRIC, V$WAITCLASSMETRIC |
| Services (10.1) | V$SERVICES, V$ACTIVE_SERVICES, V$SERVICE_STATS, V$SERVICE_EVENT, V$SERVICE_WAIT_CLASS, V$SERV_MOD_ACT_STATS |
| Shared servers | V$CIRCUIT, V$DISPATCHER, V$DISPATCHER_RATE, V$SHARED_SERVER, V$QUEUE, V$REQDIST |
| Parallel query | V$PX_PROCESS, V$PX_SESSION, V$PX_SESSTAT, V$PQ_SESSTAT, V$PQ_SYSSTAT, V$PQ_TQSTAT, V$PQ_SLAVE |
| Segment statistics | V$SEGMENT_STATISTICS, V$SEGSTAT, V$SEGSTAT_NAME, V$STATISTICS_LEVEL |
| Resource Manager | V$RSRC_PLAN, V$RSRC_CONSUMER_GROUP, V$RSRC_SESSION_INFO (10.2), V$RSRC_PLAN_HISTORY (10.2) |
| LogMiner | V$LOGMNR_CONTENTS, V$LOGMNR_LOGS, V$LOGMNR_DICTIONARY, V$LOGMNR_PARAMETERS, V$LOGMNR_STATS |
| Streams and AQ | V$AQ, V$STREAMS_CAPTURE, V$STREAMS_APPLY_COORDINATOR, V$STREAMS_APPLY_SERVER, V$BUFFERED_QUEUES (10.1), V$PROPAGATION_SENDER (10.1) |
| Replication / materialized views | V$MVREFRESH, V$REPLPROP, V$REPLQUEUE |
| Database links and heterogeneous services | V$DBLINK, V$GLOBAL_TRANSACTION, V$GLOBAL_BLOCKED_LOCKS, V$HS_AGENT, V$HS_SESSION, V$HS_PARAMETER |
| Security | V$ENABLEDPRIVS, V$PWFILE_USERS, V$VPD_POLICY, V$WALLET (10.2), V$XML_AUDIT_TRAIL (10.2) |
| Direct path load (SQL*Loader) | V$LOADISTAT, V$LOADPSTAT |
| Object dependencies | V$OBJECT_DEPENDENCY, V$OBJECT_USAGE, V$DB_OBJECT_CACHE |
| Fixed views (meta) | V$FIXED_TABLE, V$FIXED_VIEW_DEFINITION, V$INDEXED_FIXED_COLUMN |
| Miscellaneous | V$CONTEXT, V$GLOBALCONTEXT, V$LICENSE, V$RESERVED_WORDS, V$TIMEZONE_NAMES, V$TYPE_SIZE, V$SCHEDULER_RUNNING_JOBS (10.2), V$RESUMABLE |
The (10.1) and (10.2) marks give the Oracle release where the view first appeared; views without a mark exist in earlier releases.
Notes
- A view can belong to several topics (for example
V$TEMPFILEfor sorting and files,V$TRANSACTIONfor undo and transactions). - On RAC, most
V$views have a globalGV$counterpart (one row per instance). - Source of the original list: askershad.blogspot.com.