V$UNDOSTAT

学习动态性能表第19篇--V$UNDOSTAT V$UNDOSTAT中的常用列 Endtime：以10分钟为间隔的结束时间 UndoBlocksUsed：使用的undo块总数 TxnConcurrency：事务并发执行的最大数 TxnTotal：在时间段内事务执行总数 QueryLength：查询长度的最大值 ExtentsStolen：在时间段内undo区必须从一个undo段转到另一个的次数 SSTooOldError：在时间段内'Snapshot Too Old'错误发生的次数 UNDOTSN：这段时间内最后活动的undo表空间ID 　　视图的第一行显示了当前时间段的统计，其它的每一条记录分别以每10分钟一个区间。24小时循环，一天最多144条记录。 示例： 1.本例显示undo空间从16:27到之前24小时内的各项统计。 SQL>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 在统计项收集过程中，undo消耗最高发生在15:30-15:40这个时间段。10分钟内有1187个undo块被占用(基本上每秒钟2个块)。同时，最高事务并发也是在相同的时间段，45个事务被并发执行。执行的最长查询(1202秒)是在15:20-15:30之间，需要注意的是查询实际上是15:00-15:10段即开始并直到15:20这个时间段。 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/junsisi/archive/2007/08/29/1764231.aspx ----------------- V$UNDOSTAT V$UNDOSTATdisplays a histogram of statisticaldatato 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.Oracleuses this view to tune undo usage in the system. The view returns null values if the system is in manual undo management mode. --补：此视图主要是记录undo tablespace使用情况，采用10分钟为间隔采样，最多存储7day的数据, Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by theBEGIN_TIMEcolumn 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. --补：每条记录对应一个10分钟的采样数据，其中每一列是一个独立属性的具体体现 Column Datatype Description BEGIN_TIME DATE Identifies the beginning of the time interval END_TIME DATE Identifies the end of the time interval UNDOTSN --采样间隔的undo tablespace标识号,如果当时有多个活动的undo tablespace,仅记录最后一个活动的标识号 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 --采样间隔共用多少undo blocks，用它大致可以估计出你undo tablespace的大小了 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 --采样间隔运行最少事务的时间，注意是以秒计算，用它可以评估undo_rentition是否配置合理了 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 theUNDO_RETENTIONinitialization 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 --采样间隔运行时间最长的sqlid 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 --多少次尝试从其它事务想得到未过期的extents NUMBER Number of attempts to obtain undo space by stealing unexpired extents from other transactions UNXPBLKRELCNT --被其它事务使用的来自于undo segements未过期的块数 NUMBER Number of unexpired blocks removed from certain undo segments so they can be used by other transactions UNXPBLKREUCNT --事务重用的未过期的undo block数 NUMBER Number of unexpired undo blocks reused by transactions EXPSTEALCNT ---多少次尝试从其它undo segment得到未过期的undo block NUMBER Number of attempts to steal expired undo blocks from other undo segments EXPBLKRELCNT --从其它undo segment得到的过期undo block数 NUMBER Number of expired undo blocks stolen from other undo segments EXPBLKREUCNT --同样undo segment中，被重用的过期undo block数 NUMBER Number of expired undo blocks reused within the same undo segments SSOLDERRCNT --标识发生了多少次ora-01555错误，根据它可以确认undo_rentition是否配置合理 NUMBER Identifies the number of times the errorORA-01555occurred. You can use this statistic to decide whether or not theUNDO_RETENTIONinitialization parameter is set properly given the size of the undo tablespace. Increasing the value ofUNDO_RETENTIONcan reduce the occurrence of this error. NOSPACEERRCNT --多少次向undo tablespace发出空间请求,修正行为就是添加更多的undo tablespace size 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 --采样间隔活动extents的block数 NUMBER Total number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period UNEXPIREDBLKS ---采样间隔未过期extents的block数 NUMBER Total number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period EXPIREDBLKS ---采样间隔过期extents的block块 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 --oracle给出不同负荷下的undo_retention智能建议 System

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/196700/viewspace-720734/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/196700/viewspace-720734/