Home » RDBMS Server » Performance Tuning » tuning undo (oracle10.2.0.4 hp-ux ia)
tuning undo [message #420482] Sun, 30 August 2009 13:40 Go to next message
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
Re: tuning undo [message #420483 is a reply to message #420482] Sun, 30 August 2009 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not commit inside loop.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: tuning undo [message #420484 is a reply to message #420482] Sun, 30 August 2009 14:01 Go to previous message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
This is a FAQ & you are supposed to SEARCH before posting

http://www.orafaq.com/forum/?SQ=81eabcb5253a997580beefbccba36e28&t=search&srch="snapshot+too+old"&btn_submit=Search&field=subject&forum_limiter=&search_logic=AND&sort_order=DESC&author=

The session which recorded error is victim (& possibly culprit).
Some session is doing a long running query.
Some session is doing DML & COMMIT against same table(s).
Result is ORA-01555.
visit http://asktom.oracle.com
do keyword search on "ORA-01555"
find & fix session doing DML.
Previous Topic: time consuming query
Next Topic: "Database Time Spent Waiting (%)" are at 100 for event class "Concurrency"
Goto Forum:
  


Current Time: Sat Oct 01 12:39:16 CDT 2016

Total time taken to generate the page: 0.11383 seconds