Dynamic Performance Tables

Dynamic Performance Tables

Throughout its operation, Oracle maintains a set of virtual tables that record current
database activity. These tables are created by Oracle and are called dynamic
performance tables.

Database administrators can query and create views on the tables and grant access
to those views to other users. These views are called fixed views because they
cannot be altered or removed by the database administrator.

SYS owns the dynamic performance tables. By default, they are available only to
the user SYS and to users granted SELECT ANY TABLE system privilege, such as
SYSTEM. Their names all begin with V_$. Views are created on these tables, and
then public synonyms are created for the views. The synonym names begin
with V$.

Each view belongs to one of the following categories:

1.Current State Views

2.Counter/Accumulator Views

3. Information Views

Current State Views

The views listed in Table 24–1 give a picture of what is currently happening on the
system.

Counter/Accumulator Views
These views keep track of how many times some activity has occurred since
instance/session startup. Select from the view directly to see activity since startup.

Table 24–1 Current State Views

Fixed View Description

V$LOCK Locks currently held/requested on the instance
V$LATCHHOLDER Sessions/processes holding a latch
V$OPEN_CURSOR Cursors opened by sessions on the instance
V$SESSION Sessions currently connected to the instance
V$SESSION_WAIT Different resources sessions are currently waiting for

Counter/Accumulator Views

These views keep track of how many times some activity has occurred since
instance/session startup. Select from the view directly to see activity since startup.

If you are interested in activity happening in a given time interval, then take a
snapshot before and after the time interval, and the delta between the two
snapshots provides the activity during that time interval. This is similar to how
operating system utilities like sar, vmstat, and iostat work. Tools provided by
Oracle, like Statspack and BSTAT/ESTAT, do this delta to provide a report of
activity in a given interval.

Note: Snapshots should be taken during steady-state, not
immediately after system startup. Extra overhead is incurred
during system ramp-up, which may not accurately reflect the
performance of the system at steady-state.

Table 24–2 Summary Since Session Startup

Fixed View Description

V$DB_OBJECT_CACHE Object level statistics in shared pool
V$FILESTAT File level summary of the I/O activity
V$LATCH Latch activity summary
V$LATCH_CHILDREN Latch activity for child latches
V$LIBRARYCACHE Namespace level summary for shared pool
V$LIBRARY_CACHE_
MEMORY
Summary of the current memory use of the library
cache, by library cache object type
V$MYSTAT Resource usage summary for your own session
V$ROLLSTAT Rollback segment activity summary
V$ROWCACHE Data dictionary activity summary
V$SEGMENT_
STATISTICS
User-friendly DBA view for real-time monitoring of
segment-level statistics
V$SEGSTAT High-efficiency view for real-time monitoring of
segment-level statistics
V$SESSION_EVENT Session-level summary of all the waits for current
sessions
V$SESSTAT Session-level summary of resource usage since
session startup
V$LIBRARY_CACHE_
MEMORY
Simulation of the shared pool's LRU list mechanism
V$SQL Child cursor details for V$SQLAREA
V$SQLAREA Shared pool details for statements/anonymous
blocks
V$SYSSTAT Summary of resource usage
V$SYSTEM_EVENT Instance wide summary of resources waited for
V$UNDOSTAT Histogram of undo usage. Each row represents a
10-minute interval.
V$WAITSTAT Break down of buffer waits by block class


Information ViewsIn information views, the information is not as dynamic as in the current state view.
Hence, it does not need to be queried as often as the current state views

Table 24–3 Information Views
Fixed View Description
V$MTTR_TARGET_
ADVICE
Advisory information collected by MTTR advisory,
when FAST_START_MTTR_TARGET is set
V$PARAMETER and
V$SYSTEM_
PARAMETER
Parameters values for your session
Instance wide parameter values
V$PROCESS Server processes (background and foreground)
V$SEGSTAT_NAME Statistics property view for segment-level statistics
V$SQL_PLAN Execution plan for cursors that were recently
executed
V$SQL_PLAN_
STATISTICS
Execution statistics of each operation in the
execution plan
V$SQL_PLAN_
STATISTICS_ALL
Concatenates information in V$SQL_PLAN with
execution statistics from V$SQL_PLAN_
STATISTICS and V$SQL_WORKAREA
V$SQLTEXT SQL text of statements in the shared pool
V$STATISTICS_
LEVEL
Status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter