반응형
|
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
-- ##########################
-- AWR_sysmetric
--
--
-- ##########################
SELECT CAST(MIN(sn.begin_interval_time) OVER (PARTITION BY sn.dbid,sn.snap_id) AS DATE) snap_time
, sn.instance_number
, ss.metric_name||' - '||ss.metric_unit metric_name_unit
, ss.maxval
, ss.average
, ss.standard_deviation
FROM dba_hist_sysmetric_summary ss, dba_hist_snapshot sn
WHERE sn.snap_id = ss.snap_id
AND sn.dbid = ss.dbid
AND sn.instance_number = ss.instance_number
AND sn.instance_number = 1
AND sn.begin_interval_time BETWEEN SYSDATE -1 AND SYSDATE
ORDER BY sn.snap_id
;
-- ##########################
-- AWR sysstat
--
--
-- ##########################
SELECT CAST(MIN(sn.begin_interval_time) OVER (PARTITION BY sn.dbid,sn.snap_id) AS DATE) snap_time
, ss.dbid
, sn.instance_number
, ss.stat_name
, ss.value
, ss.value - LAG(ss.value) OVER (PARTITION BY ss.dbid,ss.instance_number,ss.stat_id ORDER BY sn.snap_id nulls FIRST) Delta_value
, EXTRACT(hour FROM end_interval_time-begin_interval_time)*3600
- EXTRACT(hour FROM sn.snap_timezone - LAG(sn.snap_timezone) OVER (PARTITION BY ss.dbid,ss.instance_number,ss.stat_id ORDER BY sn.snap_id nulls FIRST))*3600
+ EXTRACT(minute FROM end_interval_time-begin_interval_time)*60
+ EXTRACT(second FROM end_interval_time-begin_interval_time) DeltaT_sec
, ROUND((ss.value - LAG(ss.value) OVER (PARTITION BY ss.dbid,ss.instance_number,ss.stat_id ORDER BY sn.snap_id nulls FIRST)) /
( EXTRACT(hour FROM end_interval_time-begin_interval_time)*3600
- EXTRACT(hour FROM sn.snap_timezone - LAG(sn.snap_timezone) OVER (PARTITION BY ss.dbid,ss.instance_number,ss.stat_id ORDER BY sn.snap_id nulls FIRST))*3600
+ EXTRACT(minute FROM end_interval_time-begin_interval_time)*60
+ EXTRACT(second FROM end_interval_time-begin_interval_time)),2) Rate_value
FROM dba_hist_sysstat ss, dba_hist_snapshot sn
WHERE sn.snap_id = ss.snap_id
AND sn.dbid = ss.dbid
AND sn.instance_number = ss.instance_number
AND sn.instance_number = 1
AND sn.begin_interval_time BETWEEN SYSDATE -1 AND SYSDATE
--AND ss.stat_name = 'gc current blocks served'
ORDER BY sn.snap_id
;
-- ##########################
-- AWR system_event
--
--
-- ##########################
SELECT CAST(MIN(sn.begin_interval_time) OVER (PARTITION BY sn.dbid,sn.snap_id) AS DATE) snap_time
, sn.instance_number
, ss.event_name
, ROUND((ss.total_waits - lag(ss.total_waits) OVER (PARTITION BY ss.dbid,ss.instance_number,ss.event_id ORDER BY sn.snap_id nulls FIRST)) /
( EXTRACT(hour FROM end_interval_time-begin_interval_time)*3600
- EXTRACT(hour FROM sn.snap_timezone - lag(sn.snap_timezone) OVER (PARTITION BY ss.dbid,ss.instance_number,ss.event_id ORDER BY sn.snap_id nulls FIRST))*3600
+ EXTRACT(minute FROM end_interval_time-begin_interval_time)*60
+ EXTRACT(second FROM end_interval_time-begin_interval_time)),2) Waits_per_sec
, ROUND((ss.time_waited_micro - lag(ss.time_waited_micro) OVER (PARTITION BY ss.dbid,ss.instance_number,ss.event_id ORDER BY sn.snap_id nulls FIRST)) /
( EXTRACT(hour FROM end_interval_time-begin_interval_time)*3600
- EXTRACT(hour FROM sn.snap_timezone - lag(sn.snap_timezone) OVER (PARTITION BY ss.dbid,ss.instance_number,ss.event_id ORDER BY sn.snap_id nulls FIRST))*3600
+ EXTRACT(minute FROM end_interval_time-begin_interval_time)*60
+ EXTRACT(second FROM end_interval_time-begin_interval_time)),2) Rate_timewaited
FROM dba_hist_system_event ss, dba_hist_snapshot sn
WHERE sn.snap_id = ss.snap_id
AND sn.dbid = ss.dbid
AND sn.instance_number = ss.instance_number
AND sn.instance_number = 1
AND sn.begin_interval_time BETWEEN SYSDATE -1 AND SYSDATE
ORDER BY sn.snap_id
;
|
cs |
반응형
'[DATABASE] ORACLE > AWR' 카테고리의 다른 글
| 날짜별(구간별) Wait Event 발생 현황 비교 (0) | 2022.02.16 |
|---|---|
| 날짜별(구간별) DB 성능 비교 (0) | 2022.02.16 |
| AWR Top 3 Wait event (and CPU) (0) | 2021.12.30 |
| AWR/ASH/ADDM Report (0) | 2021.12.29 |
| AWR 스냅샷 관리 (0) | 2021.12.29 |
댓글