Oracle UNDO 参数
查看 UNDO 相关参数：
(1) 查看 undo_retention 参数
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
参数： undo_retention
undo_retention表示已经提交或回滚的事物在 UNDO EXTENT 中保留的时间；
当事物结束时间 <= undo_retention 时， UNDO EXTENT在dba_undo_extents.status中 状态为 UNEXPIRED;
当事物结束时间 > undo_retention 时， UNDO EXTENT在dba_undo_extents.status中 状态为 EXPIRED;
当事物没有结束时 ， UNDO EXTENT在dba_undo_extents.status中 状态为 ACTIVE;
select t.status,sum(t.blocks)*8/1024||'M' from dba_undo_extents t group by t.status;
STATUS SUM(T.BLOCKS)*8/1024||'M'
--------- -----------------------------------------
UNEXPIRED 11.125M
EXPIRED 41.125M
ACTIVE 19.125M
(2) 查看隐含参数 _undo_autotune(sys 用户下查询 )
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm='_undo_autotune';
NAME
VALUE
DESCRIPTION
-------------------------- -------------------------- --------------------------------------------------------------------------------
_undo_autotune
TRUE enable auto tuning of undo_retention
参数： _undo_autotune
从 10.2 版本开始， oracle 默认采用自动调整 undo retention 的方法， Oracle 会自动调整 undo retention 时间，来保证不会出现 ORA-01555 错误，这可能会导致 UNDO 表空间自动扩展过快，过期的 undo extent 长期不会释放，当 undo tablespace 有空闲空间时，系统自动调大 undo_retention 来保留更多的 undo blocks ， Oracle Database 基于 undo 表空间大小和系统活动自动调整 undo retention ，可能会导致 ORA-30036 ；
当使用的UNDO表空间非自动增长，tuned_undoretention是基于UNDO表空间大小的使用率计算出来的，在一些情况下，特别是较大的UNDO表空间时，这将计算出较大的值。
--- 查看 Oracle 自动调整 UNDO RETENTION 的值：
SELECT TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD HH24:MI:SS') BEGIN_TIME,TUNED_UNDORETENTION FROM V$UNDOSTAT;
BEGIN_TIME TUNED_UNDORETENTION
------------------- -------------------
2016-05-06 09:57:37 1718
2016-05-06 09:47:37 1177
2016-05-06 09:37:37 1779
2016-05-06 09:27:37 1177
2016-05-06 09:17:37 1778
2016-05-06 09:07:37 1177
2016-05-06 08:57:37 1770
2016-05-06 08:47:37 1158
8 rows selected
(3) 查看 UNDO 表空间是否 noguarantee 状态
select tablespace_name,
block_size,
extent_management,
segment_space_management,
contents,
retention
from dba_tablespaces
where tablespace_name = 'UNDOTBS1';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT CONTENTS RETENTION
------------------------------ ---------- ----------------- ------------------------ --------- -----------
UNDOTBS1 8192 LOCAL MANUAL UNDO
NOGUARANTEE
参数： GUARANTEE
GUARANTEE 保证 undo_retention 参数所设定的时间有效，这个是 10g 的新功能。默认该功能没有开启，即事物在小于 undo_retention 时间时也可能被覆盖。开启 GUARANTEE 后可以保证 事物在小于等于 undo_retention 时间不会被覆盖。 GUARANTEE 和参数 _undo_autotune 结合使用可以保证 UNDO 中事物长时间不被覆盖，避免 ORA-01555 错误。
--- alter tablespace undotbs1 retention guarantee;
--- 查看隐含参数 _collect_undo_stats (sys)
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm='_collect_undo_stats';
NAME
VALUE
DESCRIPTION
------------------------------- -------------------------- -------------------------------------------
_collect_undo_stats TRUE Collect Statistics v$undostat
参数： _collect_undo_stats
--- 查看隐含参数 _smu_debug_mode (sys)
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_smu_debug_mode';
NAME
VALUE
DESCRIPTION
----------------------------------- --------------------------- --------------------------------------------------------------------------------
_smu_debug_mode 0 - set debug event for testing SMU operations
- Set the following instance parameter:
_smu_debug_mode=33554432
With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a
percentage of the fixed size undo tablespace. Instead it is set to the maximum
of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.
文档 ID 420525.1
设置以下的实例参数：
_smu_debug_mode=33554432
设置该参数，
TUNED_UNDORETENTION
就不基于
undo
表空间大小的使用率计算，代替的是设置
(MAXQUERYLEN +300)
和
UNDO_RETENTION
的最大值。
select MAXQUERYLEN,begin_time,end_time from V$UNDOSTAT a order by 1 desc;
MAXQUERYLEN BEGIN_TIME END_TIME
----------- ----------- -----------
938 2016/5/6 9: 2016/5/6 10
938 2016/5/6 9: 2016/5/6 9:
938 2016/5/6 9: 2016/5/6 9:
930 2016/5/6 8: 2016/5/6 9:
337 2016/5/6 9: 2016/5/6 9:
336 2016/5/6 10 2016/5/6 10
336 2016/5/6 9: 2016/5/6 9:
335 2016/5/6 9: 2016/5/6 9:
302 2016/5/6 8: 2016/5/6 8:
9 rows selected
UNDO EXTENT 使用原理：
当 UNDO 表空间自动扩展空间不足或者 UNDO 表空间是非自动扩展并且已经达到上限， Oracle 会尝试重复使用同一个 undo 段下面 EXPIRED 状态的 EXTENT ，如果本段中没有这样的 EXTENT ，就会去偷别的段下面 EXPIRED 状态的 EXTENT ，如果依然没有这样的 EXTENT ，就会使用本段 UNEXPIRED 的 EXTENT ，如果还是没有，那么会去偷别的段的 UNEXPIRED 的 EXTENT ，这个都没有，就会报错。
Full UNDO Tablespace In 10gR2 and above ( 文档 ID 413732.1)
The Undo Block allocation algorithm in Automatic Undo Management is the following :
1. If the current extent has more free blocks then the next free
block is allocated.
2. Otherwise, if the next extent expired then wrap in the next extent and
return the first block.
3. If the next extent is not expired then get space from the UNDO tablespace.
If a free extent is available then allocate it to the undo segment and return
the first block in the new extent.
4. If there is no free extent available, then steal expired extents from
offline undo segments. De-allocate the expired extent from the offline undo
segment and add it to the undo segment. Return the first free block of the
extent.
5. If no expired extents are available in offline undo segments, then steal
from online undo segments and add the new extents to the current undo
segment. Return the first free block of the extent.
6. Extend the file in the UNDO tablespace. If the file can be extended then add
an extent to the current undo segment and then return the block.
7. Tune down retention in decrements of 10% and steal extents that were
unexpired, but now expired with respect to the lower retention value.
8. Steal unexpired extents from any offline undo segments.
9. Try to reuse unexpired extents from own undo segment. If all extents are
currently busy (they contains uncommitted information) go to the step 10. Otherwise,
wrap into the next extent.
10. Try to steal unexpired extents from any online undo segment.
11. If all the above fails then return ORA-30036 unable to extend segment by %s
in undo tablespace '%s'
When the UNDO tablespace is created with NO AUTOEXTEND, following the allocation algorithm, here is the explanation for this correct behavior:
For a fixed size UNDO tablespace (NO AUTOEXTEND), starting with
10.2, we provide max retention given the fixed undo space, which is set to a
value based on the UNDO tablespace size.
This means that even if the undo_retention is set to a number of seconds (900
default), the fixed UNDO tablespace supports a bigger undo_retention time
interval (e.g: 36 hours), based on the tablespace size, thing that makes the
undo extents to be UNEXPIRED. But this doesn't indicate that there are no
available undo extents when a transaction will be run in the database, as the
UNEXPIRED undo segments will be reused.
---查看 UNDO 表空间是否自动扩展：
select tablespace_name,file_name,autoextensible,maxbytes from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE MAXBYTES
------------------------------ -------------------------------------------------------------------------------- -------------- ----------
UNDOTBS1 D:\APP_10.2.0.4\CHEN_DATAFILE\UNDOTBS01.DBF YES 3435972198
---查看UNDO表空间使用情况
SELECT a.tablespace_name as tablespace_name,
to_char(b.total / 1024 / 1024, 999999.99) as Total,
to_char((b.total - a.free) / 1024 / 1024, 999999.99) as Used,
to_char(a.free / 1024 / 1024, 999999.99) as Free,
to_char(round((total - free) / total, 4) * 100, 999.99) as Used_Rate
FROM (SELECT tablespace_name, sum(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = 'UNDOTBS1'
ORDER BY a.tablespace_name;
TABLESPACE_NAME TOTAL USED FREE USED_RATE
------------------------------ ---------- ---------- ---------- ---------
UNDOTBS1 645.00 72.38 572.63 11.22
select t.status,sum(t.blocks)*8/1024||'M' from dba_undo_extents t group by t.status;
STATUS SUM(T.BLOCKS)*8/1024||'M'
--------- -----------------------------------------
UNEXPIRED 29.25M
EXPIRED 41.125M
ACTIVE 1M
select sum(t.blocks)*8/1024||'M' as USED from dba_undo_extents t; ---约等于上个SQL的unexpired+expired+active的值
USED
-----------------------------------------
71.375M
和 UNDO 相关的常用视图和数据字典
a).DBA_ROLLBACK_SEGS
DBA_ROLLBACK_SEGS describes rollback segments.
b).V$ROLLSTAT
V$ROLLSTAT contains rollback segment statistics.
c).V$TRANSACTION
V$TRANSACTION lists the active transactions in the system.
d).V$UNDOSTAT
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.
e).DBA_UNDO_EXTENTS
DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo
tablespaces in the database. This view shows the
status and size of each extent in the undo tablespace.
参考：
http://blog.itpub.net/23135684/viewspace-1065601/
---《 监控和管理Oracle UNDO 表空间的使用 》
MOS文档 ID 420525.1
MOS文档 ID 413732.1
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
