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

• Counter/Accumulator Views

• Information Views

  See Also: Oracle9i Database Reference for a complete list of the dynamic performance views and their columns

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:

1. Physical reads and blocks read can be different because of multiblock read calls.

2. Physical writes and blocks written can differ because of direct writes by processes.

3. Sum(Physical blocks read) should correlate closely with physical reads from V$SYSSTAT.

4. Sum(Physical blocks written) should correlate closely with physical writes from V$SYSSTAT.

5. 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:

1. Use V$LOCK to find the sessions holding the lock.

2. Use V$SESSION to find the SQL statements being executed by the sessions holding the lock and waiting for the lock.

3. Use V$SESSION_WAIT to find what the session holding the lock is doing.

4. 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

1. This is used to find the session holding the lock, if a session is waiting for a lock.

2. This can be used to find the locked object for DML locks (type = `TM').

3. 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

  See Also: Oracle9i Database Reference and "Wait Events"

  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

Example - Finding Current Waits on the System

SELECT event, SUM(DECODE(wait_time,0,1,0)) "Curr", SUM(DECODE(wait_time,0,0,1)) 
"Prev", COUNT(*)"Total"
FROM V$SESSION_WAIT
GROUP BY event ORDER BY 4;

EVENT                                         Prev  Curr   Tot
--------------------------------------------- ---- ----- -----
PL/SQL lock timer                                0     1     1
SQL*Net more data from client                    0     1     1
smon timer                                       0     1     1
pmon timer                                       0     1     1
SQL*Net message to client                        2     0     2
db file scattered read                           2     0     2
rdbms ipc message                                0     7     7
enqueue                                          0    12    12
pipe get                                         0    12    12
db file sequential read                          3    10    13
latch free                                       9     6    15
SQL*Net message from client                    835  1380  2215

This query, which groups the data by event and by wait_time (0=waiting, nonzero=not waiting), shows the following:

• Most of the sessions are waiting for idle events like SQL*Net message from client, pipe get, PMON timer, and so on.

• The number of sessions using the CPU can be approximated by the number of sessions not waiting (prev), except for one problem: there seem to be a lot of sessions that are not waiting for anything (hence actively using resources) and whose last wait was SQL*Net message from client.

The next step should be to check V$SESSION to see if the session is active or not. Only count the session as actively waiting or using a resource if it is active. Use the statement below to accomplish this. The total column counts the total of all the sessions, however the currently waiting and previously waited (using resource) columns only count active sessions.

SELECT event,
       SUM(DECODE(wait_Time,0,0,DECODE(s.status,'ACTIVE',1,0))) "Prev",
       SUM(DECODE(wait_Time,0,1,DECODE(s.status,'ACTIVE',1,0))) "Curr",
       COUNT(*) "Tot"
  FROM V$SESSION S,V$SESSION_WAIT  w
 WHERE s.sid = w.sid
 GROUP BY event ORDER BY 4;

EVENT                                     Prev  Curr   Tot
---------------------------------------- ----- ----- -----
SQL*Net message to client                    1     1     1 <- idle event
buffer busy waits                            1     1     1
file open                                    1     1     1
pmon timer                                   0     1     1 <- idle event
smon timer                                   0     1     1 <- idle event
log file sync                                0     1     1
db file scattered read                       0     2     2
rdbms ipc message                            0     7     7 <- idle event
pipe get                                     0    12    12 <- idle event
enqueue                                      0    14    14
latch free                                  10    17    20
db file sequential read                      7    22    23
SQL*Net message from client                  0  1383  2240 <- idle event

Now sessions are counted as actively waiting or using a resource only if they are active. This highlights the following:

• There are a total of 2324 sessions.

• 20 sessions are actively using resources (active sessions without an active wait).

• 1463 sessions are waiting.

• 58 of these are waiting for nonidle events. The idle events here being SQL*Net message from client, pipe get, rdbms ipc message, PMON timer, SMON timer, and SQL*Net message to client.

  See Also: "Wait Events"

• 14 sessions are locked out (and probably screaming about poor performance).

• PMON and SMON are sleeping on their timers.

• 24 sessions are waiting for I/O calls to return (db file%read).

V$SESSTAT

V$SESSTAT stores session-specific resource usage statistics, beginning at login and ending at logout.

Similar to V$SYSSTAT, this view stores the following types of statistics:

• A count of the number of times an action occurred (user commits)

• A running total of volumes of data generated, accessed or manipulated (redo size)

• If TIMED_STATISTICS is true, the cumulative time spent performing some actions (CPU used by this session)

The differences between V$SYSSTAT and V$SESSTAT are the following:

• V$SESSTAT only stores data on a per session basis, whereas V$SYSSTAT stores the accumulated values for all sessions.

• V$SESSTAT is transitory, and is lost after a session logs out. V$SYSSTAT is cumulative, and is only lost when the instance is shutdown.

• V$SESSTAT does not include the name of the statistic. In order to find the statistic name, this view must be joined to either V$SYSSTAT or V$STATNAME.

V$SESSTAT can be used to find sessions with the following:

• The highest resource usage

• The highest average resource usage rate (ratio of resource usage to logon time)

• The current resource usage rate (delta between two snapshots)

Useful Statistics in V$SESSTAT

The most referenced statistics in V$SESSTAT are a subset of those described for V$SYSSTAT and include session logical reads, CPU used by this session, db block changes, redo size, physical writes, parse count (hard), parse count (total), sorts (memory), and sorts (disk).

Useful Columns for V$SESSTAT

• SID: Session identifier

• STATISTIC#: Resource identifier

• VALUE: Resource usage

Join Columns for V$SESSTAT

Columns:	Fixed View:	Joined Column(s):
STATISTIC#	V$STATNAME	STATISTIC#
SID	V$SESSION	SID

Example - Finding the Top Sessions with Highest Logical and Physical I/O Rates Currently Connected to the Database

The following SQL statement shows the logical and physical read rates (per second) for all active sessions connected to the database. Rates for logical and physical I/O are calculated using the elapsed time since logon (from V$SESSION.LOGON_TIME). This might not be particularly accurate for sessions connected to the database for long periods, but it is sufficient for this example.

To determine the STATISTIC#'s for the session logical reads and physical reads statistics:

SELECT name, statistic# 
  FROM V$STATNAME 
 WHERE name IN ('session logical reads','physical reads') ;

NAME                           STATISTIC#
------------------------------ ----------
session logical reads                   9
physical reads                         40

Use these values in the following query, which orders the sessions by resource usage:

SELECT ses.sid
     , DECODE(ses.action,NULL,'online','batch')          "User"
     , MAX(DECODE(sta.statistic#,9,sta.value,0))
       /greatest(3600*24*(sysdate-ses.logon_time),1)     "Log IO/s"
     , MAX(DECODE(sta.statistic#,40,sta.value,0))
       /greatest(3600*24*(sysdate-ses.logon_time),1)     "Phy IO/s"
     , 60*24*(sysdate-ses.logon_time)                    "Minutes"
 FROM V$SESSION ses
    , V$SESSTAT sta
WHERE ses.status     = 'ACTIVE'
  AND sta.sid        = ses.sid
  AND sta.statistic# IN (9,40)
GROUP BY ses.sid, ses.action, ses.logon_time
ORDER BY
        SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
      / greatest(3600*24*(sysdate-ses.logon_time),1)  DESC;

  SID User   Log IO/s Phy IO/s Minutes
----- ------ -------- -------- -------
 1951 batch       291    257.3       1
  470 online    6,161     62.9       0
  730 batch     7,568     43.2     197
 2153 online    1,482     98.9      10
 2386 batch     7,620     35.6      35
 1815 batch     7,503     35.5      26
 1965 online    4,879     42.9      19
 1668 online    4,318     44.5       1
 1142 online      955     69.2      35
 1855 batch       573     70.5       8
 1971 online    1,138     56.6       1
 1323 online    3,263     32.4       5
 1479 batch     2,857     35.1       3
  421 online    1,322     46.8      15
 2405 online      258     50.4       8
 

To better show the impact of each individual session on the system, the results were ordered by the total resource usage per second. The resource usage was calculated by adding session logical reads and (a weighted) physical reads.

Physical reads was weighted by multiplying the raw value by a factor of 100, to indicate that a physical I/O is significantly more expensive than reading a buffer already in the cache.

To calculate the physical I/O weighting factor, the following assumptions were made:

• Average wait for a physical I/O (PIO) was 10 ms (queried from V$SYSTEM_EVENT.AVERAGE_WAIT for the events db file sequential read and db file scattered read).

• Average logical I/O rate (LIO) was 13000/second/CPU (queried from V$SYSSTAT for the statistic name session logical reads. This statistic was divided by the elapsed time in seconds and the number of CPUs on the system).

• This provides a ratio of 130 logical reads per 10 ms, and 1 physical read per 10 ms for this configuration. This ratio was rounded to the ballpark number of 100.

V$SQL

A SQL statement can map to multiple cursors, because the objects referred to in the cursor can differ from user to user. If there are multiple cursors (child cursors) present, then V$SQLAREA provides aggregated information for all the cursors.

For looking at individual cursors, V$SQL can be used. This view contains cursor level details for the SQL. It can be used when trying to locate the session or person responsible for parsing the cursor.

V$SQL_PLAN

This view provides a way of examining the execution plan for cursors that were recently executed.

The information in this view is very similar to the output of an EXPLAIN PLAN statement. However, EXPLAIN PLAN shows a theoretical plan that can be used if this statement were to be executed, whereas V$SQL_PLAN contains the actual plan used. The execution plan obtained by the EXPLAIN PLAN statement can be different from the execution plan used to execute the cursor, because the cursor might have been compiled with different values of session parameters (for example, HASH_AREA_SIZE).

Uses for V$SQL_PLAN Data

• Determining the current execution plan

• Identifying the effect of creating an index on a table

• Finding cursors containing a certain access path (for example, full table scan or index range scan)

• Identifying indexes that are, or are not, selected by the optimizer

• Determining whether the optimizer selects the particular execution plan (for example, nested loops join) expected by the developer

This view can also be used as a key mechanism in plan comparison. Plan comparison can be useful when the following types of changes occur:

• Dropping or creating indexes

• Running the ANALYZE statement on the database objects

• Modifying initialization parameter values

• Switching from the rule-based optimizer to the cost-based optimizer

• After migrating the application or the database to a new release

If previous plans are kept (for example, selected from V$SQL_PLAN and stored in permanent Oracle tables for reference), then it is then possible to identify how changes in the performance of a SQL statement can be correlated with changes in the execution plan for that statement.

Useful Columns for V$SQL_PLAN

The view contains almost all PLAN_TABLE columns, in addition to new columns. The columns that are also present in the PLAN_TABLE have the same values:

• ADDRESS: Address of the handle to the parent for this cursor

• HASH_VALUE: Hash value of the parent statement in the library cache

The two columns ADDRESS and HASH_VALUE can be used to join with V$SQLAREA to add the cursor-specific information.

• CHILD_NUMBER: Child cursor number using this execution plan

The columns ADDRESS, HASH_VALUE and CHILD_NUMBER can be used to join with V$SQL to add the child cursor specific information.

• OPERATION: Name of the internal operation performed in this step; for example, TABLE ACCESS

• OPTIONS: A variation on the operation described in the OPERATION column; for example, FULL

• OBJECT_NODE: Name of the database link used to reference the object (a table name or view name); for local queries using parallel execution, this column describes the order in which output from operations is consumed

• OBJECT#: Object number of the table or the index

• OBJECT_OWNER: Name of the user who owns the schema containing the table or index

• OBJECT_NAME: Name of the table or index

• OPTIMIZER: Current mode of the optimizer for the first row in the plan (statement line); for example, CHOOSE. In case the operation is a database access (e.g, TABLE ACCESS), it tells whether the object is analyzed or not

• ID: A number assigned to each step in the execution plan

• PARENT_ID: ID of the next execution step that operates on the output of the current step

• DEPTH: The depth (or level) of the operation in the tree; that is, it is not necessary to do a CONNECT BY to get the level information generally used to indent the rows from the PLAN_TABLE - the root operation (statement) has level 0.

• POSITION: Order of processing for operations that all have the same PARENT_ID

• COST: Cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null

• CARDINALITY: The estimate, by the cost-based optimizer, of the number of rows produced by the operation

• BYTES: The estimate, by the cost-based optimizer, of the number of bytes produced by the operation

• OTHER_TAG: Describes the contents of the OTHER column (see Chapter 9, "Using EXPLAIN PLAN" for values)

• PARTITION_START: The start partition of a range of accessed partition

• PARTITION_STOP: The stop partition of a range of accessed partitions

• PARTITION_ID: The step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns

• OTHER: Other information that is specific to the execution step that a user may find useful (see Chapter 9, "Using EXPLAIN PLAN" for values)

• DISTRIBUTION: For parallel query, stores the method used to distribute rows from producer query servers to consumer query servers.

• CPU_COST: The CPU cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null

• IO_COST: The I/O cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null

• TEMP_SPACE: The temporary space usage of the operation (sort or hash-join) as estimated by the optimizer's cost-based approach; for statements that use the T

The DEPTH column replaces the LEVEL pseudo-column produced by the CONNECT BY operator, which sometimes is used in SQL scripts to help indent the PLAN_TABLE data.

Join Columns for V$SQL_PLAN

The columns ADDRESS, HASH_VALUE and CHILD_NUMBER are used to join with V$SQL or V$SQLAREA to fetch the cursor-specific information; for example, BUFFER_GETS, or with V$SQLTEXT to return the full text of the SQL statement.

Column(s):                      Fixed View:              Joined Column(s)

address, hash_value             v$sqlarea                address, hash_value 

address, hash_value,            v$sql                    address, hash_value
child_number                                             child_number

address, hash_value             v$sqltext                address, hash_value 

Example - Determining the Optimizer Plan for a SQL Statement

The following statement shows the EXPLAIN PLAN for a specified SQL statement. Looking at the plan for a SQL statement is one of the first steps in tuning a SQL statement. The SQL statement to return the plan for is identified by the statement's HASH_VALUE and address.

See Also: "Identifying and Gathering Data on Resource-Intensive SQL" for information on how to identify SQL statements to tune

A SQL*Plus query and sample output from V$SQL_PLAN (assumes only one child cursor):

column id          format 999 newline
column operation   format a20
column operation   format a20
column options     format a15
column object_name format a22 trunc
column optimizer   format a3  trunc

SELECT id
     , lpad (' ', depth) || operation operation
     , options
     , object_name
     , optimizer
     , cost
  FROM V$SQL_PLAN
 WHERE hash_value = 2446703096
   AND address    = '80E3A1D8'
 START WITH id = 0
 CONNECT BY 
       (     prior id           = parent_id
         AND prior hash_value   = hash_value
         AND prior child_number = child_number
       )
 ORDER SIBLINGS BY id, position;

  ID OPERATION            OPTIONS         OBJECT_NAME            OPT       COST
---- -------------------- --------------- ---------------------- --- ----------
   0 SELECT STATEMENT                                            CHO
   1  SORT                ORDER BY                                            6
   2   NESTED LOOPS                                                           4
   3    HASH JOIN                                                             3
   8    TABLE ACCESS      BY INDEX ROWID  ORDERS                              1
   9     INDEX            RANGE SCAN      ORDERS_PK                           2
   4     TABLE ACCESS     BY INDEX ROWID  ORDERS_LINE_ITEM                    1
   5      INDEX           RANGE SCAN      ORDERS_LINE_ITEM_PK                 2
   6     TABLE ACCESS     BY INDEX ROWID  ORDERS_STATUS                       1
   7      INDEX           RANGE SCAN      ORDERS_STATUS_PK                    2

10 rows selected.

V$SQL_PLAN shows the plan for a cursor, not for a SQL statement. The difference is that a SQL statement can have more than one cursor associated with it, with each cursor further identified by a CHILD_NUMBER. Below are a few examples of how a SQL statement can result in more than one cursor:

• When the same table name resolves to two separate tables:

  User1: SELECT * FROM EMPLOYEE;

  User2: SELECT * FROM EMPLOYEE;

  Where user2 has his own employee table, and user1 uses the table referenced by a public synonym.

• When the environment for user1 differs from user2. For example, if user2 specified the first rows (ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS) in their login script, and user1 did not.

  If the results of querying V$SQL_PLAN for a HASH_VALUE and ADDRESS result in more than one plan appearing, it is because this SQL statement has more than one child cursor. In this case, for each child cursor (identified by CHILD_NUMBER), look at the plan to identify whether they differ significantly.

V$SQLAREA

This view keeps track of all the shared cursors present in the shared pool. It has one row for every SQL statement present in the shared pool. It is an invaluable view for finding the resource usage of a SQL statement.

Information columns in V$SQLAREA

• HASH_VALUE: Hash value of the SQL statement

• ADDRESS: SGA address for the SQL statement

The above two columns are used to identify the SQL statement. Sometimes, two different statements could hash to the same value. In such cases, it is necessary to use the address along with the hash_value.

• PARSING_USER_ID: User who parsed the first cursor for the statement

• VERSION_COUNT: Number of cursors for the statement

• KEPT_VERSIONS: Cursors of the statement pinned using DBMS_SHARED_POOL.KEEP()

• SHARABLE_MEMORY: Total shared memory used by the cursor

• PERSISTENT_MEMORY: Total persistent memory used by the cursor

• RUNTIME_MEMORY: Total runtime memory used by the cursor

• SQL_TEXT: Up to first 1000 characters of SQL statement

• MODULE, ACTION: Information about the session parsing the first cursor if set using DBMS_APPLICATION_INFO

Other Useful Columns in V$SQLAREA

These columns get incremented with each execution of the statement.

• BUFFER_GETS: Logical I/Os caused by this statement

• DISK_READS: Physical I/O requests due to the statement

• SORTS: Sorts performed due to the statement

• CPU_TIME: Time taken by the CPU due to the statement

• ELAPSED_TIME: Total time spent due to the statement

• PARSE_CALLS: Parse calls (hard and soft) for the statement

• EXECUTIONS: Number of times the statement was executed

• INVALIDATIONS: Total invalidations for statement's cursors

• LOADS: Loads (and reloads) for the statement

• ROWS_PROCESSED: Total rows processed by this statement

Join Columns in V$SQLAREA

Columns:	Fixed View:	Joined Column(s):
HASH_VALUE, ADDRESS	V$SESSION	SQL_HASH_VALUE, SQL_ADDRESS
HASH_VALUE, ADDRESS	V$SQLTEXT, V$SQL, V$OPEN_CURSOR	HASH_VALUE, ADDRESS
SQL_TEXT	V$DB_OBJECT_CACHE	NAME

Example - Finding Resource-Intensive SQL

There are several costs you can use:

• Total logical I/O (LIO), LIO/execution

• Total physical I/O (PIO), PIO/execution

• PIO/LIO (poor cache hit ratio)

• parse_calls, parse_calls/executions

  SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
    FROM V$SQLAREA
   WHERE buffer_gets > 10000000
      OR disk_reads > 1000000
   ORDER BY buffer_gets + 100*disk_reads DESC;
  
  HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS PARSE_CALLS
  ---------- ---------- ----------- ---------- -----------
  2676594883        126     7583140    6199113         126
  4074144966        126     7264362    6195433          49
   228801498        136   236116544    2371187         136
   360282550       5467    21102603    4476317        2355
  1559420740        201     8197831    4537591          39
  3213702248   28039654   364516977         44         131
  1547710012        865     7579025    3337735         865
  3000880481       4481     3676546    2212658        2885
  1398193708       4946    73018658    1515257        1418
  1052917712    8342025   201246652      38240      327462
   371697988          7    74380777     862611           7
  1514306888    3922461    29073852    1223482         268
  1848522009          1     1492281    1483635           1
  1478599096   28042103   140210513        594         164
   226079402      21473    22121577    1034787        4484
   478652562       4468    21669366    1020370        4438
  2054874295      73520   118272694      29987       73520
  

Note: If a statement is executing for the first time on the system and responsible for large fraction of the current resource usage, then this statement does not find that statement, because the BUFFER_GETS and DISK_READS statistics do not get updated until the statement finishes execution.

Example - Finding Resources Used by a SQL Statement

SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
  FROM V$SQLAREA
 WHERE hash_Value = 228801498
   AND address = hextoraw('CBD8E4B0');

HASH_VALUE BUFFER_GETS DISK_READS EXECUTIONS PARSE_CALLS
---------- ----------- ---------- ---------- -----------
 228801498   236116544    2371187        136         136

V$SQLTEXT

This view contains the complete SQL text for the SQL statements in the shared pool.

Note: V$SQLAREA only contains only the first 1000 characters.

Useful Columns for V$SQLTEXT

• HASH_VALUE: Hash value for the SQL statement

• ADDRESS: Address of the SQL statement cursor in SGA

• SQL_TEXT: Statement text in 64 character chunks

• PIECE: Ordering information for the SQL statement pieces

Join Columns for V$SQLTEXT

Columns:	Fixed View:	Joined Column(s):
HASH_VALUE, ADDRESS	V$SQL, V$SESSION	HASH_VALUE, ADDRESS
HASH_VALUE. ADDRESS	V$SESSION	SQL_HASH_VALUE. SQL_ADDRESS

Example - Finding the SQL Statement for a Hash Value

SELECT sql_text
  FROM V$SQLTEXT
 WHERE hash_value = 228801498
 ORDER BY piece;

SQL_TEXT
----------------------------------------------------------------
select dbsu.primary_flag, i.site_use_code, i.rowid   
from ra_customers dbc, ra_addresses dbad, ra_site_uses dbsu, ra_customers_
interface i 
where (((((((i.orig_system_customer_ref=dbc.orig_system_reference and 
dbad.address_id=dbsu.address_id) and i.site_use_code=dbsu.site_use_code) and 
dbsu.status='A') and dbad.customer_id=dbc.customer_id) and i.request_id=:b0) and 
nvl(i.validated_flag,'N')<>'Y') and ((i.primary_site_use_flag='Y' and 
dbsu.primary_flag='Y') or dbsu.site_use_code in ('STMTS','DUN','LEGAL'))) 

group by dbsu.primary_flag,i.orig_system_customer_ref,i.site_use_code,i.insert_update_flag,i.rowid

V$SYSSTAT

V$SYSSTAT stores instance-wide statistics on resource usage, cumulative since the instance was started.

Similar to V$SESSTAT, this view stores the following types of statistics:

• A count of the number of times an action occurred (user commits)

• A running total of volumes of data generated, accessed, or manipulated (redo size)

• If TIMED_STATISTICS is true, then the cumulative time spent performing some actions (CPU used by this session)

Useful Columns in V$SYSSTAT

• STATISTIC#: Identifier for the statistic

• NAME: Statistic name

• VALUE: Resource usage

The value for each statistic stores the resource usage for that statistic since instance startup. Below are sample column values for the statistic execute count.

Statistic#:	Name:	Value:
215	execute count	19,003,070

Note: The STATISTIC# for a statistic can change between releases. Do not rely on STATISTIC# to remain constant. Instead, use the statistic NAME column to query the VALUE.

Uses for V$SYSSTAT Data

The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed from V$SYSSTAT data.

Data in this view is also used for monitoring system resource usage and how the system's resource usage changes over time. As with most performance data, examine the system's resource usage over an interval. To do this, take a snapshot of the data within the view at the beginning of the interval and another at the end. The difference in the values (end value - begin value) for each statistic is the resource used during the interval. This is the methodology used by Oracle tools such as Statspack and BSTAT/ESTAT.

In order to compare one interval's data with another, the data can be normalized (per transaction, per execution, per second, or per logon). Normalizing the data on both workloads makes identifying the variances between the two workloads easier. This type of comparison is especially useful after patches have been applied, applications have been upgraded, or simply over time to see how increases in user population or data growth affects the resource usage.

You can also use V$SYSSTAT data to examine the resource consumption of contended-for resources that were identified by querying the V$SYSTEM_EVENT view.

Useful Statistics for V$SYSSTAT

Below are some of the V$SYSSTAT statistics that are most useful during tuning, along with an explanation of the statistic. This list is in alphabetical order.

See Also: Oracle9i Database Reference for a complete list of statistics and their description

Key Database Usage Indicators

• CPU used by this session: The total amount of CPU used by all sessions, excluding background processes. This unit for this statistic is hundredths of a second. Calls that complete in less than 10ms are rounded up to this unit.

• db block changes: The number of changes made to database blocks in the SGA that were part of an insert, update, or delete operation. This statistic is a rough indication of total database work. On a per transaction level, this statistic indicates the rate at which buffers are being dirtied.

• execute count: The total number of SQL statement executions (including recursive SQL).

• logons current: Sessions currently connected to the instance. When using two snapshots across an interval, an average value (rather than the difference) should be used.

• logons cumulative: The total number of logons since the instance started. To determine the number of logons in a particular period, subtract the end value from the begin value. A useful derived statistic is to divide the number of connections between a begin and end time, and divide this by the number of seconds the interval covered. This gives the logons rate per second. Optimally, there should be no more than two logons per second. To contrast, a logon rate of 50 per second is considered very high. Applications that continually connect and disconnect from the database (for example, once per transaction) do not scale well.

• parse count (hard): The number of parse calls that resulted in a miss in the shared pool. A hard parse occurs when a SQL statement is executed and the SQL statement is either not in the shared pool, or it is in the shared pool but it cannot be shared because part of the metadata for the two SQL statements is different. This can happen if a SQL statement is textually identical to a preexisting SQL statement, but the tables referred to in the two statements resolve to physically different tables. A hard parse is a very expensive operation in terms of CPU and resource use (for example, latches), because it requires Oracle to allocate memory within the shared pool, then determine the execution plan before the statement can be executed.

• parse count (total): The total number of parse calls, both hard and soft. A soft parse occurs when a session executes a SQL statement, and the statement is already in the shared pool and can be used. For a statement to be used (that is, shared) all data pertaining to the existing SQL statement (including data such as the optimizer execution plan) must be equally applicable to the current statement being issued. These two statistics are used to calculate the soft-parse ratio.

• parse time cpu: Total CPU time spent parsing in hundredths of a second. This includes both hard and soft parses.

• parse time elapsed: The total elapsed time for the parse call to complete.

• physical reads: The number of blocks read from the operating system. It includes physical reads into the SGA buffer cache (a buffer cache miss) and direct physical reads into the PGA (for example, during direct sort operations). This statistic is not the number of I/O requests.

• physical writes: The number of database blocks written from the SGA buffer cache to disk by DBWR and from the PGA by processes performing direct writes.

• redo log space requests: The number of times a server process waited for space in the redo logs, typically because a log switch is needed.

• redo size: The total amount of redo generated (and hence written to the log buffer), in bytes. This statistic (normalized per second or per transaction) is a good indicator of update activity.

• session logical reads: The number of logical read requests that can be satisfied in the buffer cache or by a physical read.

• sorts (memory) and sorts (disk): sorts (memory) is the number of sort operations that fit inside the SORT_AREA_SIZE (and hence did not require an on-disk sort). sorts (disk) is the number of sort operations that were larger than SORT_AREA_SIZE and had to use space on disk to complete the sort. These two statistics are used to compute the in-memory sort ratio.

• sorts (rows): The total number of rows sorted. This statistic can be divided by the 'sorts (total)' statistic to determine rows per sort. It is an indicator of data volumes and application characteristics.

• table fetch by rowid: The number of rows returned using ROWID (due to index access or because a SQL statement of the form "where rowid = &rowid" was issued).

• table scans (rows gotten): The total number of rows processed during full table scans.

• table scans (blocks gotten): The number of blocks scanned during full table scans, excluding those for split rows.

• user commits + user rollbacks: This provides the total number of transactions on the system. This number is used as the divisor when calculating the per-transaction ratios for other statistics. For example, to calculate the number of logical reads per transaction, use the following formula: session logical reads / (user commits + user rollbacks).

Notes on Physical I/O

A physical read as reported by Oracle might not result in an actual physical disk I/O operation. This is possible because most operating systems have an operating system filesystem cache where the block might be present. Alternatively, the block might also be present in disk or controller level cache, again avoiding an actual I/O. A physical read as reported by Oracle merely indicates that the required block was not in the buffer cache (or in the case of a direct read operation, was required to be read into private memory).

Instance Efficiency Ratios From V$SYSSTAT Statistics

Below are typical instance efficiency ratios calculated from V$SYSSTAT data. Each ratio's computed value should all be as close as possible to 1:

Buffer cache hit ratio: This is a good indicator of whether the buffer cache is too small.

1 - ((physical reads - physical reads direct - physical reads direct (lob)) / session logical reads)

Soft parse ratio: This shows whether there are many hard parses on the system. The ratio should be compared to the raw statistics to ensure accuracy. For example, a soft parse ratio of .2 typically indicates a high hard parse rate. However, if the total number of parses is low, then the ratio should be disregarded.

1 - ( parse count (hard) / parse count (total) )

In-memory sort ratio: This shows the proportion of sorts that are performed in memory. Optimally, in an operational (OLTP) system, most sorts are small and can be performed solely as in-memory sorts.

sorts (memory) / ( sorts (memory) + sorts (disk) )

Parse to execute ratio: In an operational environment, optimally a SQL statement should be parsed once and executed many times.

1 - (parse count/execute count)

Parse CPU to total CPU ratio: This shows how much of the total CPU time used was spent on activities other than parsing. When this ratio is low, the system is performing too many parses.

1 - (parse time cpu / CPU used by this session)

Parse time CPU to parse time elapsed: Often, this can indicate latch contention. The ratio calculates whether the time spent parsing is allocated to CPU cycles (that is, productive work) or whether the time spent parsing was not spent on CPU cycles. Time spent parsing not on CPU cycles usually indicates that the time was spent sleeping due to latch contention.

parse time cpu / parse time elapsed

Load Profile Data from V$SYSSTAT Statistics

To determine the load profile of the system, normalize the following statistics per second and per transaction: logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.

The normalized data can be examined to see if the 'rates' are high, or it can be compared to another baseline data set to identify how the system profile is changing over time. For example, block changes per transaction is calculated by the following:

db block changes / ( user commits + user rollbacks )

Additional computed statistics that measure load include the following:

Blocks changed per read: This shows the proportion of block changes to block reads. It is an indication of whether the system is predominantly read only or whether the system performs many data changes (inserts/updates/deletes).

db block changes / session logical reads

Rows per sort:

sorts (rows) / ( sorts (memory) + sorts (disk) )

Join Columns for V$SYSSTAT

Column:	Fixed View:	Joined Column(s):
STATISTIC#	V$STATNAME	STATISTIC#

V$SYSTEM_EVENT

This view is a summary of waits for an event by an instance. While V$SESSION_WAIT shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all the event waits on the instance since it started. It is useful to get a historical picture of waits on the system. By taking two snapshots and doing the delta on the waits, you can determine the waits on the system in a given time interval.

Useful Columns for V$SYSTEM_EVENT

• EVENT: Name of the wait event

• TOTAL_WAITS: Total number of waits for this event

• 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

Example - Finding the Total Waits on the System

SELECT event, total_waits waits, total_timeouts timeouts,
       time_waited total_time, average_wait avg
  FROM V$SYSTEM_EVENT 
  ORDER BY 4 DESC;

EVENT                                   WAITS  TIMEOUTS TOTAL_TIME      AVG
---------------------------------- ---------- --------- ---------- --------
SQL*Net message from client         112079628         0 8622695365    76.93
virtual circuit status               83559794   1168000 4275791401    51.17
rdbms ipc message                   131463191 115900505 2865926648    21.80
dispatcher timer                    311975975 168152330 2296760866     7.36
PX Idle Wait                          7198490   7198559 1439690729   199.99
pmon timer                             939711    939639  287866277   306.33
smon timer                               9892      9114  287627013 29076.73
lock manager wait for remote mes     72001548  71967858  287526387     3.99
db file sequential read              29419894         0   32395392     1.10
PL/SQL lock timer                       19725     19688   29702609  1505.83
log file sync                         7055611        86    9550819     1.35
log file parallel write               7184801         4    8123534     1.13
SQL*Net more data from client          991402         0    3543149     3.57
db file parallel write                 727317         0    3012928     4.14
control file parallel write            950531         0    1975646     2.07
log file sequential read              1162465         0     813715     0.69
enqueue                                  9975      7692     423191    42.42
direct path read                       453873         0     298944     0.65
db file scattered read                 347172         0     292875     0.84
row cache lock                         472207        25     169365     0.35
direct path write                      124323         0     132075     1.06
buffer busy due to global cache        148122         0     122381     0.82
SQL*Net more data to client          17171954        52     101762     0.00
db file parallel read                   68849         0     100842     1.46
DFS lock handle                         18615      1080      97651     5.24
SQL*Net message to client           112079756         0      77604     0.00
control file sequential read            65793         0      62560     0.95
buffer busy waits                      132402        97      60351     0.45
latch free                              67675     57975      58365     0.86
log file switch completion               1449        24      34244    23.63
db file single write                    10868         0      25518     2.34
SQL*Net break/reset to client           19130         0       9387     0.49
LGWR wait for redo copy                120199       356       8613     0.07
global cache lock busy                   4447         0       7574     1.70
undo segment extension                5363841   5363828       6375     0.00
log file single write                    2143         0       6267     2.92
refresh controlfile command              2644         0       4837     1.82
library cache load lock                    49        10       3859    78.75
file open                              178566         0       2930     0.01
switch logfile command                    100         0       2468    24.68
library cache pin                        9261         1       1716     0.18
pipe get                                    9         3       1460   162.22
rdbms ipc reply                         10296         0        846     0.08
wait for gms registration                  32        32        672    21.00
process startup                            43         2        662    15.39
file identify                            5438         0        584     0.10
control file single write                 332         0        475     1.43
Null event                                 17        17        409    24.05
log buffer space                           18         0        209    11.61
wait for lock db to unfreeze                1         1        199   199.00
local write wait                           11         0         44     4.00
LMON wait for LMD to inherit commu          1         1         10    10.00
wait for lock db to become frozen           2         2          3     1.50
instance state change                       2         0          0     0.00
global cache bg acks                        2         0          0     0.00
buffer deadlock                           141       141          0     0.00

To find the bottlenecks:

• Remove the idle events.

• Examine the time spent waiting for different events.

• Examine the average time per wait also, because some waits (like log file switch completion) might happen only periodically, but cause a big performance hit when they happen.

V$UNDOSTAT

This view monitors how undo space and transactions are executed in the current instance. Statistics for undo space consumption, transaction concurrency, and length of queries in the instance are available.

Useful Columns for V$UNDOSTAT

• Endtime: End time for each ten minute interval

• UndoBlocksUsed: Total number of undo blocks consumed

• TxnConcurrency: Maximum number of transactions executed concurrently

• TxnTotal: Total number of transactions executed within the interval

• QueryLength: Maximum length of queries, in seconds executed in the instance

• ExtentsStolen: Number of times an undo extent must be transferred from one undo segment to another within the interval

• SSTooOldError: Number of 'Snapshot Too Old' errors that occurred within the interval

• UNDOTSN: undo tablespaces in service during each time period

The first row of the view shows statistics for the current time interval. Each subsequent row represents a ten minute interval. There is a total of 144 rows, spanning a 24 hour cycle.

Example - V$UNDOSTAT

The example below shows how undo space is consumed in the system for the previous 24 hours from the time 16:07.

SELECT * FROM V$UNDOSTAT;

End-Time UndoBlocks  TxnConcrcy  TxnTotal  QueryLen ExtentsStolen  SSTooOldError
-------- ----------  ----------  --------  -------- -------------  -------------
16:07          252          15       1511     25         2              0
16:00          752          16       1467    150         0              0
15:50          873          21       1954     45         4              0
15:40         1187          45       3210    633        20              1
15:30         1120          28       2498   1202         5              0
15:20          882          22       2002     55         0              0

Among the statistics collected, you see that the peak undo consumption happened at the interval of (15:30, 15:40). 1187 undo blocks were consumed in 10 minutes (or about two blocks per second). Also, the highest transaction concurrency occurred during that same period with 45 transactions executing at the same time. The longest query (1202 seconds) was executed (and ended) in the period (15:20, 15:30). Note that the query actually was started in the interval (15:00, 15:10) and continued until around 15:20.

V$WAITSTAT

This view keeps a summary all buffer waits since instance startup. It is useful for breaking down the waits by class if you see a large number of buffer busy waits on the system.

Useful Columns for V$WAITSTAT

• class: Class of block (data segment header, undo segment header, data block)

• waits: Number of waits for this class of blocks

• time: Total time waited for this class of block

Reasons for Waits

The following are possible reasons for waits:

• Undo segment header: not enough rollback segments

• Data segment header/freelist: freelist contention

• Data block

• Large number of CR clones for the buffer

• Range scans on indexes with large number of deletions

• Full table scans on tables with large number of deleted rows

• Blocks with high concurrency

  See Also: Chapter 22, "Instance Tuning" for more information on wait events