| tuning undo [message #420482] |
Sun, 30 August 2009 13:40  |
suzyque54
Messages: 14
Registered: December 2006
|
Junior Member |
|
|
I recently received ORA-ORA-1555 snapshot too old error. I have plenty of space in the undo tablespace. My question is should I increase the undo_retention parameter? Base on one query I ran it shows the optimal undo retention should be set 19577(over 5.5 hours), this seems very high. Below are the queries I ran and my results. Any advice would be appreciated. Thanks!
1. UNDO PARAMETERS SET
sql> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
2. NEEDED UNDO SIZE [MByte)
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'
/
---------------------------------------------------------------
ACTUAL UNDO SIZE [MB) UNDO RETENTION [Sec] NEEDED UNDO SIZE (MB]
--------------------- -------------------- -------------------
36800 900 1691.78906
3. OPTIMAL UNDO RETENTION [Sec]
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'
/
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ ---------------------- ----------------
36800 900 19577
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
