learn-oracle
Backing Up to the Oracle Flash Recovery Area: Basic Scenarios







Dynamic Performance Views for Oracle Tuning

Dynamic Performance Tables

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

Database administrators can query and create views on the tables and grant access to those views to other users. These views are called fixed views because they cannot be altered or removed by the database administrator.

SYS owns the dynamic performance tables. By default, they are available only to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM. Their names all begin with V_$. Views are created on these tables, and then public synonyms are created for the views. The synonym names begin with V$.

Each view belongs to one of the following categories:

Current State Views

The views listed in Table 24-1 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$LATCHHOLDER

Sessions/processes holding a latch

V$OPEN_CURSOR

Cursors opened by sessions on the instance

V$SESSION

Sessions currently connected to the instance

V$SESSION_WAIT

Different resources sessions are currently waiting for

Counter/Accumulator Views

These views keep track of how many times some activity has occurred since instance/session startup. Select from the view directly to see activity since startup.

If you are interested in activity happening in a given time interval, then take a snapshot before and after the time interval, and the delta between the two snapshots provides the activity during that time interval. This is similar to how operating system utilities like sar, vmstat, and iostat work. Tools provided by Oracle, like Statspack and BSTAT/ESTAT, do this delta to provide a report of activity in a given interval.


Note:

Snapshots should be taken during steady-state, not immediately after system startup. Extra overhead is incurred during system ramp-up, which may not accurately reflect the performance of the system at steady-state.


Table 24-2  Summary Since Session Startup
Fixed View Description

V$DB_OBJECT_CACHE

Object level statistics in shared pool

V$FILESTAT

File level summary of the I/O activity

V$LATCH

Latch activity summary

V$LATCH_CHILDREN

Latch activity for child latches

V$LIBRARYCACHE

Namespace level summary for shared pool

V$LIBRARY_CACHE_MEMORY

Summary of the current memory use of the library cache, by library cache object type

V$MYSTAT

Resource usage summary for your own session

V$ROLLSTAT

Rollback segment activity summary

V$ROWCACHE

Data dictionary activity summary

V$SEGMENT_STATISTICS

User-friendly DBA view for real-time monitoring of segment-level statistics

V$SEGSTAT

High-efficiency view for real-time monitoring of segment-level statistics

V$SESSION_EVENT

Session-level summary of all the waits for current sessions

V$SESSTAT

Session-level summary of resource usage since session startup

V$LIBRARY_CACHE_MEMORY

Simulation of the shared pool's LRU list mechanism

V$SQL

Child cursor details for V$SQLAREA

V$SQLAREA

Shared pool details for statements/anonymous blocks

V$SYSSTAT

Summary of resource usage

V$SYSTEM_EVENT

Instance wide summary of resources waited for

V$UNDOSTAT

Histogram of undo usage. Each row represents a 10-minute interval.

V$WAITSTAT

Break down of buffer waits by block class

Information 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-3  Information Views
Fixed View Description

V$MTTR_TARGET_ADVICE

Advisory information collected by MTTR advisory, when FAST_START_MTTR_TARGET is set

V$PARAMETER and V$SYSTEM_PARAMETER

Parameters values for your session

Instance wide parameter values

V$PROCESS

Server processes (background and foreground)

V$SEGSTAT_NAME

Statistics property view for segment-level statistics

V$SQL_PLAN

Execution plan for cursors that were recently executed

V$SQL_PLAN_STATISTICS

Execution statistics of each operation in the execution plan

V$SQL_PLAN_STATISTICS_ALL

Concatenates information in V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA

V$SQLTEXT

SQL text of statements in the shared pool

V$STATISTICS_LEVEL

Status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter

Description of Dynamic Performance Views

This section discusses the details of some of the 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.

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 following columns keep statistics on the object since its first load:

  • LOADS: Number of times this object had to be loaded
  • INVALIDATIONS: Number of times this object was invalidated

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):

  • 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

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.

