Checking Redo Log Switch and Redo Generating by Session

Focus of this article is to check the redo log switch frequency as well as the sessions causing huge redo logs generation. A sudden surge in archived log generation can make DBA look into the issue to find out since when redo log generation has spiked. Following is the query we can easily find everywhere on the internet to find out the redo logs switch frequency. Of course this is not written by me. I do not know who is the original writer of this query therefore I can refer to that great guy or website.

SQL> set lines 200 pages 100 SQL> select to_date(to_char(first_time,'DD-MM-YYYY'),'DD-MM-YYYY') day, to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23", sum(1) "TOTAL_IN_DAY" from v$log_history group by to_date(to_char(first_time,'DD-MM-YYYY'),'DD-MM-YYYY') order by day /

If you have RAC, you can use following version of query. Replace THREAD# and INST_ID values to the instance number for which redo log switches are needed to be found.

SQL> set lines 200 pages 100 SQL> select to_date(to_char(first_time,'DD-MM-YYYY'),'DD-MM-YYYY') day, to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23", sum(1) "TOTAL_IN_DAY" from gv$log_history where thread#=1 and inst_id=1 group by to_date(to_char(first_time,'DD-MM-YYYY'),'DD-MM-YYYY') order by day /

Finding sessions with huge Redo Log Generation

If you see a sudden spike in redo log switches or archived log generation, you would probably like to find out which sessions/process are causing this. Following is the query that can help you finding out the sessions responsible for huge block change and returns you the top session that are responsible doing block changes (block changes cause redo log generation). You can compare the total block changes with reference to their log on time to analyze the redo log generation from individual sessions. The culprit session(s) would be the ones doing a lot of block changes since their log on time. Execute the query frequently (for example every 10 seconds) to monitor the block changes by suspected sessions to confirm if they are the real culprit.

SQL> set lines 200 pages 80 SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS'; SQL> SELECT i.inst_id,s.logon_time,s.sid, s.serial#, s.username, s.program, i.block_changes FROM gv$session s, gv$sess_io i WHERE s.sid = i.sid and s.inst_id=i.inst_id ORDER BY 7 ;