Optimize Oracle UNDO Parameters
来源:百度文库 编辑:神马文学网 时间:2024/04/29 00:24:20
Overview
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionallytransaction undo information was stored in Rollback Segments until a commit orrollback statement was issued, at which point it was made available for overlaying.
Best of all, automatic undo management allows the DBA to specify how long undoinformation should be retained after commit, preventing "snapshot too old" errorson long running queries.
This is done by setting the UNDO_RETENTION parameter. The default is 900seconds (5 minutes), and you can set this parameter to guarantee that Oracle keepsundo logs for extended periods of time.
Rather than having to define and manage rollback segments, you can simply define anUndo tablespace and let Oracle take care of the rest. Turning on automatic undomanagement is easy. All you need to do is create an undo tablespace and setUNDO_MANAGEMENT = AUTO.
However it is worth to tune the following importantparameters
The size of the UNDO tablespace
The UNDO_RETENTION parameter
Calculate UNDO_RETENTION for given UNDO Tabespace
You can choose to allocate a specific size for the UNDO tablespaceand then set the UNDO_RETENTION parameter to an optimal value according to the UNDOsize and the database activity. If your disk space is limited and you do not want toallocate more space than necessary to the UNDO tablespace, this is the way toproceed. The following query will help you to optimize the UNDO_RETENTIONparameter:
Because these following queries use the V$UNDOSTATstatistics, run the queries only after the database has been running with UNDO for asignificant and representative time!
Actual Undo Size
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#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = ‘db_block_size‘;
DB_BLOCK_SIZE [Byte]
--------------------
4096
Optimal Undo Retention
209‘715‘200 / (3.12166667 * 4‘096) = 16‘401 [Sec]
Using Inline Views, you can do all in one query!
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,
(
SELECTMAX(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‘
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401
Calculate Needed UNDO Size for givenDatabase Activity
If you are not limited by disk space, then it would be better tochoose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocatethe appropriate size to the UNDO tablespace according to the databaseactivity:
Again, all in one query:
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_sizeFROM v$datafile a,v$tablespace b,dba_tablespaces cWHERE c.contents = ‘UNDO‘AND c.status = ‘ONLINE‘AND b.name = c.tablespace_nameAND a.ts# = b.ts#) d,v$parameter e,v$parameter f,(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_secFROM v$undostat) gWHERE e.name = ‘undo_retention‘AND f.name = ‘db_block_size‘/ACTUAL UNDO SIZE [MByte]------------------------200UNDO RETENTION [Sec]--------------------10800NEEDED UNDO SIZE [MByte]------------------------131.695313
The previous query may return a "NEEDED UNDO SIZE" that is lessthan the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You canchoose to resize your UNDO tablespace to a lesser value or increase yourUNDO_RETENTION parameter to use the additional space.
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionallytransaction undo information was stored in Rollback Segments until a commit orrollback statement was issued, at which point it was made available for overlaying.
Best of all, automatic undo management allows the DBA to specify how long undoinformation should be retained after commit, preventing "snapshot too old" errorson long running queries.
This is done by setting the UNDO_RETENTION parameter. The default is 900seconds (5 minutes), and you can set this parameter to guarantee that Oracle keepsundo logs for extended periods of time.
Rather than having to define and manage rollback segments, you can simply define anUndo tablespace and let Oracle take care of the rest. Turning on automatic undomanagement is easy. All you need to do is create an undo tablespace and setUNDO_MANAGEMENT = AUTO.
However it is worth to tune the following importantparameters
The size of the UNDO tablespace
The UNDO_RETENTION parameter
Calculate UNDO_RETENTION for given UNDO Tabespace
You can choose to allocate a specific size for the UNDO tablespaceand then set the UNDO_RETENTION parameter to an optimal value according to the UNDOsize and the database activity. If your disk space is limited and you do not want toallocate more space than necessary to the UNDO tablespace, this is the way toproceed. The following query will help you to optimize the UNDO_RETENTIONparameter:
Because these following queries use the V$UNDOSTATstatistics, run the queries only after the database has been running with UNDO for asignificant and representative time!
Actual Undo Size
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#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = ‘db_block_size‘;
DB_BLOCK_SIZE [Byte]
--------------------
4096
Optimal Undo Retention
209‘715‘200 / (3.12166667 * 4‘096) = 16‘401 [Sec]
Using Inline Views, you can do all in one query!
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,
(
SELECTMAX(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‘
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401
Calculate Needed UNDO Size for givenDatabase Activity
If you are not limited by disk space, then it would be better tochoose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocatethe appropriate size to the UNDO tablespace according to the databaseactivity:
Again, all in one query:
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_sizeFROM v$datafile a,v$tablespace b,dba_tablespaces cWHERE c.contents = ‘UNDO‘AND c.status = ‘ONLINE‘AND b.name = c.tablespace_nameAND a.ts# = b.ts#) d,v$parameter e,v$parameter f,(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_secFROM v$undostat) gWHERE e.name = ‘undo_retention‘AND f.name = ‘db_block_size‘/ACTUAL UNDO SIZE [MByte]------------------------200UNDO RETENTION [Sec]--------------------10800NEEDED UNDO SIZE [MByte]------------------------131.695313
The previous query may return a "NEEDED UNDO SIZE" that is lessthan the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You canchoose to resize your UNDO tablespace to a lesser value or increase yourUNDO_RETENTION parameter to use the additional space.
Optimize Oracle UNDO Parameters
Oracle Undo Redo通俗理解
UNDO还原表空间使用率马上就达到100%了,如何清理?(页 1) - Oracle - ...
grab dredger parameters
undo功能的实现
Cancel(取消)和Undo(撤销)
重建UNDO\TEMP表空间示例
实用的TextBox控件的Undo属性
A simple framework for adding undo/redo suppo...
如何在RichTextBox中实现Undo功能aaa
Oracle技术文章
下一个Oracle?
ORACLE常用命令
Oracle触发器
oracle 常用命令
oracle 注意事项
oracle 常用命令
oracle job
Oracle介绍
oracle认证
Oracle text
oracle字符集
oracle字符集
Oracle调优