Dynamic Performance Views.........

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.

Example 24–1 Summary of Shared Pool Executions and Memory Usage

The following query shows the distribution of 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(sharable_mem)
FROM V$DB_OBJECT_CACHE
GROUP BY type, kept;
Example 24–2 Finding Objects with Large Number of Loads

SELECT owner, name sharable_mem, kept, loads
FROM V$DB_OBJECT_CACHE
WHERE loads > 1
OR invalidations > 0
ORDER BY loads DESC;
Example 24–3 Finding Large Unpinned Objects

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


SELECT owner, name, sharable_mem, kept
FROM V$DB_OBJECT_CACHE
WHERE sharable_mem > 102400
AND kept = ‘NO’
ORDER BY sharable_mem DESC;

V$FILESTAT

This view keeps information on physical I/O activity 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):

n Number of physical reads and writes
n Number of blocks read and written
n 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.

Notes on V$FILESTAT

Physical reads and blocks read can be different because of multiblock read calls.
Physical writes and blocks written can differ because of direct writes by
processes.

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

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

Reads (into buffer cache as well as direct reads) are done by server processes.
Writes from buffer cache are handled only by the DBWR. The direct writes are
handled by the server processes.

Example 24–4 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#;
Example 24–5 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;
Example 24–6 Querying V$LATCH
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, name));
Initially, the snap_id has been set to 0. After some interval of time, the snap_
latch table is updated with the snap_id set to 1:
INSERT INTO snap_latch
SELECT 1, sysdate, a.*
FROM V$LATCH a;

Note that you must increment the snap_id each time you use the previous SQL
statement to insert records.
After you inserted records for consecutive intervals, use the following SELECT
statement to displays statistics. Note that zero is substituted when there is an attempt to divide by zero.

SELECT SUBSTR(a.name,1,20) NAME, (a.gets-b.gets)/1000 "Gets(K)",
(a.gets-b.gets)/(86400*(a.snap_date-b.snap_date)) "Get/s",
DECODE ((a.gets-b.gets), 0, 0, (100*(a.misses-b.misses)/(a.gets-b.gets))) MISS,
DECODE ((a.misses-b.misses), 0, 0,(100*(a.spin_gets-b.spin_gets)/(a.misses-b.misses))) SPIN,
(a.immediate_gets-b.immediate_gets)/1000 "Iget(K)",
(a.immediate_gets-b.immediate_gets)/ (86400*(a.snap_date-b.snap_date)) "IGet/s",
DECODE ((a.immediate_gets-b.immediate_gets), 0, 0,
(100*(a.immediate_misses-b.immediate_misses)/ (a.immediate_gets-b.immediate_gets))) IMISS
FROM snap_latch a, snap_latch b
WHERE a.name = b.name
AND 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;
Before running the previous SQL statement, you may want to specify various
display formatting setups, such as:

SET LIN 120
SET PAGES 60
SET NUMFORMAT 999999.9


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 the V$LATCH_CHILDREN view.

SELECT name, count(*)
FROM v$latch_children
ORDER BY count(*) desc;
V$LATCHHOLDER

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
This latch is useful in finding sessions that might be holding latches for a significant
amount of time.

Example 24–9 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:
n Specific parts (namespace) of the library cache (shared pool)
n Possible causes of problems
Then use V$DB_OBJECT_CACHE, V$SQLAREA to get more details.


Example 24–10 Querying V$LIBRARYCACHE

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:

n High RELOADS or INVALIDATIONS

n Low GETHITRATIO or GETPINRATIO

High number of RELOADS could be due to the following:

n Objects being invalidated (large number of INVALIDATIONS)

n Objects getting swapped out of memory

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

Low PINHITRATIO could indicate the following:

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

n 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$LIBRARY_CACHE_MEMORY

This fixed view summarizes the current memory use of the library cache, by library
cache object type. The view can be queried often, without increasing library cache
latch contention

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 blocked on.
4. Use V$SESSION to get more details about the program and user holding the
lock.

Useful Columns for V$LOCK

n SID: Identifier of the session holding/requesting the lock
n TYPE: Type of lock
n LMODE: Lock mode in which the session holds the lock
n REQUEST: Lock mode in which the session requests the lock
n ID1, ID2: Lock resource identifiers

