[AWR] 시스템 성능이 느려질 때 … 시스템 부하 상황 추출 쿼리

haisins

7년 ago

<추출 쿼리>

SELECT	i.db_name, i.instance_number, i.host_name
,	s.snap_id, s.begin_interval_time, s.secs
,	e1.wait_class, e1.event_name
,	e2.total_Waits - e1.total_Waits as total_waits
,	e2.total_timeouts - e1.total_timeouts as total_timeouts
,	e2.time_waited_micro - e1.time_waited_micro as time_waited_micro
,	(e2.time_waited_micro - e1.time_waited_micro)/1000000 as time_waited
,	(e2.time_waited_micro - e1.time_waited_micro)/1000000/s.secs as time_waited_per_second
FROM	(
	SELECT	s.dbid, s.instance_number, s.startup_time
	, 	snap_id, begin_interval_time
	,	LEAD(snap_id,1) OVER (
			PARTITION BY dbid, instance_number, startup_time 
			ORDER BY begin_interval_time) as next_snap_id
	,	LEAD(begin_interval_time,1) OVER (
			PARTITION BY dbid, instance_number 
			ORDER BY begin_interval_time) as next_begin_interval_time
	,	end_interval_time
	,	86400*(TO_NUMBER(TO_CHAR(end_interval_time,'J'))
	       -TO_NUMBER(TO_CHAR(begin_interval_time,'J')))
	+	3600*(EXTRACT(HOUR FROM end_interval_time)-EXTRACT(HOUR FROM begin_interval_time))
	+	60*(EXTRACT(MINUTE FROM end_interval_time)-EXTRACT(MINUTE FROM begin_interval_time))
	+	EXTRACT(SECOND FROM end_interval_time)-EXTRACT(SECOND FROM begin_interval_time) secs
	FROM	dba_hist_snapshot s
	WHERE  s.begin_interval_time > TRUNC(SYSDATE) - 7
	) s
INNER JOIN dba_hist_database_instance i
	ON i.dbid = s.dbid 
	AND i.instance_number = s.instance_number
	AND i.startup_time = s.startup_time	
INNER JOIN dba_hist_system_event e1 --v$system_event
	ON e1.snap_id = s.snap_id
	AND e1.dbid = s.dbid
	AND e1.instance_number = s.instance_number
INNER JOIN dba_hist_system_event e2
	ON e2.snap_id = s.next_snap_id
	AND e2.dbid = s.dbid
	AND e2.instance_number = s.instance_number
WHERE s.next_snap_id IS NOT NULL
AND  e1.event_id = e2.event_id
AND  e1.wait_class_id = e2.wait_class_id
AND	( e2.total_Waits > e1.total_Waits
	OR e2.total_timeouts > e1.total_timeouts
	OR e2.time_waited_micro > e1.time_waited_micro)
ORDER BY 1,2,3,4 ;

SELECT i.db_name, i.instance_number, i.host_name

, s.snap_id, s.begin_interval_time, s.secs

, e1.wait_class, e1.event_name

, e2.total_Waits - e1.total_Waits as total_waits

, e2.total_timeouts - e1.total_timeouts as total_timeouts

, e2.time_waited_micro - e1.time_waited_micro as time_waited_micro

, (e2.time_waited_micro - e1.time_waited_micro)/1000000 as time_waited

, (e2.time_waited_micro - e1.time_waited_micro)/1000000/s.secs as time_waited_per_second

FROM (

SELECT s.dbid, s.instance_number, s.startup_time

, snap_id, begin_interval_time

, LEAD(snap_id,1) OVER (

PARTITION BY dbid, instance_number, startup_time

ORDER BY begin_interval_time) as next_snap_id

, LEAD(begin_interval_time,1) OVER (

PARTITION BY dbid, instance_number

ORDER BY begin_interval_time) as next_begin_interval_time

, end_interval_time

, 86400*(TO_NUMBER(TO_CHAR(end_interval_time,'J'))

-TO_NUMBER(TO_CHAR(begin_interval_time,'J')))

+ 3600*(EXTRACT(HOUR FROM end_interval_time)-EXTRACT(HOUR FROM begin_interval_time))

+ 60*(EXTRACT(MINUTE FROM end_interval_time)-EXTRACT(MINUTE FROM begin_interval_time))

+ EXTRACT(SECOND FROM end_interval_time)-EXTRACT(SECOND FROM begin_interval_time) secs

FROM dba_hist_snapshot s

WHERE s.begin_interval_time > TRUNC(SYSDATE) - 7

) s

INNER JOIN dba_hist_database_instance i

ON i.dbid = s.dbid

AND i.instance_number = s.instance_number

AND i.startup_time = s.startup_time

INNER JOIN dba_hist_system_event e1 --v$system_event

ON e1.snap_id = s.snap_id

AND e1.dbid = s.dbid

AND e1.instance_number = s.instance_number

INNER JOIN dba_hist_system_event e2

ON e2.snap_id = s.next_snap_id

AND e2.dbid = s.dbid

AND e2.instance_number = s.instance_number

WHERE s.next_snap_id IS NOT NULL

AND e1.event_id = e2.event_id

AND e1.wait_class_id = e2.wait_class_id

AND ( e2.total_Waits > e1.total_Waits

OR e2.total_timeouts > e1.total_timeouts

OR e2.time_waited_micro > e1.time_waited_micro)

ORDER BY 1,2,3,4 ;

<수행 화면>