Oracle Dynamic Performance Views
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Note: V$ view are views based on X$ arrays. The GV$ views are Global V$ views that have, as their first column, the instance identifier (INST_ID). You should always use GV$ rather than V$ where-ever possible.
Categories
Query to identify V$ views not duplicated as GV$ views set linesize 121

SELECT object_name
FROM dba_objects_ae
WHERE object_name LIKE 'V%$%'
AND object_type = 'VIEW'
MINUS
SELECT SUBSTR(object_name, 2)
FROM dba_objects_ae
WHERE object_name LIKE 'GV%$%'
AND object_type = 'VIEW'
ORDER BY 1;
 
Advanced Queuing
 
gv$aq gv$buffered_publishers
gv$aq1 gv$buffered_queues
gv$aq_background_coordinator gv$buffered_subscribers
gv$aq_bmap_nondur_subscribers gv$persistent_publishers
gv$aq_cross_instance_jobs gv$persistent_qmn_cache
gv$aq_job_coordinator gv$persistent_queues
gv$aq_message_cache gv$persistent_subscribers
gv$aq_msgbm gv$qmon_coordinator_stats
gv$aq_nondur_registrations gv$qmon_server_stats
gv$aq_nondur_subscriber gv$qmon_tasks
gv$aq_nondur_subscriber_lwm gv$qmon_task_stats
gv$aq_notification_clients gv$queue
gv$aq_server_pool gv$queueing_mth
gv$aq_subscriber_load  
 
Advisors
 
gv$advisor_current_sqlplan gv$pga_target_advice
gv$advisor_progress gv$pga_target_advice_histogram
gv$java_pool_advice gv$px_buffer_advice
gv$mttr_target_advice  
 
Archive Logging
 
gv$archive gv$archive_processes
gv$archived_log gv$proxy_archivedlog
gv$archive_dest v$proxy_archivelog_details
gv$archive_dest_status v$proxy_archivelog_summary
gv$archive_gap  
 
ASH
 
gv$active_session_history gv$max_active_sess_target_mth
gv$ash_info  
 
ASM
 
gv$asm_acfsrepl gv$asm_audit_clean_events
gv$asm_acfsrepltag gv$asm_audit_config_params
gv$asm_acfssnapshots gv$asm_audit_last_arch_ts
gv$asm_acfstag gv$asm_client
gv$asm_acfsvolumes gv$asm_disk
gv$asm_acfs_encryption_info gv$asm_diskgroup
gv$asm_acfs_security_info gv$asm_diskgroup_stat
gv$asm_acfs_sec_admin gv$asm_disk_iostat
gv$asm_acfs_sec_cmdrule gv$asm_disk_stat
gv$asm_acfs_sec_realm gv$asm_estimate
gv$asm_acfs_sec_realm_filter gv$asm_files
gv$asm_acfs_sec_realm_group gv$asm_filesystem
gv$asm_acfs_sec_realm_user gv$asm_operation
gv$asm_acfs_sec_rule gv$asm_template
gv$asm_acfs_sec_ruleset gv$asm_user
gv$asm_acfs_sec_ruleset_rule gv$asm_usergroup
gv$asm_alias gv$asm_usergroup_member
gv$asm_attribute gv$asm_volume
gv$asm_audit_cleanup_jobs gv$asm_volume_stat
 
Backup and Recovery (RMAN)
 