Common Lock Types
Several common locks are described in this section.

TX: Row Transaction Lock

n This lock is required in exclusive mode (mode 6) to change data.
n One lock is acquired for each active transaction. It is released when the
transaction ends due to a commit or rollback.
n 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
(mode 4). It is released when the session gets an ITL entry for the block.
n 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.
n The lock points to the rollback segment and transaction table entries for the
transaction.
Do the following to avoid contention on this enqueue:
n To avoid contention on TX-6 enqueues, review the application.
n To avoid contention on TX-4 enqueues, consider increasing INITRANS for the
object.

TM: DML Lock

n 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.
n This lock is also acquired in shared mode when executing DML statements such
as INSERT, UPDATE, or DELETE. This prevents other sessions from executing a
DDL statement on the same object concurrently.
n For every object whose data is being changed, a TM lock is required.
n The lock points to the object.
To avoid contention on TM enqueues, consider disabling the table lock for the
object. Disabling the table lock prevents any DDL from executing on the object.


ST - Space Transaction Lock

n There is only one lock for each database (not instance).
n 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.
n 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:
n Use true temporary tablespaces, utilizing tempfiles. Temporary segments are
not created and dropped after every sort to disk.
n Use locally managed tablespaces
n Size rollback segments to avoid dynamic extension and shrinking, or use
automatic undo management.
n Avoid application practices that create and drop database objects.

UL - User Defined Locks

Users can define their own locks.

Common Modes for Request/Lmode

0: None
2: Row Share: used for shared DML locks
n 4: Share: used for shared TX when waiting for ITL entry
n 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.



Example 24–11 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 LM

Example 24–12 Finding the Statements being Executed by These Sessions

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

Example 24–13 Finding the Text for These SQL Statements
HASH_VALUE SQL_TEXT
---------- ----------------------------------------------------------------
1141994875 S
ELECT 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 idle

Is the session executing long running queries while holding the lock?


V$MTTR_TARGET_ADVICE

V$MTTR_TARGET_ADVICE contains rows that predict the number of physical I/Os
for the MTTR corresponding to each row. The rows also compute a physical I/O
factor, which is the ratio of the number of estimated I/Os to the number of I/Os
actually performed by the current MTTR setting during the measurement interval.

V$MYSTAT
This view is a subset of V$SESSTAT returning current session’s statistics. When
auditing resource usage for sessions through 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.

Example 24–14 Finding 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 115860



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 24–15 Finding Sessions That Have More Than 400 Cursors Open


SELECT sid, count(*)
FROM v$open_cursor
GROUP BY sid
HAVING COUNT(*) > 400
ORDER BY count(*) 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
n NAME: Name of the parameter
n VALUE: Current value for this session (if modified within the session);
otherwise, the instance-wide value
n ISDEFAULT: Whether the parameter value is the default value
n ISSES_MODIFIABLE: Whether this parameter can be modified at the session
level
n ISSYS_MODIFIABLE: Whether this parameter can be modified at an
instance-wide level dynamically after the instance has started
n 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
n ISADJUSTED: Whether Oracle has adjusted a value specified by the user

n DESCRIPTION: Brief description of the parameter
n UPDATE_COMMENT: Set if a comment has been supplied by the DBA for this
parameter

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 24–16 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 preceding 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.


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:
n If the bottleneck on the database server is related to an operating system
resource (for example, CPU, memory), and if the top resource users are
localized within a small set of server processes, then perform the following
steps

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.
n 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.
n 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.
n 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
n PID: Oracle process ID of the process
n SPID: Operating system process ID of the process


Example 24–17 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 24–18 Finding 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
n USN: Rollback segment number

n RSSIZE: Current size of the rollback segment

n XACTS: Number of active transactions

Columns Useful for Doing a Delta Over a Period of Time

n WRITES: Number of bytes written to the rollback segment

n SHRINKS: Number of times the rollback segment grew past OPTIMAL and
shrank back

n EXTENDS: Number of times the rollback segment had to extend because there
was an active transaction in the next extent

n WRAPS: Number of times the rollback segment wrapped around

