Search notes:

Oracle Dynamic Performance (V$) Views

Dynamic performance views are views that query their data mainly from dynamic performance (x$) tables.

Creation of the dynamic performance views

Oracle's dynamic performance views are created by $ORACLE_HOME/rdbms/admin/catalog.sql.
The V$… (and GV$…) names are in fact public synonyms for objects whose name starts with V_$… (or GV_$…).

V$ and GV$ views

For most dynamic performance views, two versions exist: one that starts with the prefix v$… and one that starts with gv$…. The g stands for global. These gv$ views are relevant for RAC environments and are used to query information for all instances in the RAC network. So, they have an additional inst_id.
In fact, the v$ views have basically the following form
create view v$XYZ as
select
   col_1,
   col_2,
   col_3,
   …
from
   gv$XYZ
where
   inst_id = USERENV('Instance');

V$FIXED_VIEW_DEFINITION

The dynamic performance views and their definition can be queried from v$fixed_view_definition.
The names of V$ views can also be queried from x$kqfvi:
select
   kqfvinam
from
   x$kqfvi;
v$active_session_history
v$alert_types
v$archive
v$archive_gap
v$archive_dest Shows all archived redo logs destinations.
v$archive_dest_status allows to find status and errors for each of the defined archive destinations.
v$archived_log shows successfully archived redo logs
v$archive_gap lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).
v$archive_process provides information on the archive processes. It can be used to find out if an ARCH process is active or not.
v$ash_info
v$bgprocess Background processes
v$bh describes each block in the database buffer cache.
v$buffer_pool
v$code_clause Supported clause and parameter names for the data vault alter system and alter session commands.
v$column_statistics
v$containers
v$context
v$controlfile shows the location of control files.
v$controlfile_record_section See http://www.adp-gmbh.ch/ora/concepts/controlfiles.html#sections
v$database access database related information
v$database_block_corrupution records data blocks that were corrupted since the last backup.
v$database_incarnation
v$database_key_info
v$datafile Returns a record for each data file.
v$datafile_header: Various information about data file headers
v$dataguard_status Shows errors in a dataguard environment.
v$db_object_cache displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool.
v$db_transportable_platform
v$dbfile
v$dblink
v$diag_alert_ext
v$diag_dir_ext
v$diag_info displays the state of the Automatic Diagnostic Repository (ADR).
v$diag_trace_file lists all trace files that are currently present in the Automatic Diagnostic Repository (ADR) in the current container.
v$diag_trace_file_contents Shows the content of trace files.
v$enabledprivs
v$encrypted_tablespaces
v$encryption_wallet
v$enqueue_lock
v$enqueue_stat
v$enqueue_statistics
v$eventmetric new with Oracle 10, improved timing and statistics.
v$event_name
v$filestat Compare with v$iostat_file and dba_hist_filestatxs
v$file_histogram
v$filemetric
v$filemetric_history
v$fixed_table contains the name of all V$, X$ and GV$ tables.
v$fixed_view_definition contains the definitions for views recorded in v$fixed_table.
v$flashback_database_log
v$flash_recovery_area_usage see also v$recovery_file_dest
v$hm_check
v$indexed_fixed_column Columns of fixed (X$) tables that are indexed.
v$instance
v$instance_recovery can be used to determine the optimal size for log files.
v$io_calibration_status Shows the I/O calibration status in the instance.
v$iostat_file Compare with v$filestat
v$iostat_function
v$java_library_cache_memory
Oracle: V$LATCH
v$latch_children
v$latchholder
v$latch_misses
v$librarycache statistics about library cache performance and activity.
v$license
v$lock
v$lock_activty deprecated by v$segment_statistics
v$lock_type
v$locked_object: who is locking what
v$log Contains information on each log group
v$logfile Contains information on each online redo log file.
v$loghist contains an entry for every log switch. Is obsolete, v$log_history should be used instead.
v$logmnr_contents see dbms_logmnr
v$log_history contains an entry for every log switch.
v$logstdby can be used to verify that archived redo logs are being applied to standby databases.
v$managed_standby Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service.
v$memory_dynamic_components Current sizes of all dynamically tuned memory components.
v$memory_target_advice Rough estimation of influence of value of memory_target on DB time.
v$metric Values of the most recent statistic values captured by AWR.
v$metricname
v$metric_history
v$mystat records statistical data about the session that accesses it.
v$nls_parameters NLS Parameters that are in effect.
v$nls_valid_values
v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index … monitor usage.
v$obsolete_parameter
v$ofsmount
v$ofs_stats
v$open_cursor Cursors (of all sessions) that are currently opened and parsed, or cached.
v$option which options are installed on a server.
v$parameter List the name value pairs of the init file or the spfile.
v$parameter_valid_values
v$pdbs
v$pq_sesstat
v$pgastat statistics about the PGA
v$pga_target_advice
v$pq_slave
v$pq_sysstat
v$pq_tqstat stores some statistics of the the current session's most recently parallelly executed SQL statement
v$process
v$process_memory
v$process_memory_detail
v$process_memory
v$pwfile_users Lists all users who have been granted sysdba or sysoper privileges. See adding users to the password file.
v$px_process
v$px_process_sysstat
v$px_session shows information about sessions that are executing SQL statement parallelly.
v$recover_file which data files need recovery?
v$recovery_file_dest
v$redo_dest_resp_histogram contains the response times for each redo transport destination.
v$reserved_words Which words are reserved keywords in Oracle.
v$resource_limit
v$result_cache_memory
v$result_cache_objects Objects in the result cache
v$rman_backup_type
v$rman_compression_algorithm
v$rman_configuration
v$rollname names of rollback segments. This view's usn column can be joined with the field usn in v$rollstat and with xidusn of v$transaction.
v$rollstat
v$rowcache Information about the data dictionary cache
v$segment_statistics
v$segstat
v$segstat_name
v$services
v$ses_optimizer_env
v$sess_io
v$sess_time_model
v$session
v$session_connect_info
v$session_cursor_cache
v$session_event
v$session_fix_control Compare with v$system_fix_control
v$session_longops gives feedback on long running queries.
v$session_wait What is each session currently waiting for.
v$sesstat is similar to v$mystat except except that it shows cumulated statistics for all sessions.
v$sga how much memory does the shared global area use?
v$sgainfo
v$sga_dynamic_free_memory information about the amount of SGA memory available for future dynamic SGA resize operations.
v$sga_dynamic_components information about sga resize operations since startup.
v$sga_resize_ops
v$sgastat show free space in the shared global area: select * from v$sgastat where name = 'free memory'
v$spparameter
v$sort_usage
v$sort_segment
v$spparameter returns values stored in the SPFILE
v$sqlarea
v$sql_bind_data
v$sql_bind_capture This view captures bind variables for all sessions and is faster than setting 10046 on level 4.
v$sql_bind_metadata
v$sqlcommand
v$sql_cursor
v$sql_feature
v$sql_feature_dependency
v$sql_feature_hierarchy
v$sqlfn_arg_metadata
v$sqlfn_metadata contains metadata about SQL operators and functions.
v$sql_hint
v$sql_monitor
v$sql_optimizer_env
v$sql_plan
v$sql_plan_monitor
v$sql_plan_statistics provides actual (not estimated as in v$sql_plan) for every SQL plan operation of an executed SQL statement.
v$sql_reoptimization_hints
v$sql_shared_cursor Explains why a particular child cursor is not shared with existing child cursors.
v$sql_shared_memory
v$sqlstats Contains one record for an SQLID and displays a subset of the columns found in V$SQL and V$SQLAREA that have statistical performance data.
v$sqltext
v$sqltext_with_newlines
v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number].
v$sql_workarea_active
v$sql_workarea_histogram
v$standby_log
v$statistics_level displays status-information about the statistics and advisories that are controlled by the init parameter statistics_level.
v$statname get decoded names for the statistic# field of v$mystat, v$sysstat and v$sesstat.
v$stats_advisor_action
v$stats_advisor_findings
v$stats_advisor_rationales
v$stats_advisor_recs
v$stats_advisor_rules
v$subcache
v$sysaux_occupants Shows how much space some components (such as PL/Scope, Workspace Manager etc.) need in the SYSAUX tablespace.
v$sys_time_model
v$sysmetric
v$sysstat
v$system_event
v$system_fix_control Compare with v$session_fix_control
v$system_parameter
v$system_wait_class
v$sys_optimizer_env
v$tablespace
v$tempfile Returns a record for each temp file.
v$temporary_lobs
v$timezone_file
v$timezone_names
v$thread
v$toplevelcall
v$transaction
v$transportable_platform Lists data endianness for different platforms on which Oracle is available.
v$type_size Sizes (in bytes) of some database components, to be used to estimate data block capacity.
v$undostat
v$unified_audit_record_format
v$unified_audit_trail
v$version
v$vpd_policy
v$wait_chains See MOS notes 1428210.1 and 452358.1.

Thanks

Various people notified me of errors on this page and helped me improve it: in no particual order Elizabeth Seager, Mark Ramsay, Shaik Abdul Raheem, Simon Kissane, George Milliken

See also

Data dictionary
$ORACLE_HOME/rdbms/admin/cdfixed.sql installs some (all?) fixed views.
$ORACLE_HOME/sqlplus/admin/plustrce.sql creates a role which is used by by the SQL*Plus command set autotrace … statistics to access the dynamic performance views v$sesstat, v$statname and v$mystat.
x$kqfvt

Index