gv$backup gv$db_transportable_platform
v$backup_archivelog_details gv$deleted_object
v$backup_archivelog_summary gv$fast_start_servers
gv$backup_async_io gv$fast_start_transactions
v$backup_controlfile_details gv$database_block_corruption
v$backup_controlfile_summary gv$instance_recovery
v$backup_copy_details v$proxy_copy_details
v$backup_copy_summary v$proxy_copy_summary
gv$backup_corruption gv$proxy_datafile
gv$backup_datafile v$recovery_area_usage
v$backup_datafile_details gv$recover_file
v$backup_datafile_summary v$recovery_file_dest
gv$backup_device gv$recovery_file_status
v$backup_files gv$recovery_log
gv$backup_nonlogged gv$recovery_progress
gv$backup_piece gv$recovery_status
v$backup_piece_details v$restore_range
gv$backup_redolog v$rman_backup_job_details
gv$backup_set v$rman_backup_subjob_details
v$backup_set_details v$rman_backup_type
v$backup_set_summary gv$rman_compression_algorithm
gv$backup_spfile gv$rman_configuration
v$backup_spfile_details v$rman_encryption_algorithms
v$backup_spfile_summary gv$rman_output
gv$backup_sync_io gv$tsm_sessions
gv$copy_corruption v$rman_status
gv$copy_nonlogged v$sbt_restore_range
gv$datafile_copy v$unusable_backupfile_details
 
Buffers
 
gv$bh gv$buffer_pool_statistics
gv$buffer_pool  
 
Caches
bsp = block server background process
gv$bsp gv$library_cache_memory
gv$cache gv$map_comp_list
v$cache_lock gv$map_element
v$cache_transfer gv$map_ext_element
gv$class_ping gv$map_file
gv$class_cache_transfer gv$map_file_extent
gv$cr_block_server gv$map_file_io_stack
gv$current_block_server gv$map_library
gv$db_cache_advice gv$map_subelement
gv$db_object_cache gv$propagation_receiver
gv$gcshvmaster_info gv$propagation_sender
gv$gcspfmaster_info gv$rowcache
gv$hvmaster_info gv$rowcache_parent
gv$instance_cache_transfer gv$rowcache_subordinate
gv$java_library_cache_memory gv$subcache
gv$javapool gv$temp_ping
gv$librarycache  
 
Connection Pool
 
gv$cpool_cc_info gv$cpool_conn_info
gv$cpool_cc_stats gv$cpool_stats
 
Container Database
 
gv$aq_cross_instance_jobs gv$con_system_wait_class
gv$containers gv$con_sys_time_model
gv$con_sysstat v$pdbs
gv$con_system_event v$pdb_incarnation
 
Control Files
 
gv$controlfile gv$controlfile_record_section
 
Cursors and SQL Statements
 
gv$object_dependency gv$sql_join_filter
gv$open_cursor gv$sql_monitor
gv$sql gv$sql_plan
gv$sqlarea gv$sql_plan_monitor
gv$sqlarea_plan_hash gv$sql_plan_statistics
gv$sqlstats gv$sql_plan_statistics_all
gv$sqlstats_plan_hash gv$sql_redirection
gv$sqltext gv$sql_shared_cursor
gv$sqltext_with_newlines gv$sql_shared_memory
gv$sql_bind_capture gv$sql_workarea
gv$sql_bind_data gv$sql_workarea_active
gv$sql_bind_metadata gv$sql_workarea_histogram
gv$sql_cursor  
 
Data Guard & Standby Databases
 
gv$dataguard_config gv$logstdby_progress
gv$dataguard_stats gv$logstdby_state
v$dataguard_status gv$logstdby_stats
gv$dg_broker_config gv$logstdby_transaction
gv$foreign_archived_log gv$managed_standby
gv$fs_failover_histogram gv$rfs_thread
gv$fs_failover_stats gv$standby_log
gv$logstdby v$standby_event_histogram
gv$logstdby_process  
 
Database Links & Heterogeneous Services
 
gv$dblink gv$hs_parameter
gv$hs_agent gv$hs_session
 
Data Pump
 
gv$datapump_job gv$datapump_session
 
Diagnostics
 