n GETS: Number of header gets

n WAITS: Number of header waits

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.

Uses for V$ROWCACHE Data
n 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.
n 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 active also helps.
n 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. Possible solutions
include sizing the next extents appropriately, or using locally managed
tablespaces. If the space allocation is occurring on the temp tablespace, then use
a true temporary tablespace for the temp.
n Identify large amounts of sequence number generation occurring. Modifications
to dc_sequences indicates this. Check to see whether the number of cache
entries for each 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 24–20 Querying 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$SEGMENT_STATISTICS
This is a user-friendly view, available with Oracle9i Release 2 (9.2) and higher, that
allows real-time monitoring of segment-level statistics, enabling a DBA to identify
performance problems associated with an individual table or index.

V$SEGSTAT

This is a high-efficiency view for real-time monitoring of segment-level statistics,
available with Oracle9i Release 2 (9.2) and higher.


V$SEGSTAT_NAME

This is a statistics property view for segment-level statistics, available with Oracle9i
Release 2 (9.2) and higher

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.

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.
STATUS: This column identifies if the session is:
n Active: executing a SQL statement (waiting for/using a resource)
n Inactive: waiting for more work (that is, SQL statements)
n 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
n SID: Session identifier, used to join to other columns
n 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)
n 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:
n OSUSER: Operating system user name for the client process
n MACHINE: Machine where the client process is executing
n TERMINAL: Terminal (if applicable) where the client process is running
n PROCESS: Process ID of the client process
n 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:
n CLIENT_INFO: Set in DBMS_APPLICATION_INFO
n ACTION: Set in DBMS_APPLICATION_INFO
n MODULE: Set in DBMS_APPLICATION_INFO
The following V$SESSION columns are also useful:

ROW_WAIT_OBJ#
n ROW_WAIT_FILE#
n ROW_WAIT_BLOCK#
n ROW_WAIT_ROW#

Example 24–21 Finding Your Session

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


SID OSUSER USERNAME MACHINE PROCESS
----- ---------- ----------- ----------- --------
398 amerora PERFSTAT rgmdbs1 26582

Example 24–22 Finding a Session When the Machine Is Known

SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl'
AND machine = 'rgmdbs1';

SID OSUSER USERNAME MACHINE TERMINAL
---- --------- --------- ---------- ----------
398 amerora PERFSTAT rgmdbs1 pts/tl

Example 24–23 Finding the 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
n SID: Identifier for the session
n EVENT: Name of the wait event
n TOTAL_WAITS: Total number of waits for this event by this session
n TIME_WAITED: Total time waited for this event (in hundredths of a second)
n 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 24–24 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 time_waited 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 for every session connected to the instance. It
indicates if the session is:

Using a resource
n Waiting for a resource
n Idle (waiting on one of the idle events)
Useful Columns for V$SESSION_WAIT
n SID: Session identifier for the session
n EVENT: Event the session is currently waiting for, or the last event the session
had to wait for
n 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
n SEQ#: Gets incremented with every wait of the session
n P1, P2, P3: Wait event specific details for the wait
n P1TEXT, P2TEXT, P3TEXT: Description of P1,P2,P3 for the given event
Table 24–19 shows an example of how the EVENT, SEQ#, and WAIT_TIME might
change over a period of time:
See Also: Oracle9i Database Reference and "Wait Events" on
page 22-24
Table 24–18 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


Example 24–25 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 count(*);
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:
n Most of the sessions are waiting for idle events like SQL*Net message from
client, pipe get, PMON timer, and so on.
n 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
following statement 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 count(*);

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:
n There are a total of 2324 sessions.
n 20 sessions are actively using resources (active sessions without an active wait).
n 1463 sessions are waiting.
n 58 of these are waiting for non-idle 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.
n 14 sessions are locked out (and may be experiencing poor performance).
n PMON and SMON are sleeping on their timers.
n 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:
n A count of the number of times an action occurred, such as user commits
n A running total of volumes of data generated, accessed or manipulated, such as
redo size
n The cumulative time spent performing some actions, such as CPU used by
this session, if TIMED_STATISTICS is set to TRUE

