Magic number in optimal redo size query in Oracle 9i

0 votes

1 answer

147 views

                          I got this query from this blog post  to determine the optimal online redo log size in pre-Oracle10 databases.

I'm stuck with Oracle 9i for that particular instannce for reasons beyond my pay grade and in this version the view V$INSTANCE_RECOVERY lacks the OPTIMAL_LOGFILE_SIZE column):



    SELECT
          (SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",
          ROUND((20 / AVERAGE_PERIOD) * (SELECT AVG(BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
    FROM 
         (
                    SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD
                    FROM V$ARCHIVED_LOG
                    WHERE FIRST_TIME > SYSDATE - 3
                    AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN
                    '00:00' AND '23:59'
          );

I indented the query to understand in better but I fail to understand why the author uses the magic number 20.

**Can someone tell me why the 20 and whether this query can be otherwise improved?**
                        

Asked by Tulains Córdova (723 rep)

Dec 30, 2016, 01:28 PM
Last activity: Dec 30, 2016, 01:33 PM

Magic number in optimal redo size query in Oracle 9i

Related Questions