How to Optimize Oracle UNDO Parameters

Hi,

In this article, We will discuss optimizing ORACLE UNDO parameters. Transaction undo information is stored in rollback segments until a commit or rollback statement was executed. Sometimes we get an error “ORA-01555:Snapshot Too Old” on long running queries.  This error occures when we set UNDO parameters incorrectly. With UNDO_RETENTION parameter, automatic undo management allows to specify how long undo information must be retained after commit. The default value of this parameter is 900s. (15 min). If you set   UNDO_MANAGEMENT  parameter to AUTO and create a UNDO tablespace then ORACLE will manage it.

So how should UNDO_RETENTION up period? The answer to this question is proportional to transcend size UNDO Tablespace. Criticality of data and according to the disk, you can give an area for the UNDO tablespace. Calculating the optimum size of the amount of time you should set this parameter UNDO_RETENTION.

Now do a little mathematical calculation. First going to check our Undo tablespace size with the following query;

SELECT SUM(a.bytes) as 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#;

Then find the number of undo block per second;

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) as UNDO_BLOCK_PER_SECOND
  FROM v$undostat;

Let’s find the database block size;

SELECT TO_NUMBER(value) as DB_BLOCK_SIZE
 FROM v$parameter
WHERE name = 'db_block_size';

Now, according to the result obtained from the three that we will calculate the optimum time for UNDO_RETENTION parameter. Will use the formula as follows;

UNDO RETENTION TIME=(UNDO SIZE)/(DB BLOCK SIZE × UNDO BLOCK PER SECOND)

Put the values into the formula above to calculate undo retention time.

Now let’s look at the event reversal. If you have enough disk space and critical database transactions. For example, you set UNDO retention time is 1 hour (3600 sec) to keep our committed undo data. In this situation, you must allocate the appropriate UNDO tablespace size according to undo retention time. In this case, formula;

UNDO SIZE=UNDO RETENTION TIME × DB BLOCK SIZE × UNDO BLOCK PER SECOND

Finally, we solve them all with a single query to find optimum UNDO_RETENTION period;

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
       SUBSTR(e.value,1,25) as UNDO_RETENTION,
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) as OPTIMUM_UNDO_RETENTION
  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';

To find UNDO tablespace size according to the UNDO RETENTION period;

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
       SUBSTR(e.value,1,25) as UNDO_RETENTION,
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      as NEEDED_UNDO_SIZE"
  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';

You can find ORA-1555 error count that you’re wondering in the last few from the moment our database is opened with the following query;

  select '"ORA-01555 (Snapshot too old)" hata sayısı: '
  || sum(ssolderrcnt)
  from v$undostat;

Wishing to be healthy databases with optimum parameters 🙂

Talip Hakan ÖZTÜRK

Advertisement