undo_retention设置以及优化

来源：互联网发布：nginx 代理静态文件编辑：程序博客网时间：2024/06/06 07:24

检查undo的管理方式
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 14400
undo_tablespace string UNDOTBS1
检查数据库的undo表空间是否自动扩展
select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE,STATUS,MAXBYTES,USER_BYTES from dba_data_files where tablespace_name like '%UNDO%';
FILE_NAME TABLESPACE_NAME AUT STATUS MAXBYTES USER_BYTES
----------------------------------------------- -------------------- --- --------- ---------- ----------
+DATA/cytestdb/datafile/undotbs1.258.843308485 UNDOTBS1 YES AVAILABLE 3.4360E+10 2227175424
+DATA/cytestdb/datafile/undotbs2.273.843308649 UNDOTBS2 YES AVAILABLE 3.4360E+10 2200961024

针对自动拓展的undo表空间设置undo_retention是有效的，在undo_retention的范围内，undo表空间中的数据将尽量不被重新覆盖，仅仅是尽量，不是完全。但是打开了自动拓展之后，有可能在undo_retention时间段内扩展到很大，造成隐患。
针对没有开自动拓展的undo表空间，设置了undo_retention之后，在oracle 10g之后会忽略掉该设置，而是根据undo表空间的大小和使用率来自动调整undo信息的保留时间。
在这种数据库的自动管理方式下，会造成大量的udno extent处于unexpired状态，从而使undo表空间的使用率可能一直是100%。要想屏蔽掉数据库的自我管理：
1）、设置隐含参数_smu_debug_mode=33554432。这样设置后，在确定哪些块可以被覆盖时，时间依据max(undo_retention,v$undostat.mqxquerylen+300s)
2）、设置隐含参数_undo_autotune=false ，Oracle将会直接使用undo_retention设置的阈值作为回滚段信息的保留时间。
set linesize 132
column name format a30
column value format a25
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%_&par%'
order by
translate(x.ksppinm, ' _', ' ')
/
Enter value for par: undo_autotune
old 14: x.ksppinm like '%_&par%'
new 14: x.ksppinm like '%_undo_autotune%'

NAME VALUE ISDEFAULT ISMOD ISADJ
------------------------------ ------------------------- --------- ---------- -----
_undo_autotune TRUE TRUE FALSE FALSE

SQL> /
Enter value for par: smu_debug_mode
old 14: x.ksppinm like '%_&par%'
new 14: x.ksppinm like '%_smu_debug_mode%'

NAME VALUE ISDEFAULT ISMOD ISADJ
------------------------------ ------------------------- --------- ---------- -----
_smu_debug_mode 0 TRUE FALSE FALSE

所谓隐含参数的修改，建议直接在pfile中添加，然后生成spfile；当然一般不建议修改隐含参数！

undo_retention的优化：
在生产环境中，尽量减少ORA-1555的出现，应该将数据库的undo_retention调整到一个合适的值
优化的依据：
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

OPTIMAL UNDO RETENTION=ACTUAL_UNDO_SIZE/(DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC)

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!

SQL> SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#; 8

UNDO_SIZE
----------
4430233600

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
8.68333333

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]" FROM v$parameter WHERE name = 'db_block_size';

DB_BLOCK_SIZE [KByte]
---------------------
8192
在磁盘空间紧张的情况下，可以调整undo_retention
查询undo大小，undo_retention实际大小和优化大小：
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
在磁盘空间空闲的范围内，保证undo_retention不变，调整undo_size
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

0 0