Oracle9i Database Performance Guide and Reference Release 1 (9.0.1) Part Number A87503-02 |
|
Throughout its operation, Oracle maintains a set of "virtual" tables that record current database activity. These tables are called dynamic performance tables.
Dynamic performance tables are not true tables, and they should not be accessed by most users. However, database administrators can query and create views on the tables and grant access to those views to other users. These views are called fixed views because they cannot be altered or removed by the database administrator.
SYS
owns the dynamic performance tables. By default, they are available only to the user SYS
and to users granted SELECT
ANY
TABLE
system privilege, such as SYSTEM
. Their names all begin with V_$
. Views are created on these tables, and then public synonyms are created for the views. The synonym names begin with V$
.
This chapter provides detailed information on several of these views that can help you tune your system and investigate performance problems.
Each view belongs to one of the following categories:
Oracle9i Database Reference for a complete list of the dynamic performance views and their columns
See Also:
These views give a picture of what is currently happening on the system.
Fixed View: | Description: |
V$LOCK |
Locks currently held/requested on the instance |
V$LATCH_HOLDER |
Sessions/processes holding a latch |
V$OPEN_CURSOR |
Cursors opened by sessions on the instance |
V$SESSION |
Sessions currently connected to the instance |
V$SESSION_WAIT |
Different resources sessions are currently waiting for |
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.
Fixed View: | Description: |
V$MYSTAT |
Resource usage summary for your own session |
V$SESSION_EVENT |
Session-level summary of all the waits for current sessions |
V$SESSTAT |
Session-level summary of resource usage since session startup |
Fixed View: | Description: |
V$DB_OBJECT_CACHE |
Object level statistics in shared pool |
V$FILESTAT |
File level summary of the I/O activity |
V$LATCH |
Latch activity summary |
V$LATCH_CHILDREN |
Aggregate summary for each type of latch |
V$LIBRARYCACHE |
Namespace level summary for shared pool |
V$ROLLSTAT |
Rollback activity summary |
V$ROWCACHE |
Data dictionary activity summary |
V$SQL |
Child cursor details for |
V$SQLAREA |
Shared pool details for statements/anonymous blocks |
V$SYSSTAT |
Summary of resource usage |
V$SYSTEM_EVENT |
Instance wide summary of resources waited for |
V$UNDOSTAT |
Undo space summary for a ten minute interval |
V$WAITSTAT |
Break down of buffer waits by class |
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.
Fixed View: | Description: |
V$PARAMETER and V$SYSTEM_PARAMETER |
Parameters values for your session Instance wide parameter values |
V$PROCESS |
Server processes (background and foreground) |
V$SQL_PLAN |
Execution plan for cursors that were recently executed |
V$SQLTEXT |
SQL text of statements in the shared pool |
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.
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
The columns below keep statistics on the object since it's first load:
EXECUTIONS
: Number of executions by this object
LOADS
: Number of times this object had to be loaded
INVALIDATIONS
: Number of times this object was invalidated
The following query shows the distribution of the usage (executions) and the shared pool memory across different type of objects. It also shows if any of the objects have been pinned in the shared pool using the procedure DBMS_SHARED_POOL
.KEEP
().
SELECT type, kept, COUNT(*), SUM(executions), SUM(sharable_mem) FROM V$DB_OBJECT_CACHE GROUP BY kept, type;
SELECT owner, name, executions, sharable_mem, kept, loads FROM V$DB_OBJECT_CACHE WHERE loads > 1 OR invalidations > 0 ORDER BY loads DESC
The following query finds all objects using large amounts of memory or executed multiple times. They can be pinned using DBMS_SHARED_POOL
.KEEP
().
SELECT owner, name, executions, sharable_mem, kept FROM V$DB_OBJECT_CACHE WHERE ( executions > 10 OR sharable_mem > 102400 ) AND kept = `NO' ORDER BY 3 desc, 4 DESC
This view keeps a summary of physical I/O requests for each file. This is useful in isolating where the I/O activity is happening if the bottleneck is I/O related. V$FILESTAT
shows the following information for database I/O (but not for log file I/O):
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.
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
physical
reads
from V$SYSSTAT
.
physical
writes
from V$SYSSTAT
.
Column: | View: | Joined Column(s): |
FILE# |
DBA_DATA_FILES |
FILE_ID |
The following query monitors the values of physical reads and physical writes over some period of time while your application is running:
SELECT NAME, PHYRDS, PHYWRTS FROM V$DATAFILE df, V$FILESTAT fs WHERE df.FILE# = fs.FILE#;
The above query also retrieves the name of each datafile from the dynamic performance view V$DATAFILE
. Sample output might look like the following:
NAME PHYRDS PHYWRTS -------------------------------------------- ---------- ---------- /oracle/ora70/dbs/ora_system.dbf 7679 2735 /oracle/ora70/dbs/ora_temp.dbf 32 546
The PHYRDS
and PHYWRTS
columns of V$FILESTAT
can also be obtained through SNMP.
The total I/O for a single disk is the sum of PHYRDS
and PHYWRTS
for all the database files managed by the Oracle instance on that disk. Determine this value for each of your disks. Also, determine the rate at which I/O occurs for each disk by dividing the total I/O by the interval of time over which the statistics were collected.
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. |
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
.
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
Columns: | Fixed View: | Joined Column(s): |
NAME |
V$LATCH_CHILDREN V$LATCHHOLDER V$LATCHNAME |
NAME |
NAME |
V$LATCH_MISSES |
PARENT_NAME |
LATCH# |
V$LATCH_CHILDREN V$LATCHNAME |
LATCH# |
In the following example, a table is created to hold data queried from V$LATCH
:
CREATE TABLE snap_latch as SELECT 0 snap_id, sysdate snap_date, a.* FROM V$LATCH a; ALTER TABLE snap_latch add ( constraint snap_filestat primary key (snap_id, file#) ); SELECT name, (a.gets-b.gets)/1000 "Gets(K)", a.gets-b.gets)/(86400*(a.snap_date-b.snap_date)) "Get/s", 100*(a.misses-b.misses)/(a.gets-b.gets) MISS, 100*(a.spin_gets-b.spin_gets)/(a.misses-b.misses) SPIN, (a.gets-b.gets)/1000 "Gets(K)", (a.immediate_gets-b.immediate_gets)/1000 "Iget(K)", (a.immediate_gets-b.immediate_gets)/ (86400*(a.snap_date-b.snap_date)) "IGet/s", 100*(a.immediate_misses-b.immediate_misses)/ (a.immediate_gets-b.immediate_gets) IMISS FROM snap_latch a, snap_latch b WHERE a.snap_id = b.snap_id + 1 AND ( (a.misses-b.misses) > 0.001*(a.gets-b.gets) or (a.immediate_misses-b.immediate_misses) > 0.001*(a.immediate_gets-b.immediate_gets)) ORDER BY 2 DESC; INSERT INTO snap_latch SELECT 1, sysdate, a.* FROM V$LATCH a;
The example shows the latch statistics obtained by doing a delta over a period of one hour (like with the V$FILESTAT
numbers). Those latches that had misses less than 0.1% of the gets have bee filtered out.
NAME Gets(K) Get/s MISS SPIN IGets(K) IGet/s IMISS ------------------ -------- ------- ----- ------ -------- ------- ----- cache buffers chai 255,272 69,938 0.4 99.9 3,902 1,069 0.0 library cache 229,405 62,851 9.1 96.9 51,653 14,151 3.7 shared pool 24,206 6,632 14.1 72.1 0 0 0.0 latch wait list 1,828 501 0.4 99.9 1,836 503 0.5 row cache objects 1,703 467 0.7 98.9 1,509 413 0.2 redo allocation 984 270 0.2 99.7 0 0 0.0 messages 116 32 0.2 100.0 0 0 0.0 cache buffers lru 91 25 0.3 99.0 7,214 1,976 0.3 modify parameter v 2 0 0.1 100.0 0 0 0.0 redo copy 0 0 92.3 99.3 1,460 400 0.0
When examining latch statistics, look at the following:
There seems to be a lot of contention for the redo copy latch with a 92.3 percent miss rate. But, look carefully. Redo copy latches are obtained mostly in immediate mode. The numbers for immediate gets look fine, and the immediate gets are several orders of magnitude bigger than the willing to wait gets. So, there is no contention for redo copy latches.
However, there does seem to be contention for the shared pool and library cache latches. Consider looking at the sleeps for these latches to see if there is actually a problem.
NAME Gets(K) Get/s MISS SPIN SL01 SL02 SL03 SL04 ------------------ -------- ------- ----- ------ ----- ----- ----- ----- cache buffers chai 255,272 69,938 0.4 99.9 0.1 0.0 0.0 0.0 library cache 229,405 62,851 9.1 96.9 3.0 0.1 0.0 0.0 shared pool 24,206 6,632 14.1 72.1 22.4 4.8 0.8 0.0 latch wait list 1,828 501 0.4 99.9 0.1 0.0 0.0 0.0 row cache objects 1,703 467 0.7 98.9 0.6 0.0 0.4 0.0 redo allocation 984 270 0.2 99.7 0.1 0.0 0.2 0.0 messages 116 32 0.2 100.0 0.0 0.0 0.0 0.0 cache buffers lru 91 25 0.3 99.0 1.0 0.0 0.0 0.0 modify parameter v 2 0 0.1 100.0 0.0 0.0 0.0 0.0 redo copy 0 0 92.3 99.3 0.0 0.7 0.0 0.0
You can see that there is a 14% miss rate on the shared pool latches. 72% of the missed latched without relinquishing the CPU (having to sleep even once) by spinning. There are some misses for which you have to sleep multiple times.
Investigate why the shared pool latch is needed so many times. Look at the SQL being run by sessions holding or waiting for the latch, as well as the resource usage characteristics of the system. Compare them with baselines when there was no problem.
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.
There are multiple latches in the database for some type of latches. V$LATCH
provides aggregate summary for each type of latch. To look at individual latches, query V$LATCH_CHILDREN
.
SELECT name, COUNT(*) FROM V$LATCH_CHILDREN GROUP BY name ORDER BY 2 DESC; NAME COUNT(*) ---------------------------------------- ---------- global tx hash mapping 2888 global transaction 2887 cache buffers chains 2048 latch wait list 32 Token Manager 23 enqueue hash chains 22 session idle bit 22 redo copy 22 process queue reference 20 Checkpoint queue latch 11 library cache 11 msg queue latch 11 session queue latch 11 process queue 11 cache buffers lru chain 11 done queue latch 11 channel operations parent latch 4 session switching 4 message pool operations parent latch 4 ksfv messages 2 parallel query stats 2 channel handle pool latch 1 temp table ageout allocation latch 1
This view is useful to see if the session holding the latch is changing. Most of the time, the latch is held for such a small time that it is impossible to join to some other table to see the SQL statement being executed or the events that latch holder is waiting for.
The main use for this view is to see that the latch is not stuck on some session.
Columns: | Fixed View: | Joined Column(s): |
LADDR |
V$LATCH_CHILDREN |
ADDR |
NAME |
V$LATCH, V$LATCHNAME, V$LATCH_CHILDREN |
NAME |
PID |
V$PROCESS |
PID |
SID |
V$SESSION |
SID |
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.
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:
Then use V$DB_OBJECT_CACHE
, V$SQLAREA
to get more details.
NAMESPACE
: Class of objects (SQL area, trigger, and so on)
GETS
: Handle requests for objects of this namespace
GETHITS
: Requests that found handle in the cache
PINS
: PIN requests for objects of this namespace
PINHITS
: Requests able to reuse an existing PIN
RELOADS
: Number of times objects stored in the library cache had to be reloaded into memory because part of the object had been flushed from the cache. If there are a significant number of reloads, then reusable information is being flushed from the library cache. This requires a reload/rebuild of the object before it can again be accessed.
INVALIDATIONS
: The number of times objects were invalidated. For example, an object is invalidated automatically by Oracle when it is no longer safe to execute. If the optimizer statistics for a table were recomputed, then all SQL statements currently in the library cache at the time the recompute occurred would be invalidated, because their execution plans may no longer be optimal.
GETHITRATIO
(GETHITS
/GETS
) and GETPINRATIO
(PINHITS
/PINS
) can be used if just examining activity since instance startup. If examining activity over a specified time interval, it is better to compute these from the differences in snapshots before and after the interval.
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 number of RELOADS
could be due to the following:
Low GETHITRATIO
could indicate that objects are getting swapped out of memory.
Low PINHITRATIO
could indicate the following:
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.
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:
V$LOCK
to find the sessions holding the lock.
V$SESSION
to find the SQL statements being executed by the sessions holding the lock and waiting for the lock.
V$SESSION_WAIT
to find what the session holding the lock is doing.
V$SESSION
to get more details about the program and user holding the lock.
SID
: Identifier of the session holding/requesting the lock
TYPE
: Type of lock
LMODE
: The mode the lock is held in
REQUEST
: The mode the lock is requested in
ID1
, ID2
: Lock resource identifiers
Do the following to avoid contention on this enqueue:
Users can define their own locks.
Any row in V$LOCK
either has LMODE=0
(indicating it is a request) or REQUEST=0
(indicating it is a held lock).
For DML locks, ID1
is the object_id.
For TX locks, ID1
points to the rollback segment and transaction table entry.
Columns: | Fixed View: | Joined Column(s): |
SID |
V$SESSION |
SID |
ID1, ID2, TYPE |
V$LOCK |
ID1, ID2, TYPE |
ID1 |
DBA_OBJECTS |
OBJECT_ID |
TRUNC(ID1/65536) |
V$ROLLNAME |
USN |
V$TRANSACTION
.
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
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
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:
This view is a subset of V$SESSTAT
returning current session's statistics. When auditing resource usage for sessions via triggers, use V$MYSTAT
to capture the resource usage, because it is much cheaper than scanning the rows in V$SESSTAT
.
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.
Columns: | Fixed View: | Joined Column(s): |
|
|
|
SID |
V$SESSION |
SID |
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.
SELECT sid, COUNT(*) FROM V$OPEN_CURSOR GROUP BY sid HAVING COUNT(*) > 400 ORDER BY 2 DESC; SID COUNT(*) ----- ---------- 2359 456 1796 449 1533 445 1135 442 1215 442 810 437 1232 429 27 426 1954 421 2067 421 1037 416 1584 413 416 407 398 406 307 405 1545 403
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';
NAME
: Name of the parameter
VALUE
: Current value for this session (if modified within the session); otherwise, the instance-wide value
ISDEFAULT
: Whether this parameter has been specified by the user as an initialization parameter
ISSES_MODIFIABLE
: Whether this parameter can be modified at the session level
ISSYS_MODIFIABLE
: Whether this parameter can be modified at an instance-wide level dynamically after the instance has started
ISMODIFIED
: Whether this parameter has been modified after instance startup, and if so, whether it was modified at the session level or at the instance (system) level
ISADJUSTED
: Whether Oracle has adjusted a value specified by the user
DESCRIPTION
: Brief description of the parameter
UPDATE_COMMENT
: Set if a comment has been supplied by the DBA for this parameter
See Also:
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
.
column name format a20 column value format a10 column isdefault format a5 column isses_modifiable format a5 SELECT name, value, isdefault, isses_modifiable, issys_modifiable, ismodified FROM V$PARAMETER WHERE name = 'sort_area_size'; NAME VALUE ISDEF ISSES ISSYS_MOD ISMODIFIED -------------------- ---------- ----- ----- --------- ---------- sort_area_size 1048576 TRUE TRUE DEFERRED MODIFIED
The above example shows that the SORT_AREA_SIZE
initialization parameter was not set as an initialization parameter on instance startup, but was modified at the session level (indicated by the ISMODIFIED
column having the value of MODIFIED
) for this session.
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:
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.
Column: | Fixed View: | Joined Column(s): |
ADDR |
V$SESSION |
PADDR |
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)
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.
This view keeps a summary of statistics for each rollback segment since startup.
USN
: Rollback segment number
RSSIZE
: Current size of the rollback segment
XACTS
: Number of active transactions
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
Column: | Fixed View: | Joined Column(s): |
USN |
V$ROLLNAME |
USN |
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.
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.
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
GETS
appear for the DC_USERS
cache within the sample period, then it is likely that there are large number of distinct users created within the database, and that the application is logging the users on and off frequently. To verify this, check the logon rate and also the number of users in the system. The parse rates will also be high. If this is a large OLTP system with a middle tier, then it might be more efficient to manage individual accounts on the middle tier, allowing the middle tier to logon as a single use: the application owner. Reducing logon/logoff rate by keeping connections alive also helps.
DC_SEGMENTS
, DC_USED_EXTENTS
, and DC_FREE_EXTENTS
can indicate much dynamic space allocation, in which case the solution is to size next extents appropriately.
dc_sequences
indicates this. Check to see whether the number of cache entries per sequence number are sufficient for then number of changes.
GETS
to DC_COLUMNS
, DC_VIEWS
and DC_OBJECTS
caches.
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;
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.
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:
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:
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
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.
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
This is a list of columns that we can use to join to other fixed views.
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid = userenv('SESSIONID'); SID OSUSER USERNAME MACHINE PROCESS ----- ---------- ----------- ----------- -------- 1011 vsaksena SYSTEM dlsun1653 15912
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION WHERE terminal = 'ttyAH/AHHh' AND machine = 'prodseq4'; SID OSUSER USERNAME MACHINE TERMINAL ---- --------- --------- ---------- ---------- 58 vsaksena APPS_US prodseq4 ttyAH/AHHh
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
.
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.
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
Column: | Fixed View: | Joined Column(s): |
SID |
V$SESSION |
SID |
SELECT s.sid, bgp.name FROM V$SESSION s, V$BGPROCESS bgp WHERE bgp.name LIKE '%DBW%' AND bgp.paddr = s.paddr; SELECT event, total_waits waits, total_timeouts timeouts, time_waited total_time, average_wait avg FROM V$SESSION_EVENT WHERE sid = 3 ORDER BY 4 DESC; EVENT WAITS TIMEOUTS TOTAL_TIME AVG ------------------------------ -------- -------- ---------- ----- rdbms ipc message 1684385 921495 284706709 169.03 db file parallel write 727326 0 3012982 4.14 latch free 157 157 281 1.78 control file sequential read 123 0 61 0.49 file identify 45 0 29 0.64 direct path read 41 0 5 0.12 file open 49 0 2 0.04 db file sequential read 2 0 2 1.00
This is a key view for finding bottlenecks. It tells what every session in the database is currently waiting for (or the last event waited for by the session if it is not waiting for anything). This view can be used as a starting point to find which direction to proceed in when a system is experiencing performance problems.
V$SESSION_WAIT
has a row in V$SESSION_WAIT
for every session connected to an instance. It indicates if the session is:
SID
: Session identifier for the session
EVENT
: Event the session is currently waiting for, or the last event the session had to wait for
WAIT_TIME
: Time (in hundredths of a second) that the session waited for the event; if the WAIT_TIME
is 0, then the session is currently waiting for the event
SEQ#
: Gets incremented with every wait of the session
P1
, P2
, P3
: Wait event specific details for the wait
P1TEXT
, P2TEXT
, P3TEXT
: Description of P1
,P2
,P3
for the given event
Table 24-5 Wait Time Description
Below is an example of how the EVENT
, SEQ#
, and WAIT_TIME
might change over a period of time:
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.
Column: | Fixed View: | Joined Column(s): |
SID |
V$SESSION |
SID |
SELECT event, SUM(DECODE(wait_time,0,1,0)) "Curr", SUM(DECODE(wait_time,0,0,1)) "Prev", COUNT(*)"Total" FROM V$SESSION_WAIT GROUP BY event ORDER BY 4; EVENT Prev Curr Tot --------------------------------------------- ---- ----- ----- PL/SQL lock timer 0 1 1 SQL*Net more data from client 0 1 1 smon timer 0 1 1 pmon timer 0 1 1 SQL*Net message to client 2 0 2 db file scattered read 2 0 2 rdbms ipc message 0 7 7 enqueue 0 12 12 pipe get 0 12 12 db file sequential read 3 10 13 latch free 9 6 15 SQL*Net message from client 835 1380 2215
This query, which groups the data by event and by wait_time (0=waiting, nonzero=not waiting), shows the following:
SQL*Net
message
from
client
, pipe
get
, PMON
timer
, and so on.
SQL*Net
message
from
client
.
The next step should be to check V$SESSION
to see if the session is active or not. Only count the session as actively waiting or using a resource if it is active. Use the statement below to accomplish this. The total column counts the total of all the sessions, however the currently waiting and previously waited (using resource) columns only count active sessions.
SELECT event, SUM(DECODE(wait_Time,0,0,DECODE(s.status,'ACTIVE',1,0))) "Prev", SUM(DECODE(wait_Time,0,1,DECODE(s.status,'ACTIVE',1,0))) "Curr", COUNT(*) "Tot" FROM V$SESSION S,V$SESSION_WAIT w WHERE s.sid = w.sid GROUP BY event ORDER BY 4; EVENT Prev Curr Tot ---------------------------------------- ----- ----- ----- SQL*Net message to client 1 1 1 <- idle event buffer busy waits 1 1 1 file open 1 1 1 pmon timer 0 1 1 <- idle event smon timer 0 1 1 <- idle event log file sync 0 1 1 db file scattered read 0 2 2 rdbms ipc message 0 7 7 <- idle event pipe get 0 12 12 <- idle event enqueue 0 14 14 latch free 10 17 20 db file sequential read 7 22 23 SQL*Net message from client 0 1383 2240 <- idle event
Now sessions are counted as actively waiting or using a resource only if they are active. This highlights the following:
SQL*Net
message
from
client
, pipe
get
, rdbms
ipc
message
, PMON
timer
, SMON
timer
, and SQL*Net
message
to
client
.
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:
user
commits
)
redo
size
)
TIMED_STATISTICS
is true, the cumulative time spent performing some actions (CPU
used
by
this
session
)
The differences between V$SYSSTAT
and V$SESSTAT
are the following:
V$SESSTAT
only stores data on a per session basis, whereas V$SYSSTAT
stores the accumulated values for all sessions.
V$SESSTAT
is transitory, and is lost after a session logs out. V$SYSSTAT
is cumulative, and is only lost when the instance is shutdown.
V$SESSTAT
does not include the name of the statistic. In order to find the statistic name, this view must be joined to either V$SYSSTAT
or V$STATNAME
.
V$SESSTAT
can be used to find sessions with the following:
The 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)
.
Columns: | Fixed View: | Joined Column(s): |
STATISTIC# |
V$STATNAME |
STATISTIC# |
SID |
V$SESSION |
SID |
The following SQL statement shows the logical and physical read rates (per second) for all active sessions connected to the database. Rates for logical and physical I/O are calculated using the elapsed time since logon (from V$SESSION
.LOGON_TIME
). This might not be particularly accurate for sessions connected to the database for long periods, but it is sufficient for this example.
To determine the STATISTIC#
's for the session
logical
reads
and physical
reads
statistics:
SELECT name, statistic# FROM V$STATNAME WHERE name IN ('session logical reads','physical reads') ; NAME STATISTIC# ------------------------------ ---------- session logical reads 9 physical reads 40
Use these values in the following query, which orders the sessions by resource usage:
SELECT ses.sid , DECODE(ses.action,NULL,'online','batch') "User" , MAX(DECODE(sta.statistic#,9,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s" , MAX(DECODE(sta.statistic#,40,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s" , 60*24*(sysdate-ses.logon_time) "Minutes" FROM V$SESSION ses , V$SESSTAT sta WHERE ses.status = 'ACTIVE' AND sta.sid = ses.sid AND sta.statistic# IN (9,40) GROUP BY ses.sid, ses.action, ses.logon_time ORDER BY SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) ) / greatest(3600*24*(sysdate-ses.logon_time),1) DESC; SID User Log IO/s Phy IO/s Minutes ----- ------ -------- -------- ------- 1951 batch 291 257.3 1 470 online 6,161 62.9 0 730 batch 7,568 43.2 197 2153 online 1,482 98.9 10 2386 batch 7,620 35.6 35 1815 batch 7,503 35.5 26 1965 online 4,879 42.9 19 1668 online 4,318 44.5 1 1142 online 955 69.2 35 1855 batch 573 70.5 8 1971 online 1,138 56.6 1 1323 online 3,263 32.4 5 1479 batch 2,857 35.1 3 421 online 1,322 46.8 15 2405 online 258 50.4 8
To better show the impact of each individual session on the system, the results were ordered by the total resource usage per second. The resource usage was calculated by adding session
logical
reads
and (a weighted) physical
reads
.
Physical reads was weighted by multiplying the raw value by a factor of 100, to indicate that a physical I/O is significantly more expensive than reading a buffer already in the cache.
To calculate the physical I/O weighting factor, the following assumptions were made:
V$SYSTEM_EVENT.AVERAGE_WAIT
for the events db
file
sequential
read
and db
file
scattered
read
).
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).
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.
This view provides a way of examining the execution plan for cursors that were recently executed.
The information in this view is very similar to the output of an EXPLAIN
PLAN
statement. However, EXPLAIN
PLAN
shows a theoretical plan that can be used if this statement were to be executed, whereas V$SQL_PLAN
contains the actual plan used. The execution plan obtained by the EXPLAIN
PLAN
statement can be different from the execution plan used to execute the cursor, because the cursor might have been compiled with different values of session parameters (for example, HASH_AREA_SIZE
).
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:
ANALYZE
statement on the database objects
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.
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.
The columns ADDRESS
, HASH_VALUE
and CHILD_NUMBER
can be used to join with V$SQL
to add the child cursor specific information.
OPERATION
: Name of the internal operation performed in this step; for example, TABLE
ACCESS
OPTIONS
: A variation on the operation described in the OPERATION
column; for example, FULL
OBJECT_NODE
: Name of the database link used to reference the object (a table name or view name); for local queries using parallel execution, this column describes the order in which output from operations is consumed
OBJECT#
: Object number of the table or the index
OBJECT_OWNER
: Name of the user who owns the schema containing the table or index
OBJECT_NAME
: Name of the table or index
OPTIMIZER
: Current mode of the optimizer for the first row in the plan (statement line); for example, CHOOSE
. In case the operation is a database access (e.g, TABLE
ACCESS
), it tells whether the object is analyzed or not
ID
: A number assigned to each step in the execution plan
PARENT_ID
: ID of the next execution step that operates on the output of the current step
DEPTH
: The depth (or level) of the operation in the tree; that is, it is not necessary to do a CONNECT
BY
to get the level information generally used to indent the rows from the PLAN_TABLE
- the root operation (statement) has level 0.
POSITION
: Order of processing for operations that all have the same PARENT_ID
COST
: Cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null
CARDINALITY
: The estimate, by the cost-based optimizer, of the number of rows produced by the operation
BYTES
: The estimate, by the cost-based optimizer, of the number of bytes produced by the operation
OTHER_TAG
: Describes the contents of the OTHER
column (see Chapter 9, "Using EXPLAIN PLAN" for values)
PARTITION_START
: The start partition of a range of accessed partition
PARTITION_STOP
: The stop partition of a range of accessed partitions
PARTITION_ID
: The step that has computed the pair of values of the PARTITION_START
and PARTITION_STOP
columns
OTHER
: Other information that is specific to the execution step that a user may find useful (see Chapter 9, "Using EXPLAIN PLAN" for values)
DISTRIBUTION
: For parallel query, stores the method used to distribute rows from producer query servers to consumer query servers.
CPU_COST
: The CPU cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null
IO_COST
: The I/O cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null
TEMP_SPACE
: The temporary space usage of the operation (sort or hash-join) as estimated by the optimizer's cost-based approach; for statements that use the T
The DEPTH
column replaces the LEVEL
pseudo-column produced by the CONNECT
BY
operator, which sometimes is used in SQL scripts to help indent the PLAN_TABLE
data.
The columns ADDRESS
, HASH_VALUE
and CHILD_NUMBER
are used to join with V$SQL
or V$SQLAREA
to fetch the cursor-specific information; for example, BUFFER_GETS
, or with V$SQLTEXT
to return the full text of the SQL statement.
Column(s): Fixed View: Joined Column(s) address, hash_value v$sqlarea address, hash_value address, hash_value, v$sql address, hash_value child_number child_number address, hash_value v$sqltext address, hash_value
The following statement shows the EXPLAIN
PLAN
for a specified SQL statement. Looking at the plan for a SQL statement is one of the first steps in tuning a SQL statement. The SQL statement to return the plan for is identified by the statement's HASH_VALUE
and address.
See Also:
"Identifying and Gathering Data on Resource-Intensive SQL" for information on how to identify SQL statements to tune |
A SQL*Plus query and sample output from V$SQL_PLAN
(assumes only one child cursor):
column id format 999 newline column operation format a20 column operation format a20 column options format a15 column object_name format a22 trunc column optimizer format a3 trunc SELECT id , lpad (' ', depth) || operation operation , options , object_name , optimizer , cost FROM V$SQL_PLAN WHERE hash_value = 2446703096 AND address = '80E3A1D8' START WITH id = 0 CONNECT BY ( prior id = parent_id AND prior hash_value = hash_value AND prior child_number = child_number ) ORDER SIBLINGS BY id, position; ID OPERATION OPTIONS OBJECT_NAME OPT COST ---- -------------------- --------------- ---------------------- --- ---------- 0 SELECT STATEMENT CHO 1 SORT ORDER BY 6 2 NESTED LOOPS 4 3 HASH JOIN 3 8 TABLE ACCESS BY INDEX ROWID ORDERS 1 9 INDEX RANGE SCAN ORDERS_PK 2 4 TABLE ACCESS BY INDEX ROWID ORDERS_LINE_ITEM 1 5 INDEX RANGE SCAN ORDERS_LINE_ITEM_PK 2 6 TABLE ACCESS BY INDEX ROWID ORDERS_STATUS 1 7 INDEX RANGE SCAN ORDERS_STATUS_PK 2 10 rows selected.
V$SQL_PLAN
shows the plan for a cursor, not for a SQL statement. The difference is that a SQL statement can have more than one cursor associated with it, with each cursor further identified by a CHILD_NUMBER
. Below are a few examples of how a SQL statement can result in more than one cursor:
User1: SELECT
* FROM
EMPLOYEE
;
User2: SELECT
* FROM
EMPLOYEE
;
Where user2 has his own employee table, and user1 uses the table referenced by a public synonym.
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.
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.
The above two columns are used to identify the SQL statement. Sometimes, two different statements could hash to the same value. In such cases, it is necessary to use the address along with the hash_value.
PARSING_USER_ID
: User who parsed the first cursor for the statement
VERSION_COUNT
: Number of cursors for the statement
KEPT_VERSIONS
: Cursors of the statement pinned using DBMS_SHARED_POOL
.KEEP
()
SHARABLE_MEMORY
: Total shared memory used by the cursor
PERSISTENT_MEMORY
: Total persistent memory used by the cursor
RUNTIME_MEMORY
: Total runtime memory used by the cursor
SQL_TEXT
: Up to first 1000 characters of SQL statement
MODULE
, ACTION
: Information about the session parsing the first cursor if set using DBMS_APPLICATION_INFO
These columns get incremented with each execution of the statement.
BUFFER_GETS
: Logical I/Os caused by this statement
DISK_READS
: Physical I/O requests due to the statement
SORTS
: Sorts performed due to the statement
CPU_TIME
: Time taken by the CPU due to the statement
ELAPSED_TIME
: Total time spent due to the statement
PARSE_CALLS
: Parse calls (hard and soft) for the statement
EXECUTIONS
: Number of times the statement was executed
INVALIDATIONS
: Total invalidations for statement's cursors
LOADS
: Loads (and reloads) for the statement
ROWS_PROCESSED
: Total rows processed by this statement
There are several costs you can use:
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
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
This view contains the complete SQL text for the SQL statements in the shared pool.
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
Columns: | Fixed View: | Joined Column(s): |
HASH_VALUE, ADDRESS |
V$SQL, V$SESSION |
HASH_VALUE, ADDRESS |
HASH_VALUE. ADDRESS |
V$SESSION |
SQL_HASH_VALUE. SQL_ADDRESS |
SELECT sql_text FROM V$SQLTEXT WHERE hash_value = 228801498 ORDER BY piece; SQL_TEXT ---------------------------------------------------------------- select dbsu.primary_flag, i.site_use_code, i.rowid from ra_customers dbc, ra_addresses dbad, ra_site_uses dbsu, ra_customers_ interface i where (((((((i.orig_system_customer_ref=dbc.orig_system_reference and dbad.address_id=dbsu.address_id) and i.site_use_code=dbsu.site_use_code) and dbsu.status='A') and dbad.customer_id=dbc.customer_id) and i.request_id=:b0) and nvl(i.validated_flag,'N')<>'Y') and ((i.primary_site_use_flag='Y' and dbsu.primary_flag='Y') or dbsu.site_use_code in ('STMTS','DUN','LEGAL')))
group by dbsu.primary_flag,i.orig_system_customer_ref,i.site_use_code,i.insert_update_flag,i.rowid
V$SYSSTAT
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:
user
commits
)
redo
size
)
TIMED_STATISTICS
is true, then the cumulative time spent performing some actions (CPU
used
by
this
session
)
The value for each statistic stores the resource usage for that statistic since instance startup. Below are sample column values for the statistic execute
count
.
Statistic#: | Name: | Value: |
215 |
execute count |
19,003,070 |
The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed from V$SYSSTAT
data.
Data in this view is also used for monitoring system resource usage and how the system's resource usage changes over time. As with most performance data, examine the system's resource usage over an interval. To do this, take a snapshot of the data within the view at the beginning of the interval and another at the end. The difference in the values (end value - begin value) for each statistic is the resource used during the interval. This is the methodology used by Oracle tools such as Statspack and BSTAT
/ESTAT
.
In order to compare one interval's data with another, the data can be normalized (per transaction, per execution, per second, or per logon). Normalizing the data on both workloads makes identifying the variances between the two workloads easier. This type of comparison is especially useful after patches have been applied, applications have been upgraded, or simply over time to see how increases in user population or data growth affects the resource usage.
You can also use V$SYSSTAT
data to examine the resource consumption of contended-for resources that were identified by querying the V$SYSTEM_EVENT
view.
Below are some of the V$SYSSTAT
statistics that are most useful during tuning, along with an explanation of the statistic. This list is in alphabetical order.
CPU
used
by
this
session
: The total amount of CPU used by all sessions, excluding background processes. This unit for this statistic is hundredths of a second. Calls that complete in less than 10ms are rounded up to this unit.
db
block
changes
: The number of changes made to database blocks in the SGA that were part of an insert, update, or delete operation. This statistic is a rough indication of total database work. On a per transaction level, this statistic indicates the rate at which buffers are being dirtied.
execute
count
: The total number of SQL statement executions (including recursive SQL).
logons
current
: Sessions currently connected to the instance. When using two snapshots across an interval, an average value (rather than the difference) should be used.
logons
cumulative
: The total number of logons since the instance started. To determine the number of logons in a particular period, subtract the end value from the begin value. A useful derived statistic is to divide the number of connections between a begin and end time, and divide this by the number of seconds the interval covered. This gives the logons rate per second. Optimally, there should be no more than two logons per second. To contrast, a logon rate of 50 per second is considered very high. Applications that continually connect and disconnect from the database (for example, once per transaction) do not scale well.
parse
count
(hard)
: The number of parse calls that resulted in a miss in the shared pool. A hard parse occurs when a SQL statement is executed and the SQL statement is either not in the shared pool, or it is in the shared pool but it cannot be shared because part of the metadata for the two SQL statements is different. This can happen if a SQL statement is textually identical to a preexisting SQL statement, but the tables referred to in the two statements resolve to physically different tables. A hard parse is a very expensive operation in terms of CPU and resource use (for example, latches), because it requires Oracle to allocate memory within the shared pool, then determine the execution plan before the statement can be executed.
parse
count
(total)
: The total number of parse calls, both hard and soft. A soft parse occurs when a session executes a SQL statement, and the statement is already in the shared pool and can be used. For a statement to be used (that is, shared) all data pertaining to the existing SQL statement (including data such as the optimizer execution plan) must be equally applicable to the current statement being issued. These two statistics are used to calculate the soft-parse ratio.
parse
time
cpu
: Total CPU time spent parsing in hundredths of a second. This includes both hard and soft parses.
parse
time
elapsed
: The total elapsed time for the parse call to complete.
physical
reads
: The number of blocks read from the operating system. It includes physical reads into the SGA buffer cache (a buffer cache miss) and direct physical reads into the PGA (for example, during direct sort operations). This statistic is not the number of I/O requests.
physical
writes
: The number of database blocks written from the SGA buffer cache to disk by DBWR and from the PGA by processes performing direct writes.
redo
log
space
requests
: The number of times a server process waited for space in the redo logs, typically because a log switch is needed.
redo
size
: The total amount of redo generated (and hence written to the log buffer), in bytes. This statistic (normalized per second or per transaction) is a good indicator of update activity.
session
logical
reads
: The number of logical read requests that can be satisfied in the buffer cache or by a physical read.
sorts
(memory)
and sorts
(disk)
: sorts
(memory)
is the number of sort operations that fit inside the SORT_AREA_SIZE
(and hence did not require an on-disk sort). sorts
(disk)
is the number of sort operations that were larger than SORT_AREA_SIZE
and had to use space on disk to complete the sort. These two statistics are used to compute the in-memory sort ratio.
sorts
(rows)
: The total number of rows sorted. This statistic can be divided by the 'sorts (total)' statistic to determine rows per sort. It is an indicator of data volumes and application characteristics.
table
fetch
by
rowid
: The number of rows returned using ROWID
(due to index access or because a SQL statement of the form "where rowid = &rowid" was issued).
table
scans
(rows
gotten)
: The total number of rows processed during full table scans.
table
scans
(blocks
gotten)
: The number of blocks scanned during full table scans, excluding those for split rows.
user
commits
+ user
rollbacks
: This provides the total number of transactions on the system. This number is used as the divisor when calculating the per-transaction ratios for other statistics. For example, to calculate the number of logical reads per transaction, use the following formula: session
logical
reads
/ (user
commits
+ user
rollbacks
).
A physical read as reported by Oracle might not result in an actual physical disk I/O operation. This is possible because most operating systems have an operating system filesystem cache where the block might be present. Alternatively, the block might also be present in disk or controller level cache, again avoiding an actual I/O. A physical read as reported by Oracle merely indicates that the required block was not in the buffer cache (or in the case of a direct read operation, was required to be read into private memory).
Below are typical instance efficiency ratios calculated from V$SYSSTAT
data. Each ratio's computed value should all be as close as possible to 1:
Buffer cache hit ratio: This is a good indicator of whether the buffer cache is too small.
1 - ((physical
reads
- physical
reads
direct
- physical
reads
direct
(lob))
/ session
logical
reads
)
Soft parse ratio: This shows whether there are many hard parses on the system. The ratio should be compared to the raw statistics to ensure accuracy. For example, a soft parse ratio of .2 typically indicates a high hard parse rate. However, if the total number of parses is low, then the ratio should be disregarded.
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.
Parse to execute ratio: In an operational environment, optimally a SQL statement should be parsed once and executed many times.
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.
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.
To determine the load profile of the system, normalize the following statistics per second and per transaction: logons
cumulative
, parse
count
(total)
, parse
count
(hard)
, executes
, physical
reads
, physical
writes
, block
changes
, and redo
size
.
The normalized data can be examined to see if the 'rates' are high, or it can be compared to another baseline data set to identify how the system profile is changing over time. For example, block changes per transaction is calculated by the following:
Additional computed statistics that measure load include the following:
Blocks changed per read: This shows the proportion of block changes to block reads. It is an indication of whether the system is predominantly read only or whether the system performs many data changes (inserts/updates/deletes).
Rows per sort:
Column: | Fixed View: | Joined Column(s): |
STATISTIC# |
V$STATNAME |
STATISTIC# |
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.
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
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:
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.
The example below shows how undo space is consumed in the system for the previous 24 hours from the time 16:07.
SELECT * FROM V$UNDOSTAT; End-Time UndoBlocks TxnConcrcy TxnTotal QueryLen ExtentsStolen SSTooOldError -------- ---------- ---------- -------- -------- ------------- ------------- 16:07 252 15 1511 25 2 0 16:00 752 16 1467 150 0 0 15:50 873 21 1954 45 4 0 15:40 1187 45 3210 633 20 1 15:30 1120 28 2498 1202 5 0 15:20 882 22 2002 55 0 0
Among the statistics collected, you see that the peak undo consumption happened at the interval of (15:30, 15:40). 1187 undo blocks were consumed in 10 minutes (or about two blocks per second). Also, the highest transaction concurrency occurred during that same period with 45 transactions executing at the same time. The longest query (1202 seconds) was executed (and ended) in the period (15:20, 15:30). Note that the query actually was started in the interval (15:00, 15:10) and continued until around 15:20.
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.
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
The following are possible reasons for waits:
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|