V$UNDOSTAT

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================
V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns NULL values if the system is in manual undo management mode.
Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.

Column	Datatype	Description
`BEGIN_TIME`	`DATE`	Identifies the beginning of the time interval
`END_TIME`	`DATE`	Identifies the end of the time interval
`UNDOTSN`	`NUMBER`	Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported.
`UNDOBLKS`	`NUMBER`	Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.
`TXNCOUNT`	`NUMBER`	Identifies the total number of transactions executed within the period
`MAXQUERYLEN`	`NUMBER`	Identifies the length of the longest query (in seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the `UNDO_RETENTION` initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view.
`MAXQUERYID`	`VARCHAR2(13)`	SQL identifier of the longest running SQL statement in the period
`MAXCONCURRENCY`	`NUMBER`	Identifies the highest number of transactions executed concurrently within the period
`UNXPSTEALCNT`	`NUMBER`	Number of attempts to obtain undo space by stealing unexpired extents from other transactions
`UNXPBLKRELCNT`	`NUMBER`	Number of unexpired blocks removed from certain undo segments so they can be used by other transactions
`UNXPBLKREUCNT`	`NUMBER`	Number of unexpired undo blocks reused by transactions
`EXPSTEALCNT`	`NUMBER`	Number of attempts to steal expired undo blocks from other undo segments
`EXPBLKRELCNT`	`NUMBER`	Number of expired undo blocks stolen from other undo segments
`EXPBLKREUCNT`	`NUMBER`	Number of expired undo blocks reused within the same undo segments
`SSOLDERRCNT`	`NUMBER`	Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the `UNDO_RETENTION` initialization parameter is set properly given the size of the undo tablespace. Increasing the value of `UNDO_RETENTION` can reduce the occurrence of this error.
`NOSPACEERRCNT`	`NUMBER`	Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.
`ACTIVEBLKS`	`NUMBER`	Total number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period
`UNEXPIREDBLKS`	`NUMBER`	Total number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period
`EXPIREDBLKS`	`NUMBER`	Total number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the period
`TUNED_UNDORETENTION`	`NUMBER`	Amount of time (in seconds) for which undo will not be recycled from the time it was committed. At any point in time, the latest value of `TUNED_UNDORETENTION` is used to determine whether data committed at a particular time in the past can be recycled.

Note:

1. This V$UNDOSTAT view contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management (ASM) mode.

The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. Statistics are available for UNDO space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.

2. Explain the meaning of columns:

When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.

If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.

If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

3. In 10g DBA_HIST_UNDOSTAT view contains statistical snapshots of V$UNDOSTAT information.

4. Oracle initialization parameter UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. The parameter can only be honoured if the current undo tablespace has enough space.

5. The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

6. Scripts using

-- See how much undo information has been written in the past hour.

select begin_time, end_time, undoblks, txncount,

maxconcurrency, nospaceerrcnt

from V$UNDOSTAT

where begin_time > sysdate-(1/24);

BEGIN_TI END_TIME UNDOBLKS TXNCOUNT MAXCONCURRENCY NOSPACEERRCNT

-------- -------- ---------- ---------- -------------- -------------

10:11:49 10:19:56 155 228 3 0

10:01:49 10:11:49 177 763 3 0

09:51:49 10:01:49 150 593 3 0

09:41:49 09:51:49 398 1797 5 0

09:31:49 09:41:49 1340 7060 5 0

09:21:49 09:31:49 163 2124 4 0

Note: This query shows how much redo has been used over the last hour. Here we can see that there have been 7060 transactions from 09:31 – 09:41 using 1340 undo blocks and none of them received any errors due to space management.

-- To find the time of the longest query in the instance:

select begin_time, MAXQUERYLEN, undoblks

from   V$UNDOSTAT

where  begin_time > sysdate-(1/12);

BEGIN_TI MAXQUERYLEN   UNDOBLKS

-------- ----------- ----------

10:21:49           0          2

10:11:49           0        223

10:01:49           0        177

09:51:49           0        150

09:41:49           0        398

09:31:49          36       1340   <--- 36 seconds

09:21:49           0        163

09:11:49           0         82

09:01:49           0        326

08:51:49           0        253

08:41:49           0        186

Note: This shows that the longest query that has been run was 36 seconds long. To ensure this query never gets ORA-01555 set UNDO_RETENTION to at least 36 seconds. If UNDO_RETENTION is set too high the UNDO tablespace will need to be very large. If to low ORA-01555 may occur to often.