Oracle Dynamic Performance Views
Version 11.2.0.2
 
General Information
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_name NOT IN (
  SELECT SUBSTR(object_name, 2)
  FROM dba_objects_ae
  WHERE object_name LIKE 'GV$%')
ORDER BY 1;
 
Advanced Queuing
 
gv$aq gv$persistent_publishers gv$qmon_server_stats
gv$aq1 gv$persistent_qmn_cache gv$qmon_task_stats
gv$buffered_publishers gv$persistent_queues gv$qmon_tasks
gv$buffered_queues gv$persistent_subscribers gv$queue
gv$buffered_subscribers gv$qmon_coordinator_stats gv$queueing_mth
 
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 v$proxy_archivelog_details
gv$archive_dest v$proxy_archivelog_summary
gv$archive_dest_status  
gv$archive_gap  
gv$archive_processes  
gv$archived_log  
gv$proxy_archivedlog  
 
ASH
 
gv$ash_info gv$active_session_history gv$max_active_sess_target_mth
 
ASM
 
gv$asm_acfs_encryption_info gv$asm_disk gv$asm_operation
gv$asm_acfs_security_info gv$asm_disk_iostat gv$asm_template
gv$asm_acfssnapshots gv$asm_disk_stat gv$asm_user
gv$asm_acfsvolumes gv$asm_diskgroup gv$asm_usergroup
gv$asm_alias gv$asm_diskgroup_stat gv$asm_usergroup_member
gv$asm_attribute gv$asm_file gv$asm_volume
gv$asm_client gv$asm_filesystem gv$asm_volume_stat
 
Backup and Recovery (RMAN)
 
gv$backup v$backup_archivelog_details
gv$backup_async_io v$backup_archivelog_summary
gv$backup_datafile v$backup_controlfile_details
gv$backup_device v$backup_controlfile_summary
gv$backup_piece v$backup_copy_details
gv$backup_redolog v$backup_copy_summary
gv$backup_set v$backup_corruption
gv$backup_spfile v$backup_datafile_details
gv$backup_sync_io v$backup_datafile_summary
gv$database_incarnation v$backup_files
gv$datafile_copy v$backup_piece_details
gv$db_transportable_platform v$backup_set_details
gv$deleted_object v$backup_set_summary
gv$fast_start_servers v$backup_spfile_details
gv$fast_start_transactions v$backup_spfile_summary
gv$instance_recovery v$block_change_tracking
gv$proxy_datafile gv$copy_corruption
gv$recover_file gv$database_block_corruption
gv$recovery_area_usage v$proxy_copy_details
gv$recovery_file_status v$proxy_copy_summary
gv$recovery_log v$recovery_file_dest
gv$recovery_progress v$rman_backup_job_details
gv$recovery_status v$rman_backup_subjob_details
gv$rman_output v$rman_backup_type
gv$tsm_sessions v$rman_compression_algorithm
  v$rman_configuration
  v$rman_encryption_algorithms
  v$rman_status
  v$unusable_backupfile_details
 
Buffers
 
gv$bh gv$buffer_pool gv$buffer_pool_statistics
 
Caches
bsp = block server background process
gv$bsp gv$map_element
gv$class_ping gv$map_ext_element
gv$cr_block_server gv$map_file
gv$current_block_server gv$map_file_extent
gv$db_cache_advice gv$map_file_io_stack
gv$db_object_cache gv$map_library
gv$gcshvmaster_info gv$map_subelement
gv$gcspfmaster_info gv$propagation_receiver
gv$hvmaster_info gv$propagation_sender
gv$instance_cache_transfer gv$rowcache
gv$java_library_cache_memory gv$rowcache_parent
gv$javapool gv$rowcache_subordinate
gv$library_cache_memory gv$subcache
gv$librarycache gv$temp_ping
gv$map_comp_list  
 
Connection Pool
 
gv$cpool_cc_info gv$cpool_cc_stats gv$cpool_conn_info gv$cpool_stats
 
Control Files
 
gv$controlfile gv$controlfile_record_section
 
Cursors and SQL Statements
 
gv$object_dependency gv$sql_redirection
gv$open_cursor gv$sql_shared_cursor
gv$sql 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 gv$sqlarea
gv$sql_join_filter gv$sqlarea_plan_hash
gv$sql_monitor gv$sqlstats
gv$sql_plan gv$sqlstats_plan_hash
gv$sql_plan_monitor gv$sqltext
gv$sql_plan_statistics gv$sqltext_with_newlines
gv$sql_plan_statistics_all  
 
Data Guard & Standby Databases
 
gv$dataguard_config gv$logstdby_progress
gv$dataguard_stats gv$logstdby_state
v$dataguard_status gv$logstdby_stats
gv$foreign_archived_log gv$logstdby_transaction
gv$fs_failover_histogram gv$managed_standby
gv$fs_failover_stats gv$rfs_thread
gv$logstdby gv$standby_log
gv$logstdby_process v$standby_event_histogram
 
