반응형
Oracle AWR, ASH, ADDM Report 출력 방법
|
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
--#######################################################
-- 스냅샷 구간 검색
--
-- 검색일자 조건으로 SNAP_ID 추출
--
--#######################################################
SELECT MIN(SNAP_ID) AS MIN_SNAP
, MAX(SNAP_ID) AS MAX_SNAP
FROM DBA_HIST_SNAPSHOT
WHERE 1=1
AND INSTANCE_NUMBER = 1
AND TO_DATE('2021-12-29 14:29','YYYY-MM-DD HH24:MI') <= END_INTERVAL_TIME
AND TO_DATE('2021-12-29 14:45','YYYY-MM-DD HH24:MI') >= BEGIN_INTERVAL_TIME
;
--#######################################################
-- AWR 정보
--#######################################################
SQL> @?/rdbms/admin/awrinfo.sql
--#######################################################
-- AWR 보고서
--
-- Automatic Workload Repository
-- DB 성능통계 자료 자동수집 기능
--
--#######################################################
--(1)
SQL> @?/rdbms/admin/awrrpt.sql
--(2)
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(2366539342 --> dbid
,2 --> inst_num
,100752 --> start snap_id
,100757 ) --> end snap_id
);
--#######################################################
-- AWR 구간 비교 보고서
--#######################################################
--(1)
SQL> @?/rdbms/admin/awrddrpt.sql
--(2)
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(3667654331 --> source_dbid
,1 --> source_inst_num
,103462 --> source_start_snap_id
,103486 --> source_end_snap_id
,3667654331 --> target_dbid
,1 --> target_inst_num
,105574 --> target_start_snap_id
,105598 ) --> target_end_snap_id
);
--#######################################################
-- AWR SQL 보고서
--#######################################################
--(1)
SQL> @?/rdbms/admin/awrsqrpt.sql
--(2)
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML(2366539342 --> dbid
,1 --> inst_num
,40060 --> start snap_id
,40061 --> end snap_id
,'050d17w64mwfs') --> sql_id
);
--#######################################################
-- ASH 보고서
--
-- Active Session History
-- 세션활동에 대한 세부 정보 제공(단기 성능문제 식별에 도움)
--
--#######################################################
--(1)
SQL> @?/rdbms/admin/ashrpt.sql
--(2)
alter session set nls_language='AMERICAN';
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(2366539342 --> dbid
,1 --> inst_num
,to_date('2021-12-29 09:00','YYYY-MM-DD HH24:MI') --> start time
,to_date('2021-12-29 09:45','YYYY-MM-DD HH24:MI')) --> end time
);
--(3)
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(2366539342 --> dbid
,1 --> inst_num
,to_date('2021-12-29 01:00:00','YYYY-MM-DD HH24:MI:SS') --> start time
,to_date('2021-12-29 07:00:00','YYYY-MM-DD HH24:MI:SS') --> end time
,0 --> options
,5 --> slot_width (5:5초 단위)
,null --> sid
,null--'14yr0g4a7zwn5' --> sql_id
,null --> wait_class
,null --> service_hash
,null --> module
,null --> action
,null --> client_id
,null --> plsql_entry
,0 --> data_src (1:memory, 2:disk, 0:both)
,null ) --> container (12c)
);
--#######################################################
-- ADDM 보고서
--
-- Automatic Database Diagnostic Monitor
-- AWR 데이터를 분석하여 잠재적인 성능 병목 현상을 식별하고 그에 대한 원인분석과 해결을 위한 권장사항 제공.
--
--#######################################################
--(1)
SQL> @?/rdbms/admin/addmrpt.sql
|
cs |
반응형
'[DATABASE] ORACLE > AWR' 카테고리의 다른 글
| 날짜별(구간별) Wait Event 발생 현황 비교 (0) | 2022.02.16 |
|---|---|
| 날짜별(구간별) DB 성능 비교 (0) | 2022.02.16 |
| AWR Sysmetric/Sysstat/System_event (0) | 2021.12.30 |
| AWR Top 3 Wait event (and CPU) (0) | 2021.12.30 |
| AWR 스냅샷 관리 (0) | 2021.12.29 |
댓글