Join Columns for V$FILESTAT

Table 24-4 lists the join columns for V$FILESTAT.

Table 24-4 Join Columns for V$FILESTAT
Column View Joined Column(s)

FILE#

DBA_DATA_FILES

FILE_ID

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#;

The preceding 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/ora81/dbs/ora_system.dbf                   7679       2735
/oracle/ora81/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 for each batch, with all blocks in the same batch given the same time after the completion of the write I/O.


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;

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
  • WAIT_TIME: Elapsed time spent waiting for this latch
  • MISSES_WAITLIST: Number of latch misses on the associated wait list latch
  • SLEEPS_WAITLIST: Number of sleeps while trying to acquire the associated wait list latch
  • WAIT_TIME_WAITLIST: Wait time for the associated wait list latch

Join Columns for V$LATCH

Table 24-5 lists the join columns for V$LATCH.

Table 24-5 Join Columns for V$LATCH
Column 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#

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

Example 24-7 Sample Latch Statistics

The following example output shows the latch statistics obtained by doing a delta over a period of one hour as was done with the V$FILESTAT numbers. Those latches that had misses less than 0.1% of the gets have been 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?
  • How many sleeps were on the latch?

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 running a query that checks the sleeps for these latches to see if there is actually a problem, such as the following output:

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

Example 24-8 Finding the Number of Multiple Latches on the System

SELECT name, count(*) 
  FROM v$latch_children
 ORDER BY count(*) 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$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.

Join Columns for V$LATCHHOLDER

Table 24-6 lists the join columns for V$LATCHHOLDER.

Table 24-6 Join Columns for V$LATCHHOLDER
Column 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 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:

  • 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. For example, 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 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:

  • 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$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. Column descriptions are listed in Table 24-7.

Table 24-7 V$LIBRARY_CACHE_MEMORY Column Description
Column Name Datatype Column Description

libcache_object_name

char(24)

Name of the library cache object type

libcache_pinned_memory

number

Amount of library cache memory pinned for all library cache objects of this type

libcache_pinned_count

number

Number of library cache objects of this type currently pinned

libcache_unpinned_memory

number

Amount of library cache memory unpinned for all library cache objects of this type

libcache_unpnned_count

number

Number of library cache objects of this type currently unpinned

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

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

Common Lock Types

Several common locks are described in this section.

TX: Row Transaction Lock
  • This lock is required in exclusive mode (mode 6) to change 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 (mode 4). 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.

Do the following to avoid contention on this enqueue:

  • To avoid contention on TX-6 enqueues, review the application.
  • To avoid contention on TX-4 enqueues, consider increasing INITRANS for the object.
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 when executing DML statements such as INSERT, UPDATE, or DELETE. This prevents other sessions from executing a DDL statement on the same object concurrently.
  • For every object whose data is being changed, a TM lock is required.
  • 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
  • There is only one lock for each 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, utilizing tempfiles. Temporary segments are not created and dropped after every sort to disk.
  • Use locally managed tablespaces
  • Size rollback segments to avoid dynamic extension and shrinking, or use automatic undo management.
  • 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

Table 24-8 lists the join columns for V$LOCK.

Table 24-8 Join Columns for V$LOCK
Column View Joined Column(s)

SID

V$SESSION

SID

ID1, ID2, TYPE

V$LOCK

ID1, ID2, TYPE

ID1

DBA_OBJECTS

OBJECT_ID

TRUNCID1/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 through V$TRANSACTION.

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      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 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);

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

Example 24-13 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 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. Column descriptions are listed in Table 24-9.

Table 24-9  V$MTTR_TARGET_ADVICE Column Description
Column Name Datatype Column Description

MTTR_TARGET_FOR_ESTIMATE

NUMBER

MTTR setting being simulated. It should equal to the current MTTR setting if this is the first row of the view.

ADVICE_STATUS

VARCHAR2(5)

