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表空間(資料檔案)通常情況下對於資料庫及操作影響更小，具有更好的效能以及減少備份所需的開銷等。

轉自：

檢查及設定合理的undo表空間

相關文章