<추출 쿼리>
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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 ; |
<수행 화면>