Database Links & Heterogeneous Services
 
gv$dblink gv$hs_parameter
gv$hs_agent gv$hs_session
 
Data Pump
 
gv$datapump_job gv$datapump_session
 
Diagnostics
 
gv$diag_info v$diag_adr_control
gv$incmeter_config v$diag_adr_invalidation
gv$incmeter_info v$diag_alert_ext (source is x$dbgalertext)
gv$incmeter_summary v$diag_ams_xaction
  v$diag_critical_error
  v$diag_dde_user_action
  v$diag_dde_user_action_def
  v$diag_dde_usr_act_param
  v$diag_dde_usr_act_param_def
  v$diag_dde_usr_inc_act_map
  v$diag_dde_usr_inc_type
  v$diag_diagv_incident
  v$diag_dir_ext
  v$diag_em_diag_job
  v$diag_em_target_info
  v$diag_em_user_activity
  v$diag_inc_meter_config
  v$diag_incckey
  v$diag_incident
  v$diag_incident_file
  v$diag_inc_meter_impt_def
  v$diag_inc_meter_info
  v$diag_inc_meter_pk_impts
  v$diag_inc_meter_summary
  v$diag_ips_configuration
  v$diag_ips_file_copy_log
  v$diag_ips_file_metadata
  v$diag_ips_package
  v$diag_ips_package_file
  v$diag_ips_package_history
  v$diag_ips_package_incident
  v$diag_ips_pkg_unpack_hist
  v$diag_ips_progress_log
  v$diag_ips_remote_package
  v$diag_pickleerr
  v$diag_problem
  v$diag_relmd_ext
  v$diag_sweeperr
  v$diag_vem_user_actlog
  v$diag_vem_user_actlog1
  v$diag_vhm_run
  v$diag_view
  v$diag_viewcol
  v$diag_vincident
  v$diag_vincident_file
  v$diag_vinc_meter_info
  v$diag_vips_file_copy_log
  v$diag_vips_file_metadata
  v$diag_vips_package_file
  v$diag_vips_package_history
  v$diag_vips_package_main_int
  v$diag_vips_package_size
  v$diag_vips_pkg_file
  v$diag_vips_pkg_inc_cand
  v$diag_vips_pkg_inc_dtl
  v$diag_vips_pkg_inc_dtl1
  v$diag_vips_pkg_main_problem
  v$diag_vnot_exist_incident
  v$diag_vproblem
  v$diag_vproblem1
  v$diag_vproblem2
  v$diag_vproblem_bucket
  v$diag_vproblem_bucket1
  v$diag_vproblem_bucket_count
  v$diag_vproblem_int
  v$diag_vproblem_lastinc
  v$diag_vshowcatview
  v$diag_vshowincb
xx v$diag_vshowincb_i
yyy v$diag_vtest_exists
  v$diag_v_actinc
  v$diag_v_actprob
  v$diag_v_inccount
  v$diag_v_incfcount
  v$diag_v_inc_meter_info_prob
  v$diag_v_ipsprbcnt
  v$diag_v_ipsprbcnt1
  v$diag_v_nfcinc
  v$diag_v_swperrcount
 
Direct Loader
 
gv$loadistat gv$loadpstat
 
Direct NFS
 
gv$dnfs_channels gv$dnfs_servers gv$nfs_clients gv$nfs_open_files
gv$dnfs_files gv$dnfs_stats gv$nfs_locks  
 
Exadata
 
gv$cell gv$cell_state
gv$cell_config gv$cell_thread_history
gv$cell_request_totals  
 
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
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
 
Flashback Database
 
gv$flashback_database_log v$flashback_txn_graph
gv$flashback_database_logfile v$flashback_txn_mods
gv$flashback_database_stat  
gv$recovery_area_usage  
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$goldengate_capture gv$goldengate_message_tracking gv$goldengate_transaction
 
Hang Detection
 
v$hang_info v$hang_session_info
 
Health Management
 
v$diag_hm_fdg_set gv$hm_run
v$diag_hm_message gv$ir_failure
gv$hm_check gv$ir_failure_set
gv$hm_check_param gv$ir_manual_checklist
gv$hm_finding gv$ir_repair
gv$hm_info gv$session_fix_control
gv$hm_recommendation 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$lock_activity
gv$dlm_traffic_controller gv$lock_element
gv$enqueue_lock gv$lock_type
gv$enqueue_stat gv$locked_object
gv$enqueue_statistics gv$locks_with_collisions
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_callback gv$logmnr_parameters
gv$logmnr_contents gv$logmnr_process
gv$logmnr_dictionary gv$logmnr_region
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$replprop gv$replqueue
 
Memory Utilization
 