Current status of MTTR simulation (ON|READY|OFF).

DIRTY_LIMIT

NUMBER

The dirty buffer limit derived from the MTTR being simulated.

ESTD_CACHE_WRITES

NUMBER

The estimated number of cache physical writes under this MTTR.

ESTD_CACHE_WRITE_FACTOR

NUMBER

The estimated cache physical write ratio under this MTTR. It is the ratio of the estimated number of cache writes to the number of cache writes under current MTTR setting.

ESTD_TOTAL_WRITES

NUMBER

The estimated total number of physical write under this MTTR.

ESTD_TOTAL_WRITE_FACTOR

NUMBER

The estimated total physical write ratio under this MTTR. It is the ratio of the estimated total number of physical writes to the total number of physical writes under current MTTR setting.

ESTD_TOTAL_IOS

NUMBER

The estimated total number of I/Os under this MTTR.

ESTD_TOTAL_IO_FACTOR

NUMBER

The estimated total I/O ratio under this MTTR. It is the ratio of the estimated total number of I/Os to the total number of I/Os under current MTTR setting.

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.

Join Columns for V$OPEN_CURSOR

Table 24-10 lists the join columns for V$OPEN_CURSOR.

Table 24-10 Join Columns for V$OPEN_CURSOR
Column View Joined Column(s)

HASH_VALUE, ADDRESS

V$SQLAREA, V$SQL, V$SQLTEXT

HASH_VALUE, ADDRESS

SID

V$SESSION

SID

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

  • NAME: Name of the parameter
  • VALUE: Current value for this session (if modified within the session); otherwise, the instance-wide value
  • ISDEFAULT: Whether the parameter value is the default value
  • 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:

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.


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), 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.
  • 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

Table 24-11 lists the join columns for V$PROCESS.

Table 24-11 Join Columns for V$PROCESS
Column View Joined Column(s)

ADDR

V$SESSION

PADDR

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

  • 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

Table 24-12 lists the join columns for V$ROLLSTAT.

Table 24-12 Join Columns for V$ROLLSTAT
Column View Joined Column(s)

USN

V$ROLLNAME

USN

Example 24-19 Querying 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:

    "Sizing the Shared Pool" for details about 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 active 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. 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.
  • 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.

Table 24-13  V$SEGMENT_STATISTICS View
Column Datatype Description

OWNER

VARCHAR2(30)

Owner of the object

OBJECT_NAME

VARCHAR2(30)

Name of the object

SUBOBJECT_NAME

VARCHAR2(30)

Name of the sub-object

TABLESPACE_NAME

VARCHAR2(30)

Name of the table space to which the object belongs

TS#

NUMBER

Tablespace number

OBJ#

NUMBER

Dictionary object number

DATAOBJ#

NUMBER

Data object number

OBJECT_TYPE

VARCHAR2(18)

Type of the object

STATISTIC_NAME

VARCHAR2(64)

Name of the statistic

STATISTIC#

NUMBER

Statistic number

VALUE

NUMBER

Statistic value

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.

Table 24-14  V$SEGSTAT View
Column Datatype Description

TS#

NUMBER

Tablespace number

OBJ#

NUMBER

Dictionary object number

DATAOBJ#

NUMBER

Data object number

STATISTIC_NAME

VARCHAR2(64)

Name of the statistic

STATISTIC#

NUMBER

Statistic number

VALUE

NUMBER

Statistic value

V$SEGSTAT_NAME

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

Table 24-15  V$SEGSTAT_NAME View
Column Datatype Description

STATISTIC#

NUMBER

Statistic number

NAME

VARCHAR2(64)

Statistic name

SAMPLED

VARCHAR2(3)

Whether or not it is a sampled statistic

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.


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

The following V$SESSION columns are also useful:

  • ROW_WAIT_OBJ#
  • ROW_WAIT_FILE#
  • ROW_WAIT_BLOCK#
  • ROW_WAIT_ROW#

