Tuesday, January 22, 2019

How to know the undo retention and undo recommended size?

/* Advisor for Undo RETENTION*/

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';


/*Advisor for Undo SIZE*/

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';

No comments:

Post a Comment