undo表空間使用率

undo表空間是Oracle數據庫非常重要的表空間，它的使用率也是DBA關注的重點，但是在繁忙的生產系統中，很容易看到undo表空間使用率非常高的情況，甚至達到100%。那么，undo表空間大小應該設置多大？undo塊是怎樣進行分配的？undo表空間使用率很高的時候應該怎么處理？本文針對上述問題進行說明。
一、undo表空間大小設置
1、首先根據數據庫和業務的情況，先預設一個undo 表空間的大小；
2、待數據庫運行一段時間后，可以按照如下方法估算下合理的undo表空間大小：
計算公式：UR*(USP*DBS)
UR表示undo_retention參數值
UPS表示每秒產生的undo塊數量
DBS表示數據庫塊大小

SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks / ((end_time - begin_time) * 86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
WHERE name = 'undo_tablespace'));

二、自動管理的undo塊的分配算法
1、如果當前extent中還有空閑塊，在需要空間時會繼續使用本extent中的下一個空閑塊；
2、當前extent使用完后，如果下一個extent是expired，就跳轉到下一個extent的第一個數據塊；
3、如果下一個extent不是expired，就從undo表空間申請空間，如果undo表空間中存在空閑的空間，就分配新的extent加入到undo
segment，然后跳轉到新的undo extent的第一個數據塊；
4、如果undo表空間沒有空閑的空間，就從offine的undo segment中偷取（steal）expired的extent，將offine的undo segment中的
expired的 extent分配給當前的undo segment，并跳轉到新加入的extent的第一個數據塊；
5、如果在offine的undo segment中沒有expired的extent，就從online的undo segment中偷取expired的extent加入到當前undo
segment，并跳轉到新加入的extent的第一個空閑塊；
6、如果online的segment中沒有expired的extent，就擴展undo表空間數據文件（如果開啟了自動擴展），添加新的extent到當前的
undo segment；
7、如果undo表空間數據文件不能擴展，調低10%的retention值，然后偷取現在變為expired的undo extent；
8、從任意一個offine的undo segment中偷取unexpired的extent；
9、嘗試重用當前段中unexpired的extent，如果所有的extent處于currently busy（事務未提交），轉入到第10步；
10、嘗試重用任意一個online的undo segment中的unexpired extent；
11、如果上述所有的步驟都失敗，拋出ORA-30036 unable to extend segment by %s in undo tablespace '%s'。

從以上undo塊的分配算法可以看出，當undo表空間的使用率很高的時候，不一定就要增加undo表空間的大小，因為oracle會重用expired
的extent或者unexpired的extent。即使undo表空間使用率到達100%，數據庫也不一定會報錯。
undo表空間的使用率可以采用下面的方法查詢：

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

但是，如果undo表空間使用率非常高，且遭遇了ORA-01555或者ORA-30036，就需要進行分析。首先要分析undo表空間的大小設置是否合理，可以使用上文中估算undo表空間合理大小的算法進行計算。如果undo表空間已經足夠大，但是還是遭遇ORA-01555或者ORA-30036，就需要從以下幾個方面進行排查：
1、對于ORA-01555需要查看查詢語句的運行時間是不是太長，undo_retention設置是否過小；
2、對于ORA-30036需要排查事務是否過大，undo_retention設置是否過大；
3、對于ORA-30036還需要關注以下信息：

--查看是否有長時間被占用的undo塊
select begin_time,
end_time,
undoblks,
tuned_undoretention,
maxquerylen,
maxqueryid
from v$undostat;
如果有長時間被占用的undo塊，在10g版本中需要關注Bug 5387030
--查詢正在被使用的undo表空間的比例
SELECT
((SELECT (NVL(SUM(bytes),0))
FROM dba_undo_extents
WHERE tablespace_name=''
AND status IN ('ACTIVE','UNEXPIRED')) * 100)/
(SELECT SUM(bytes)
FROM dba_data_files
WHERE tablespace_name='')
"PCT_INUSE"
FROM dual;
--查看分配給undo表空間的extent的狀態
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*)
FROM DBA_UNDO_EXTENTS where tablespace_name = \'UNDOTBS1\' GROUP BY STATUS;
--查詢正在使用undo段的事務及使用的undo表空間的大小
select start_time,
username,
s.MACHINE,
s.OSUSER,
r.name,
ubafil,
ubablk,
t.status,
(used_ublk * p.value) / 1024 blk, --使用undo表空間的大小
used_urec,
s1.SQL_ID,
s1.SQL_TEXT
from v$transaction t, v$rollname r, v$session s, v$parameter p,v$sql s1
where xidusn = usn
and s.saddr = t.ses_addr
and p.name = 'db_block_size'
and s.SADDR=s1.ADDRESS(+)
order by 1;

對于ORA-01555，最優的解決辦法是縮短查詢語句的運行時間，需要對sql進行優化，對于ORA-30036，盡量將大事務進行拆分，分批提交。

新聞名稱：undo表空間使用率
文章鏈接：http://www.xueling.net.cn/article/pggocj.html

老熟女激烈的高潮_日韩一级黄色录像_亚洲1区2区3区视频_精品少妇一区二区三区在线播放_国产欧美日产久久_午夜福利精品导航凹凸

重慶分公司，新征程啟航

undo表空間使用率

其他資訊