Query REDO LOG Switch Count

Published by Patrizia Gardis on 6 March 20196 March 2019

With the article “Query REDO LOG Switch Count,” we can quickly identify the time periods when the database is in full activity.

Furthermore, in case you find numerous messages of “Checkpoint not complete” in the database’s alert log like the following message:

Thu Nov 26 15:16:35 2019
Thread 1 cannot allocate new log, sequence 788067
Checkpoint not complete

Which you can check if the number is high through the following command:

grep "Checkpoint not complete"  alert_nomedb.log |wc -l

Tuning is needed on the REDO LOG because undersizing will surely cause numerous switches, which could lead to significant slowdowns.

I have often significantly improved database performance by identifying the correct size of the REDO LOG based on the database load.

Thanks to the query to see the amount of switches in an hour of the REDO LOG, it is possible to determine if the size of the REDO LOG is correct.

Oracle recommends approximately 1 switch every 15 or 20 minutes, which is 4 or 5 switches per hour.

So, you can adjust accordingly; based on the number of REDO LOG switches, you will see if increasing the size to reduce switches is necessary.

Query REDO LOG Switch Count:

select to_char(first_time,'YYYY-MM-DD HH24'),count(*) from v$log_history group by
to_char(first_time,'YYYY-MM-DD HH24') order by
to_char(first_time,'YYYY-MM-DD HH24') ;

• Query REDO LOG Switch Count
• Query REDO LOG file name and status

To view my guides on CRS Commands, click here!
At this LINK, instead, you’ll find the SQL queries.

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin