Exploring Oracle Internals -
General Oracle 11g Database Administration
One key task of the Oracle database professional is to perform
daily monitoring to ensure that the database environment is healthy
for Oracle 11g. 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$DATABASE
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_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
configurationwithin Oracle 11g.
V$NFS_CLIENTS
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_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 Eventsfor 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
Next, a useful way to generate the complete listing for all
Oracle 11g v$ dynamic performance views
within the Oracle database environment will be shown.
|
|
 |
|
Frustrated by the Hidden or
Undocumented Features of Oracle?
The landmark book
"Exploring
Oracle Internals: Tips and Tricks for the Oracle DBA"
will take you far beyond the standard features of the Oracle
database into the hidden and undocumented realms formerly
reserved for Oracle insiders. You will discover valuable
tips and tricks for mastering your Oracle database, and you can
buy it for
30% off directly from the publisher.
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