gv$memory_dynamic_components gv$sga_dynamic_free_memory
gv$memory_resize_ops gv$sga_resize_ops
gv$memory_target_advice gv$sga_target_advice
gv$pgastat gv$sgainfo
gv$process_memory gv$sgastat
gv$sga gv$shared_pool_advice
gv$sga_current_resize_ops gv$shared_pool_reserved
gv$sga_dynamic_components  
 
Metrics
 
gv$alert_types gv$metricgroup gv$sysmetric
gv$event_histogram gv$metricname gv$sysmetric_history
gv$eventmetric gv$metric_history gv$sysmetric_summary
gv$metric gv$sqlpa_metric gv$threshold_types
 
Miscellaneous
 
gv$bgprocess gv$securefile_timer
gv$blocking_quiesce gv$segspace_usage
gv$calltag gv$sql_cs_selectivity
gv$context gv$sql_cs_statistics
gv$corrupt_xid_list v$sqlcommand
gv$database gv$sqlfn_metadata
gv$db_pipes gv$sqlfn_arg_metadata
gv$emon gv$sscr_sessions
gv$license gv$subscr_registration_stats
gv$listener_network gv$sysaux_occupants
gv$object_dml_frequencies gv$timer
v$object_usage gv$timezone_file
gv$option gv$timezone_names
gv$policy_history gv$toplevelcall
gv$resumable gv$type_size
gv$scheduler_running_jobs gv$workload_replay_thread
gv$sql_cs_histogram  
SELECT *
FROM gv$type_size
ORDER BY 3;
 
OLAP
 
gv$aw_aggregate_op gv$aw_olap gv_olapi_memory_op_history
gv$aw_allocate_op gv$aw_session_info gv_olapi_session_history
gv$aw_calc gv_olapi_iface_object_history  
gv$aw_longops gv_olapi_iface_op_history  
 
Optimizer
 
gv$ses_optimizer_env gv$sql_optimizer_env
gv$sql_hint gv$sys_optimizer_env
 
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$parameter2
gv$nls_valid_values gv$spparameter
gv$obsolete_parameter gv$system_parameter
gv$parameter gv$system_parameter2
gv$parameter_valid_values  
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;
 
Parallel Query
 
gv$execution gv$px_buffer_advice
gv$parallel_degree_limit_mth gv$px_instance_group
gv$pq_sesstat gv$px_process
gv$pq_slave gv$px_process_sysstat
gv$pq_sysstat gv$px_session
gv$pq_tqstat gv$px_sesstat
 
Processes
 
gv$process_group gv$process_memory_detail_prog
gv$process_memory_detail  
 
Real Application Clusters (RAC)
 
gv$active_instances gv$gc_elements_with_collisions
gv$cluster_interconnects gv$file_cache_transfer
gv$configured_interconnects gv$instance_cache_transfer
gv$dynamic_remaster_stats gv$temp_cache_transfer
gv$gc_element  
 
Redo Logs
 
gv$instance_log_group gv$loghist gv$thread
gv$log gv$log_history v$rollname
gv$logfile gv$redo_dest_resp_histogram  
 
Resource Usage
Resource limits and resource utilization
gv$access gv$rsrc_consumer_group_cpu_mth
gv$iostat_consumer_group gv$rsrc_plan
gv$rsrcmgrmetric gv$rsrc_plan_cpu_mth
gv$rsrcmgrmetric_history gv$rsrc_plan_history
gv$rsrc_consume_group_cpu_mth gv$rsrc_session_info
gv$rsrc_consumer_group v$rsrc_consumer_group_cpu_mth
gv$rsrc_cons_group_history  
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$rollstat gv$undostat
 
Rules
 
gv$rule gv$rule_set gv$rule_set_aggregate_stats
 
Security and Privileges
 
gv$context v$object_privilege
gv$enabledprivs gv$pwfile_users
gv$encrypted_tablespaces gv$vpd_policy
gv$encryption_wallet gv$wallet
gv$globalcontext gv$xml_audit_trail
 
Services
 
gv$active_services gv$servicemetric
gv$service_event gv$servicemetric_history
gv$service_stats gv$services
 
Sessions
Session specific information for connected sessions
gv$active_sess_pool_mth gv$session_cursor_cache
gv$active_session_history 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_client  
 
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$sys_time_model
gv$segstat gv$sysstat
gv$segstat_name gv$system_cursor_cache
gv$serv_mod_act_stats gv$system_event
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 v$wait_chains
gv$session_blockers  
gv$session_wait  
gv$session_wait_class  
gv$session_wait_history  
gv$system_wait_class  
gv$waitclassmetric  
gv$waitclassmetric_history  
gv$waitstat  
 
Workload Management
 
gv$wlm_pc_stats gv$wlm_pcmetric gv$wlm_pcmetric_history
 
XStreams
Heterogeneous Streams
gv$xstream_capture gv$xstream_outbound_server
gv$xstream_message_tracking gv$xstream_transaction
 
Related Topics
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-2013 Daniel A. Morgan All Rights Reserved