CALCULATE AND ESTIMATE OPTIMAL UNDO FOR DATABASE ORACLE
To estimate the optimal undo retention for the sql queries, below query can be used
Optimal undo retention:
Formula:
kIsH@Xhydra<>--OPTIMAL_UNDO = SUM(UNDO_DATAFILE_SIZE) / (DB_BLOCK_SIZE * UNDO_BLOCK_PER_SECOND)
set lines 200 pages 1000
col "OPTIMAL_UNDO_RETENTION" for 9999999999999
col "DEFAULT_UNDO_RETENTION" for a20
col "UNDO_S" for 9999999999999
WITH undo_size as (
select sum(bytes) as "UNDO_S" from dba_data_files where file_name like '%undo%'),
db_block_size as (
select value as "BLOCK_SIZE" from v$parameter where name like '%db_block_size%'),
undo_block_per_second as (
select max(undoblks / ((end_time - begin_time) * 24 * 3600)) as "UNDOBLKPERSEC" from v$undostat)
select (select value from v$parameter where name like '%undo_retention%') as "DEFAULT_UNDO_RETENTION",
round((UNDO_S/1073741824),2) as TOTAL_UNDO_SIZE_GB,
round((UNDO_S / (BLOCK_SIZE * UNDOBLKPERSEC))) as "OPTIMAL_UNDO_RETENTION"
from undo_size,
db_block_size,
undo_block_per_second;
Output:
DEFAULT_UNDO_RETENTI TOTAL_UNDO_SIZE_GB OPTIMAL_UNDO_RETENTION
-------------------- ------------------ ----------------------
900 5.68 1602391
Optimal undo tablespace:
Formula:
kIsH@Xhydra<>--UNDO_TABLESPACE_SIZE = DB_BLOCK_SIZE * UNDO_BLOCK_PER_SECOND * UNDO_RETENTION
set lines 200 pages 1000
col "OPTIMAL_UNDO_RETENTION" for 9999999999999
col "DEFAULT_UNDO_RETENTION" for a20
col "UNDO_S" for 9999999999999
WITH undo_size as (
select sum(bytes) as "UNDO_S" from dba_data_files where file_name like '%undo%'),
db_block_size as (
select value as "BLOCK_SIZE" from v$parameter where name like '%db_block_size%'),
undo_block_per_second as (
select max(undoblks / ((end_time - begin_time) * 24 * 3600)) as "UNDOBLKPERSEC" from v$undostat)
select (select value from v$parameter where name like '%undo_retention%') as "DEFAULT_UNDO_RETENTION",
round((UNDO_S / (BLOCK_SIZE * UNDOBLKPERSEC))) as "OPTIMAL_UNDO_RETENTION",
round((UNDO_S/1073741824),2) as TOTAL_UNDO_SIZE_GB,
round((round((UNDO_S / (BLOCK_SIZE * UNDOBLKPERSEC))) * BLOCK_SIZE * UNDOBLKPERSEC)/1073741824,2) as "OPTIMAL_UNDO_TABLESPACE_GB"
from undo_size,
db_block_size,
undo_block_per_second;
Output:
DEFAULT_UNDO_RETENTI OPTIMAL_UNDO_RETENTION TOTAL_UNDO_SIZE_GB OPTIMAL_UNDO_TABLESPACE_GB
-------------------- ---------------------- ------------------ --------------------------
900 32591 5.68 5.68