Performance Views
V$UNDOSTAT
Use to monitor and configure database system to achieve efficient use of UNDO space. V$UNDOSTAT shows various undo and transaction statistics, such as the amount of undo space consumed in the instance. In earlier releases, undo space management was performed using rollback segments. This method is now called manual undo management mode.
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. 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. Each column in a row contains statistics gathered during this 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 1008 rows, spanning a 7 day cycle.
If space problems occur so a user receives a segment out of space error then the column NOSPACEERRCNT will increase. If this happens then the undo tablespace is now to small and will need to be increased via enlarging a datafile or adding another data file to the tablespace.
V$UNDOSTAT
Use to monitor and configure database system to achieve efficient use of UNDO space. V$UNDOSTAT shows various undo and transaction statistics, such as the amount of undo space consumed in the instance. In earlier releases, undo space management was performed using rollback segments. This method is now called manual undo management mode.
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. 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. Each column in a row contains statistics gathered during this 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 1008 rows, spanning a 7 day cycle.
| Column | Datatypes | 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 number of seconds) executed in the instance during the period. Use this statistic to estimate the proper setting of undo_retention. |
| 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. Use statistic to decide whether or not undo_retention 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. |
See how much undo information has been written in the past hour.
select begin_time, end_time, undoblks, txncount, maxconcurrency as maxcon,nospaceerrcnt from V$UNDOSTAT where begin_time > sysdate-(1/24);
BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT MAXCON NOSPACEERRCNT
-------------- -------------- ---------- ---------- ---------- -------------
01-04-30 14:07 01-04-30 14:14 1883 7 3 0
01-04-30 13:57 01-04-30 14:07 488 34 3 0
01-04-30 13:47 01-04-30 13:57 0 2 1 0
01-04-30 13:37 01-04-30 13:47 0 2 1 0
01-04-30 13:27 01-04-30 13:37 0 2 1 0
01-04-30 13:17 01-04-30 13:27 0 2 1 0
This query shows how much redo has been used over the last hour. With the last 20 minutes being the only time that has used undo space. Here we can see that there have been 34 transactions from 13:57 – 14:07 using 488 undo blocks and none of them received any errors due to space management. And from 14:07 until present there are 7 transactions in total using 1883 undo blocks and so far no space problems.
If space problems occur so a user receives a segment out of space error then the column NOSPACEERRCNT will increase. If this happens then the undo tablespace is now to small and will need to be increased via enlarging a datafile or adding another data file to the tablespace.
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_TIME MAXQUERYLEN UNDOBLKS -------------- ----------- ---------- 01-04-30 15:27 15 482 01-04-30 15:17 14 1895 01-04-30 15:07 0 1 01-04-30 14:57 0 0 01-04-30 14:47 0 0 01-04-30 14:37 0 1 01-04-30 14:27 738 3426 <--- 738 seconds 01-04-30 14:17 377 2564 01-04-30 14:07 236 2277 01-04-30 13:57 21 488 01-04-30 13:47 0 0 01-04-30 13:37 0 0This shows that the longest query that has been run was 738 seconds long. To ensure this query never gets ORA-1555 set undo_retention to at least 750 seconds. If undo_retention is set to high the UNDO tablespace will need to be very large. If to low ORA-1555 may occur to often.
| Related Parameters: | |
| undo_management | Specifies which undo space management mode the system should use. |
| undo_retention | Specifies (in seconds) the amount of committed undo information to retain in the database |
| undo_tablespace | Specifies the undo tablespace to used during startup. |
| undo_suppress_errors |
| Related Commands: | |
| ALTER SYSTEM |
| Related Views: | |
| DBA_ROLLBACK_SEGS | Describes rollback segments. |
| DBA_UNDO_EXTENTS | Describes the extents comprising the segments in all undo tablespaces in the database. |
| V$ROLLNAME | Lists the names of all online rollback segments. |
| V$ROLLSTAT | For automatic undo management mode, information reflects behavior of the undo segments in the UNDO tablespace. |
| V$TRANSACTION | Contains undo segment information. |
| Related Packages: | |
| DBMS_FLASHBACK | Flash back to a version of the database at a specific time or a specific. |
No comments:
Post a Comment