marți, 11 noiembrie 2014

Need a lot of undo capabilities? You have to set this...

When you experience the "error ORA-01555: snapshot too old", i am sure you wish to have had larger undo provisioned space.

sql>  show parameter undo_retention

undo_retention                       integer     300000

Keep in mind the displayed value is measured in seconds. The first thought will be to raise ad infinitum the value of the above parameter. But this is not posible, because you need a lot of space, and the database storage is not endless. Anyway, the database engine will not honour any request of keeping undo information beyond of its capabilities. Will erase the oldest undo info and will continue to keep in shape the data. 
So, you need to set properly the undo_retention parameter. Take the following script and use it to obtain the optimal value:

select (SELECT SUM(a.bytes)
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND = c.tablespace_name
 AND a.ts# = b.ts#)

( SELECT TO_NUMBER(value)   FROM v$parameter  WHERE name = 'db_block_size') -
(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)

)   FROM v$undostat)) 
"undo_retention optimal value" 
from dual;

Niciun comentariu:

Trimiteți un comentariu