Note: Timed statistics are automatically collected for the database
if the initialization parameter STATISTICS_LEVEL is set to
TYPICAL or ALL. If STATISTICS_LEVEL is set to BASIC, then you
must set TIMED_STATISTICS to TRUE to enable collection of
timed statistics.
If you explicitly set DB_CACHE_ADVICE, TIMED_STATISTICS, or
TIMED_OS_STATISTICS, either in the initialization parameter file
or by using ALTER_SYSTEM or ALTER SESSION, the explicitly set
value overrides the value derived from STATISTICS_LEVEL

The differences between V$SYSSTAT and V$SESSTAT are the following:
n V$SESSTAT only stores data for each session, whereas V$SYSSTAT stores the
accumulated values for all sessions.
n 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.
n 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:
n The highest resource usage
n The highest average resource usage rate (ratio of resource usage to logon time)
n 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
n SID: Session identifier
STATISTIC#: Resource identifier
n VALUE: Resource usage


Example 24–26 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 (each
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 each 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:
n 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).
n 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 for each 10 ms, and 1 physical read for
each 10 ms for this configuration. This ratio was rounded to the ballpark
number of 100

V$SHARED_POOL_ADVICE


V$SHARED_POOL_ADVICE displays information about estimated parse time
savings in the shared pool for different sizes. The sizes range from 50% to 200% of
the current shared pool size, in equal intervals. The value of the interval depends on
the current size of the shared pool.

Table 24–22 V$SHARED_POOL_ADVICE View
Column Datatype Description
SHARED_POOL_SIZE_FOR_
ESTIMATE
NUMBER Shared pool size for the estimate (in megabytes)
SHARED_POOL_SIZE_FACTOR NUMBER Size factor with respect to the current shared pool size
ESTD_LC_SIZE NUMBER Estimated memory in use by the library cache (in megabytes)
ESTD_LC_MEMORY_OBJECTS NUMBER Estimated number of library cache memory objects in the shared pool of
the specified size
ESTD_LC_TIME_SAVED NUMBER Estimated elapsed parse time saved (in seconds), owing to library cache
memory objects being found in a shared pool of the specified size.
ESTD_LC_TIME_SAVED_FACTOR NUMBER Estimated parse time saved factor with respect to the current shared
pool size
ESTD_LC_MEMORY_OBJECT_HITS NUMBER Estimated number of times a library cache memory object was found in
a shared pool of the specified size

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.
The PLAN_HASH_VALUE column contains the numerical representation of the SQL
plan for the cursor and can be used for comparing plans. PLAN_HASH_VALUE
allows you to easily identify whether or not two plans are the same without
comparing the two plans line by line.


V$SQL_PLAN


This view provides a way of examining the execution plan for cursors that were
executed and are still cached.
Normally, the information in this view is very similar to the output of an EXPLAIN
PLAN statement. However, EXPLAIN PLAN shows a theoretical plan that might 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
n Determining the current execution plan
n Identifying the effect of creating an index on a table
n Finding cursors containing a certain access path (for example, full table scan or
index range scan)
n Identifying indexes that are, or are not, selected by the optimizer
n 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:
n Dropping or creating indexes
n Running the ANALYZE statement on the database objects
n Modifying initialization parameter values
n Switching from the rule-based optimizer to the cost-based optimizer
n After upgrading 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:
n ADDRESS: Address of the handle to the parent for this cursor
n 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.
n 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.
n OPERATION: Name of the internal operation performed in this step; for
example, TABLE ACCESS
n OPTIONS: A variation on the operation described in the OPERATION column;
for example, FULL
n 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
n OBJECT#: Object number of the table or the index

OBJECT_OWNER: Name of the user who owns the schema containing the table
or index
n OBJECT_NAME: Name of the table or index
n 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
n ID: A number assigned to each step in the execution plan
n PARENT_ID: ID of the next execution step that operates on the output of the
current step
n 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.
n POSITION: Order of processing for operations that all have the same PARENT_
ID
n 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
n CARDINALITY: The estimate, by the cost-based optimizer, of the number of
rows produced by the operation
n BYTES: The estimate, by the cost-based optimizer, of the number of bytes
produced by the operation
n OTHER_TAG: Describes the contents of the OTHER column (see Chapter 9,
"Using EXPLAIN PLAN" for values)
n PARTITION_START: The start partition of a range of accessed partition

