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, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)