[Oracle] oracle log switch 횟수 조회

oracle의 pivot을 활용한 log switch 횟수 조회 쿼리 작성 

 

oracle의 v$log_history뷰에서 제공하는 기간으로 시간별 log switch가 몇번 발생 했는지를 조회한다.

 

 

1. 조회쿼리

SELECT 
	day,
	h00, h01, h02, h03, h04, h05, h06, h07, h08, h09, h10, h11, 
	h12, h13, h14, h15, h16, h17, h18, h19, h20, h21, h22, h23,
	h00 + h01 + h02 + h03 + h04 + h05 + h06 + h07 + h08 + h09 + h10 + h11 + 
	h12 + h13 + h14 + h15 + h16 + h17 + h18 + h19 + h20 + h21 + h22 + h23 as total
FROM (
		SELECT 
			to_char(FIRST_TIME, 'yyyy-mm-dd') AS DAY,
			to_char(first_time, 'hh24') AS log_switch_hour
		FROM V$LOG_HISTORY
		WHERE thread# = 1
	)
pivot (count(*) 
		FOR log_switch_hour IN ('00' as h00, 
                            	'01' as h01, 
                            	'02' as h02, 
                            	'03' as h03, 
                            	'04' as h04, 
                            	'05' as h05, 
                            	'06' as h06, 
                            	'07' as h07, 
                            	'08' as h08, 
                            	'09' as h09, 
                            	'10' as h10, 
                            	'11' as h11, 
                            	'12' as h12, 
                            	'13' as h13, 
                            	'14' as h14, 
                            	'15' as h15, 
                            	'16' as h16, 
                            	'17' as h17, 
                            	'18' as h18, 
                            	'19' as h19, 
                            	'20' as h20, 
                            	'21' as h21, 
                            	'22' as h22, 
                            	'23' as h23));

 

 

2. 조회 결과

 

시간당 log switch횟수를 조회하여 현재 redo log size 및 redo log 갯수를 조절할 수 있다.