v$diag_adr_control v$diag_vhm_run
v$diag_adr_invalidation v$diag_view
v$diag_alert_ext (source is x$dbgalertext) v$diag_viewcol
v$diag_ams_xaction v$diag_vincident
v$diag_critical_error v$diag_vincident_file
v$diag_dde_user_action v$diag_vinc_meter_info
v$diag_dde_user_action_def v$diag_vips_file_copy_log
v$diag_dde_usr_act_param v$diag_vips_file_metadata
v$diag_dde_usr_act_param_def v$diag_vips_package_file
v$diag_dde_usr_inc_act_map v$diag_vips_package_history
v$diag_dde_usr_inc_type v$diag_vips_package_main_int
v$diag_dfw_config_capture v$diag_vips_package_size
v$diag_dfw_config_item v$diag_vips_pkg_file
v$diag_diagv_incident v$diag_vips_pkg_inc_cand
v$diag_dir_ext v$diag_vips_pkg_inc_dtl
v$diag_em_diag_job v$diag_vips_pkg_inc_dtl1
v$diag_em_target_info v$diag_vips_pkg_main_problem
v$diag_em_user_activity v$diag_vnot_exist_incident
v$diag_incckey v$diag_vproblem
v$diag_incident v$diag_vproblem1
v$diag_incident_file v$diag_vproblem2
v$diag_inc_meter_config v$diag_vproblem_bucket
v$diag_inc_meter_impt_def v$diag_vproblem_bucket1
v$diag_inc_meter_info v$diag_vproblem_bucket_count
v$diag_inc_meter_pk_impts v$diag_vproblem_int
v$diag_inc_meter_summary v$diag_vproblem_lastinc
gv$diag_info v$diag_vshowcatview
v$diag_ips_configuration v$diag_vshowincb
v$diag_ips_file_copy_log v$diag_vshowincb_i
v$diag_ips_file_metadata v$diag_vtest_exists
v$diag_ips_package v$diag_v_actinc
v$diag_ips_package_file v$diag_v_actprob
v$diag_ips_package_history v$diag_v_inccount
v$diag_ips_package_incident v$diag_v_incfcount
v$diag_ips_pkg_unpack_hist v$diag_v_inc_meter_info_prob
v$diag_ips_progress_log v$diag_v_ipsprbcnt
v$diag_ips_remote_package v$diag_v_ipsprbcnt1
v$diag_pickleerr v$diag_v_nfcinc
v$diag_problem v$diag_v_swperrcount
v$diag_relmd_ext gv$incmeter_config
v$diag_sweeperr gv$incmeter_info
v$diag_vem_user_actlog gv$incmeter_summary
v$diag_vem_user_actlog1  
 
Direct Loader
 
gv$loadistat gv$loadpstat
 
Direct NFS
 
gv$dnfs_channels gv$nfs_clients
gv$dnfs_files gv$nfs_locks
gv$dnfs_servers gv$nfs_open_files
gv$dnfs_stats  
 
Exadata
 
gv$cell gv$cell_request_totals
gv$cell_config gv$cell_state
gv$cell_ofl_thread_history gv$cell_thread_history
 
Features and Feature Usage
 
gv$sql_feature gv$sql_feature_dependency gv$sql_feature_hierarchy
 
Fixed Views
 
v$fixed_table v$transportable_platform
v$fixed_view_definition v$version
v$indexed_fixed_column  
SELECT * FROM v$version;

BANNER                                                                       CON_ID
---------------------------------------------------------------------------- ------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production      0
PL/SQL Release 12.1.0.1.0 - Production                                            0
CORE 12.1.0.1.0 Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production                           0
NLSRTL Version 12.1.0.1.0 - Production                                            0
 
Flashback Database
 
gv$flashback_database_log v$recovery_area_usage
gv$flashback_database_logfile v$flashback_txn_graph
gv$flashback_database_stat v$flashback_txn_mods
gv$restore_point  
 
Flashback Recovery Area
 
v$flash_recovery_area_usage v$recovery_area_usage
 
Flashback Transaction
 
v$flashback_txn_graph v$flashback_txn_mods
 
GoldenGate
 