n PARTITION_STOP: The stop partition of a range of accessed partitions
n PARTITION_ID: The step that has computed the pair of values of the
PARTITION_START and PARTITION_STOP columns
n OTHER: Other information that is specific to the execution step that a user may
find useful (see Chapter 9, "Using EXPLAIN PLAN" for values)
n DISTRIBUTION: For parallel query, stores the method used to distribute rows
from producer query servers to consumer query servers

n 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
n 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
n TEMP_SPACE: Temporary space usage of the operation (sort or hash-join) as
estimated by the optimizer's cost-based approach; for statements that use the
rule-based approach, this column is null
n ACCESS_PREDICATES: Predicates used to locate rows in an access structure;
for example, start or stop predicates for an index range scan
n FILTER_PREDICATES: Predicates used to filter rows before producing them
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.


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 for which to return the plan is identified by the
statement's HASH_VALUE and address.
Example 24–27 shows a query and sample output from V$SQL_PLAN (assumes only
one child cursor):
Example 24–27 Querying V$SQL_PLAN


SELECT /* TAG */ count(*)
FROM employees e, departments d
WHERE e.department_id = d.department_id;

COUNT(*)
----------
14

column operation format a20
column options format a20
column object_name format a20
column cost format a20
column cost format 9999
SELECT sql_text, address, hash_value
FROM v$sql
WHERE sql_text like '%TAG%';
SQL_TEXT ADDRESS HASH_VALUE
-------- -------- ----------
82117BEC 171077025


SELECT sql_text, address, hash_value
FROM v$sql
WHERE sql_text LIKE '%TAG%'
SELECT /* TAG */ count(*)
FROM employees e, departments d
WHERE e.department_id = d.department_id
SQL_TEXT ADDRESS HASH_VALUE
-------- -------- ----------
82157784 1224822469

SELECT operation, options, object_name, cost
FROM v$sql_plan
WHERE address = '\ 82157784'
AND hash_value = 1224822469;

OPERATION OPTIONS OBJECT_NAME COST
-------------------- ------------- ------------------ ----
SELECT STATEMENT 5
SORT
AGGREGATE
HASH JOIN 5
TABLE ACCESS FULL DEPARTMENTS 2
TABLE ACCESS FULL EMPLOYEES 2
6 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. The following are a few examples of
how a SQL statement can result in more than one cursor:
n When the same table name resolves to two separate tables:
User1: SELECT * FROM EMPLOYEES;
User2: SELECT * FROM EMPLOYEES;
Where user2 has his own employee table, and user1 uses the table referenced by
a public synonym.
n 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$SQL_PLAN_STATISTICS

This view provides, for each cached cursor, the execution statistics of each operation
in the execution plan.
To view row source statistics in this view, the DBA must set the parameter
STATISTICS_LEVEL to ALL.


V$SQL_PLAN_STATISTICS_ALL

This table concatenates information from V$SQL_PLAN with execution statistics
from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA. V$SQL_WORKAREA
contains memory usage statistics for row sources that use SQL memory (for
example, hash-join and sort).

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
n HASH_VALUE: Hash value of the SQL statement
n ADDRESS: SGA address for the SQL statement
These 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.
n PARSING_USER_ID: User who parsed the first cursor for the statement
n VERSION_COUNT: Number of cursors for the statement
n KEPT_VERSIONS: Cursors of the statement pinned using DBMS_SHARED_
POOL.KEEP()
n SHARABLE_MEMORY: Total shared memory used by the cursor
n PERSISTENT_MEMORY: Total persistent memory used by the cursor
n RUNTIME_MEMORY: Total runtime memory used by the cursor
n SQL_TEXT: Up to first 1000 characters of SQL statement
n 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

n BUFFER_GETS: Number of logical reads for this statement
n DISK_READS: Number of physical reads for this statement
n SORTS: Number of sorts for this statement
n CPU_TIME: CPU time used for parsing and executing this statement
n ELAPSED_TIME: Elapsed time for parsing and executing this statement
n PARSE_CALLS: Number of parse calls (hard and soft) for this statement
n EXECUTIONS: Number of times this statement was executed
n INVALIDATIONS: Number of times the cursors for this statement have been
invalidated
n LOADS: Number of loads (and reloads) for this statement
n ROWS_PROCESSED: Total number of rows this statement returns

