How Many Redo Log Switches Per Hour

dbtut

6 years ago

If Redo Log Switch count is too much, you can increase the redo log sizes if you need.

However, the larger the Redo Log, the longer the recovery time.

With the help of the following script, you can see the number of redo log switches on a hourly basis for a database.

SELECT TRUNC (FIRST_TIME) DAY,
COUNT (*) NB_SWITCHS,
TRUNC (COUNT (*) * LOG_SIZE / 1024) TOTAL_SIZE_KB,
TO_CHAR (COUNT (*) / 24, '9999.9') AVG_SWITCHS_PER_HOUR
FROM V$LOGHIST, (SELECT AVG (BYTES) LOG_SIZE FROM V$LOG)
GROUP BY TRUNC (FIRST_TIME), LOG_SIZE;

SELECT TRUNC (FIRST_TIME) DAY,

COUNT (*) NB_SWITCHS,

TRUNC (COUNT (*) * LOG_SIZE / 1024) TOTAL_SIZE_KB,

TO_CHAR (COUNT (*) / 24, '9999.9') AVG_SWITCHS_PER_HOUR

FROM V$LOGHIST, (SELECT AVG (BYTES) LOG_SIZE FROM V$LOG)

GROUP BY TRUNC (FIRST_TIME), LOG_SIZE;

If you want to learn more about redo log you should read below articles

“Oracle Redo Log And Archive Log“.

“How To Change the Size of Redo Log Files”