Oracle v$ View List

 11g Dynamic v$ Performance Views

With each new major release and version of Oracle, dozens if not hundreds of new dynamic performance v$ views are added. For instance, in Oracle 10gR2, there are 398 dynamic performance v$ views. In Oracle 11gR1 there are now 484 dynamic performance v$ views which means that between 10gR2 and 11gR1, there are 86 new v$ views in 11gR1.

So how does one find out what all of the dynamic performance views are within Oracle 11g? Well, one needs a query such as the following to run against the v$fixed_table to obtain the full listing.

Oracle creates the v$ views from the x$ structures.  The actual v$ view create view definitions are located in $ORACLE_HOME/rdbms/admin directory so you can see how the v$ views are built on the x$ structures.

Oracle 11g New Features

The follow v$ views are used by the new 11g result cache:

v$result_cache_statistics
v$result_cache_memory
v$result_cache_objects
v$result_cache_dependency
v$client_result_cache_stats                                                
 
The Oracle 11g RAT suite includes database replay and the following v$ view provides details on usage and status for this key new 11g feature with Oracle.

V$WORKLOAD_REPLAY_THREAD

For the new 11g feature for the Incident Repair component of the 11g ADR, there are the following v$ views:

v$ir_failure
v$ir_repair                                                                
v$ir_manual_checklist                                                      
v$ir_failure_set                                                            

The 11g health monitor (HM) is linked to the following v$ views listed below:

v$hm_check
v$hm_check_param                        
v$hm_run                                                                  
v$hm_finding                                                               
v$hm_recommendation         
v$hm_info                                                                  

V$ Views-Oracle 11g NFS New Features

Oracle 11g provides a new feature to setup NFS based files within Oracle 11g. The following v$ views provide the details for NFS configuration within Oracle 11g.

v$nfs_clients
v$nfs_open_files
v$nfs_locks
v$iostat_network

Oracle 11g Backup and Recovery

Backup and Recovery is the most important function of the serious Oracle DBA and can mean the difference between life and death in terms of corporate safety and job security. The following v$ views provide details for Oracle 11g Backup and Recovery.

v$rman_backup_subjob_details
v$rman_backup_job_details
v$backup_set_details
v$backup_piece_details
v$backup_copy_details
$backup
v$recovery_status
v$recovery_file_status
v$backup_set
v$backup_piece
v$backup_datafile
v$backup_redolog
v$backup_corruption
v$backup_device
v$backup_spfile
v$backup_sync_io
v$backup_async_io
v$recover_file
v$rman_status
v$rman_output
v$backup_datafile_details
v$backup_controlfile_details
v$backup_archivelog_details
v$backup_spfile_details
v$backup_datafile_summary
v$backup_controlfile_summary
v$backup_archivelog_summary
v$backup_spfile_summary
v$backup_set_summary
v$recovery_progress
v$rman_backup_type
v$rman_configuration

Oracle 11g RAC and ASM, Oracle 11g Data Guard and 11g Streams

Oracle 11g RAC and ASM provide highly available robust architectures for performance, service and reliability as part of the Oracle Maximum Availability Architecture (MAA). The following v$ views provide details on these key HA technologies.

11g Data Guard

v$dataguard_config
v$dataguard_status
v$managed_standby
v$logstdby
v$logstdby_stats
v$logstdby_transaction
v$logstdby_process
v$logstdby_progress
v$logstdby_state


11g Streams

v$streams_apply_coordinator
v$streams_apply_server
v$streams_apply_reader
v$streams_capture
v$streams_transaction
v$streams_message_tracking

11g RAC

v$cluster_interconnects
v$configured_interconnects
v$dynamic_remaster_stats
v$dlm_misc
v$dlm_latch
v$dlm_convert_local
v$dlm_convert_remote
v$ges_enqueue
v$ges_blocking_enqueue
v$dlm_all_locks
v$dlm_locks
v$dlm_ress
v$global_blocked_locks 

11g ASM

