檢查及設定合理的undo表空間
UNDO是用於實現併發控制以及構建一致性讀，也就是在資料變更之前產生前映象，以保證使用者能夠回滾或撤銷對所作的修改。是資料庫完整性的重要組成部分。因此合理的設計及配置undo以及使用undo都將對資料庫有較大的影響。通常情況下，對於大規模資料的刪除，更新操作，我們建議使用分批刪除分次提交以減少對undo的佔用和衝擊。那麼對於undo的大小到底應該設定多大？是啟用自動擴充套件還是關閉自動擴充套件？這個問題仁者見仁，智者見智，見下文。
1、當前資料庫環境及undo配置資訊
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
|
[sql]
sys@SYTST> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
sys@SYTST> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS
sys@SYTST> select tablespace_name,file_name,AUTOEXTENSIBLE,bytes/1024/1024 size_mb
2 from dba_data_files where tablespace_name like '%UNDO%';
TABLESPACE_NAME FILE_NAME AUT SIZE_MB
------------------------------ ------------------------------------------------------- --- ----------
UNDOTBS /u02/database/SYTST/undo/undoSYTST1.dbf NO 20
--建立演示表t
sys@SYTST> CREATE TABLE t
2 AS
3 SELECT rownum AS id,
4 round(5678+dbms_random.normal*1234) AS n1,
5 mod(255+trunc(dbms_random.normal*1000),255) AS n2,
6 dbms_random.string('p',255) AS pad
7 FROM dual
8 CONNECT BY level <= 100000
9 ORDER BY dbms_random.value;
Table created.
--直接用指令碼得到undo的資訊及建議值
sys@SYTST> @chk_advs_undo
Session altered.
- Undo Analysis started at : 24/10/2013 14:39:58 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 17/10/2013 14:39:58
End Time : 24/10/2013 14:39:58
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS
Current undo tablespace size (datafile size now) : 20M
Current undo tablespace size (consider autoextend) : 20M
AUTOEXTEND for undo tablespace is : OFF
Current undo retention : 900
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:The undo tablespace is OK. --->當前的undo配置合理
Undo Space Recommendation
-------------------------
Allocated undo space is sufficient for the current workload.
Retention Recommendation
------------------------
The best possible retention with current configuration is : 5996 Seconds
The longest running query ran for : 52 Seconds
The undo retention required to avoid errors is : 52 Seconds
PL/SQL procedure successfully completed.
2、模擬undo超出並獲得建議值
[sql]
--先檢視當前已產生的undo
sys@SYTST> @mystat "undo change"
sys@SYTST> set echo off
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 363568
--我們來更新之前建立表t上的pad列
sys@SYTST> update t set pad=dbms_random.string('l',255);
update t set pad=dbms_random.string('l',255)
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS' --->得到了錯誤提示，無法擴充套件undo，因為當前環境undo未啟用自動擴充套件
--Author : Leshami
--Blog : https://blog.csdn.net/leshami
--看看update語句到底產生了多少undo
sys@SYTST> @mystat2
sys@SYTST> set echo off
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
undo change vector size 18817316 18,453,748
--由下可知，上面的update語句已經產生了17M以上的undo,而當前的undo的大小為20M，顯然不夠，所以我們收到了ORA-30036
sys@SYTST> select 18453748/1024/1024 from dual;
18453748/1024/1024
------------------
17.5988655
--再次獲得undo建議值
sys@SYTST> @chk_advs_undo
Session altered.
- Undo Analysis started at : 24/10/2013 14:49:07 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 17/10/2013 14:49:07
End Time : 24/10/2013 14:49:07
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS
Current undo tablespace size (datafile size now) : 20M
Current undo tablespace size (consider autoextend) : 20M
AUTOEXTEND for undo tablespace is : OFF
Current undo retention : 900
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 48 MB
Undo Space Recommendation
-------------------------
Minimum Recommendation : Size undo tablespace to 48 MB
Rationale : Increase undo tablespace size so that long running queries will not fail
Recommended Undo Tablespace Size : 48M --->這裡給出了undo的建議值
Retention Recommendation
------------------------ --->下面是一些和undo相關的建議，如可能的保留時間等等
The best possible retention with current configuration is : 306 Seconds
The longest running query ran for : 52 Seconds
The undo retention required to avoid errors is : 52 Seconds
PL/SQL procedure successfully completed.
3、獲得undo資訊及建議值指令碼
[sql]
--指令碼來自Oracle. 參考Metalink: Doc ID 1579035.1
--以下指令碼適用於Oracle 10g以上版本
SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(6);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
|
4、後記
a、undo究竟要多大，推薦的做法是根據你的業務高峰期得到一個峰值，然後適當高於峰值來啟用自動擴充套件。
b、undo保留時間與所需的空間成正比，保留時間越長，空間開銷越大，不過出現ORA-01555的機率也就越低。
c、在未啟用自動擴充套件的情形下，對於新的事務，UNDO_RETENTION會回收空閒空間，並重新使用，如果啟用自動擴充套件，則該功能被忽略，而是先擴充套件。
d、小的undo表空間(資料檔案)通常情況下對於資料庫及操作影響更小，具有更好的效能以及減少備份所需的開銷等。
轉自：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29487349/viewspace-2152439/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- UNDO表空間空間回收及切換
- undo表空間容量
- undo表空間使用率100%的原因檢視
- 2.6.8 設定UNDO空間管理方法
- Innodb：Undo 表空間巨大
- 更改undo表空間大小
- MySQL InnoDB Undo表空間配置MySql
- 【UNDO】Oracle undo表空間使用率過高，因為一個查詢Oracle
- 表空間集自包含檢查
- 2.5.5 使用自動Undo管理: 建立 Undo 表空間
- ORACLE線上切換undo表空間Oracle
- Oracle切換undo表空間操作步驟Oracle
- MySQL UNDO表空間獨立和截斷MySql
- [20210527]rman與undo表空間備份.txt
- 一次交換空間設定不合理引發的故障
- 表空間利用率及表空間的補充
- undo表空間使用率過高解決
- oracle中undo表空間丟失處理方法Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- MySQL 5.7新特性之線上收縮undo表空間MySql
- Oracle 19c 線上縮減 UNDO 表空間 容量Oracle
- SQLServer的檢查點、redo和undoSQLServer
- Linux檢查Docker映象,容器的磁碟空間LinuxDocker
- 臨時表空間和回滾表空間使用率查詢
- 刪除UNDO表空間並處理ORA-01548問題
- 12C關於CDB、PDB 回滾undo表空間的總結
- 眾創空間，提高辦公設計合理性
- 18_深入解析Oracle undo原理(2)_undo表空間使用率100%問題處理Oracle
- AUTOCAD——工作空間設定
- 查詢表空間使用情況
- 表空間使用量查詢
- 檢視oracle臨時表空間佔用率的檢視Oracle
- 如何檢查Mac磁碟空間，mac磁碟空間其他怎麼清理Mac
- 16、表空間 建立表空間
- 查詢表空間使用情況的指令碼指令碼
- Oracle查詢表空間的每日增長量Oracle
- oracle 臨時表空間的增刪改查Oracle
- oracle表空間使用率查詢Oracle