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!
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)