24
Dynamic Performance Views for Tuning

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

Dynamic performance tables are not true tables, and they should not be accessed by most users. However, 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$.

This chapter provides detailed information on several of these views that can help you tune your system and investigate performance problems.

Each view belongs to one of the following categories:

Current State Views

These views give a picture of what is currently happening on the system.

Table 24-1 Current State Views

Fixed View:	Description:
V$LOCK	Locks currently held/requested on the instance
V$LATCH_HOLDER	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:

To avoid the extra overhead incurred as caches are loaded, do not take snapshots immediately after system startup.

Table 24-2 Summary Since Session Startup

Fixed View:	Description:
V$MYSTAT	Resource usage summary for your own session
V$SESSION_EVENT	Session-level summary of all the waits for current sessions
V$SESSTAT	Session-level summary of resource usage since session startup

Table 24-3 Summary Since Instance 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	Aggregate summary for each type of latch
V$LIBRARYCACHE	Namespace level summary for shared pool
V$ROLLSTAT	Rollback activity summary
V$ROWCACHE	Data dictionary activity summary
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	Undo space summary for a ten minute interval
V$WAITSTAT	Break down of buffer waits by class

Information Views

In 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-4 Information Views

Fixed View:	Description:
V$PARAMETER and V$SYSTEM_PARAMETER	Parameters values for your session Instance wide parameter values
V$PROCESS	Server processes (background and foreground)
V$SQL_PLAN	Execution plan for cursors that were recently executed
V$SQLTEXT	SQL text of statements in the shared pool

V$DB_OBJECT_CACHE

This view provides object level statistics for objects in the library cache (shared pool). This view provides more details than V$LIBRARYCACHE and is useful for finding active objects in the shared pool.

Useful Columns for V$DB_OBJECT_CACHE

Most of the columns of this table provide current state information.

OWNER: Object owner
NAME: Object name (First 1000 characters of SQL text for anonymous blocks/cursors)
TYPE: Type of object (for example, sequence, procedure, function, package, package body, trigger)
KEPT: Tells if the object is pinned in the shared pool (yes, no)
SHARABLE_MEM: Amount of sharable memory used
PINS: Sessions currently executing this object
LOCKS: Sessions currently locking this object

Instantaneous State Columns

The columns below keep statistics on the object since it's first load:

EXECUTIONS: Number of executions by this object
LOADS: Number of times this object had to be loaded
INVALIDATIONS: Number of times this object was invalidated

Example - Summary of Shared Pool Executions and Memory Usage

The following query shows the distribution of the usage (executions) and the shared pool memory across different type of objects. It also shows if any of the objects have been pinned in the shared pool using the procedure DBMS_SHARED_POOL.KEEP().

SELECT type, kept, COUNT(*), SUM(executions), SUM(sharable_mem)
  FROM V$DB_OBJECT_CACHE
 GROUP BY kept, type;

Example - Finding Objects with Large Number of Loads

SELECT owner, name, executions, sharable_mem, kept, loads
FROM V$DB_OBJECT_CACHE
WHERE loads > 1
OR invalidations > 0
ORDER BY loads DESC

Example - Finding Large Unpinned Objects

The following query finds all objects using large amounts of memory or executed multiple times. They can be pinned using DBMS_SHARED_POOL.KEEP().