gv$gg_apply_coordinator gv$goldengate_capabilities
gv$gg_apply_reader gv$goldengate_capture
gv$gg_apply_receiver gv$goldengate_message_tracking
gv$gg_apply_server v$goldengate_table_stats
 
Hang Detection
 
v$hang_info gv$hang_statistics
v$hang_session_info  
 
Health Management
 
v$diag_hm_fdg_set gv$hm_info
v$diag_hm_finding gv$hm_recommendation
v$diag_hm_info gv$hm_run
v$diag_hm_message gv$ir_failure
v$diag_hm_recommendation gv$ir_failure_set
v$diag_hm_run gv$ir_manual_checklist
gv$hm_check gv$ir_repair
gv$hm_check_param gv$session_fix_control
gv$hm_finding gv$system_fix_control
 
I/O
 
gv$file_histogram gv$iofuncmetric_history
gv$file_optimized_histogram gv$iostat_consumer_group
gv$filemetric gv$iostat_file
gv$filemetric_history gv$iostat_function
gv$filestat gv$iostat_function_detail
gv$tempstat gv$iostat_network
gv$iofuncmetric gv$io_calibration_status
CREATE TABLE avg_read_write
(inst_id, avg_read_time, avg_write_time) AS
SELECT inst_id, AVG(average_read_time), AVG(average_write_time)
FROM gv$filemetric_history
WHERE 0=1
GROUP BY inst_id;

CREATE OR REPLACE PROCEDURE file_io_metrics AUTHID DEFINER IS
BEGIN
  INSERT INTO avg_read_write
  (inst_id, avg_read_time, avg_write_time)
  SELECT inst_id, AVG(average_read_time), AVG(average_write_time)
  FROM gv$filemetric_history
  WHERE average_read_time IS NOT NULL
  AND average_write_time IS NOT NULL
  GROUP BY inst_id
  ORDER BY 1;

  COMMIT;
END file_io_metrics;
/

exec file_io_metrics

-- sample output
INST_ID    AVG(AVERAGE_READ_TIME) AVG(AVERAGE_WRITE_TIME)
---------- ---------------------- -----------------------
         5             .844172725              .027063522
         6             .542269401              .029147572
         7             .846074066              .186608746
         8            1.102195470              .071913735
         9             .519971963              .029391265
        10             .586967514              .071486951
 
Instance
 
gv$active_instances gv$instance
 
Keywords
Reserved Words

Warning: This is an incomplete list of reserved words.
desc gv$reserved_words

set linesize 121
set pagesize 0

SELECT keyword
FROM gv$reserved_words
ORDER BY 1;
 
Latches & Locks
 
gv$dlm_all_locks gv$latchname
gv$dlm_convert_local gv$latch_children
gv$dlm_convert_remote gv$latch_misses
gv$dlm_latch gv$latch_parent
gv$dlm_locks gv$libcache_locks
gv$dlm_misc gv$lock
gv$dlm_ress gv$locked_object
gv$dlm_traffic_controller gv$locks_with_collisions
gv$enqueue_lock gv$lock_activity
gv$enqueue_stat gv$lock_element
gv$enqueue_statistics gv$lock_type
gv$event_name gv$mutex_sleep
gv$file_ping gv$mutex_sleep_history
gv$ges_blocking_enqueue gv$process
gv$ges_enqueue gv$resource
gv$global_blocked_locks gv$resource_limit
gv$latch gv$transaction_enqueue
gv$latchholder gv_$_lock
 
Log Miner
 
gv$logmnr_contents gv$logmnr_parameters
gv$logmnr_dictionary gv$logmnr_process
gv$logmnr_dictionary_load gv$logmnr_session
gv$logmnr_latch gv$logmnr_stats
gv$logmnr_logfile gv$logmnr_transaction
gv$logmnr_logs  
 
Materialized View Replication
 
gv$mvrefresh gv$replqueue
gv$replprop  
 
Memory Utilization
 
