| Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-02 |
|
|
View PDF |
| Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-02 |
|
|
View PDF |
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:
| See Also:
Oracle9i Database Reference for a complete list of the dynamic performance views and their columns |
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:
The views listed in Table 24-1 give a picture of what is currently happening on the system.
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.
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.
This section discusses the details of some of the dynamic performance views.
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 ownerNAME: 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 usedPINS: Sessions currently executing this objectLOCKS: Sessions currently locking this objectThe following columns keep statistics on the object since its first load:
LOADS: Number of times this object had to be loadedINVALIDATIONS: Number of times this object was invalidatedThe 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;
SELECT owner, name 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. 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;
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):
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 filePHYRDS: Number of physical reads donePHYBLKRD: Number of physical blocks readPHYWRTS: Number of physical writes donePHYBLKWRT: Number of physical blocks writtenphysical reads from V$SYSSTAT.physical writes from V$SYSSTAT.Table 24-4 lists the join columns for V$FILESTAT.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
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.
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 nameIMMEDIATE_GETS: Requests for the latch in immediate modeIMMEDIATE_MISSES: IMMEDIATE_GETS that failedGETS: Requests for the latch in a willing to wait modeMISSES: GETS that did not obtain the latch on first trySPIN_GETS: GETS that got the latch within SPIN_GET tries and did not have to sleepSLEEP1-SLEEP3: GETS that succeeded only after sleeping one to three timesSLEEP4: GETS that only succeeded after sleeping four or more timesWAIT_TIME: Elapsed time spent waiting for this latchMISSES_WAITLIST: Number of latch misses on the associated wait list latchSLEEPS_WAITLIST: Number of sleeps while trying to acquire the associated wait list latchWAIT_TIME_WAITLIST: Wait time for the associated wait list latchTable 24-5 lists the join columns for V$LATCH.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
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
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:
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.
Do not tune latches. If you see latch contention, then it is a symptom of a part of SGA experiencing abnormal resource usage. Latches control access with certain assumptions (for example, a cursor is parsed once and executed many times). To fix the problem, examine the resource usage for the parts of SGA experiencing contention. Merely looking at V$LATCH does not address the problem.
| See Also:
Oracle9i Database Concepts for more information on latches |
There are multiple latches in the database for some type of latches. V$LATCH provides aggregate summary for each type of latch. To look at individual latches, query the V$LATCH_CHILDREN view.
SELECT name, count(*) FROM v$latch_children ORDER BY count(*) desc; 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.
This latch is useful in finding sessions that might be holding latches for a significant amount of time.
Table 24-6 lists the join columns for V$LATCHHOLDER.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 namespaceGETHITS: Requests that found handle in the cachePINS: PIN requests for objects of this namespacePINHITS: Requests able to reuse an existing PINRELOADS: 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.
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 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.
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 blocked on.V$SESSION to get more details about the program and user holding the lock.SID: Identifier of the session holding/requesting the lockTYPE: Type of lockLMODE: Lock mode in which the session holds the lockREQUEST: Lock mode in which the session requests the lockID1, ID2: Lock resource identifiersSeveral common locks are described in this section.
Do the following to avoid contention on this enqueue:
INITRANS for the object.INSERT, UPDATE, or DELETE. This prevents other sessions from executing a DDL statement on the same object concurrently.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.
Do the following to avoid contention on this enqueue:
Users can define their own locks.
| See Also:
Oracle9i Database Concepts for more information on 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.
Table 24-8 lists the join columns for V$LOCK.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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:
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.
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.
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.
Table 24-10 lists the join columns for V$OPEN_CURSOR.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
|
|
|
|
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 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
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 parameterVALUE: Current value for this session (if modified within the session); otherwise, the instance-wide valueISDEFAULT: Whether the parameter value is the default valueISSES_MODIFIABLE: Whether this parameter can be modified at the session levelISSYS_MODIFIABLE: Whether this parameter can be modified at an instance-wide level dynamically after the instance has startedISMODIFIED: 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) levelISADJUSTED: Whether Oracle has adjusted a value specified by the userDESCRIPTION: Brief description of the parameterUPDATE_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 preceding example shows that the SORT_AREA_SIZE initialization parameter was not set as an initialization parameter on instance startup, but was modified at the session level (indicated by the ISMODIFIED column having the value of MODIFIED) for this session.
|
Note: Use caution when querying from |
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.Table 24-11 lists the join columns for V$PROCESS.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
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 numberRSSIZE: Current size of the rollback segmentXACTS: Number of active transactionsWRITES: Number of bytes written to the rollback segmentSHRINKS: Number of times the rollback segment grew past OPTIMAL and shrank backEXTENDS: Number of times the rollback segment had to extend because there was an active transaction in the next extentWRAPS: Number of times the rollback segment wrapped aroundGETS: Number of header getsWAITS: Number of header waitsTable 24-12 lists the join columns for V$ROLLSTAT.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
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 cacheCOUNT: Number of entries allocated to this cacheUSAGE: Current number of used entriesGETS: Total number of requestsGETMISSES: Number of requests resulting in dictionary cache missSCANS: Number of scan requestsSCANMISSES: Number of times a scan failed to find the required dataMODIFICATIONS: Number of additions, changes or deletions of cache entriesDLM_REQUESTS: Number of DLM Real Application Clusters requestsDLM_CONFLICTS: Number of DLM Real Application Clusters conflictsDLM_RELEASES: Number of DLM Real Application Clusters releases| See Also:
"Sizing the Shared Pool" for details about tuning the shared pool |
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.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.dc_sequences indicates this. Check to see whether the number of cache entries for each 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 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.
This is a high-efficiency view for real-time monitoring of segment-level statistics, available with Oracle9i Release 2 (9.2) and higher.
This is a statistics property view for segment-level statistics, available with Oracle9i Release 2 (9.2) and higher.
| Column | Datatype | Description |
|---|---|---|
|
|
|
Statistic number |
|
|
|
Statistic name |
|
|
|
Whether or not it is a sampled statistic |
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.
|
Note: When selecting from SQL*Plus, make sure that you have the column defined with adequate width (11 numbers wide) to see the complete number. |
STATUS: This column identifies if the session is:
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 columnsSERIAL#: 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 sessionThe 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 processMACHINE: Machine where the client process is executingTERMINAL: Terminal (if applicable) where the client process is runningPROCESS: Process ID of the client processPROGRAM: Client program being executed by the client processTo 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_INFOACTION: Set in DBMS_APPLICATION_INFOMODULE: Set in DBMS_APPLICATION_INFOThe following V$SESSION columns are also useful:
Table 24-16 is a list of several columns that can be used to join to other fixed views.
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid = userenv('SESSIONID'); SID OSUSER USERNAME MACHINE PROCESS ----- ---------- ----------- ----------- -------- 398 amerora PERFSTAT rgmdbs1 26582
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1'; SID OSUSER USERNAME MACHINE TERMINAL ---- --------- --------- ---------- ---------- 398 amerora PERFSTAT rgmdbs1 pts/tl
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 sessionEVENT: Name of the wait eventTOTAL_WAITS: Total number of waits for this event by this sessionTIME_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 outTable 24-17 is a list of join columns for V$SESSION_EVENT.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
SELECT s.sid, bgp.name FROM V$SESSION s, V$BGPROCESS bgp WHERE bgp.name LIKE '%DBW%' AND bgp.paddr = s.paddr; SELECT event, total_waits waits, total_timeouts timeouts, time_waited total_time, average_wait avg FROM v$session_event WHERE sid = 3 ORDER BY time_waited DESC; EVENT WAITS TIMEOUTS TOTAL_TIME AVG ------------------------------ -------- -------- ---------- ----- rdbms ipc message 1684385 921495 284706709 169.03 db file parallel write 727326 0 3012982 4.14 latch free 157 157 281 1.78 control file sequential read 123 0 61 0.49 file identify 45 0 29 0.64 direct path read 41 0 5 0.12 file open 49 0 2 0.04 db file sequential read 2 0 2 1.00
This is a key view for finding bottlenecks. It tells what every session in the database is currently waiting for (or the last event waited for by the session if it is not waiting for anything). This view can be used as a starting point to find which direction to proceed in when a system is experiencing performance problems.
V$SESSION_WAIT has a row for every session connected to the instance. It indicates if the session is:
SID: Session identifier for the sessionEVENT: Event the session is currently waiting for, or the last event the session had to wait forWAIT_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 eventSEQ#: Gets incremented with every wait of the sessionP1, P2, P3: Wait event specific details for the waitP1TEXT, P2TEXT, P3TEXT: Description of P1,P2,P3 for the given event
Table 24-19 shows an example of how the EVENT, SEQ#, and WAIT_TIME might change over a period of time:
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.
Table 24-20 is a list of join columns for V$SESSION_WAIT.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
SELECT event, sum(decode(wait_time,0,1,0)) "Curr", sum(decode(wait_time,0,0,1)) "Prev", count(*)"Total" FROM v$session_wait GROUP BY event ORDER BY count(*); EVENT Prev Curr Tot --------------------------------------------- ---- ----- ----- PL/SQL lock timer 0 1 1 SQL*Net more data from client 0 1 1 smon timer 0 1 1 pmon timer 0 1 1 SQL*Net message to client 2 0 2 db file scattered read 2 0 2 rdbms ipc message 0 7 7 enqueue 0 12 12 pipe get 0 12 12 db file sequential read 3 10 13 latch free 9 6 15 SQL*Net message from client 835 1380 2215
This query, which groups the data by event and by wait_time (0=waiting, nonzero=not waiting), shows the following:
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 following statement to accomplish this. The total column counts the total of all the sessions, however the currently waiting and previously waited (using resource) columns only count active sessions.
SELECT event, sum(decode(wait_Time,0,0,DECODE(s.status,'ACTIVE',1,0))) "Prev", sum(decode(wait_Time,0,1,DECODE(s.status,'ACTIVE',1,0))) "Curr", count(*) "Tot" FROM v$session s, v$session_wait w WHERE s.sid = w.sid GROUP BY event ORDER BY count(*); EVENT Prev Curr Tot ---------------------------------------- ----- ----- ----- SQL*Net message to client 1 1 1 <- idle event buffer busy waits 1 1 1 file open 1 1 1 pmon timer 0 1 1 <- idle event smon timer 0 1 1 <- idle event log file sync 0 1 1 db file scattered read 0 2 2 rdbms ipc message 0 7 7 <- idle event pipe get 0 12 12 <- idle event enqueue 0 14 14 latch free 10 17 20 db file sequential read 7 22 23 SQL*Net message from client 0 1383 2240 <- idle event
Now sessions are counted as actively waiting or using a resource only if they are active. This highlights the following:
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 commitsredo sizeCPU used by this session, if TIMED_STATISTICS is set to TRUE
| See Also:
"Setting the Level of Statistics Collection" for information about |
The differences between V$SYSSTAT and V$SESSTAT are the following:
V$SESSTAT only stores data for each session, whereas V$SYSSTAT stores the accumulated values for all sessions.V$SESSTAT is transitory, and is lost after a session logs out. V$SYSSTAT is cumulative, and is only lost when the instance is shutdown.V$SESSTAT does not include the name of the statistic. In order to find the statistic name, this view must be joined to either V$SYSSTAT or V$STATNAME.V$SESSTAT can be used to find sessions with the following:
The 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).
Table 24-21 lists the join columns for V$SESSTAT.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
|
|
|
|
The following SQL statement shows the logical and physical read rates (each second) for all active sessions connected to the database. Rates for logical and physical I/O are calculated using the elapsed time since logon (from V$SESSION.LOGON_TIME). This might not be particularly accurate for sessions connected to the database for long periods, but it is sufficient for this example.
To determine the STATISTIC#'s for the session logical reads and physical reads statistics:
SELECT name, statistic# FROM V$STATNAME WHERE name IN ('session logical reads','physical reads') ; NAME STATISTIC# ------------------------------ ---------- session logical reads 9 physical reads 40
Use these values in the following query, which orders the sessions by resource usage:
SELECT ses.sid , DECODE(ses.action,NULL,'online','batch') "User" , MAX(DECODE(sta.statistic#,9,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s" , MAX(DECODE(sta.statistic#,40,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s" , 60*24*(sysdate-ses.logon_time) "Minutes" FROM V$SESSION ses , V$SESSTAT sta WHERE ses.status = 'ACTIVE' AND sta.sid = ses.sid AND sta.statistic# IN (9,40) GROUP BY ses.sid, ses.action, ses.logon_time ORDER BY SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) ) / greatest(3600*24*(sysdate-ses.logon_time),1) DESC; SID User Log IO/s Phy IO/s Minutes ----- ------ -------- -------- ------- 1951 batch 291 257.3 1 470 online 6,161 62.9 0 730 batch 7,568 43.2 197 2153 online 1,482 98.9 10 2386 batch 7,620 35.6 35 1815 batch 7,503 35.5 26 1965 online 4,879 42.9 19 1668 online 4,318 44.5 1 1142 online 955 69.2 35 1855 batch 573 70.5 8 1971 online 1,138 56.6 1 1323 online 3,263 32.4 5 1479 batch 2,857 35.1 3 421 online 1,322 46.8 15 2405 online 258 50.4 8
To better show the impact of each individual session on the system, the results were ordered by the total resource usage each second. The resource usage was calculated by adding session logical reads and (a weighted) physical reads.
Physical reads was weighted by multiplying the raw value by a factor of 100, to indicate that a physical I/O is significantly more expensive than reading a buffer already in the cache.
To calculate the physical I/O weighting factor, the following assumptions were made:
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).V$SHARED_POOL_ADVICE displays information about estimated parse time savings in the shared pool for different sizes. The sizes range from 50% to 200% of the current shared pool size, in equal intervals. The value of the interval depends on the current size of the shared pool.
A SQL statement can map to multiple cursors, because the objects referred to in the cursor can differ from user to user. If there are multiple cursors (child cursors) present, then V$SQLAREA provides aggregated information for all the cursors.
For looking at individual cursors, V$SQL can be used. This view contains cursor level details for the SQL. It can be used when trying to locate the session or person responsible for parsing the cursor.
The PLAN_HASH_VALUE column contains the numerical representation of the SQL plan for the cursor and can be used for comparing plans. PLAN_HASH_VALUE allows you to easily identify whether or not two plans are the same without comparing the two plans line by line.
This view provides a way of examining the execution plan for cursors that were executed and are still cached.
Normally, the information in this view is very similar to the output of an EXPLAIN PLAN statement. However, EXPLAIN PLAN shows a theoretical plan that might be used if this statement were to be executed, whereas V$SQL_PLAN contains the actual plan used. The execution plan obtained by the EXPLAIN PLAN statement can be different from the execution plan used to execute the cursor, because the cursor might have been compiled with different values of session parameters (for example, HASH_AREA_SIZE).
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 objectsIf previous plans are kept (for example, selected from V$SQL_PLAN and stored in permanent Oracle tables for reference), then it is then possible to identify how changes in the performance of a SQL statement can be correlated with changes in the execution plan for that statement.
|
Note: Oracle Corporation strongly recommends that you use the However, you must use the |
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 cursorHASH_VALUE: Hash value of the parent statement in the library cacheThe 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 ACCESSOPTIONS: A variation on the operation described in the OPERATION column; for example, FULLOBJECT_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 consumedOBJECT#: Object number of the table or the indexOBJECT_OWNER: Name of the user who owns the schema containing the table or indexOBJECT_NAME: Name of the table or indexOPTIMIZER: 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 notID: A number assigned to each step in the execution planPARENT_ID: ID of the next execution step that operates on the output of the current stepDEPTH: 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_IDCOST: Cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is nullCARDINALITY: The estimate, by the cost-based optimizer, of the number of rows produced by the operationBYTES: The estimate, by the cost-based optimizer, of the number of bytes produced by the operationOTHER_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 partitionPARTITION_STOP: The stop partition of a range of accessed partitionsPARTITION_ID: The step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columnsOTHER: 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 nullIO_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 nullTEMP_SPACE: Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is nullACCESS_PREDICATES: Predicates used to locate rows in an access structure; for example, start or stop predicates for an index range scanFILTER_PREDICATES: Predicates used to filter rows before producing themThe 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.
Table 24-23 lists the join columns for V$SQL_PLAN.
The following statement shows the EXPLAIN PLAN for a specified SQL statement. Looking at the plan for a SQL statement is one of the first steps in tuning a SQL statement. The SQL statement for which to return the plan is identified by the statement's HASH_VALUE and address.
Example 24-27 shows a query and sample output from V$SQL_PLAN (assumes only one child cursor):
SELECT /* TAG */ count(*) FROM employees e, departments d WHERE e.department_id = d.department_id; COUNT(*) ---------- 14 column operation format a20 column options format a20 column object_name format a20 column cost format a20 column cost format 9999 SELECT sql_text, address, hash_value FROM v$sql WHERE sql_text like '%TAG%'; SQL_TEXT ADDRESS HASH_VALUE -------- -------- ---------- 82117BEC 171077025 SELECT sql_text, address, hash_value FROM v$sql WHERE sql_text LIKE '%TAG%' SELECT /* TAG */ count(*) FROM employees e, departments d WHERE e.department_id = d.department_id SQL_TEXT ADDRESS HASH_VALUE -------- -------- ---------- 82157784 1224822469 SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address = '\ 82157784' AND hash_value = 1224822469; OPERATION OPTIONS OBJECT_NAME COST -------------------- ------------- ------------------ ---- SELECT STATEMENT 5 SORT AGGREGATE HASH JOIN 5 TABLE ACCESS FULL DEPARTMENTS 2 TABLE ACCESS FULL EMPLOYEES 2 6 rows selected.
V$SQL_PLAN shows the plan for a cursor, not for a SQL statement. The difference is that a SQL statement can have more than one cursor associated with it, with each cursor further identified by a CHILD_NUMBER. The following are a few examples of how a SQL statement can result in more than one cursor:
User1: SELECT * FROM EMPLOYEES;
User2: SELECT * FROM EMPLOYEES;
Where user2 has his own employee table, and user1 uses the table referenced by a public synonym.
ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS) in their login script, and user1 did not.
If the results of querying V$SQL_PLAN for a HASH_VALUE and ADDRESS result in more than one plan appearing, it is because this SQL statement has more than one child cursor. In this case, for each child cursor (identified by CHILD_NUMBER), look at the plan to identify whether they differ significantly.
| See Also:
"Identifying and Gathering Data on Resource-Intensive SQL" for information on how to identify SQL statements to tune |
This view provides, for each cached cursor, the execution statistics of each operation in the execution plan.
To view row source statistics in this view, the DBA must set the parameter STATISTICS_LEVEL to ALL.
| Column | Datatype | Description |
|---|---|---|
|
|
|
Address of the handle to the parent for this cursor |
|
|
|
Hash value of the parent statement in the library cache. The two columns ( |
|
|
|
Child cursor number using this work area. The columns ( |
|
|
|
Number assigned to each step in the execution plan. |
|
|
|
Number of times this cursor has been executed. |
|
|
|
Number of times this operation has been started,Foot 1 during the last execution. |
|
|
|
Number of times this operation has been started, accumulated over the past executions. |
|
|
|
Number of rows produced by the row source, during the last execution. |
|
|
|
Number of rows produced by the row source, accumulated over the past executions. |
|
|
|
Number of buffers retrieved in consistent mode, during the last execution. Buffers are usually retrieved in consistent mode for queries. |
|
|
|
Number of buffers retrieved in consistent mode, accumulated over the past executions. Buffers are usually retrieved in consistent mode for queries. |
|
|
|
Number of buffers retrieved in current mode, during the last execution. Buffers are retrieved in current mode for statements such as |
|
|
|
Number of buffers retrieved in current mode, accumulated over the past executions. Buffers are retrieved in current mode for statements such as |
|
|
|
Number of physical disk reads performed by the operation, during the last execution. |
|
|
|
Number of physical disk reads performed by the operation, accumulated over the past executions. |
|
|
|
Number of physical disk writes performed by the operation, during the last execution. |
|
|
|
Number of physical disk writes performed by the operation, accumulated over the past executions. |
|
|
|
Elapsed time (in microseconds) corresponding to this operation, during the last execution. |
|
|
|
Elapsed time (in microseconds) corresponding to this operation, accumulated over the past executions. |
This table concatenates information from V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA. V$SQL_WORKAREA contains memory usage statistics for row sources that use SQL memory (for example, hash-join and sort).
| Column | Datatype | Description |
|---|---|---|
|
|
|
Address of the handle to the parent for this cursor |
|
|
|
Hash value of the parent statement in the library cache. The two columns ( |
|
|
|
Child cursor number using this execution plan. The columns ( |
|
|
|
Name of the internal operation performed in this step--for example, |
|
|
|
A variation on the operation described in the |
|
|
|
Name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, this column describes the order in which output from operations is consumed. |
|
|
|
Object number of the table or the index. |
|
|
|
Name of the user who owns the schema containing the table or index. |
|
|
|
Name of the table or index. |
|
|
|
Current mode of the optimizer for the first row in the plan (statement line)--for example, |
|
|
|
Number assigned to each step in the execution plan. |
|
|
|
ID of the next execution step that operates on the output of the current step. |
|
|
|
Depth (or level) of the operation in the tree; it is not necessary to do a |
|
|
|
Order of processing for operations that all have the same |
|
|
|
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. |
|
|
|
Estimate, by the cost-based optimizer, of the number of rows produced by the operation. |
|
|
|
Estimate, by the cost-based optimizer, of the number of bytes produced by the operation. |
|
|
|
Describes the contents of the |
|
|
|
Start partition of a range of accessed partitions. |
|
|
|
Stop partition of a range of accessed partitions. |
|
|
|
Step that has computed the pair of values of the |
|
|
|
Other information that is specific to the execution step that a user may find useful; see Table 9-2 for values. |
|
|
|
Method used to distribute rows from producer query servers to consumer query servers; see Table 9-3 for values. |
|
|
|
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. |
|
|
|
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. |
|
|
|
Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. |
|
|
|
Predicates used to locate rows in an access structure; for example, start or stop predicates for an index range scan. |
|
|
|
Predicates used to filter rows before producing them. |
|
|
|
Number of times this cursor has been executed. |
|
|
|
Number of times this operation has been started, during the last execution. |
|
|
|
Number of times this operation has been started, accumulated over the past executions. |
|
|
|
Number of rows produced by the row source, during the last execution. |
|
|
|
Number of rows produced by the row source, accumulated over the past executions. |
|
|
|
Number of buffers retrieved in consistent mode, during the last execution. Buffers are usually retrieved in consistent mode for queries. |
|
|
|
Number of buffers retrieved in consistent mode, accumulated over the past executions. Buffers are usually retrieved in consistent mode for queries. |
|
|
|
Number of buffers retrieved in current mode, during the last execution. Buffers are retrieved in current mode for statements such as |
|
|
|
Number of buffers retrieved in current mode, accumulated over the past executions. Buffers are retrieved in current mode for statements such as |
|
|
|
Number of physical disk reads performed by the operation, during the last execution. |
|
|
|
Number of physical disk reads performed by the operation, accumulated over the past executions. |
|
|
|
Number of physical disk writes performed by the operation, during the last execution. |
|
|
|
Number of physical disk writes performed by the operation, accumulated over the past executions. |
|
|
|
Elapsed time (in microseconds) corresponding to this operation, during the last execution. |
|
|
|
Elapsed time (in microseconds) corresponding to this operation, accumulated over the past executions. |
|
|
|
Sizing policy for this work area. Values are either |
|
|
|
Estimated size (in KB) required by this work area to execute the operation completely in memory (optimal execution). This is either derived from optimizer statistics or from previous executions. |
|
|
|
Estimated size (in KB) required by this work area to execute the operation in a single pass. This is either derived from optimizer statistics or from previous executions. |
|
|
|
Memory size, in KB, used by this work area during the last execution of the cursor. |
|
|
|
Whether this work area ran using |
|
|
|
Degree of parallelism used, during the last execution of the cursor. |
|
|
|
Number of times this work area was active. |
|
|
|
Number of times this work area ran in optimal mode. |
|
|
|
Number of times this work area ran in one pass mode. |
|
|
|
Number of times this work area ran under the one-pass memory requirement. |
|
|
|
Time the work area has been active (in microseconds). |
|
|
|
Maximum temporary space used. |
|
|
|
Temporary space used. |
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.
These two columns are used to identify the SQL statement. Sometimes, two different statements could hash to the same value. In such cases, it is necessary to use the address along with the hash_value.
PARSING_USER_ID: User who parsed the first cursor for the statementVERSION_COUNT: Number of cursors for the statementKEPT_VERSIONS: Cursors of the statement pinned using DBMS_SHARED_POOL.KEEP()SHARABLE_MEMORY: Total shared memory used by the cursorPERSISTENT_MEMORY: Total persistent memory used by the cursorRUNTIME_MEMORY: Total runtime memory used by the cursorSQL_TEXT: Up to first 1000 characters of SQL statementMODULE, ACTION: Information about the session parsing the first cursor if set using DBMS_APPLICATION_INFOThese columns get incremented with each execution of the statement.
BUFFER_GETS: Number of logical reads for this statementDISK_READS: Number of physical reads for this statementSORTS: Number of sorts for this statementCPU_TIME: CPU time used for parsing and executing this statementELAPSED_TIME: Elapsed time for parsing and executing this statementPARSE_CALLS: Number of parse calls (hard and soft) for this statementEXECUTIONS: Number of times this statement was executedINVALIDATIONS: Number of times the cursors for this statement have been invalidatedLOADS: Number of loads (and reloads) for this statementROWS_PROCESSED: Total number of rows this statement returnsTable 24-26 lists the join columns for V$SQLAREA.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
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 statementADDRESS: Address of the SQL statement cursor in SGASQL_TEXT: Statement text in 64 character chunksPIECE: Ordering information for the SQL statement piecesTable 24-27 lists the join columns for V$SQLTEXT.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
|
|
|
|
SELECT sql_text FROM V$SQLTEXT WHERE hash_value = 228801498 ORDER BY piece; SQL_TEXT ---------------------------------------------------------------- select dbsu.primary_flag, i.site_use_code, i.rowid from ra_customers dbc, ra_addresses dbad, ra_site_uses dbsu, ra_customers_ interface i where (((((((i.orig_system_customer_ref=dbc.orig_system_reference and dbad.address_id=dbsu.address_id) and i.site_use_code=dbsu.site_use_code) and dbsu.status='A') and dbad.customer_id=dbc.customer_id) and i.request_id=:b0) and nvl(i.validated_flag,'N')<>'Y') and ((i.primary_site_use_flag='Y' and dbsu.primary_flag='Y') or dbsu.site_use_code in ('STMTS','DUN','LEGAL')))
group by dbsu.primary_flag,i.orig_system_customer_ref,i.site_use_code,i.insert_update_flag,i.rowid
V$STATISTICS_LEVEL lists the status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter. Each row of V$STATISTICS_LEVEL represents one of these statistics or advisories.
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. The following are sample column values for the statistic execute count.
| Statistic# | Name | Value |
|---|---|---|
|
|
|
|
|
Note: The |
The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed from V$SYSSTAT data.
Data in this view is also used for monitoring system resource usage and how the system's resource usage changes over time. As with most performance data, examine the system's resource usage over an interval. To do this, take a snapshot of the data within the view at the beginning of the interval and another at the end. The difference in the values (end value - begin value) for each statistic is the resource used during the interval. This is the methodology used by Oracle tools such as Statspack and BSTAT/ESTAT.
In order to compare one interval's data with another, the data can be normalized (for each transaction, for each execution, for each second, or for each logon). Normalizing the data on both workloads makes identifying the variances between the two workloads easier. This type of comparison is especially useful after patches have been applied, applications have been upgraded, or simply over time to see how increases in user population or data growth affects the resource usage.
You can also use V$SYSSTAT data to examine the resource consumption of contended-for resources that were identified by querying the V$SYSTEM_EVENT view.
This section describes some of the V$SYSSTAT statistics that are most useful during tuning, along with an explanation of the statistic. This list is in alphabetical order.
| See Also:
Oracle9i Database Reference for a complete list of statistics and their description |
CPU used by this session: The total amount of CPU used by all sessions, excluding background processes. This unit for this statistic is hundredths of a second. Calls that complete in less than 10ms are rounded up to this unit.db block changes: The number of changes made to database blocks in the SGA that were part of an insert, update, or delete operation. This statistic is a rough indication of total database work. On a for each transaction level, this statistic indicates the rate at which buffers are being dirtied.execute count: The total number of SQL statement executions (including recursive SQL).logons current: Sessions currently connected to the instance. When using two snapshots across an interval, an average value (rather than the difference) should be used.logons cumulative: The total number of logons since the instance started. To determine the number of logons in a particular period, subtract the end value from the begin value. A useful derived statistic is to divide the number of connections between a begin and end time, and divide this by the number of seconds the interval covered. This gives the logon rate. Optimally, there should be no more than two logons each second. To contrast, a logon rate of 50 a second is considered very high. Applications that continually connect and disconnect from the database (for example, once for each transaction) do not scale well.parse count (hard): The number of parse calls that resulted in a miss in the shared pool. A hard parse occurs when a SQL statement is executed and the SQL statement is either not in the shared pool, or it is in the shared pool but it cannot be shared because part of the metadata for the two SQL statements is different. This can happen if a SQL statement is textually identical to a preexisting SQL statement, but the tables referred to in the two statements resolve to physically different tables. A hard parse is a very expensive operation in terms of CPU and resource use (for example, latches), because it requires Oracle to allocate memory within the shared pool, then determine the execution plan before the statement can be executed.parse count (total): The total number of parse calls, both hard and soft. A soft parse occurs when a session executes a SQL statement, and the statement is already in the shared pool and can be used. For a statement to be used (that is, shared) all data pertaining to the existing SQL statement (including data such as the optimizer execution plan) must be equally applicable to the current statement being issued. These two statistics are used to calculate the soft-parse ratio.parse time cpu: Total CPU time spent parsing in hundredths of a second. This includes both hard and soft parses.parse time elapsed: The total elapsed time for the parse call to complete.physical reads: The number of blocks read from the operating system. It includes physical reads into the SGA buffer cache (a buffer cache miss) and direct physical reads into the PGA (for example, during direct sort operations). This statistic is not the number of I/O requests.physical writes: The number of database blocks written from the SGA buffer cache to disk by DBWR and from the PGA by processes performing direct writes.redo log space requests: The number of times a server process waited for space in the redo logs, typically because a log switch is needed.redo size: The total amount of redo generated (and hence written to the log buffer), in bytes. This statistic (normalized over seconds or over transactions) is a good indicator of update activity.session logical reads: The number of logical read requests that can be satisfied in the buffer cache or by a physical read.sorts (memory) and sorts (disk): sorts (memory) is the number of sort operations that fit inside the SORT_AREA_SIZE (and hence did not require an on disk sort). sorts (disk) is the number of sort operations that were larger than SORT_AREA_SIZE and had to use space on disk to complete the sort. These two statistics are used to compute the in-memory sort ratio.sorts (rows): The total number of rows sorted. This statistic can be divided by the 'sorts (total)' statistic to determine rows for each sort. It is an indicator of data volumes and application characteristics.table fetch by rowid: The number of rows returned using ROWID (due to index access or because a SQL statement of the form "where rowid = &rowid" was issued).table scans (rows gotten): The total number of rows processed during full table scans.table scans (blocks gotten): The number of blocks scanned during full table scans, excluding those for split rows.user commits + user rollbacks: This provides the total number of transactions on the system. This number is used as the divisor when calculating the ratios for each transaction for other statistics. For example, to calculate the number of logical reads for each transaction, use the following formula: session logical reads / (user commits + user rollbacks).A physical read as reported by Oracle might not result in an actual physical disk I/O operation. This is possible because most operating systems have an operating system files system cache where the block might be present. Alternatively, the block might also be present in disk or controller level cache, again avoiding an actual I/O. A physical read as reported by Oracle merely indicates that the required block was not in the buffer cache (or in the case of a direct read operation, was required to be read into private memory).
The following are typical instance efficiency ratios calculated from V$SYSSTAT data. Each ratio's computed value should all be as close as possible to 1:
Buffer cache hit ratio: This is a good indicator of whether the buffer cache is too small.
1 - ((physicalreads-physicalreadsdirect-physicalreadsdirect(lob))/sessionlogicalreads)
Soft parse ratio: This shows whether there are many hard parses on the system. The ratio should be compared to the raw statistics to ensure accuracy. For example, a soft parse ratio of 0.2 typically indicates a high hard parse rate. However, if the total number of parses is low, then the ratio should be disregarded.
1 - (parsecount(hard)/parsecount(total))
In-memory sort ratio: This shows the proportion of sorts that are performed in memory. Optimally, in an operational (OLTP) system, most sorts are small and can be performed solely as in-memory sorts.
sorts(memory)/ (sorts(memory)+sorts(disk))
Parse to execute ratio: In an operational environment, optimally a SQL statement should be parsed once and executed many times.
1 - (parsecount/executecount)
Parse CPU to total CPU ratio: This shows how much of the total CPU time used was spent on activities other than parsing. When this ratio is low, the system is performing too many parses.
1 - (parsetimecpu/CPUusedbythissession)
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.
parsetimecpu/parsetimeelapsed
To determine the load profile of the system, normalize the following statistics over seconds and over transactions: logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.
The normalized data can be examined to see if the 'rates' are high, or it can be compared to another baseline data set to identify how the system profile is changing over time. For example, block changes for each transaction is calculated by the following:
dbblockchanges/ (usercommits+user rollbacks)
Additional computed statistics that measure load include the following:
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).
dbblockchanges/sessionlogicalreads
sorts(rows)/ (sorts(memory)+sorts(disk))
Table 24-30 lists the join columns for V$SYSSTAT.
| Column | View | Joined Column(s) |
|---|---|---|
|
|
|
|
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 eventTOTAL_WAITS: Total number of waits for this eventTIME_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 outSELECT 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
log file switch completion) might happen only periodically, but cause a big performance hit when they happen.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 intervalUndoBlocksUsed: Total number of undo blocks consumedTxnConcurrency: Maximum number of transactions executed concurrentlyTxnTotal: Total number of transactions executed within the intervalQueryLength: Maximum length of queries, in seconds executed in the instanceExtentsStolen: Number of times an undo extent must be transferred from one undo segment to another within the intervalSSTooOldError: Number of 'Snapshot Too Old' errors that occurred within the intervalUNDOTSN: undo tablespaces in service during each time periodThe 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.
This example shows how undo space is consumed in the system for the previous 24 hours from the time 16:07.
SELECT * FROM V$UNDOSTAT; End-Time UndoBlocks TxnConcrcy TxnTotal QueryLen ExtentsStolen SSTooOldError -------- ---------- ---------- -------- -------- ------------- ------------- 16:07 252 15 1511 25 2 0 16:00 752 16 1467 150 0 0 15:50 873 21 1954 45 4 0 15:40 1187 45 3210 633 20 1 15:30 1120 28 2498 1202 5 0 15:20 882 22 2002 55 0 0
Among the statistics collected, you see that the peak undo consumption happened at the interval of (15:30, 15:40). 1187 undo blocks were consumed in 10 minutes (or about two blocks a second). Also, the highest transaction concurrency occurred during that same period with 45 transactions executing at the same time. The longest query (1202 seconds) was executed (and ended) in the period (15:20, 15:30). Note that the query actually was started in the interval (15:00, 15:10) and continued until around 15:20.
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 blockstime: Total time waited for this class of blockThe following are possible reasons for waits:
| See Also:
Chapter 22, "Instance Tuning" for more information on wait events |
|
 Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|