v$asm_template
v$asm_alias
v$asm_file
v$asm_client
v$asm_diskgroup
v$asm_diskgroup_stat
v$asm_disk
v$asm_disk_stat
$asm_disk_iostat
v$asm_operation
v$asm_attribute

Performance Tuning Oracle 11g

Performance tuning is a complex task that daunts many Oracle DBAs. It is as much an art as a science. While GUI tools such as Oracle Enterprise Manager and Quest TOAD provide a nice slick graphical interface, to dig into the internal nuts and bolts of database performance, the serious Oracle performance analyst relies on reports generated by v$ dynamic performance views from within the Oracle 11g data dictionary. The following v$ views provide insight into database tuning for Oracle 11g.

Wait Events for 11g

v$session
v$waitclassmetric
v$waitclassmetric_history
v$waitstat
v$wait_chains

Oracle 11g Concurrency and SQL Tuning

v$lock
v$sql
v$sqlarea
v$sesstat
v$mystat
v$sess_io
v$sysstat
v$statname
v$osstat
v$active_session_history
v$active_sess_pool_mth
v$session_wait
v$session_wait_class
v$system_wait_class
v$transaction
v$locked_object
v$latch
v$latch_children
v$latch_parent
v$latchname
v$latchholder
v$latch_misses
v$enqueue_lock
v$transaction_enqueue
v$sys_optimizer_env
v$ses_optimizer_env
v$sql_optimizer_env
v$sql_plan
v$sql_plan_statistics
v$sql_plan_statistics_all


Oracle 11g Memory Tuning

v$sga
v$sgastat
v$sgainfo
v$sga_current_resize_ops
v$sga_resize_ops
v$sga_dynamic_components
v$sga_dynamic_free_memory
v$pgastat
v$sql_workarea_histogram
v$pga_target_advice_histogram                                              
v$pga_target_advice
v$memory_current_resize_ops
v$memory_resize_ops
v$memory_dynamic_components
v$library_cache_memory
v$shared_pool_advice
v$java_library_cache_memory
v$java_pool_advice
v$streams_pool_advice

 

---

 

For a description of the DBA wait event views, see the "free 11g poster" by UNISYS.

Here is a list of the v$ views with notes from my site: (Work in progress):

v$archive_dest
v$archive_dest_status
v$archive_gap
v$archive_processes
v$archived_log
v$bh
v$buffer_pool
v$buffer_pool_statistics
v$controlfile
v$controlfile_record_section
v$database
v$datafile
v$datafile_header
v$dataguard_status
v$db_object_cache
v$enqueue_stat
v$event_name
v$eventmetric
v$filemetric
v$filestat
v$fixed_table
v$fixed_view_definition
v$instance
v$latch
v$librarycache
v$lock
v$locked_object
v$log
v$log_history
v$logfile
v$logmnr_contents
v$logstdby
v$managed_standby
v$mystat
v$nls_parameters
v$nls_valid_values
v$object_usage
v$open_cursor
v$option
v$parameter
v$pgastat
v$process
v$pwfile_users
v$recover_file
v$reserved_words
v$resource_limit
v$rollname
v$rollstat
v$session
v$session_event
v$session_longops
v$session_wait
v$session_wait_history
v$sessmetric
v$sesstat
v$sga
v$sga_dynamic_components
v$sga_resize_ops
v$sgastat
v$sort_segment
v$sort_usage
v$spparameter
v$sql
v$sql_bind_capture
v$sql_bind_data
v$sql_cursor
v$sql_plan
v$sql_text_with_newlines
v$sql_workarea
v$sqlarea
v$sqltext
v$sqltext_with_newlines
v$standby_log
v$statname
v$sysaux_occupants
v$sysmetric
v$sysmetric_history
v$sysstat
v$system_event
v$tempfile
v$tempseg_usage
v$tempseg_usage
v$tempstat
v$thread
v$timer
v$timezone_names
v$transaction
v$transportable_platform
v$undostat
v$version
v$waitstat