疑惑：undo与datafile的存储结构

xteitxu

UNDO可以存储我删除对象的前镜像，删除前的情况如下（这是测试库，只有我一人在操作数据库，没有其它的数据操作）：
16:12:34 SQL> select bytes/1024/1024 from user_segments where segment_name='TEST';
BYTES/1024/1024
---------------
             272
UNDO表空间的使用情况 ：
TABLESPACE_NAME                                              EXTENT_MANAGEMENT    SEGMENT_SPAC     SUM_MB    FREE_MB USE_PRECENT
------------------------------------------------------------ -------------------- ------------ ---------- ---------- -----------
UNDOTBS1                                                     LOCAL                MANUAL             2330       1765       24.23
我把TEST表所有数据DETELE后UNDO的使用情况 ：
TABLESPACE_NAME                                              EXTENT_MANAGEMENT    SEGMENT_SPAC     SUM_MB    FREE_MB USE_PRECENT
------------------------------------------------------------ -------------------- ------------ ---------- ---------- -----------
UNDOTBS1                                                     LOCAL                MANUAL             2330       1298       44.27

从UNDO中看到使用了476M左右，而TEST表的segment大小是272M左右，为什么到了UNDO会占用那么多空间呢？是UNDO与datafile 的存储结构有差异吗？

Maclean Liu(刘相兵

与其说是 是UNDO与datafile 的存储结构有差异吗？

本质是undo record和 普通的 row piece是有差异的， 对于这个问题 请不要停留在表面， 你至少要dump一下 undo和 普通的data block才能知道差异有多大

xteitxu

谢谢刘大   随后我看下dump吧
有人也给出答案让首先去理解一下UNDO的uS/uR/uU/ eS/eR/eU这几个状态，理解了就明白了
我在GOOGLE  MOS也没找到这些参数的详细说明   能否借用一下你的搜索能力，给个LINK

uS - unexpired Stolen
uR - unexpired Released
uU - unexpired reUsed
eS - expired   Stolen
eR - expired   Released
eU - expired   reUsed

yanshoupeng

uS - unexpired Stolen  :Number of attempts to obtain undo space by stealing unexpired extents from other transactions
uR - unexpired Released Number of unexpired blocks removed from certain undo segments so they can be used by other transactions
uU - unexpired reUsed  Number of unexpired undo blocks reused by transactions
eS - expired   Stolen  Number of attempts to steal expired undo blocks from other undo segments
eR - expired   Released  Number of expired undo blocks stolen from other undo segments
eU - expired   reUsed  Number of expired undo blocks reused within the same undo segments

yanshoupeng

V$UNDOSTAT
Oracle 9i (and also available in 10g) provides a view to see how well the undo tablespace is performing. The V$UNDOSTAT view contains one line for each ten minute interval covering the last 7 days. This view can be used to determine if the undo tablespace needs to be larger or smaller. The following query is a common one for this view:

select to_char(begin_time,'MM/DD HH24:MI') as begin_time,
undoblks,maxquerylen,unxpstealcnt,expstealcnt,ssolderrcnt,nospaceerrcnt
from v$undostat;
The first column (BEGIN_TIME) will be the start time of the interval, for that row of data. The next row of data will be for the interval ten minutes later. The V$UNDOSTAT also contains an END_TIME column, which should be equal to the BEGIN_TIME column in the subsequent row.

The UNDOBLKS column indicates the total number of undo blocks that were used during that time period. A good rule of thumb is to find the maximum value in this column and multiply it by the default database block size and use that value for the minimum size of the undo tablespace. The following query can find the maximum number of undo blocks used in any 10 minutes interval:

select max(undoblks) from v$undostat;
The MAXQUERYLEN column shows the maximum query length, in seconds, of any query in that time interval. The undo_retention initialization parameter should be set to a value larger than the maximum query length. Set this parameter to a value larger than the value returned by this query:

select max(maxquerylen) from v$undostat;
If there is not enough space in the undo tablespace, then expired undo blocks may be stolen to satisfy new transactions. Or worse yet, unexpired undo blocks may be stolen. The EXPSTEALCNT column shows how many times expired undo blocks were stolen for that time interval. The UNXPSTEALCNT column shows how many times unexpired undo blocks were stolen. The NOSPACEERRCNT column shows how many times there was no free space of any kind in the undo tablespace. If these columns contain non-zero values, consider increasing the size of the undo tablespace.

It is possible for long running queries to not have undo available for read consistency. When this happens, the long running query will run into the ORA-1555, snapshot too old error message. The SSOLDERRCNT column shows how many times queries received the ORA-1555 error message during that time interval. If this column contains non-zero values, consider raising the undo_retention initialization parameter.

donyorcl001

学习了。

xteitxu

yanshoupeng 发表于 2013-3-4 22:13
V$UNDOSTAT
Oracle 9i (and also available in 10g) provides a view to see how well the undo tablespace ...

非常感谢