How to find Max, Min and Avg redo generation for a database
I am trying to find out maximum, minimal and average redo a database generate per day. This information, along with knowledge of
specific database will be helpful for me to determine of the archive log directory space allocation is sufficient for our database activity.
I just want to ensure this query is correct or not.
select upper(B.HOST_NAME) HOST_NAME, upper(B.INSTANCE_NAME) INSTANCE_NAME, A.* FROM (
select max(redo_in_gb) MAX_REDO_IN_GB,min(redo_in_gb) MIN_REDO_IN_GB,avg(redo_in_gb) AVG_REDO_IN_GB from (
select sum(blocks * block_size)/(1024*1024*1024) REDO_IN_GB ,trunc(first_time) as "RUN_DATE"
from v$archived_log where trunc(first_time) > (select trunc(resetlogs_time) from v$database_incarnation where status = 'CURRENT')
group by trunc(first_time))) A, V$INSTANCE B
Thank You
Giridhar