Contact Us

Oracle Scripts
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

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 


Overall Rating (0)

0 out of 5 stars
  • No comments found

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Feb 2017  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
    1  2  3  4

Enquire For Training

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner