? Oracle 10gr2的后續(xù)版本中添加了UNDO信息最短保留時(shí)間段自動(dòng)調(diào)優(yōu)的特性，不再僅僅依據(jù)參數(shù)UNDO_RETENTION的設(shè)定，其調(diào)優(yōu)原則如下： 1 當(dāng)UNDO TABLESPACE為 fixed- size，Oracle將根據(jù)表空間的大小和歷史使用情況，自動(dòng)調(diào)整undo信息保存時(shí)間，同時(shí)忽略 undo_retention的值除非 undo_retention的guarantee 特性被啟用。 2 當(dāng)UNDO TABLESPACE為AUM時(shí)，Oracle將動(dòng)態(tài)調(diào)整撤銷信息最短保留時(shí)間為該時(shí)段最長(zhǎng)查詢時(shí)間(MAXQUERYLEN)加上300秒或參數(shù)UNDO_RETENTION間的較大者，即MAX((MAXQUERYLEN+300),UNDO_RENTION)； 在自動(dòng)調(diào)整啟用的情況下，實(shí)際的撤銷信息最短保留時(shí)間可以通過(guò)查詢V$UNDOSTAT視圖上的TUNED_UNDORETENTION列獲得。往往最短保存時(shí)間遠(yuǎn)遠(yuǎn)大于設(shè)定的UNDO_RETENTION。在無(wú)法就UNDO TABLESPACE做相應(yīng)修改的情況，可以通過(guò)修改隱式參數(shù)”_UNDO_AUTOTUNE”為FALSE關(guān)閉該自動(dòng)調(diào)優(yōu)特性。以上設(shè)定生效后，V$UNDOSTAT視圖上TUNED_UNDORETENTION列不再更新，且撤銷信息最短保留時(shí)間固定為參數(shù)UNDO_RETENTION的設(shè)定值。該參數(shù)可以不用重啟數(shù)據(jù)庫(kù)而動(dòng)態(tài)設(shè)置生效。 下面做實(shí)驗(yàn)說(shuō)明undo自動(dòng)調(diào)整的功能以及其弊端：注：實(shí)驗(yàn)環(huán)境中無(wú)他事務(wù)。 進(jìn)行一個(gè)dml 語(yǔ)句不提交，查看dba_undo_extents 關(guān)于回滾段的信息， YANG@yangdb-rac3> show parameter undo NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TYPE ? ? ? ?VALUE ------------------------------------ ----------- ------------------------------ undo_management ? ? ? ? ? ? ? ? ? ? ?string ? ? ?AUTO undo_retention ? ? ? ? ? ? ? ? ? ? ? integer ? ? 60 undo_tablespace ? ? ? ? ? ? ? ? ? ? ?string ? ? ?UNDOTBS1
YANG@yangdb-rac3> update bind set status='VALID' where wner='YANG'; 5 rows updated. YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status 2 ? ? ?from dba_undo_extents 3 ? ? ?where status='ACTIVE';
SEGMENT_NAME ? ? ? ? TABLESPACE_NAME ?EXTENT_ID STATUS -------------------- --------------- ---------- --------- _SYSSMU3_2097677531$ UNDOTBS1 ? ? ? ? ? ? ? ? 2 ACTIVE YANG@yangdb-rac3> commit;? Commit complete. YANG@yangdb-rac3> --等待一分鐘之后 YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status 2 ? ? from dba_undo_extents 3 ? ? where status='ACTIVE';
SEGMENT_NAME ? ? ? ? TABLESPACE_NAME ?EXTENT_ID STATUS -------------------- --------------- ---------- --------- _SYSSMU3_2097677531$ UNDOTBS1 ? ? ? ? ? ? ? ? 2 UNEXPIRED?
YANG@yangdb-rac3> ?--等待一分鐘之后 YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status 2 ? ? from dba_undo_extents 3 ? ? where status='ACTIVE';
SEGMENT_NAME ? ? ? ? TABLESPACE_NAME ?EXTENT_ID STATUS -------------------- --------------- ---------- --------- _SYSSMU3_2097677531$ UNDOTBS1 ? ? ? ? ? ? ? ? 2 UNEXPIRED? 可以看到提交一分鐘之后，回滾段_SYSSMU3_2097677531$的狀態(tài)依然為UNEXPIRED，盡管undo_retention 設(shè)置為60s。本應(yīng)該釋放的undo卻未被及時(shí)釋放。其實(shí)這也是為什么生產(chǎn)環(huán)境中undo表空間總是接近100%的原因。 由之前的介紹oracle提供的undo自動(dòng)調(diào)優(yōu)技術(shù)，只是將undo_retention做為一個(gè)參考值，而實(shí)際設(shè)置的undo_retention時(shí)間有v$undostat.tuned_undoretention 而定，查看其信息； ? YANG@yangdb-rac3> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ; Session altered. YANG@yangdb-rac3> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat; BEGIN_TIME ? ? ? ? ?END_TIME ? ? ? ? ? ?TUNED_UNDORETENTION ------------------- ------------------- ------------------- 2011/10/16 19:46:29 2011/10/16 19:56:29 ? ? ? ? ?1412 --進(jìn)行查詢時(shí)候的保留時(shí)間明顯大于60s 2011/10/16 19:36:29 2011/10/16 19:46:29 ? ? ? ? ?2017? 2011/10/16 19:26:29 2011/10/16 19:36:29 ? ? ? ? ?1416 2011/10/16 19:16:29 2011/10/16 19:26:29 ? ? ? ? ?2018 2011/10/16 19:06:29 2011/10/16 19:16:29 ? ? ? ? ?1418 2011/10/16 18:56:29 2011/10/16 19:06:29 ? ? ? ? ?2022 2011/10/16 18:46:29 2011/10/16 18:56:29 ? ? ? ? ?1421 2011/10/16 18:36:29 2011/10/16 18:46:29 ? ? ? ? ?2026 2011/10/16 18:26:29 2011/10/16 18:36:29 ? ? ? ? ?1425 2011/10/16 18:16:29 2011/10/16 18:26:29 ? ? ? ? ?2028 。 。 。 464 rows selected. every coin has two sides,UNDO自動(dòng)優(yōu)化功能能夠最大限度的使用undo表空間,滿足大部分的sql執(zhí)行，但是也帶來(lái)一個(gè)問(wèn)題：很多事務(wù)執(zhí)行完畢之后，發(fā)現(xiàn)UNDO表空間會(huì)在很長(zhǎng)時(shí)間都一直保持著使用率是接近100%的狀態(tài)，active 狀態(tài)的很少。 這種接近狀態(tài)還無(wú)法手工的收縮，甚至于重啟數(shù)據(jù)庫(kù)實(shí)例也無(wú)法緩解，而此時(shí)常常會(huì)收到undo表空間的監(jiān)控報(bào)警。 可以通過(guò)修改隱式參數(shù)” _UNDO_AUTOTUNE”為FALSE關(guān)閉該自動(dòng)調(diào)優(yōu)特性。以上設(shè)定生效后，V$UNDOSTAT視圖上TUNED_UNDORETENTION列不再更新，且撤銷信息最短保留時(shí)間固定為參數(shù)UNDO_RETENTION的設(shè)定值。該參數(shù)可以不用重啟數(shù)據(jù)庫(kù)而動(dòng)態(tài)設(shè)置生效。 禁用UNDO自動(dòng)優(yōu)化之后，Oracle不再的每十分鐘記錄一次當(dāng)前UNDO使用情況了，在動(dòng)態(tài)視圖V$UNDOSTAT中也只保留禁止undo自動(dòng)調(diào)優(yōu)之前的數(shù)據(jù)： YANG@yangdb-rac3> conn /as sysdba Connected. SYS@yangdb-rac3> alter system set "_undo_autotune"=false; System altered. YANG@yangdb-rac3>conn yang/yang Connected. YANG@yangdb-rac3>--10分鐘之后 YANG@yangdb-rac3>SELECT count(1) FROM v$undostat; COUNT(1) ---------- 464 --還是之前的個(gè)數(shù) YANG@yangdb-rac3>SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum =1; BEGIN_TIME ? ? ? ? ?END_TIME ? ? ? ? ? ?TUNED_UNDORETENTION ------------------- ------------------- ------------------- 2011/10/16 19:56:29 2011/10/16 20:08:11 ? ? ? ? ? ? ? ?1592? --- YANG@yangdb-rac3> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum<2; BEGIN_TIME ? ? ? ? ?END_TIME ? ? ? ? ? ?TUNED_UNDORETENTION ------------------- ------------------- ------------------- 2011/10/16 19:56:29 2011/10/16 21:08:53 ? ? ? ? ? ? ? ?1592 上面兩個(gè)是在不同時(shí)間進(jìn)行的查詢， v$undostat的記錄不足每十分鐘進(jìn)行一次統(tǒng)計(jì)。 再次做一個(gè)實(shí)驗(yàn)：這次事務(wù)提交一分鐘之后，undo段的狀態(tài)變?yōu)镋XPIRED YANG@yangdb-rac3> UPDATE bind set status ='INVALID' where status='VALID'; 72747 rows updated. YANG@yangdb-rac3> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ; Session altered. YANG@yangdb-rac3> select sysdate from dual; SYSDATE ------------------- 2011/10/16 20:13:41 YANG@yangdb-rac3> commit; Commit complete. --提交時(shí)立即做查詢： YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status 2 ? ? from dba_undo_extents 3 ? ? where segment_name='_SYSSMU3_2097677531$' or status='ACTIVE';
SEGMENT_NAME ? ? ? ? TABLESPACE_NAME ?EXTENT_ID STATUS -------------------- --------------- ---------- --------- _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 0 ACTIVE ?--活動(dòng)狀態(tài) _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 1 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 2 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 3 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 4 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 5 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 6 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 7 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 8 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 9 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ?10 ACTIVE _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ?11 ACTIVE
16 rows selected YANG@yangdb-rac3> SELECT count(1) FROM v$undostat; COUNT(1) ---------- 464 --過(guò)1分鐘多一些時(shí)間，再次查看undo回滾段的狀態(tài)： YANG@yangdb-rac3> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum =1; BEGIN_TIME ? ? ? ? ?END_TIME ? ? ? ? ? ?TUNED_UNDORETENTION ------------------- ------------------- ------------------- 2011/10/16 19:56:29 2011/10/16 20:14:50 ? ? ? ? ? ? ? ?1592
YANG@yangdb-rac3> select segment_name ,tablespace_name,extent_id,status 2 ? ? from dba_undo_extents 3 ? ? where segment_name='_SYSSMU9_1424341975$' or status='ACTIVE';
SEGMENT_NAME ? ? ? ? TABLESPACE_NAME ?EXTENT_ID STATUS -------------------- --------------- ---------- --------- _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 0 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 1 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 2 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 3 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 4 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 5 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 6 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 7 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 8 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ? 9 EXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ?10 UNEXPIRED _SYSSMU9_1424341975$ UNDOTBS1 ? ? ? ? ? ? ? ?11 EXPIRED 12 rows selected. 可見(jiàn)，修改隱式參數(shù)” _UNDO_AUTOTUNE”為FALSE關(guān)閉該自動(dòng)調(diào)優(yōu)特性，可以解決表空間的使用率總是100%的問(wèn)題。 附上DBA_UNDO_EXTENTS，STATUS的值及其意義： ACTIVE ? ?活動(dòng)狀態(tài)，說(shuō)明當(dāng)前這個(gè)數(shù)據(jù)區(qū)被某個(gè)正在進(jìn)行的事務(wù)使用。 EXPIRED ? 已過(guò)期，說(shuō)明已分配的數(shù)據(jù)區(qū)已經(jīng)完成了它的使命，隨時(shí)可以被分配給其它新的事務(wù)使用。 UNEXPIRED 未過(guò)期，說(shuō)明分配的數(shù)據(jù)區(qū)已經(jīng)不屬于任何的活動(dòng)事務(wù)，但是由于UNDO RETENTION設(shè)置的需要，一般情況下不會(huì)被回收重用。 相關(guān)文章： FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) [ID 461480.1] Ora-30036 On Undo Tablespace After Upgrade To 10.2.0.4 [ID 856344.1] 《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的【Oracle】undo 自动调优的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。