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"，共同學習，共同成長！！！

Oracle UNDO引數

Oracle UNDO引數

相關文章