Example 24–28 Finding Resource-intensive SQL
There are several costs you can use:
n Total logical I/O (LIO), LIO for each execution
n Total physical I/O (PIO), PIO for each execution
n PIO/LIO (poor cache hit ratio)
n parse_calls, parse_calls for each 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

Example 24–29 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$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:
n A count of the number of times an action occurred (user commits)
n A running total of volumes of data generated, accessed, or manipulated (redo
size)
n If TIMED_STATISTICS is true, then the cumulative time spent performing
some actions (CPU used by this session)
Useful Columns in V$SYSSTAT
n STATISTIC#: Identifier for the statistic
n NAME: Statistic name
n VALUE: Resource usage

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
(for each transaction, for each execution, for each second, or for each 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
This section describes 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.
Key Database Usage Indicators
n 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.
n 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 for each transaction level, this
statistic indicates the rate at which buffers are being dirtied.
n execute count: The total number of SQL statement executions (including
recursive SQL).
n 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.
n 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 logon rate. Optimally, there should
be no more than two logons each second. To contrast, a logon rate of 50 a
second is considered very high. Applications that continually connect and
disconnect from the database (for example, once for each transaction) do not
scale well.
n 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.
n 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.
n parse time cpu: Total CPU time spent parsing in hundredths of a second. This
includes both hard and soft parses.
n parse time elapsed: The total elapsed time for the parse call to complete.
n 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.
n 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.
n 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.
n redo size: The total amount of redo generated (and hence written to the log
buffer), in bytes. This statistic (normalized over seconds or over transactions) is
a good indicator of update activity.
n session logical reads: The number of logical read requests that can be
satisfied in the buffer cache or by a physical read.
n 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.
n sorts (rows): The total number of rows sorted. This statistic can be divided
by the ’sorts (total)’ statistic to determine rows for each 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).
n table scans (rows gotten): The total number of rows processed during full
table scans.
n table scans (blocks gotten): The number of blocks scanned during full
table scans, excluding those for split rows.
n user commits + user rollbacks: This provides the total number of
transactions on the system. This number is used as the divisor when calculating
the ratios for each transaction for other statistics. For example, to calculate the
number of logical reads for each 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 files system 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
The following 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 0.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 over
seconds and over transactions: 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 for each transaction is calculated
by the following:
db block changes / ( user commits + user rollbacks )
Additional computed statistics that measure load include the following:
n Blocks changed for each 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
n Rows for each sort:
sorts (rows) / ( sorts (memory) +sorts (disk) )

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
n EVENT: Name of the wait event
n TOTAL_WAITS: Total number of waits for this event
n TIME_WAITED: Total time waited for this event (in hundredths of a second)
n AVERAGE_WAIT: Average amount of time waited for this event by this session
(in hundredths of a second)
n TOTAL_TIMEOUTS: Number of times the wait timed out
Example 24–31 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;


To find the bottlenecks:
n Statspack lists idle events at the end.
n Examine the time spent waiting for different events.
n Examine the average time for each 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
n Endtime: End time for each ten minute interval
n UndoBlocksUsed: Total number of undo blocks consumed
n TxnConcurrency: Maximum number of transactions executed concurrently
n TxnTotal: Total number of transactions executed within the interval
n QueryLength: Maximum length of queries, in seconds executed in the instance
n ExtentsStolen: Number of times an undo extent must be transferred from
one undo segment to another within the interval
n SSTooOldError: Number of ’Snapshot Too Old’ errors that occurred within
the interval
n 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 24–32 Querying V$UNDOSTAT
This example 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 a 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
n class: Class of block (data segment header, undo segment header, data block)
n waits: Number of waits for this class of blocks
n 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
n Data segment header/freelist: freelist contention
n Data block
n Large number of CR clones for the buffer
n Range scans on indexes with large number of deletions
n Full table scans on tables with large number of deleted rows
n Blocks with high concurrency