What is Oracle Dynamic Performance Views
Oracle Dynamic Performance Views is a set of views(V_$) which are defined over very low level Oracle views called the X$ views View. We have views for complete set of operations. V$ names are synonyms which point to the V_$ views
What is X$view :The X$ views are very low level representations of Oracle internal database structures. They are not tables, but database representation of C structures that are maintained by the Oracle kernel.
The following key v$ dynamic views allow the DBA to quickly drill down into the Oracle 11g database engine to quickly assess the database status
V$INSTANCE
V$OPTION
V$LICENSE
V$CONTROLFILE
V$TABLESPACE
V$DATAFILE
V$LOG
V$LOGFILE
V$ARCHIVED_LOG
V$TEMPFILE
Oracle 11g New Features
The follow v$ views are used by the new 11g result cache:
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_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_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 configurationwithin Oracle 11g.
V$NFS_OPEN_FILES
V$NFS_LOCKS
V$IOSTAT_NETWORK
Oracle 11g Backup and Recovery
Backup and Recoveryis 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_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_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_SERVER
V$STREAMS_APPLY_READER
V$STREAMS_CAPTURE
V$STREAMS_TRANSACTION
V$STREAMS_MESSAGE_TRACKING
11g RAC
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_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 Eventsfor 11g
V$WAITCLASSMETRIC
V$WAITCLASSMETRIC_HISTORY
V$WAITSTAT
V$WAIT_CHAINS
Oracle 11g Concurrency and SQL Tuning
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$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