Saturday, November 26, 2011

Estimate the Optimal UNDO Tablespace size in Oracle

We can size the undo tablespace appropriately either by using automatic extension of the undo tablespace or by using the Undo Advisor for a fixed sized tablespace.If we have decided on a fixed-size undo tablespace, the Undo Advisor can help us to estimate needed capacity.The Undo Advisor relies for its analysis on data collected in the Automatic Workload Repository (AWR). It is therefore important that the AWR have adequate workload statistics available so that the Undo Advisor can make accurate recommendations. 

Below is the script which will estiate the size of the undo tablespace required and also provide the detail when the database face the Number of "ORA-01555 (Snapshot too old)" encountered since the last startup of the instance 

SQL> 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 = 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 = 'undo_retention'
AND = 'db_block_size'
set feedback off
set heading off
set lines 132
  cursor get_undo_stat is
         select d.undo_size/(1024*1024) "C1",
                substr(e.value,1,25)    "C2",
                (to_number(e.value) * to_number(f.value) *
g.undo_block_per_sec) / (1024*1024) "C3",
                round((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec)))             "C4"
           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 = 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 = 'undo_retention'
            and = 'db_block_size';
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) ||
'To optimize UNDO we have two choices :');
dbms_output.put_line('==========================================' || chr(10));
  for rec1 in get_undo_stat loop
Adjust UNDO tablespace size according to UNDO_RETENTION :'
|| chr(10));
      dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||
TO_CHAR(rec1.c1,'999999') || ' MEGS');
      dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION
(' || ltrim(TO_CHAR(rec1.c2/60,'999999'))
 || ' MINUTES)
',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');
      dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :'
|| chr(10));
      dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') ||
' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');
      dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE
(' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS) ',65,'.') || ' : ' ||
TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
  end loop;
select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;

Enjoy   :-) 

No comments: