文檔課題：UNDO表空間使用率過高的處理方式.

應(yīng)用場景：UNDO表空間使用率過高，想必很多數(shù)據(jù)庫管理員都遇到過此問題，接下來了解undo表空間的相關(guān)知識.

1、undo塊使用情況

SQL>select status,sum(bytes/1024/1024) from dba_undo_extents group by status;

STATUS SUM(BYTES/1024/1024)

--------------------------------------

EXPIRED 21.25

UNEXPIRED 10

2、當(dāng)前undo空閑

SQL> selecttablespace_name,sum(bytes/1024/1024) from dba_free_space wheretablespace_name='UNDOTBS1' group by tablespace_name

TABLESPACE_NAME SUM(BYTES/1024/1024)

----------------------------------------

UNDOTBS1 157.75

3、表空間情況

SQL>set line 200

SQL>col GRAPH for a50

SQL>col tablespace for a20

SQL> select total.ts tablespace,

total.mb total_mb,

to_char(trunc(total.mb -nvl(free.mb,0),2),'fm999990.0999') used_MB,

NVL(free.mb,0) free_mb,

DECODE(total.mb,NULL,0,NVL(ROUND((total.mb -free.mb)/(total.mb)*100,2),100)) pct_used,

CASE WHEN (total.mb IS NULL) THEN'['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'

ELSE '['|| DECODE(free.mb,

null,'XXXXXXXXXXXXXXXXXXXX',

NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100,2))/5),'X'),20,'-'),

'--------------------'))||']'

END as GRAPH

from

(select tablespace_name ts,sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,

(select tablespace_name ts,sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free

where total.ts=free.ts(+)

UNION ALL

select t3.tn tablespace,

t3.bytes total_mb,

to_char(trunc(t3.bytes -nvl(t4.bytes,0),2),'fm999990.0999') used_mb,

t4.bytes free_mb,

ROUND(((to_char(trunc(t3.bytes -nvl(t4.bytes,0),2),'fm999990.0999'))/t3.bytes)*100,2) pct_used,

CASE WHEN (t3.bytes ISNULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'

ELSE '['|| DECODE(t4.bytes,

null,'XXXXXXXXXXXXXXXXXXXX',

NVL(RPAD(LPAD('X',trunc((100-ROUND( (t4.bytes)/(t3.bytes) * 100,2))/5),'X'),20,'-'),

'--------------------'))||']' end asgraph

FROM(select tablespace_name tn,sum(bytes)/1024/1024 bytes from dba_temp_files groupby tablespace_name) t3,

(select tablespace_name tn,sum(bytes_cached)/1024/1024bytes from v$temp_extent_pool group by tablespace_name) t4

wheret3.tn = t4.tn(+)

order by5 Desc;

TABLESPACE TOTAL_MB USED_MB FREE_MB PCT_USED GRAPH

------------------------------ ---------- ---------- ------------------------------------------------------------

TEMP 97 97 0 100 [XXXXXXXXXXXXXXXXXXXX]

SYSTEM 1080 1065.625 14.375 98.67[XXXXXXXXXXXXXXXXXXX-]

SYSAUX 1400 1303 97 93.07 [XXXXXXXXXXXXXXXXXX--]

USERS 18.75 6.4375 12.3125 34.33[XXXXXX--------------]

UNDOTBS1 190 32.25 157.75 16.97[XXX-----------------]

TSP_ORDADM1 200 4 196 2 [--------------------]

TEST 150 2.625 147.375 1.75[--------------------]

TSP_ORDADM 800 1 799 .13 [--------------------]

已選擇 8 行.

4、undo_retention自動調(diào)整

undo表空間使用率跟undo_retention的值有關(guān)，保留時間越長unexpired越大，進(jìn)而undo表空間就越大.從oracle10g r2開始，oracle默認(rèn)是開啟undo表空間的自動調(diào)整功能，目的在于避免頻繁的ora-01555告警發(fā)生.意思是oracle會忽略undo_retention參數(shù)設(shè)置的閥值，而是根據(jù)undo表空間的大小和使用率來自動調(diào)整undo信息的保留時間.

通過視圖v$
undostat.tuned_undoretention可查詢該值是否有被自動調(diào)整，以下查詢語句：

SQL>select to_char(begin_time,'yyyy-mm-dd hh24:mi:ss'),to_char(end_time,'yyyy-mm-ddhh24:mi:ss'),tuned_undoretention from v$undostat

TO_CHAR(BEGIN_TIME,'YYYY-MM-DDHH24:MI:TO_CHAR(END_TIME,'YYYY-MM-DDHH24:MI:SS TUNED_UNDORETENTION

---------------------------------------------------------------------------- -------------------

2022-04-2110:10:01 2022-04-2110:20:01 900

2022-04-2110:00:01 2022-04-2110:10:01 900

2022-04-2109:50:01 2022-04-2110:00:01 2334

2022-04-2109:40:01 2022-04-2109:50:01 2330

2022-04-2109:30:01 2022-04-21 09:40:01 1726

2022-04-2109:20:01 2022-04-2109:30:01 1121

2022-04-2109:10:01 2022-04-2109:20:01 900

2022-04-2109:00:01 2022-04-2109:10:01 900

已選擇 33 行.

結(jié)論：以上可看出，undo_retention值有被自動調(diào)整到2334s.

5、相關(guān)知識

undo被使用順序.

事務(wù)會優(yōu)先使用undo空閑空間、過期狀態(tài)(expired)的undo區(qū)，然后才會嘗試擴(kuò)展表空間數(shù)據(jù)文件.在以上步驟都不能獲得undo表空間后，才會去使用未過期(unexpired)的undo區(qū).

oracle主要通過以下方式回收undoextend，使其成為undo表空間的空閑空間：

a、smon進(jìn)程周期性地回收過期狀態(tài)的undoextent，對于offline的undo段，會回收所有過期的undoextent.對于online的undo段，回收時會根據(jù)undo_retention時間和undo塊的使用率來確定回收的數(shù)量.undo_retention越大，能回收的undo空間就越小.

b、進(jìn)程在給事務(wù)分配undo塊時，會回收多余過期狀態(tài)的undoextent.因此，如果系統(tǒng)未過期的undo extent越多，這部分undo塊就不會返回給undo表空間而成為空閑空間，導(dǎo)致undo表空間已分配空間一直很多，同時使用率也會顯示很高.

6、解決方案

為避免undo表空間使用率過高，可采取以下措施：

a、降低undo_retention值

b、增加undo表空間數(shù)據(jù)文件

c、修改隱藏參數(shù)_undo_autotune=false來禁用自動undo保留調(diào)整功能

alter system set"_undo_autotune"=false scope=both sid='*';

d、設(shè)置隱藏參數(shù)_sum_debug_mode

alter system set "_sum_debug_mode"=33554432;

e、若undo_retention參數(shù)值在10800以下，建議設(shè)置為10800.

alter system set undo_retention=10800;