/* 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