Wednesday, October 16, 2013

ORA-1555 Snapshot Too old while a running job or query .

If we are getting this error frequently we need to think about the size of  undo tablespace and undo retention  time. Below query helped me to take optimum size of
undo tablespace and undo retention time.

                                                                        ACTUAL UNDO SIZE
OPTIMAL UNDO RETENTION =  ____________________________________
                                                       DB_BLOCK_SIZE*UNDO_BLOCK_PER_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';



                     
OPTIMAL UNDO SIZE      = UNDO RETENTION*DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC


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';
 
 
 
  Note:This above estimation undo size and retention are depends upon the calculations from v$undostat during the previous run. Please impliment this estimation carefully
  as per your requirment and resource avaialability.


Thanks,
Jyothish


No comments:

Post a Comment