Oracle Scripts
  • Register

HEADLINING: We are Oracle Gold Partners, training courses designed by Five Star Rated Oracle Press Book Authors & recognised Oracle ACE's. Contact us for your training needs.

User Rating: 0 / 5

Star inactiveStar inactiveStar inactiveStar inactiveStar inactive
Oracle always maintains a set of virtual tables that record current database activity during database operations. These tables are created internally 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

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
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
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
V$SESSTAT Session-level summary of resource usage since
session startup
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
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
Advisory information collected by MTTR advisory,
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
Execution statistics of each operation in the
execution plan
Concatenates information in V$SQL_PLAN with
execution statistics from V$SQL_PLAN_
V$SQLTEXT SQL text of statements in the shared pool
Status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter 

Add comment

Security code


All content and images shown on this site is for demo, presentation purposes only. This site is intended to exemplify a live website and does not make any claim of any kind to the validity of non-Shape5 content, images or posts published. 

Photography is used for this demo only, and is not included with this template or any membership. You may not use these images for your own without purchase. Links to purchase from their copyright owners will be provided.