gv$memory_current_resize_ops gv$sgastat
gv$memory_dynamic_components gv$sga_current_resize_ops
gv$memory_resize_ops gv$sga_dynamic_components
gv$memory_target_advice gv$sga_dynamic_free_memory
gv$pgastat gv$sga_resize_ops
gv$process_memory gv$sga_target_advice
gv$sga gv$shared_pool_advice
gv$sgainfo gv$shared_pool_reserved
 
Metrics
 
gv$alert_types gv$metric_history
gv$eventmetric gv$sqlpa_metric
gv$event_histogram gv$sysmetric
gv$metric gv$sysmetric_history
gv$metricgroup gv$sysmetric_summary
gv$metricname gv$threshold_types
 
Miscellaneous
 
gv$bgprocess gv$resumable
gv$blocking_quiesce gv$securefile_timer
gv$calltag gv$scheduler_running_jobs
gv$context v$sqlcommand
gv$corrupt_xid_list gv$sqlfn_arg_metadata
gv$database gv$sqlfn_metadata
gv$db_pipes gv$segspace_usage
v$disk_restore_range gv$sql_cs_histogram
v$editionable_types  gv$sql_cs_selectivity
gv$emon gv$sql_cs_statistics
v_$kccdi gv$sscr_sessions
v_$kccfe gv$subscr_registration_stats
v_$kxftask gv$sysaux_occupants
gv$license gv$timer
gv$listener_network gv$timezone_file
gv$object_dml_frequencies gv$timezone_names
v$object_usage (deprecated in 12cR1) gv$toplevelcall
gv$option gv$type_size
v$patches v$unified_audit_record_format
gv$policy_history  
SELECT *
FROM gv$type_size
ORDER BY 3;
 
OLAP
 
gv$aw_aggregate_op gv$aw_longops
gv$aw_allocate_op gv$aw_olap
gv$aw_calc gv$aw_session_info
 
Optimizer
 
gv$ses_optimizer_env gv$sql_reoptimization_hints
gv$sql_hint gv$sys_optimizer_env
gv$sql_optimizer_env  
 
Parallel Query
 
gv$execution gv$px_instance_group
gv$parallel_degree_limit_mth gv$px_process
gv$pq_sesstat gv$px_process_sysstat
gv$pq_slave gv$px_process_trace
gv$pq_sysstat gv$px_session
gv$pq_tqstat gv$px_sesstat
gv$px_buffer_advice  
 
Parameters
Session specific information for connected sessions.

This query shows the values that are non-default and deprecated. These will most likely trigger a warning message during startup.
gv$nls_parameters gv$parameter_valid_values
gv$nls_valid_values gv$spparameter
gv$obsolete_parameter gv$system_parameter
gv$parameter gv$system_parameter2
gv$parameter2  
desc gv$parameter

set linesize 121
set pagesize 25
col name format a35
col value format a70

SELECT name, value
FROM gv$parameter
WHERE isdefault = 'FALSE'
AND isdeprecated = 'TRUE'
ORDER BY 1;
 
Processes
 
gv$process_group gv$process_memory_detail_prog
gv$process_memory_detail  
 
Real Application Clusters (RAC)
 
gv$active_instances gv$gc_element
gv$cluster_interconnects gv$gc_elements_with_collisions
gv$configured_interconnects gv$instance_cache_transfer
gv$dynamic_remaster_stats gv$temp_cache_transfer
gv$file_cache_transfer  
 
Redo Logs
 
gv$instance_log_group gv$log_history
gv$log gv$redo_dest_resp_histogram
gv$logfile v$rollname
gv$loghist gv$thread
 
Resource Usage
Resource limits and resource utilization
gv$access gv$rsrc_cons_group_history
gv$iostat_consumer_group gv$rsrc_plan
gv$rsrcmgrmetric gv$rsrc_plan_cpu_mth
gv$rsrcmgrmetric_history gv$rsrc_plan_history
gv$rsrc_consumer_group gv$rsrc_session_info
gv$rsrc_consumer_group_cpu_mth  
desc gv$resource_limit

SELECT * FROM gv$resource_limit;
 
Result Cache
 
gv$client_result_cache_stats gv$result_cache_objects
gv$result_cache_dependency gv$result_cache_statistics
gv$result_cache_memory  
 
Rollback Segments / Undo Segments
 
v$rollname gv$undostat
gv$rollstat  
 
Rules
 
gv$rule gv$rule_set_aggregate_stats
gv$rule_set  
 
Security and Privileges
 
gv$context v$object_privilege
gv$enabledprivs gv$pwfile_users
gv$encrypted_tablespaces gv$vpd_policy
gv$encryption_keys gv$wallet
gv$encryption_wallet gv$xml_audit_trail
gv$globalcontext  
 
Services
 
gv$active_services gv$service_event
gv$servicemetric gv$service_stats
gv$servicemetric_history gv$services
 
Sessions
Session specific information for connected sessions
gv$active_session_history gv$session_cursor_cache
gv$active_sess_pool_mth gv$session_event
gv$client_stats gv$session_longops
gv$mystat gv$session_object_cache
gv$sess_io gv$session_wait
gv$sess_time_model gv$session_wait_class
gv$session gv$session_wait_history
gv$session_blockers gv$sessmetric
gv$session_connect_info gv$sesstat
desc gv$session

col username format a10
col schemaname format a10
col osuser format a25
col machine format a20
col service_name format a25
col program format a25
col terminal format a10

-- audsid: audit SID
SELECT sid, audsid, username, status, server, schemaname, osuser, type, service_name
FROM gv$session;

SELECT username, command, machine, terminal, program, state
FROM gv$session;
 
Shared Servers (formerly MTS)
 
gv$circuit gv$max_active_sess_target_mth
gv$dispatcher gv$reqdist
gv$dispatcher_config gv$shared_server
gv$dispatcher_rate gv$shared_server_monitor
 
Storage
 
gv$datafile gv$nfs_locks
gv$datafile_header gv$nfs_open_files
gv$dbfile gv$offline_range
gv$filespace_usage gv$tablespace
gv$lobstat gv$temporary_lobs
gv$nfs_clients  
 
Streams
 
gv$streams_apply_coordinator gv$streams_message_tracking
gv$streams_apply_reader gv$streams_pool_advice
gv$streams_apply_server gv$streams_pool_statistics
gv$streams_capture gv$streams_transaction
 
System Statistics
 
gv$global_transaction gv$statistics_level
gv$osstat gv$statname
gv$segment_statistics gv$sysstat
gv$segstat gv$system_cursor_cache
gv$segstat_name gv$system_event
gv$serv_mod_act_stats gv$sys_time_model
gv$sort_usage gv$transaction
 
Temp Space
 
gv$sort_segment gv$tempfile
gv$temp_extent_map gv$tempseg_usage
gv$temp_extent_pool gv$tempstat
gv$temp_space_header  
 
Waits
 
gv$service_wait_class gv$system_wait_class
gv$session_blockers gv$waitclassmetric
gv$session_wait gv$waitclassmetric_history
gv$session_wait_class gv$waitstat
gv$session_wait_history v$wait_chains
 
Workload Management
 
gv$wlm_pcmetric_history gv$wlm_pcmetric
gv$wlm_pc_stats  
 
Workload Replay
 
gv$wlm_pcmetric_history gv$wlm_pcmetric
gv$wlm_pc_stats gv$workload_replay_thread
 
XStreams
Heterogeneous Streams
gv$xstream_apply_coordinator gv$xstream_message_tracking
v$xstream_apply_reader gv$xstream_outbound_server
v$xstream_apply_receiver v$xstream_table_stats
v$xstream_apply_server gv$xstream_transaction
gv$xstream_capture  
 

Related Topics
Data Dictionary
X$ Arrays

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved