Redo log switch frequency

I was managing an environment where we needed to investigate the redo log switch frequency in our environment due to some graph spikes visualized using Grafana dashboards. A simple SELECT command using the view V$LOG_HISTORY is required to accomplish this goal. Therefore, below is presented this SQL SELECT command and the output:


SQL>
COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY, TO_CHAR(FIRST_TIME,'HH24') HOUR, COUNT(*) TOTAL_FILES FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ASC;


....

DAY             HOUR TOTAL_FILES
--------------- ---- -----
2023-04-16      19      32
2023-04-16      20      29
2023-04-16      21      24
2023-04-16      22      31
2023-04-16      23      25
2023-04-17      00      24
2023-04-17      01     642
2023-04-17      02      94
2023-04-17      03      26
2023-04-17      04     180
2023-04-17      05      56

DAY             HOUR TOTAL
--------------- ---- -----
2023-04-17      06      24
2023-04-17      07      45
2023-04-17      08      28
2023-04-17      09      33
2023-04-17      10      30
2023-04-17      11       3

2778 rows selected.

I hope this post helps you!

Related posts

Leave a Comment