SELECT owner, name, executions, sharable_mem, kept
  FROM V$DB_OBJECT_CACHE
 WHERE ( executions > 10
       OR sharable_mem > 102400 )
   AND kept = `NO'
 ORDER BY 3 desc, 4 DESC

V$FILESTAT

This view keeps a summary of physical I/O requests for each file. This is useful in isolating where the I/O activity is happening if the bottleneck is I/O related. V$FILESTAT shows the following information for database I/O (but not for log file I/O):

Number of physical reads and writes
Number of blocks read and written
Total I/O time for reads and writes

The numbers reflect activity since the instance startup. If two snapshots are taken, then the differences in the statistics provides the I/O activity for the time interval.

Useful Columns for V$FILESTAT

FILE#: Number of the file
PHYRDS: Number of physical reads done
PHYBLKRD: Number of physical blocks read
PHYWRTS: Number of physical writes done
PHYBLKWRT: Number of physical blocks written

Note:

Physical reads and blocks read can be different because of multiblock read calls.
Physical writes and blocks written can differ because of direct writes by processes.
Sum(Physical blocks read) should correlate closely with physical reads from V$SYSSTAT.
Sum(Physical blocks written) should correlate closely with physical writes from V$SYSSTAT.
Reads (into buffer cache as well as direct reads) are done by server processes. Writes from buffer cache are handled only by the DBWR. The direct writes are handled by the server processes.

Join Columns for V$FILESTAT

Column:	View:	Joined Column(s):
FILE#	DBA_DATA_FILES	FILE_ID

Example - Checking Oracle Datafile I/O

The following query monitors the values of physical reads and physical writes over some period of time while your application is running:

SELECT NAME, PHYRDS, PHYWRTS
FROM V$DATAFILE df, V$FILESTAT fs
WHERE df.FILE# = fs.FILE#;

The above query also retrieves the name of each datafile from the dynamic performance view V$DATAFILE. Sample output might look like the following:

NAME                                             PHYRDS    PHYWRTS
-------------------------------------------- ---------- ----------
/oracle/ora70/dbs/ora_system.dbf                   7679       2735
/oracle/ora70/dbs/ora_temp.dbf                       32        546

The PHYRDS and PHYWRTS columns of V$FILESTAT can also be obtained through SNMP.

The total I/O for a single disk is the sum of PHYRDS and PHYWRTS for all the database files managed by the Oracle instance on that disk. Determine this value for each of your disks. Also, determine the rate at which I/O occurs for each disk by dividing the total I/O by the interval of time over which the statistics were collected.

Note:

Although Oracle records read and write times accurately, a database that is running on Unix file system (UFS) might not reflect true disk accesses. For example, the read times might not reflect a true disk read, but rather a UFS cache hit. However, read and write times should be accurate for raw devices. Additionally, write times are only recorded per batch, with all blocks in the same batch given the same time after the completion of the write I/O.

Example - Finding the Files with Large Numbers of Multiblock Reads

The following example is useful for finding tablespaces that might be getting hit by large number of scans.

SELECT t.tablespace_name
      ,SUM(a.phyrds-b.phyrds)
       /MAX(86400*(a.snap_date-b.snap_date)) "Rd/sec"
      ,SUM(a.phyblkrd-b.phyblkrd)
       /greatest(SUM(a.phyrds-b.phyrds),1) "Blk/rd"
      ,SUM(a.phywrts-b.phywrts)
       /MAX(86400*(a.snap_date-b.snap_date)) "Wr/sec"
      ,SUM(a.phyblkwrt-b.phyblkwrt)
       /greatest(SUM(a.phywrts-b.phywrts),1) "Blk/wr"
  FROM snap_filestat a, snap_filestat b, dba_data_files t
 WHERE a.file# = b.file#
   AND a.snap_id = b.snap_id + 1
   AND t.file_id = a.file#
 GROUP BY t.tablespace_name
HAVING sum(a.phyblkrd-b.phyblkrd)
       /greatest(SUM(a.phyrds-b.phyrds),1) > 1.1
    OR SUM(a.phyblkwrt-b.phyblkwrt)
       /greatest(SUM(a.phywrts-b.phywrts),1) > 1.1
 ORDER BY 3 DESC, 5 DESC;

TABLESPACE_N Rd/sec Blk/rd Wr/sec Blk/wr
------------ ------ ------ ------ ------
TEMP            2.3   19.7    1.9   24.7
AP_T_02       287.1    7.8     .0    1.0
AP_T_01        12.9    4.0     .2    1.0
APPLSYS_T_01   63.3    2.2     .4    1.0
PO_T_01       313.5    2.1     .2    1.0
RECEIVABLE_T  401.0    1.5    2.4    1.0
SHARED_T_01     9.2    1.3     .4    1.0
SYSTEM         45.2    1.3     .3    1.0
PER_T_01       48.0    1.2     .0     .0
DBA_T_01         .2    1.0     .4    1.4

You can see that most of the multiblock reads and writes are going to TEMP tablespace, due to large sorts going to disk. Other tablespaces are getting multiblock reads due to full table scans.

See Also:

Chapter 20, "Oracle Tools to Gather Database Statistics" for an example of how to gather file I/O data.

V$LATCH

This view keeps a summary of statistics for each type of latch since instance startup. It is useful for identifying the area within SGA experiencing problems when latch contention is observed in V$SESSION_WAIT.

Useful Columns for V$LATCH

NAME: Latch name
IMMEDIATE_GETS: Requests for the latch in immediate mode
IMMEDIATE_MISSES: IMMEDIATE_GETS that failed
GETS: Requests for the latch in a willing to wait mode
MISSES: GETS that did not obtain the latch on first try
SPIN_GETS: GETS that got the latch within SPIN_GET tries and did not have to sleep
SLEEP1-SLEEP3: GETS that succeeded only after sleeping one to three times
SLEEP4: GETS that only succeeded after sleeping four or more times

Join Columns for V$LATCH

Columns:	Fixed View:	Joined Column(s):
NAME	V$LATCH_CHILDREN V$LATCHHOLDER V$LATCHNAME	NAME
NAME	V$LATCH_MISSES	PARENT_NAME
LATCH#	V$LATCH_CHILDREN V$LATCHNAME	LATCH#

V$LATCH Example

In the following example, a table is created to hold data queried from V$LATCH:

CREATE TABLE snap_latch as 
SELECT 0 snap_id, sysdate snap_date, a.* 
  FROM V$LATCH a; 
 ALTER TABLE snap_latch add 
     ( constraint snap_filestat primary key (snap_id, file#) ); 

SELECT name, (a.gets-b.gets)/1000 "Gets(K)",
       a.gets-b.gets)/(86400*(a.snap_date-b.snap_date)) "Get/s",
       100*(a.misses-b.misses)/(a.gets-b.gets) MISS,
       100*(a.spin_gets-b.spin_gets)/(a.misses-b.misses) SPIN,
       (a.gets-b.gets)/1000 "Gets(K)",
       (a.immediate_gets-b.immediate_gets)/1000 "Iget(K)",
       (a.immediate_gets-b.immediate_gets)/
       (86400*(a.snap_date-b.snap_date)) "IGet/s",
       100*(a.immediate_misses-b.immediate_misses)/
       (a.immediate_gets-b.immediate_gets) IMISS
  FROM snap_latch a, snap_latch b
 WHERE a.snap_id = b.snap_id + 1
   AND ( (a.misses-b.misses) > 0.001*(a.gets-b.gets)
       or (a.immediate_misses-b.immediate_misses) > 
       0.001*(a.immediate_gets-b.immediate_gets))
ORDER BY 2 DESC;

INSERT INTO snap_latch 
SELECT 1, sysdate, a.* 
  FROM V$LATCH a;

The example shows the latch statistics obtained by doing a delta over a period of one hour (like with the V$FILESTAT numbers). Those latches that had misses less than 0.1% of the gets have bee filtered out.


NAME                Gets(K)   Get/s  MISS   SPIN IGets(K)  IGet/s IMISS
------------------ -------- ------- ----- ------ -------- ------- -----
cache buffers chai  255,272  69,938   0.4   99.9    3,902   1,069   0.0
library cache       229,405  62,851   9.1   96.9   51,653  14,151   3.7
shared pool          24,206   6,632  14.1   72.1        0       0   0.0
latch wait list       1,828     501   0.4   99.9    1,836     503   0.5
row cache objects     1,703     467   0.7   98.9    1,509     413   0.2
redo allocation         984     270   0.2   99.7        0       0   0.0
messages                116      32   0.2  100.0        0       0   0.0
cache buffers lru        91      25   0.3   99.0    7,214   1,976   0.3
modify parameter v        2       0   0.1  100.0        0       0   0.0
redo copy                 0       0  92.3   99.3    1,460     400   0.0

When examining latch statistics, look at the following:

What is the ratio of misses/gets?
What percentage of misses are obtained by just spinning?
How many times was the latch requested?

There seems to be a lot of contention for the redo copy latch with a 92.3 percent miss rate. But, look carefully. Redo copy latches are obtained mostly in immediate mode. The numbers for immediate gets look fine, and the immediate gets are several orders of magnitude bigger than the willing to wait gets. So, there is no contention for redo copy latches.

However, there does seem to be contention for the shared pool and library cache latches. Consider looking at the sleeps for these latches to see if there is actually a problem.

NAME                Gets(K)   Get/s  MISS   SPIN  SL01  SL02  SL03  SL04
------------------ -------- ------- ----- ------ ----- ----- ----- -----
cache buffers chai  255,272  69,938   0.4   99.9   0.1   0.0   0.0   0.0
library cache       229,405  62,851   9.1   96.9   3.0   0.1   0.0   0.0
shared pool          24,206   6,632  14.1   72.1  22.4   4.8   0.8   0.0
latch wait list       1,828     501   0.4   99.9   0.1   0.0   0.0   0.0
row cache objects     1,703     467   0.7   98.9   0.6   0.0   0.4   0.0
redo allocation         984     270   0.2   99.7   0.1   0.0   0.2   0.0
messages                116      32   0.2  100.0   0.0   0.0   0.0   0.0
cache buffers lru        91      25   0.3   99.0   1.0   0.0   0.0   0.0
modify parameter v        2       0   0.1  100.0   0.0   0.0   0.0   0.0
redo copy                 0       0  92.3   99.3   0.0   0.7   0.0   0.0

You can see that there is a 14% miss rate on the shared pool latches. 72% of the missed latched without relinquishing the CPU (having to sleep even once) by spinning. There are some misses for which you have to sleep multiple times.

Investigate why the shared pool latch is needed so many times. Look at the SQL being run by sessions holding or waiting for the latch, as well as the resource usage characteristics of the system. Compare them with baselines when there was no problem.

Tuning Latches

Do not tune latches. If you see latch contention, then it is a symptom of a part of SGA experiencing abnormal resource usage. Latches control access with certain assumptions (for example, a cursor is parsed once and executed many times). To fix the problem, examine the resource usage for the parts of SGA experiencing contention. Merely looking at V$LATCH does not address the problem.

See Also:

Oracle9i Database Concepts for more information on latches

V$LATCH_CHILDREN

There are multiple latches in the database for some type of latches. V$LATCH provides aggregate summary for each type of latch. To look at individual latches, query V$LATCH_CHILDREN.

Example - Finding the Number of Multiple Latches on the System

SELECT name, COUNT(*) FROM V$LATCH_CHILDREN
 GROUP BY name ORDER BY 2 DESC;

NAME                                       COUNT(*)
---------------------------------------- ----------
global tx hash mapping                         2888
global transaction                             2887
cache buffers chains                           2048
latch wait list                                  32
Token Manager                                    23
enqueue hash chains                              22
session idle bit                                 22
redo copy                                        22
process queue reference                          20
Checkpoint queue latch                           11
library cache                                    11
msg queue latch                                  11
session queue latch                              11
process queue                                    11
cache buffers lru chain                          11
done queue latch                                 11
channel operations parent latch                   4
session switching                                 4
message pool operations parent latch              4
ksfv messages                                     2
parallel query stats                              2
channel handle pool latch                         1
temp table ageout allocation latch                1

V$LATCH_HOLDER

This view is useful to see if the session holding the latch is changing. Most of the time, the latch is held for such a small time that it is impossible to join to some other table to see the SQL statement being executed or the events that latch holder is waiting for.

The main use for this view is to see that the latch is not stuck on some session.

Join Columns for V$LATCH_HOLDER

Columns:	Fixed View:	Joined Column(s):
LADDR	V$LATCH_CHILDREN	ADDR
NAME	V$LATCH, V$LATCHNAME, V$LATCH_CHILDREN	NAME
PID	V$PROCESS	PID
SID	V$SESSION	SID

Example - Finding the SQL Statement Executed by the Latch Holder

SELECT s.sql_hash_value, l.name
  FROM V$SESSION s, V$LATCHHOLDER l
WHERE s.sid = l.sid;

SQL_HASH_VALUE NAME
-------------- --------------------------------------------
     299369270 library cache
    1052917712 library cache
    3198762001 library cache
SQL> /

SQL_HASH_VALUE NAME
-------------- --------------------------------------------
     749899113 cache buffers chains
    1052917712 library cache
SQL> /

SQL_HASH_VALUE NAME
-------------- --------------------------------------------
    1052917712 library cache
SQL> /

SQL_HASH_VALUE NAME
-------------- --------------------------------------------
     749899113 library cache
    1052917712 library cache

This example indicates that the SQL statement 1052917712 is using a lot of parsing resources. The next step is to find the resources used by the session and examine the statement.

V$LIBRARYCACHE

This view has a namespace level summary for the objects in library cache since instance startup. When experiencing performance issues related to the library cache, this view can help identify the following:

Specific parts (namespace) of the library cache (shared pool)
Possible causes of problems

Then use V$DB_OBJECT_CACHE, V$SQLAREA to get more details.

Useful Columns for V$LIBRARYCACHE

NAMESPACE: Class of objects (SQL area, trigger, and so on)
GETS: Handle requests for objects of this namespace
GETHITS: Requests that found handle in the cache
PINS: PIN requests for objects of this namespace
PINHITS: Requests able to reuse an existing PIN
RELOADS: Number of times objects stored in the library cache had to be reloaded into memory because part of the object had been flushed from the cache. If there are a significant number of reloads, then reusable information is being flushed from the library cache. This requires a reload/rebuild of the object before it can again be accessed.
INVALIDATIONS: The number of times objects were invalidated. For example, an object is invalidated automatically by Oracle when it is no longer safe to execute. If the optimizer statistics for a table were recomputed, then all SQL statements currently in the library cache at the time the recompute occurred would be invalidated, because their execution plans may no longer be optimal.

GETHITRATIO (GETHITS/GETS) and GETPINRATIO (PINHITS/PINS) can be used if just examining activity since instance startup. If examining activity over a specified time interval, it is better to compute these from the differences in snapshots before and after the interval.

Example - V$LIBRARYCACHE Query

SELECT namespace, gets, 100*gethits/gets gethitratio,
       pins, 100* pinhits/pins getpinratio,
       reloads, invalidations
  FROM V$LIBRARYCACHE
 ORDER BY gets DESC

Look for the following when querying this view:

High RELOADS or INVALIDATIONS
Low GETHITRATIO or GETPINRATIO

High number of RELOADS could be due to the following:

Objects being invalidated (large number of INVALIDATIONS)
Objects getting swapped out of memory

Low GETHITRATIO could indicate that objects are getting swapped out of memory.

Low PINHITRATIO could indicate the following:

Session not executing the same cursor multiple times (even though it might be shared across different sessions)
Session not finding the cursor shared

The next step is to query V$DB_OBJECT_CACHE/V$SQLAREA to see if problems are limited to certain objects or spread across different objects. If invalidations are high, then it might be worth investigating which of the (invalidated object's) underlying objects are being changed.

V$LOCK

This view has a row for every lock held or requested on the system. You should examine this view if you find sessions waiting for the wait event enqueue. If you find sessions waiting for a lock, then the sequence of events could be the following:

Use V$LOCK to find the sessions holding the lock.
Use V$SESSION to find the SQL statements being executed by the sessions holding the lock and waiting for the lock.
Use V$SESSION_WAIT to find what the session holding the lock is doing.
Use V$SESSION to get more details about the program and user holding the lock.

Useful Columns for V$LOCK

SID: Identifier of the session holding/requesting the lock
TYPE: Type of lock
LMODE: The mode the lock is held in
REQUEST: The mode the lock is requested in
ID1, ID2: Lock resource identifiers

Common Lock Types

TX: Row Transaction Lock

This lock is required in exclusive mode to change any data.
One lock is acquired for each active transaction. It is released when the transaction ends due to a commit or rollback.
If a block containing the row(s) to be changed does not have any ITL (interested transaction list) entries left, then the session requests the lock in shared mode. It is released when the session gets an ITL entry for the block.
If any of the rows to be changed are locked by another session, then locking session's transaction lock is requested in exclusive mode. When the locking transaction ends, this request ends, and the rows are covered under the requesting session's existing TX lock.
The lock points to the rollback segment and transaction table entries for the transaction.

TM: DML Lock

This lock is required in exclusive mode for executing any DDL statements on a database object; for example, lock table in exclusive mode, alter table, drop table.
This lock is also acquired in shared mode by any statement changing any data to prevent any DDL on the object.
For every object whose data is being changed, a TM lock is required.
The lock points to the object.

ST - Space Transaction Lock

There is only one lock per database (not instance).
This lock is required in exclusive mode for any space management activity (creation or dropping any extents) except with locally managed tablespaces.
Object creation, dropping, extension, and truncation all serialize on this lock.
Most common causes for contention on this lock are sorting to disk (not using true temporary tablespaces) or rollback segment extension and shrinking.

Do the following to avoid contention on this enqueue:

Use true temporary tablespaces (temporary segments are not created and dropped after every sort to disk).
Use locally managed tablespaces with uniform extents.
Size rollback segments to avoid dynamic extension and shrinking.
Avoid application practices that create and drop database objects.

UL - User Defined Locks

Users can define their own locks.

See Also:

Oracle9i Database Concepts for more information on locks

Common Modes for Request/Lmode

0: None
2: Row Share: used for shared DML locks
4: Share: used for shared TX when waiting for ITL entry
6: Exclusive used for row level, DML locks

Any row in V$LOCK either has LMODE=0 (indicating it is a request) or REQUEST=0 (indicating it is a held lock).

Resource Identifier ID1

For DML locks, ID1 is the object_id.

For TX locks, ID1 points to the rollback segment and transaction table entry.

Join Columns for V$LOCK

Columns:	Fixed View:	Joined Column(s):
SID	V$SESSION	SID
ID1, ID2, TYPE	V$LOCK	ID1, ID2, TYPE
ID1	DBA_OBJECTS	OBJECT_ID
TRUNC(ID1/65536)	V$ROLLNAME	USN

This is used to find the session holding the lock, if a session is waiting for a lock.
This can be used to find the locked object for DML locks (type = `TM').
This can be used to find the rollback segment in use for row transaction locks (TYPE = `TX'). However, a less cryptic join might be via V$TRANSACTION.

Example - Finding the Sessions Holding the Lock

Find the (ID1, ID2, type) for sessions waiting for a lock (LMODE=0).

Find the session holding the lock (REQUEST=0) for that ID1, ID2, type.

SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1,id2,lmode
      ,request, type 
FROM V$LOCK
 WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
 ORDER BY id1,request

SID          ID1        ID2      LMODE    REQUEST TY
------ ---------- ---------- ---------- ---------- --
1237       196705     200493          6          0 TX <- Lock Holder
 1256      196705     200493          0          6 TX <- Lock Waiter
 1176      196705     200493          0          6 TX <- Lock Waiter
938        589854     201352          6          0 TX <- Lock Holder
 1634      589854     201352          0          6 TX <- Lock Waiter

Example - Finding the Statements being Executed by These Sessions

SELECT sid, sql_hash_value 
FROM V$SESSION 
WHERE SID IN (1237,1256,1176,938,1634);

SID  SQL_HASH_VALUE
-----  --------------
  938      2078523611 <-Holder
 1176      1646972797 <-Waiter
 1237      3735785744 <-Holder
 1256      1141994875 <-Waiter
 1634      2417993520 <-Waiter

Example - Finding the Text for These SQL Statements

HASH_VALUE SQL_TEXT
---------- ----------------------------------------------------------------
1141994875 SELECT TO_CHAR(CURRENT_MAX_UNIQUE_IDENTIFIER + 1 )   FROM PO_UNI
           QUE_IDENTIFIER_CONTROL  WHERE TABLE_NAME = DECODE(:b1,'RFQ','PO_
           HEADERS_RFQ','QUOTATION','PO_HEADERS_QUOTE','PO_HEADERS') FOR UP
           DATE OF CURRENT_MAX_UNIQUE_IDENTIFIER
1646972797 SELECT TO_CHAR(CURRENT_MAX_UNIQUE_IDENTIFIER + 1 )   FROM PO_UNI
           QUE_IDENTIFIER_CONTROL  WHERE TABLE_NAME = 'PO_HEADERS'  FOR UPD
           ATE OF CURRENT_MAX_UNIQUE_IDENTIFIER
2078523611 select CODE_COMBINATION_ID,  enabled_flag,  nvl(to_char(start_da
           te_active, 'J'), -1),  nvl(to_char(end_date_active, 'J'), -1), S
           EGMENT2||'.'||SEGMENT1||'.'||||SEGMENT6,detail_posting_allowed_f
           lag,summary_flag  from GL_CODE_COMBINATIONS  where CHART_OF_ACCO
           UNTS_ID = 101  and SEGMENT2 in ('000','341','367','388','389','4
           52','476','593','729','N38','N40','Q21','Q31','U21')  order by S
           EGMENT2, SEGMENT1, SEGMENT6
2417993520 select 0 into :b0  from pa_projects where project_id=:b1 for upd
           ate
3735785744 begin :X0 := FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS(:L_ENTITY
           _NAME, :L_PKEY1, :L_PKEY2, :L_PKEY3, :L_PKEY4, :L_PKEY5, :L_FUNC
           TION_NAME, :L_FUNCTION_TYPE); end;

The locked sessions' statements show that the sessions 1176 and 1256 are waiting for a lock on the PO_UNIQUE_IDENTIFIER_CONTROL held by session 1237, while session 1634 is waiting for a lock on PA_PROJECTS held by session 938. Query V$SESSION_WAIT, V$SESSION, and V$SESSION_EVENT to get more details about the sessions and users. For example:

Who is holding the lock?
Is the session holding the lock active, or has the user gone to lunch?
Is the session executing long running queries while holding the lock?

V$MYSTAT

This view is a subset of V$SESSTAT returning current session's statistics. When auditing resource usage for sessions via triggers, use V$MYSTAT to capture the resource usage, because it is much cheaper than scanning the rows in V$SESSTAT.

V$OPEN_CURSOR

This view lists all the cursors opened by the sessions. There are several ways it can be used. For example, you can monitor the number of cursors opened by different sessions.

When diagnosing system resource usage, it is useful to query V$SQLAREA and V$SQL for expensive SQL (high logical or physical I/O). In such cases, the next step is to find it's source. On applications where users log in to the database as the same generic user (and have the same PARSING_USER_ID in V$SQLAREA), this can get difficult. The statistics in V$SQLAREA are updated after the statement completes execution (and disappears from V$SESSION.SQL_HASH_VALUE). Therefore, unless the statement is being executed again, you cannot find the session directly. However, if the cursor is still open for the session, then use V$OPEN_CURSOR to find the session(s) that have executed the statement.

Join Columns for V$OPEN_CURSOR

Columns:	Fixed View:	Joined Column(s):
`HASH_VALUE, ADDRESS`	`V$SQLAREA, V$SQL, V$SQLTEXT`	`HASH_VALUE, ADDRESS`
`SID`	`V$SESSION`	`SID`

Example 2 - Find the Session(s) that Executed a Statement

SELECT hash_value, buffer_gets, disk_reads 
FROM V$SQLAREA
WHERE disk_reads > 1000000 
ORDER BY buffer_gets DESC;

HASH_VALUE BUFFER_GETS DISK_READS
---------- ----------- ----------
1514306888   177649108    3897402
 478652562    63168944    2532721
 360282550    14158750    2482065
 226079402    40458060    1592621
2144648214     1493584    1478953
1655760468     1997868    1316010
 160130138     6609577    1212163
3000880481     2122483    1158608

8 rows selected.

SQL> SELECT sid FROM V$SESSION WHERE sql_hash_value = 1514306888 ;

no rows selected

SQL> SELECT sid FROM V$OPEN_CURSOR WHERE hash_Value = 1514306888 ;

  SID
-----
 1125
  233
  935
 1693
  531

5 rows selected.

Example 1 - Finding Sessions that have more than 400 Cursors Open

SELECT sid, COUNT(*) 
FROM V$OPEN_CURSOR
 GROUP BY sid
HAVING COUNT(*) > 400
 ORDER BY 2 DESC;

 SID   COUNT(*)
----- ----------
 2359        456
 1796        449
 1533        445
 1135        442
 1215        442
  810        437
 1232        429
   27        426
 1954        421
 2067        421
 1037        416
 1584        413
  416        407
  398        406
  307        405
 1545        403

V$PARAMETER and V$SYSTEM_PARAMETER

These views list each initialization parameter by name and show the value for that parameter. The V$PARAMETER view shows the current value for the session performing the query. The V$SYSTEM_PARAMETER view shows the instance-wide value for the parameter.

For example, executing the following query shows the SORT_AREA_SIZE parameter setting for the session executing the query:

SELECT value
  FROM V$PARAMETER
 WHERE name = 'sort_area_size';

Useful Columns for V$PARAMETER

NAME: Name of the parameter
VALUE: Current value for this session (if modified within the session); otherwise, the instance-wide value
ISDEFAULT: Whether this parameter has been specified by the user as an initialization parameter
ISSES_MODIFIABLE: Whether this parameter can be modified at the session level
ISSYS_MODIFIABLE: Whether this parameter can be modified at an instance-wide level dynamically after the instance has started
ISMODIFIED: Whether this parameter has been modified after instance startup, and if so, whether it was modified at the session level or at the instance (system) level
ISADJUSTED: Whether Oracle has adjusted a value specified by the user
DESCRIPTION: Brief description of the parameter
UPDATE_COMMENT: Set if a comment has been supplied by the DBA for this parameter
See Also:

Oracle9i Database Reference for more information on the range column values

Oracle9i Database Administrator's Guide for information on server parameter files

Uses for V$PARAMETER and V$SYSTEM_PARAMETER Data

V$PARAMETER is queried during performance tuning to determine the current settings for a parameter. For example, if the buffer cache hit ratio is low, then the value for DB_BLOCK_BUFFERS (or DB_CACHE_SIZE) can be queried to determine the current buffer cache size.

The SHOW PARAMETER statement in SQL*Plus queries data from V$PARAMETER.

Example - Determining the SORT_AREA_SIZE from within SQL*Plus

column name             format a20
column value            format a10
column isdefault        format a5
column isses_modifiable format a5

SELECT name, value, isdefault, isses_modifiable, issys_modifiable, ismodified
  FROM V$PARAMETER
 WHERE name = 'sort_area_size';

NAME                 VALUE      ISDEF ISSES ISSYS_MOD ISMODIFIED
-------------------- ---------- ----- ----- --------- ----------
sort_area_size       1048576    TRUE  TRUE  DEFERRED  MODIFIED

The above example shows that the SORT_AREA_SIZE initialization parameter was not set as an initialization parameter on instance startup, but was modified at the session level (indicated by the ISMODIFIED column having the value of MODIFIED) for this session.

Note:

Use caution when querying from V$PARAMETER. If you want to see the instance-wide parameters, use V$SYSTEM_PARAMETER view instead of V$PARAMETER.

V$PROCESS

This view contains information about all Oracle processes running on the system. It is used to relate the Oracle or operating system process ID of the server process to the database session. This is needed in several situations:

If the bottleneck on the database server is related to an operating system resource (for example, CPU, memory), then in most of the cases the processes using most of the resources are the server processes. In such a scenario, the next steps are as follows:
1. Find the resource intensive processes.
2. Find their sessions. You must relate the processes to sessions.
3. Find out why the session is using so many resources.
The SQL*Trace file names are based on the operating system process ID of the server process. To locate the trace file for a session, you must relate the session to the server process.
Some events, like rdbms ipc reply, identify the Oracle process ID of the process a session is waiting on. To find out what those processes are doing, you must find their sessions.
The background processes you see on the server (DBWR, LGWR, PMON, and so on) are all server processes. To see what they are doing in the database, you must find their session.

Useful Columns for V$PROCESS

PID: Oracle process ID of the process
SPID: Operating system process ID of the process

Join Columns for V$PROCESS

Column:	Fixed View:	Joined Column(s):
ADDR	V$SESSION	PADDR

Example 1 - Finding the Session for Server Process 20143

SELECT ' sid, serial#, aud sid : '|| s.sid||' , '||s.serial#||' , '||
       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||
       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||
       s.machine||'  -  '|| s.terminal||chr(10)||
       '        OS Process Ids : '|| s.process||' (Client)  '||
       p.spid||' - '||p.pid||' (Server)'|| chr(10)||
       '   Client Program Name : '||s.program "Session Info"
  FROM V$PROCESS P,V$SESSION s
 WHERE p.addr = s.paddr
   AND p.spid = '20143';

Session Info
-------------------------------------------------------------------
 Sid, Serial#, Aud sid : 2204 , 5552 , 14478782
     DB User / OS User : APPS   /   sifapmgr
    Machine - Terminal : finprod3  -
        OS Process Ids : 9095 (Client)  20143 - 1404 (Server)
   Client Program Name : RGRARG@finprod3 (TNS V1-V3)

Example 2 - Find the Session for PMON

SELECT ' sid, serial#, aud sid : '|| s.sid||' , '||s.serial#||' , '||
       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||
       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||
       s.machine||'  -  '|| s.terminal||chr(10)||
       '        OS Process Ids : '|| s.process||' (Client)  '||
       p.spid||' - '||p.pid||' (Server)'|| chr(10)||
       '   Client Program Name : '||s.program "Session Info"
  FROM V$PROCESS p, V$SESSION s
 WHERE p.addr = s.paddr
   AND s.program LIKE '%PMON%'

Session Info
---------------------------------------------------------------
 Sid, Serial#, Aud sid : 1 , 1 , 0
     DB User / OS User :    /   oracle
    Machine - Terminal : finprod7  -  UNKNOWN
        OS Process Ids : 20178 (Client)  20178 - 2 (Server)
   Client Program Name : oracle@finprod7 (PMON)

You can see that the client and server processes are the same for the background process, which is why we could specify the client program name.

V$ROLLSTAT

This view keeps a summary of statistics for each rollback segment since startup.

Useful Columns for V$ROLLSTAT

USN: Rollback segment number
RSSIZE: Current size of the rollback segment
XACTS: Number of active transactions

Columns Useful for doing a Delta over a Period of Time

WRITES: Number of bytes written to the rollback segment
SHRINKS: Number of times the rollback segment grew past OPTIMAL and shrank back
EXTENDS: Number of times the rollback segment had to extend because there was an active transaction in the next extent
WRAPS: Number of times the rollback segment wrapped around
GETS: Number of header gets
WAITS: Number of header waits

Join Columns for V$ROLLSTAT

Column:	Fixed View:	Joined Column(s):
USN	V$ROLLNAME	USN

Example - V$ROLLSTAT

By dividing the elapsed time by wraps, you can determine the average time taken for a rollback segment to wrap. This is useful in sizing rollback segments for long running queries to avoid 'Snapshot Too Old' errors.

Also, monitor the extends and shrinks to see if the optimal size should be increased.

V$ROWCACHE

This view displays statistics for the dictionary cache (also known as the rowcache). Each row contains statistics for the various types of dictionary cache data. Note that there is a hierarchy in the dictionary cache, so the same cache name can appear more than once.

Useful Columns for V$ROWCACHE

PARAMETER: Name of the cache
COUNT: Number of entries allocated to this cache
USAGE: Current number of used entries
GETS: Total number of requests
GETMISSES: Number of requests resulting in dictionary cache miss
SCANS: Number of scan requests
SCANMISSES: Number of times a scan failed to find the required data
MODIFICATIONS: Number of additions, changes or deletions of cache entries
DLM_REQUESTS: Number of DLM Real Application Clusters requests
DLM_CONFLICTS: Number of DLM Real Application Clusters conflicts
DLM_RELEASES: Number of DLM Real Application Clusters releases

Uses for V$ROWCACHE Data

Determine whether the dictionary cache is adequately sized. If the shared pool is too small, then the dictionary cache is not able to grow to a sufficient size to cache the required information.

See Also:
Chapter 24, "Dynamic Performance Views for Tuning" for details on tuning the shared pool
Determine whether the application is accessing the cache efficiently. If the application design uses the dictionary cache inefficiently (in this case, a larger dictionary cache will not alleviate the performance problem). For example, if a large number of GETS appear for the DC_USERS cache within the sample period, then it is likely that there are large number of distinct users created within the database, and that the application is logging the users on and off frequently. To verify this, check the logon rate and also the number of users in the system. The parse rates will also be high. If this is a large OLTP system with a middle tier, then it might be more efficient to manage individual accounts on the middle tier, allowing the middle tier to logon as a single use: the application owner. Reducing logon/logoff rate by keeping connections alive also helps.
Determine whether dynamic space allocation is occurring. A large number of similarly sized modifications for DC_SEGMENTS, DC_USED_EXTENTS, and DC_FREE_EXTENTS can indicate much dynamic space allocation, in which case the solution is to size next extents appropriately.
Identify large amounts of sequence number generation occurring. Modifications to dc_sequences indicates this. Check to see whether the number of cache entries per sequence number are sufficient for then number of changes.
Gather evidence for hard parsing. Hard parsing can also be evidenced by many GETS to DC_COLUMNS, DC_VIEWS and DC_OBJECTS caches.

Example - Viewing V$ROWCACHE Data

A good way to view dictionary cache statistics is to group the data by the cache name.

SELECT parameter
     , sum("COUNT")
     , sum(usage)
     , sum(gets)
     , sum(getmisses)
     , sum(scans)
     , sum(scanmisses)
     , sum(modifications)
     , sum(dlm_requests)
     , sum(dlm_conflicts)
     , sum(dlm_releases)
  FROM V$ROWCACHE
 GROUP BY parameter;

V$SESSION

This view has one row for every session connected to the database instance. The sessions include user sessions, as well as background processes like DBWR, LGWR, archiver.

See Also:

Oracle9i Database Concepts

Useful Columns for V$SESSION

V$SESSION is basically an information view used for finding the SID or SADDR of a user. However, it has some columns that change dynamically and are useful for examining a user. For example:

SQL_HASH_VALUE, SQL_ADDRESS: These identify the SQL statement currently being executed by the session. If NULL or 0, then the session is not executing any SQL statement. PREV_HASH_VALUE and PREV_ADDRESS identify the previous statement being executed by the session.

Note:

When selecting from SQL*Plus, make sure that you have the column defined with adequate width (11 numbers wide) to see the complete number.

STATUS: This column identifies if the session is:

Active: executing a SQL statement (waiting for/using a resource)
Inactive: waiting for more work (that is, SQL statements)
Killed: marked to be killed

The following columns provide information about the session and can be used to find a session when a combination (one or more) of the following are known:

Session Information

SID: Session identifier, used to join to other columns
SERIAL#: Counter, which is incremented each time a SID is reused by another session (when a session ends and another session starts and uses the same SID)
AUDSID: Auditing session ID uniquely identifies a session over the life of a database. It is also useful when finding the parallel query slaves for a query coordinator (during the PQ execution they have the same AUDSID)
USERNAME: The Oracle user name for the connected session

Client Information

The database session is initiated by a client process that could be running on the database server or connecting to the database across SQL*Net from a middle tier server or even a desktop. The following columns provide information about this client process:

OSUSER: Operating system user name for the client process
MACHINE: Machine where the client process is executing
TERMINAL: Terminal (if applicable) where the client process is running
PROCESS: Process ID of the client process
PROGRAM: Client program being executed by the client process

To display TERMINAL, OSUSER for users connecting from PCs, set the keys TERMINAL, USERNAME in ORACLE.INI or the Windows registry on their PCs if they are not showing up by default.

Application Information

Call the package DBMS_APPLICATION_INFO to set some information to identify the user. This shows up in the following columns:

CLIENT_INFO: Set in DBMS_APPLICATION_INFO
ACTION: Set in DBMS_APPLICATION_INFO
MODULE: Set in DBMS_APPLICATION_INFO

Join Columns for V$SESSION

This is a list of columns that we can use to join to other fixed views.

Columns:	Fixed View:	Joined Column(s):
SID	V$SESSION_WAIT, V$SESSTAT, V$LOCK, V$SESSION_EVENT, V$OPEN_CURSOR	SID
(SQL_HASH_VALUE, SQL_ADDRESS)	V$SQLTEXT, V$SQLAREA, V$SQL	(HASH_VALUE, ADDRESS)
(PREV_HASH_VALUE, PREV_SQL_ADDRESS)	V$SQLTEXT, V$SQLAREA, V$SQL	(HASH_VALUE, ADDRESS)
TADDR	V$TRANSACTION	ADDR
PADDR	V$PROCESS	ADDR

Example 1 - Finding your Session

SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
  FROM V$SESSION
 WHERE audsid = userenv('SESSIONID');

  SID OSUSER     USERNAME    MACHINE     PROCESS
----- ---------- ----------- ----------- --------
 1011 vsaksena   SYSTEM      dlsun1653   15912

Example 2 - Finding a Session When the Machine is Known

SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
  FROM V$SESSION
 WHERE terminal = 'ttyAH/AHHh'
   AND machine  = 'prodseq4';

 SID OSUSER    USERNAME  MACHINE    TERMINAL
---- --------- --------- ---------- ----------
  58 vsaksena  APPS_US   prodseq4   ttyAH/AHHh

Example 3 - SQL Statement Currently Being Run by a Session

It is a common requirement to find the SQL statement currently being executed by a given session. If a session is experiencing or responsible for a bottleneck, then the statement explains what the session might be doing.

col hash_value form 99999999999
SELECT sql_hash_value hash_value
  FROM V$SESSION WHERE sid = 406;

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653

This example waited for five seconds, executed the statement again, and repeated the action couple of times. The same hash_value comes up again and again, indicating that the statement is being executed by the session. As a next step, find the statement text using the view V$SQLTEXT and statement statistics from V$SQLAREA.

V$SESSION_EVENT

This view summarizes wait events for every session. While V$SESSION_WAIT shows the current waits for a session, V$SESSION_EVENT provides summary of all the events the session has waited for since it started.

Useful Columns for V$SESSION_EVENT

SID: Identifier for the session
EVENT: Name of the wait event
TOTAL_WAITS: Total number of waits for this event by this session
TIME_WAITED: Total time waited for this event (in hundredths of a second)
AVERAGE_WAIT: Average amount of time waited for this event by this session (in hundredths of a second)
TOTAL_TIMEOUTS: Number of times the wait timed out

Join Columns for V$SESSION_EVENT

Column:	Fixed View:	Joined Column(s):
`SID`	`V$SESSION`	`SID`

Example - Finding the Waits for the Database Writer

SELECT s.sid, bgp.name 
    FROM V$SESSION s, V$BGPROCESS bgp 
   WHERE bgp.name LIKE '%DBW%' 
     AND bgp.paddr = s.paddr; 

SELECT event, total_waits waits, total_timeouts timeouts,
       time_waited total_time, average_wait avg
  FROM V$SESSION_EVENT
 WHERE sid = 3
 ORDER BY 4 DESC;
EVENT                             WAITS TIMEOUTS TOTAL_TIME   AVG
------------------------------ -------- -------- ---------- -----
rdbms ipc message               1684385   921495  284706709 169.03
db file parallel write           727326        0    3012982   4.14
latch free                          157      157        281   1.78
control file sequential read        123        0         61   0.49
file identify                        45        0         29   0.64
direct path read                     41        0          5   0.12
file open                            49        0          2   0.04
db file sequential read               2        0          2   1.00

V$SESSION_WAIT

This is a key view for finding bottlenecks. It tells what every session in the database is currently waiting for (or the last event waited for by the session if it is not waiting for anything). This view can be used as a starting point to find which direction to proceed in when a system is experiencing performance problems.

V$SESSION_WAIT has a row in V$SESSION_WAIT for every session connected to an instance. It indicates if the session is:

Using a resource
Waiting for a resource
Idle (waiting on one of the idle events)

Useful Columns for V$SESSION_WAIT

SID: Session identifier for the session
EVENT: Event the session is currently waiting for, or the last event the session had to wait for
WAIT_TIME: Time (in hundredths of a second) that the session waited for the event; if the WAIT_TIME is 0, then the session is currently waiting for the event
SEQ#: Gets incremented with every wait of the session
P1, P2, P3: Wait event specific details for the wait

P1TEXT, P2TEXT, P3TEXT: Description of P1,P2,P3 for the given event

Table 24-5 Wait Time Description

WAIT-TIME:	Meaning:	Waiting
>0	Time waited in the last wait (in 10 ms clock ticks)	No
0	Session is currently waiting for this event	Yes
-1	Time waited in the last wait was less than 10 ms	No
-2	Timing is not enabled	No

Below is an example of how the EVENT, SEQ#, and WAIT_TIME might change over a period of time:

Table 24-6 Events Changing Over Time

Time	Seq #	Event	Wait Time	P1	P2	P3	Action	Waiting
0	43	`latch` `free`	0	800043F8	31	1	Get LRU latch	Yes
10	43	`latch free`	10	800043F8	31	1	Get free buffer	No
20	44	`db file sequential read`	0	5	1345	1	Issue the read call	Yes
30	44	`db file sequential read`	10	5	1345	1	Process the buffer	No
35	45	`enqueue`	0	1415053318	196631	6355	Lock the buffer	Yes
1040	45	`enqueue`	1000	1415053318	196631	6355	Modify the buffer	No

In this example, the session waited for a latch from 0-10, waited for db file sequential read from 20-30, waited for a lock from 35-1040. The times in between have been exaggerated for illustration purposes. Event and Seq# do not change until the session has to wait again. The Wait Time indicates if the session is actually waiting or using a resource.

Join Columns for V$SESSION_WAIT

Column:	Fixed View:	Joined Column(s):
SID	V$SESSION	SID

24 Dynamic Performance Views for Tuning

Current State Views

Table 24-1 Current State Views

Counter/Accumulator Views

Table 24-2 Summary Since Session Startup

Table 24-3 Summary Since Instance Startup

Information Views

Table 24-4 Information Views

V$DB_OBJECT_CACHE

Useful Columns for V$DB_OBJECT_CACHE

Instantaneous State Columns

Example - Summary of Shared Pool Executions and Memory Usage

Example - Finding Objects with Large Number of Loads

Example - Finding Large Unpinned Objects

V$FILESTAT

Useful Columns for V$FILESTAT

Note:

Join Columns for V$FILESTAT

Example - Checking Oracle Datafile I/O

Example - Finding the Files with Large Numbers of Multiblock Reads

V$LATCH

Useful Columns for V$LATCH

Join Columns for V$LATCH

V$LATCH Example

Tuning Latches

V$LATCH_CHILDREN

Example - Finding the Number of Multiple Latches on the System

V$LATCH_HOLDER

Join Columns for V$LATCH_HOLDER

Example - Finding the SQL Statement Executed by the Latch Holder

V$LIBRARYCACHE

Useful Columns for V$LIBRARYCACHE

Example - V$LIBRARYCACHE Query

V$LOCK

Useful Columns for V$LOCK

Common Lock Types

TX: Row Transaction Lock

TM: DML Lock

ST - Space Transaction Lock

UL - User Defined Locks

Common Modes for Request/Lmode

Resource Identifier ID1

Join Columns for V$LOCK

Example - Finding the Sessions Holding the Lock

Example - Finding the Statements being Executed by These Sessions

Example - Finding the Text for These SQL Statements

V$MYSTAT

V$OPEN_CURSOR

Join Columns for V$OPEN_CURSOR

Example 2 - Find the Session(s) that Executed a Statement

Example 1 - Finding Sessions that have more than 400 Cursors Open

V$PARAMETER and V$SYSTEM_PARAMETER

Useful Columns for V$PARAMETER

Uses for V$PARAMETER and V$SYSTEM_PARAMETER Data

Example - Determining the SORT_AREA_SIZE from within SQL*Plus

V$PROCESS

Useful Columns for V$PROCESS

Join Columns for V$PROCESS

Example 1 - Finding the Session for Server Process 20143

Example 2 - Find the Session for PMON

V$ROLLSTAT

Useful Columns for V$ROLLSTAT

Columns Useful for doing a Delta over a Period of Time

Join Columns for V$ROLLSTAT

Example - V$ROLLSTAT

V$ROWCACHE

Useful Columns for V$ROWCACHE

Uses for V$ROWCACHE Data

Example - Viewing V$ROWCACHE Data

V$SESSION

Useful Columns for V$SESSION

Session Information

Client Information

Application Information

Join Columns for V$SESSION

Example 1 - Finding your Session

Example 2 - Finding a Session When the Machine is Known

Example 3 - SQL Statement Currently Being Run by a Session

V$SESSION_EVENT

Useful Columns for V$SESSION_EVENT

24
Dynamic Performance Views for Tuning