Dynamic Performance (V$)
Views
The Oracle Server contains a set of underlying
views that are maintained by the server and accessible to the database
administrator user SYS. These views are called dynamic performance views because they are continuously
updated while a database is open and in use, and their contents relate
primarily to performance.
The file CATALOG.SQL contains definitions of the
views and public synonyms for the dynamic performance views. You must run CATALOG.SQL
to create these views and synonyms.
Dynamic performance views are identified by the
prefix V_$. Public synonyms for these views have the prefix V$. Database
administrators or users should only access the V$ objects, not the V_$ objects.
The dynamic performance views are used by
Enterprise Manager and Oracle Trace, which is the primary interface for
accessing information about system performance.
This view displays objects in the database that are
currently locked and the sessions that are accessing them.
This view maps instance names to instance numbers
for all instances that have the database currently mounted.
This view describes statistics for the queues in
the database.
This view contains information on redo log files in
need of archiving. Each row provides information for one thread. This
information is also available in V$LOG. Oracle recommends that you use
V$LOG.
displays log file information from the control file. Column,
Datatype, Description. GROUP#, NUMBER, Log group number.
This view describes, for the current instance, all
the archive log destinations, their current value, mode, and status.
This view displays archived log information from
the control file including archive log names.
This view displays the backup status of all online
datafiles.
` This view displays information about
corruptions in datafile backups from the controlfile. Note that corruptions are
not tolerated in the controlfile and archived log backups.
This view displays backup datafile and backup
controlfile information from the controlfile.
This view displays information about supported
backup devices.
This view displays information
about backup pieces from the controlfile. Each backup set consist of one or
more backup pieces.
This view displays information
about archived logs in backup sets from the controlfile. Note that online redo
logs cannot be backed up directly; they must be archived first to disk and then
backed up. An archive log backup set can contain one or more archived logs.
This view displays backup set
information from the controlfile. A backup set record is inserted after the
backup set is successfully completed.
This view describes the background
processes.
This is a Parallel Server view.
This view gives the status and number of pings for every buffer in the SGA.
This view displays information
about all buffer pools available for the instance. The "sets" pertain
to the number of LRU latch sets.
This is a Parallel Server view. This view contains information from the block header of each block in the SGA of the current instance as related to particular database objects.
This is a Parallel Server view.
V$CACHE_LOCK is similar to V$CACHE, except for the platform-specific
lock manager identifiers.
This view contains information about
virtual circuits, which are user connections to the database through
dispatchers and servers.
V$CLASS_PING displays the number
of blocks pinged per block class. Use this view to compare contentions for
blocks in different classes.
This view displays features in use
by the database instance that may prevent downgrading to a previous release.
This view lists the permanent
features in use by the database that will prevent moving back to an earlier
release.
This view lists the names of the
control files.
This view displays information
about the controlfile record sections.
This view displays information
about datafile copy corruptions from the controlfile.
This view displays information
useful for predicting the number of additional cache misses that would occur if
the number of buffers in the cache were reduced.
This view contains database
information from the control file.
This view contains datafile
information from the control file.
This view displays datafile copy
information from the controlfile.
This view displays datafile
information from the datafile headers.
This view lists all datafiles
making up the database. This view is retained for historical compatibility.
This view describes all database
links (links with IN_TRANSACTION = YES) opened by the session issuing the query
on V$DBLINK. These database links must be committed or rolled back before being
closed.
This view displays database
objects that are cached in the library cache. Objects include tables, indexes,
clusters, synonym definitions, PL/SQL procedures and packages, and triggers.
This view displays the pipes that
are currently in this database.
This view displays information
about deleted archived logs, datafile copies and backup pieces from the
controlfile. The only purpose of this view is to optimize the recovery catalog
resync operation. When an archived log, datafile copy, or backup piece is
deleted, the corresponding record is marked deleted.
This view provides information on
the dispatcher processes.
This view provides rate statistics
for the dispatcher processes.
V$DLM_CONVERT_LOCAL displays the
elapsed time for the local lock conversion operation.
V$DLM_CONVERT_REMOTE displays the
elapsed time for the remote lock conversion operation.
V$DLM_LATCH displays statistics
about DLM latch performance. The view includes totals for each type of latch
rather than statistics for each individual latch. Ideally, the value
IMM_GETS/TTL_GETS should be as close to 1 as possible.
This is a Parallel Server view.
V$DLM_LOCKS lists information of all locks currently known to lock manager that
are being blocked or blocking others.
V$DLM_MISC displays miscellaneous
DLM statistics.
This view displays which
privileges are enabled. These privileges can be found in the table
SYS.SYSTEM_PRIVILEGES_MAP.
This view displays all locks owned
by enqueue state objects. The columns in this view are identical to the columns
in V$LOCK.
This view contains information
about wait events.
This view displays information on
parallel query execution.
This view displays detailed information
on the parallel query execution tree location.
V$FALSE_PING is a Parallel Server
view. This view displays buffers that may be getting false pings.
The view V$FILE_PING displays the
number of blocks pinged per datafile. This information in turn can be used to
determine access patterns to existing datafiles and deciding new mappings from
datafile blocks to PCM locks.
This view contains information
about file read/write statistics.
This view displays all dynamic
performance tables, views, and derived tables in the database. Some V$ tables
refer to real tables and are therefore not listed.
This view contains the definitions
of all the fixed views (views beginning with V$). Use this table with caution.
Oracle tries to keep the behavior of fixed views the same from release to
release, but the definitions of the fixed views can change without notice.
This view displays information on
the currently active global transactions.
This view displays the columns in
dynamic performance tables that are indexed (X$ tables). The X$ tables can
change without notice. Use this view only to write queries against fixed views
(V$ views) more efficiently.
This view displays the state of
the current instance. This version of V$INSTANCE is not compatible with earlier
versions of V$INSTANCE.
This view lists statistics for
non-parent latches and summary statistics for parent latches. That is, the
statistics for a parent latch include counts from each of its children.
This view contains information
about the current latch holders.
This view contains information
about decoded latch names for the latches shown in V$LATCH. The rows of
V$LATCHNAME have a one-to-one correspondence to the rows of V$LATCH.
This view contains statistics
about child latches. This view includes all columns of V$LATCH plus the CHILD#
column. Note that child latches have the same parent if their LATCH# columns
match each other.
This view contains statistics
about missed attempts to acquire a latch.
This view contains statistics
about the parent latch. The columns of V$LATCH_PARENT are identical to those in
V$LATCH.
This view contains statistics
about library cache performance and activity.
This view contains information about
license limits.
This view contains SQL*Loader
statistics compiled during the execution of a direct load. These statistics
apply to the whole load. Any SELECT against this table results in "no rows
returned" since you cannot load data and do a query at the same time.
SQL*Loader statistics compiled
during the execution of a direct load. These statistics apply to the current
table. Any SELECT against this table results in "no rows returned"
since you cannot load data and do a query at the same time.
This view lists the locks
currently held by the Oracle Server and outstanding requests for a lock or
latch.
This is a Parallel Server view.
V$LOCK_ACTIVITY displays the DLM lock operation activity of the current
instance. Each row corresponds to a type of lock operation.
This is a Parallel Server view.
There is one entry in v$LOCK_ELEMENT for each PCM lock that is used by the
buffer cache. The name of the PCM lock that corresponds to a lock element is
{`BL', index, class}.
This view lists all locks acquired
by every transaction on the system.
This is a Parallel Server view.
Use this view to find the locks that protect multiple buffers, each of which
has been either force-written or force-read at least 10 times. It is very
likely that those buffers are experiencing false pings due to being mapped to
the same lock.
This view contains log file
information from the control files.
This view contains information
about redo log files.
This view contains log history
information from the control file. This view is retained for historical
compatibility. Use of V$LOG_HISTORY is recommended instead.
This view contains log
history information from the control file.
This is a Trusted Oracle Server
view that lists Trusted Oracle Server-specific initialization parameters. For
more information, see your Trusted Oracle documentation.
This view contains information for
tuning the multi-threaded server.
This view contains statistics on
the current session.
This view contains current values
of NLS parameters.
This view lists all valid values
for NLS parameters.
This view can be used to determine
what objects are depended on by a package, procedure, or cursor that is
currently loaded in the shared pool.
This view displays datafile
offline information from the controlfile. Note that the last offline range of
each datafile is kept in the DATAFILE record. IMMEDIATE.
This view lists cursors that each
user session currently has opened and parsed.
This view lists options that are
installed with the Oracle Server.
This view lists information about
initialization parameters.
This is a Parallel Server view.
The V$PING view is identical to the V$CACHE view but only displays blocks that
have been pinged at least once.
This view lists session statistics
for parallel queries.
This view lists statistics for
each of the active parallel query servers on an instance.
This view lists system statistics
for parallel queries.
This view contains statistics on
parallel query operations. The statistics are compiled after the query
completes and only remain for the duration of the session.
This view contains information
about the currently active processes. While the LATCHWAIT column indicates what
latch a process is waiting for, the LATCHSPIN column indicates what latch a
process is spinning on.
This view lists users who have
been granted SYSDBA and SYSOPER privileges as derived from the password file.
This view contains information on
the multi-thread message queues.
This view displays information
useful for estimating the performance of a large cache.
This view displays the status of
files needing media recovery.
V$RECOVERY_FILE_STATUS contains
one row for each datafile for each RECOVER command. This view contains useful
information only for the Oracle process doing the recovery.
This view lists information about
archived logs that are needed to complete media recovery.
V$RECOVERY_PROGRESS can be used to
track database recovery operations to ensure that they are not stalled, and
also to estimate the time required to complete the operation in progress.
V$RECOVERY_STATUS contains
statistics of the current recovery process. This view contains useful
information only for the Oracle process doing the recovery.
This view lists statistics for the
histogram of MTS dispatcher request times, divided into 12 buckets, or ranges
of time. The time ranges grow exponentially as a function of the bucket number.
This view contains resource name
and address information.
This view displays information
about global resource use for some of the system resources. Use this view to
monitor the consumption of resources so that you can take corrective action, if
necessary.
This view lists the names of all
online rollback segments. It can only be accessed when the database is open.
This view contains rollback
segment statistics.
This view displays statistics for
data dictionary activity. Each row contains statistics for one data dictionary
cache.
This view lists session
information for each current session.
This view displays information
about network connections for the current session.
This view displays information on
cursor usage for the current session. Note: the V$SESSION_CURSOR_CACHE view is
not a measure of the effectiveness of the SESSION_CACHED_CURSORS initialization
parameter.
This view lists information on
waits for an event by a session.
This view displays the status of certain long-running operations. It provides progression reports on operations using the columns SOFAR and TOTALWORK.
V$SESSION_OBJECT_CACHE
This view displays object cache
statistics for the current user session on the local server (instance).
This view lists the resources or events for which active sessions are waiting.
This view lists user session
statistics. To find the name of the statistic associated with each statistic
number (STATISTIC#)
This view lists I/O statistics for
each user session.
This view contains summary
information on the System Global Area.
This view contains detailed
information on the System Global Area.
This fixed view lists statistics
that help you tune the reserved pool and space within the shared pool.
This view contains information on
the shared server processes.
This view contains information
about every sort segment in a given instance. The view is only updated when the
tablespace is of the TEMPORARY type.
This view describes sort usage.
This view lists statistics on
shared SQL area without the GROUP BY clause and contains one row for each child
of the original SQL text entered.
This view displays the actual bind
data sent by the client for each distinct bind variable in each cursor owned by
the session querying this view if the data is available in the server.
This view displays bind metadata
provided by the client for each distinct bind variable in each cursor owned by
the session querying this view.
This view displays debugging
information for each cursor associated with the session querying this view.
This view displays information
about the cursor shared memory snapshot. Each SQL statement stored in the
shared pool has one or more child objects associated with it. Each child object
has a number of parts, one of which is the context heap, which holds, among
other things, the query plan.
This view lists statistics on
shared SQL area and contains one row per SQL string. It provides statistics on
SQL statements that are in memory, parsed, and ready for execution.
This view contains the text of SQL
statements belonging to shared SQL cursors in the SGA.
This view is identical to the V$SQLTEXT
view except that, to improve legibility, V$SQLTEXT_WITH_NEWLINES does not
replace newlines and tabs in the SQL statement with spaces.
This view displays decoded
statistic names for the statistics shown in the V$SESSTAT and V$SYSSTAT table
This view displays information
about the subordinate caches currently loaded into library cache memory. The
view walks through the library cache, printing out a row for each loaded
subordinate cache per library cache object.
This view lists system statistics.
To find the name of the statistic associated with each statistic number
(STATISTIC#).
This view displays similar
information to the V$SESSION_CURSOR_CACHE view except that this information is
system wide.
This view contains information on
total waits for an event.
This view contains information on system
parameters.
This view displays tablespace
information from the controlfile.
This view contains thread
information from the control file.
This view lists the elapsed time
in hundredths of seconds. Time is measured since the beginning of the epoch,
which is operating system specific, and wraps around to 0 again whenever the
value overflows four bytes (roughly 497 days).
This view lists the active
transactions in the system.
V$TRANSACTION_ENQUEUE displays
locks owned by transaction state objects.
This view lists the sizes of
various database components for use in estimating data block capacity.
Version numbers of core library
components in the Oracle Server. There is one row for each component.
V$WAITSTAT
This view lists block contention
statistics. This table is only updated when timed statistics are enabled.
No comments:
Post a Comment