24
Dynamic Performance Views for Tuning

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

The topics discussed in this chapter are:

• Dynamic Performance Tables
• Description of Dynamic Performance Views

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

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
• Counter/Accumulator Views
• Information Views

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.

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.

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.

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.

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.

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: Oracle9i Database Reference for more information on the range column values Oracle9i Database Administrator's Guide for information on server parameter files

Uses for V$PARAMETER and V$SYSTEM_PARAMETER Data

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

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

Example 24-16 Determining the SORT_AREA_SIZE From Within SQL*Plus

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

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

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

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

V$PROCESS

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

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

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#