Join Columns for V$SESSION

Table 24-16 is a list of several columns that can be used to join to other fixed views.

Table 24-16 Join Columns for V$SESSION
Column 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 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

  • 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

Table 24-17 is a list of join columns for V$SESSION_EVENT.

Table 24-17 Join Columns for V$SESSION_EVENT
Column View Joined Column(s)

SID

V$SESSION

SID

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
  • Waiting for a resource
  • Idle (waiting on one of the idle events)

Useful Columns for V$SESSION_WAIT

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

    Table 24-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

Table 24-19 shows an example of how the EVENT, SEQ#, and WAIT_TIME might change over a period of time:

Table 24-19  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

Table 24-20 is a list of join columns for V$SESSION_WAIT.

Table 24-20 Join Columns for V$SESSION_WAIT
Column View Joined Column(s)

SID

V$SESSION

SID

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:

  • 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 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:

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

    See Also:

    "Wait Events"

  • 14 sessions are locked out (and may be experiencing 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, such as user commits
  • A running total of volumes of data generated, accessed or manipulated, such as redo size
  • 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.


    See Also:

    "Setting the Level of Statistics Collection" for information about STATISTICS_LEVEL settings

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

  • V$SESSTAT only stores data for each session, 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

Table 24-21 lists the join columns for V$SESSTAT.

Table 24-21 Join Columns for V$SESSTAT
Column View Joined Column(s)

STATISTIC#

V$STATNAME

STATISTIC#

SID

V$SESSION

SID

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:

  • 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 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

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


Note:

Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for more information on this package.

However, you must use the ANALYZE statement rather than DBMS_STATS for statistics collection not related to the cost-based optimizer, such as:

  • To use the VALIDATE or LIST CHAINED ROWS clauses
  • To collect information on freelist blocks

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: 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
  • ACCESS_PREDICATES: Predicates used to locate rows in an access structure; for example, start or stop predicates for an index range scan
  • 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.

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.

Table 24-23 lists the join columns for V$SQL_PLAN.

Table 24-23 Join Columns for V$SQL_PLAN
Column View Joined Column(s)

ADDRESS, HASH_VALUE

V$SQLAREA

ADDRESS, HASH_VALUE

ADDRESS, HASH_VALUE, CHILD_NUMBER

V$SQL

ADDRESS, HASH_VALUE, CHILD_NUMBER

ADDRESS, HASH_VALUE

V$SQLTEXT

ADDRESS, HASH_VALUE

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:

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

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

    See Also:

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

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.

Table 24-24  V$SQL_PLAN_STATISTICS
Column Datatype Description

address

raw(4)

Address of the handle to the parent for this cursor

hash_value

number

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 locate the parent cursor.

child_number

number

Child cursor number using this work area. The columns (address, hash_value, and child_number) can be used to join with v$sql to locate the child cursor using this area.

operation_id

number

Number assigned to each step in the execution plan.

executions

number

Number of times this cursor has been executed.

last_starts

number

Number of times this operation has been started,Foot 1 during the last execution.

starts

number

Number of times this operation has been started, accumulated over the past executions.

last_output_rows

number

Number of rows produced by the row source, during the last execution.

output_rows

number

Number of rows produced by the row source, accumulated over the past executions.

last_cr_buffer_gets

number

Number of buffers retrieved in consistent mode, during the last execution. Buffers are usually retrieved in consistent mode for queries.

cr_buffer_gets

number

Number of buffers retrieved in consistent mode, accumulated over the past executions. Buffers are usually retrieved in consistent mode for queries.

last_cu_buffer_gets

number

Number of buffers retrieved in current mode, during the last execution. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

cu_buffer_gets

number

Number of buffers retrieved in current mode, accumulated over the past executions. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

last_disk_reads

number

Number of physical disk reads performed by the operation, during the last execution.

disk_reads

number

Number of physical disk reads performed by the operation, accumulated over the past executions.

last_disk_writes

number

Number of physical disk writes performed by the operation, during the last execution.

disk_writes

number

Number of physical disk writes performed by the operation, accumulated over the past executions.

last_elapsed_time

number

Elapsed time (in microseconds) corresponding to this operation, during the last execution.

elapsed_time

number

Elapsed time (in microseconds) corresponding to this operation, accumulated over the past executions.

1 An operation can be started several times if it is after an iterator (PARTITION or INLIST), i.e, the number of starts is in fact the number of iterations (number of partitions or number of elements in the in-list).

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

Table 24-25  V$SQL_PLAN_STATISTICS_ALL
Column Datatype Description

address

raw(4)

Address of the handle to the parent for this cursor

hash_value

number

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 information specific to the cursor.

child_number

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 information specific to the child cursor.

operation

varchar2(30)

Name of the internal operation performed in this step--for example, TABLE ACCESS.

options

varchar2(30)

A variation on the operation described in the OPERATION column--for example, FULL.

object_node

varchar2(10)

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#

number

Object number of the table or the index.

object_owner

varchar2(30)

Name of the user who owns the schema containing the table or index.

object_name

varchar2(30)

Name of the table or index.

optimizer

varchar2(20)

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 (for example, TABLE ACCESS), it tells whether the object is analyzed or not.

id

number

Number assigned to each step in the execution plan.

parent_id

number

ID of the next execution step that operates on the output of the current step.

depth

number

Depth (or level) of the operation in the tree; 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

number

Order of processing for operations that all have the same PARENT_ID.

cost

number

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

number

Estimate, by the cost-based optimizer, of the number of rows produced by the operation.

bytes

number

Estimate, by the cost-based optimizer, of the number of bytes produced by the operation.

other_tag

varchar2(35)

Describes the contents of the OTHER column; see Table 9-2 for values.

partition_start

varchar2(5)

Start partition of a range of accessed partitions.

partition_stop

varchar2(5)

Stop partition of a range of accessed partitions.

partition_id

number

Step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns.

other

varchar2(4000)

Other information that is specific to the execution step that a user may find useful; see Table 9-2 for values.

distribution

varchar2(20)

Method used to distribute rows from producer query servers to consumer query servers; see Table 9-3 for values.

cpu_cost

number

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

number

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

number

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.

access_predicates

varchar2(4000)

Predicates used to locate rows in an access structure; for example, start or stop predicates for an index range scan.

filter_predicates

varchar2(4000)

Predicates used to filter rows before producing them.

executions

number

Number of times this cursor has been executed.

last_starts

number

Number of times this operation has been started, during the last execution.

starts

number

Number of times this operation has been started, accumulated over the past executions.

last_output_rows

number

Number of rows produced by the row source, during the last execution.

output_rows

number

Number of rows produced by the row source, accumulated over the past executions.

last_cr_buffer_gets

number

Number of buffers retrieved in consistent mode, during the last execution. Buffers are usually retrieved in consistent mode for queries.

cr_buffer_gets

number

Number of buffers retrieved in consistent mode, accumulated over the past executions. Buffers are usually retrieved in consistent mode for queries.

last_cu_buffer_gets

number

Number of buffers retrieved in current mode, during the last execution. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

cu_buffer_gets

number

Number of buffers retrieved in current mode, accumulated over the past executions. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

last_disk_reads

number

Number of physical disk reads performed by the operation, during the last execution.

disk_reads

number

Number of physical disk reads performed by the operation, accumulated over the past executions.

last_disk_writes

number

Number of physical disk writes performed by the operation, during the last execution.

disk_writes

number

Number of physical disk writes performed by the operation, accumulated over the past executions.

last_elapsed_time

number

Elapsed time (in microseconds) corresponding to this operation, during the last execution.

elapsed_time

number

Elapsed time (in microseconds) corresponding to this operation, accumulated over the past executions.

policy

varchar2(10)

Sizing policy for this work area. Values are either MANUAL or AUTO.

estimated_optimal_size

number

Estimated size (in KB) required by this work area to execute the operation completely in memory (optimal execution). This is either derived from optimizer statistics or from previous executions.

estimated_onepass_size

number

Estimated size (in KB) required by this work area to execute the operation in a single pass. This is either derived from optimizer statistics or from previous executions.

last_memory_used

number

Memory size, in KB, used by this work area during the last execution of the cursor.

last_execution

varchar(10)

Whether this work area ran using OPTIMAL, ONE PASS, or under ONE PASS memory requirement (that is, MULTI-PASS), during the last execution of the cursor.

last_degree

number

Degree of parallelism used, during the last execution of the cursor.

total_executions

number

Number of times this work area was active.

optimal_executions

number

Number of times this work area ran in optimal mode.

onepass_executions

number

Number of times this work area ran in one pass mode.

multipasses_executions

number

Number of times this work area ran under the one-pass memory requirement.

active_time

number

Time the work area has been active (in microseconds).

max_tempseg_size

number

Maximum temporary space used.

tempseg_size

number

Temporary space used.

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

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.

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

Join Columns in V$SQLAREA

Table 24-26 lists the join columns for V$SQLAREA.

Table 24-26  Join Columns for V$SQLAREA
Column 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 24-28 Finding Resource-intensive SQL

There are several costs you can use:

  • Total logical I/O (LIO), LIO for each execution
  • Total physical I/O (PIO), PIO for each execution
  • PIO/LIO (poor cache hit ratio)
  • 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
    

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

Table 24-27 lists the join columns for V$SQLTEXT.

Table 24-27  Join Columns for V$SQLTEXT
Column 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 24-30 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$STATISTICS_LEVEL

V$STATISTICS_LEVEL lists the status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter. Each row of V$STATISTICS_LEVEL represents one of these statistics or advisories.

Table 24-28  V$STATISTICS_LEVEL View
Column Datatype Description

STATISTICS_NAME

VARCHAR2(64)

Name of the statistics/advisory.

DESCRIPTION

VARCHAR2(4000)

Description of what the statistics/advisory does and what it can be used for.

SESSION_STATUS

VARCHAR2(8)

ENABLED|DISABLED. Status of the statistics/advisory for this session.

SYSTEM_STATUS

VARCHAR2(8)

ENABLED|DISABLED. Status of the statistics/advisory system-wide.

ACTIVATION_LEVEL

VARCHAR2(7)

BASIC|TYPICAL|ALL. What level of STATISTICS_LEVEL enables this statistics/advisory?

STATISTICS_VIEW_NAME

VARCHAR2(64)

If there is a single view externalizing this statistics/advisory, the name of that view. If there is no such a view, this column is empty. If there are multiple views involved, the DESCRIPTION column should mention the view names.

SESSION_SETTABLE

VARCHAR2(3)

YES|NO. Whether this statistics/advisory can be set at the session level.

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. The following are sample column values for the statistic execute count.

Table 24-29 Useful Columns in V$SYSSTAT
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 (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.

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 for each 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 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.
  • 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 over seconds or over transactions) 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 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).
  • 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 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:

  • 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
    
    
    
    
  • Rows for each sort:
    sorts (rows) / ( sorts (memory) + sorts (disk) )
    

Join Columns for V$SYSSTAT

Table 24-30 lists the join columns for V$SYSSTAT.

Table 24-30 Join Columns for V$SYSSTAT
Column 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 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;

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:
  • Statspack lists idle events at the end.
  • Examine the time spent waiting for different events.
  • 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

  • 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 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

  • 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



    Source : http://docs.oracle.com/cd/B10501_01/server.920/a96533/sqlviews.htm



    More Tutorials on Oracle dba | Source : oraclebase.com




    Want to share or request more about Oracle Database Administrators in SQL Tutorial to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info