OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c AWR 비교 리포트 수집 방법
오라클 19c AWR 란 ( https://positivemh.tistory.com/1013 )
AWR 비교 리포트는 2개의 AWR 결과를 비교해서 한 페이지에 보여주는 리포트로
장애나 성능 문제가 발생했을때 시점의 AWR과 정상시점의 AWR을 비교할때 유용하게 사용할 수 있음
(일반 AWR에 비해 수집 항목은 조금 적음)
AWR 비교 리포트(AWR Diff)는 2가지 타입으로 출력할수 있음
'html' HTML format (default)
'text' Text format
html 타입이 기본값이고 text 형식으로도 출력이 가능함
이 게시글에선 AWR 비교 리포트를 수집하는 방법을 설명함
sqlplus 접속
|
1
|
$ sqlplus / as sysdba
|
AWR 리포트 수집
|
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
순서1. AWR 비교 리포트 출력용 sql 실행
SQL> @?/rdbms/admin/awrddrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
순서2. AWR 비교 리포트 출력 타입 입력, 엔터 입력시 기본값인 html 타입으로 선택됨
Enter value for report_type: html (AWR 비교 리포트 출력 타입 입력)
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual
Type Specified: html
old 1: select '&&report_type' report_type_def from dual
new 1: select 'html' report_type_def from dual
old 1: select '&&view_loc' view_loc_def from dual
new 1: select 'AWR_PDB' view_loc_def from dual
Current Instance
~~~~~~~~~~~~~~~~
old 1: select (case when '&view_loc' = 'AWR_PDB'
new 1: select (case when 'AWR_PDB' = 'AWR_PDB'
old 1: select &default_dbid dbid
new 1: select 3209222764 dbid
old 2: , &default_dbid dbid2
new 2: , 3209222764 dbid2
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
3209222764 3209222764 ORACLE19 1 1 oracle19
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 3209222764 1 ORACLE19 oracle19 oel8
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 3209222764 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
순서3. 비교대상 첫번째 AWR 리포트의 AWR 스냅샷을 확인할 일수 입력
Enter value for num_days: 2 (AWR 스냅샷을 확인할 일수 입력)
Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
oracle19 ORACLE19 25 29 Jan 2024 10:50 1
26 29 Jan 2024 12:00 1
27 29 Jan 2024 13:00 1
28 29 Jan 2024 14:00 1
29 29 Jan 2024 15:00 1
30 29 Jan 2024 16:00 1
31 29 Jan 2024 17:00 1
32 30 Jan 2024 11:10 1
33 30 Jan 2024 12:00 1
34 30 Jan 2024 13:00 1
35 30 Jan 2024 14:00 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
순서4. 비교대상 첫번째 AWR 리포트의 스냅샷 시작 시간대 번호(Snap Id) 입력
Enter value for begin_snap: 26 (비교대상 첫번째 AWR 리포트의 스냅샷 시작 번호 입력)
First Begin Snapshot Id specified: 26
순서5. 비교대상 첫번째 AWR 리포트의 스냅샷 종료 시간대 번호(Snap Id) 입력
Enter value for end_snap: 27 (비교대상 첫번째 AWR 리포트의 스냅샷 종료 번호 입력)
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 3209222764 1 ORACLE19 oracle19 oel8
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 3209222764 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
순서6. 비교대상 두번째 AWR 리포트의 AWR 스냅샷을 확인할 일수 입력
Enter value for num_days2: 2 (AWR 스냅샷을 확인할 일수 입력)
Listing the last 2 days of Completed Snapshots
25 29 Jan 2024 10:50 1
26 29 Jan 2024 12:00 1
27 29 Jan 2024 13:00 1
28 29 Jan 2024 14:00 1
29 29 Jan 2024 15:00 1
30 29 Jan 2024 16:00 1
31 29 Jan 2024 17:00 1
32 30 Jan 2024 11:10 1
33 30 Jan 2024 12:00 1
34 30 Jan 2024 13:00 1
35 30 Jan 2024 14:00 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
순서7. 비교대상 두번째 AWR 리포트의 스냅샷 시작 시간대 번호(Snap Id) 입력
Enter value for begin_snap2: 33 (비교대상 두번째 AWR 리포트의 스냅샷 시작 번호 입력)
Second Begin Snapshot Id specified: 33
순서8. 비교대상 두번째 AWR 리포트의 스냅샷 종료 시간대 번호(Snap Id) 입력
Enter value for end_snap2: 34 (비교대상 두번째 AWR 리포트의 스냅샷 종료 번호 입력)
Second End Snapshot Id specified: 34
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_26_1_33.html To use this name,
press to continue, otherwise enter an alternative.
순서9. AWR 비교 리포트의 출력 파일명 입력, 엔터 입력시 위에 있는 awrdiff_1_26_1_33.html 기본 이름으로 저장됨
(나의 경우 첫번쨰awr시작날짜_첫번쨰awr시작시간_두번쨰awr시작날짜_두번쨰awr_시작시간_awrdiff_sid.html 로 지정함)
Enter value for report_name: 20240129_1200_20240130_1200_awrdiff_oracle19.html (awr 비교 리포트의 출력 파일명 입력)
Using the report name 20240129_1200_20240130_1200_awrdiff_oracle19.html
.
.
(AWR 리포트 생성중)
.
.
<p />
Report written to 20240129_1200_20240130_1200_awrdiff_oracle19.html
완료됨
|
출력파일 로컬 pc나 gui를 볼수있는 화면에서 실행해서 AWR 리포트 확인
AWR 비교 리포트 출력 파일 샘플
text 타입
|
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
|
$ cat 20240129_1200_20240130_1200_awrdiff_oracle19.txt
WORKLOAD REPOSITORY COMPARE PERIOD REPORT
Snapshot Set DB Id Unique Name DB Role Edition Release Cluster CDB Host Std Block Size
------------ ----------- ----------- ---------------- ------- ----------- ------- --- ------------ ---------------
First (1st) 3209222764 oracle19 PRIMARY EE 19.0.0.0.0 NO NO oel7 8192
Second (2nd) 3209222764 oracle19 PRIMARY EE 19.0.0.0.0 NO NO oel7 8192
Snapshot Set Instance Inst Num
------------ ----------- ------------
First (1st) oracle19 1
Second (2nd) oracle19 1
Snapshot Set Begin Snap Id Begin Snap Time End Snap Id End Snap Time Avg Active Users Elapsed Time (min) DB time (min)
------------ -------------- ------------------------- ------------ ------------------------- -------------------------- -------------------------- --------------------------
1st 26 29-Jan-24 12:00:43 (Mon) 27 29-Jan-24 13:00:56 (Mon) 0.0 60.2 0.4
2nd 33 30-Jan-24 12:00:50 (Tue) 34 30-Jan-24 13:00:01 (Tue) 0.0 59.2 0.0
~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~
%Diff: -100.0% -1.7% -100.0%
Host Configuration Comparison
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1st 2nd Diff %Diff
----------------------------------- -------------------- -------------------- -------------------- ---------
Number of CPUs: 2 2 0 0.0
Number of CPU Cores: 2 2 0 0.0
Number of CPU Sockets: 1 1 0 0.0
Physical Memory: 3929.3M 3929.3M 0M 0.0
Load at Start Snapshot: .05 .02 -.03 -60.0
Load at End Snapshot: .1 0 -.1 -100.0
%User Time: .33 .11 -.22 -66.7
%System Time: .32 .06 -.26 -81.3
%Idle Time: 98.85 99.36 .51 0.5
%IO Wait Time: .02 0 -.02 -100.0
Cache Sizes
~~~~~~~~~~~
1st (M) 2nd (M) Diff (M) %Diff
---------------------- ---------- ---------- ---------- --------
Memory Target 1,024.0 1,024.0 0.0 0.0
.....SGA Target 564.0 564.0 0.0 0.0
..........Buffer Cache 264.0 252.0 -12.0 -4.5
..........Shared Pool 252.0 264.0 12.0 4.8
..........Large Pool 4.0 4.0 0.0 0.0
..........Java Pool 4.0 4.0 0.0 0.0
..........Streams Pool
.....PGA Target 460.0 460.0 0.0 0.0
Log Buffer 7.2 7.2 0.0 0.0
In-Memory Area
Workload Comparison
~~~~~~~~~~~~~~~~~~~ 1st Per Sec 2nd Per Sec %Diff 1st Per Txn 2nd Per Txn %Diff
--------------- --------------- ------ --------------- --------------- ------
DB time: 0.0 0.0 -100.0 0.4 0.0 -100.0
CPU time: 0.0 0.0 -100.0 0.3 0.0 -100.0
Background CPU time: 0.0 0.0 0.0 0.4 1.1 161.4
Redo size (bytes): 328,177.4 1,512.9 -99.5 20,098,434.7 298,480.7 -98.5
Logical read (blocks): 213.7 15.0 -93.0 13,087.2 2,951.6 -77.4
Block changes: 40.7 4.9 -88.0 2,494.1 965.7 -61.3
Physical read (blocks): 99.6 0.0 -99.9 6,101.8 8.9 -99.9
Physical write (blocks): 64.4 0.4 -99.4 3,942.9 71.9 -98.2
Read IO requests: 2.7 0.0 -98.5 166.1 8.7 -94.8
Write IO requests: 4.9 0.2 -96.6 302.4 33.3 -89.0
Read IO (MB): 0.8 0.0 -100.0 47.7 0.1 -99.9
Write IO (MB): 0.5 0.0 -100.0 30.8 0.6 -98.2
IM scan rows: 0.0 0.0 0.0 0.0 0.0 0.0
Session Logical Read IM: 0.0 0.0 0.0 0.0 0.0 0.0
User calls: 0.7 0.0 -97.2 43.5 3.4 -92.2
Parses (SQL): 2.1 0.7 -64.7 127.0 144.1 13.5
Hard parses (SQL): 0.3 0.1 -69.7 20.0 18.9 -5.5
SQL Work Area (MB): 0.5 0.0 -98.0 30.9 3.0 -98.0
Logons: 0.0 0.0 0.0 0.7 1.6 119.7
User logons: 0.0 0.0 0.0 0.2 0.0 -100.0
Executes (SQL): 6.3 2.0 -68.4 383.7 390.9 1.9
Transactions: 0.0 0.0 -50.0
First Second Diff
--------------- --------------- ------
% Blocks changed per Read: 19.1 32.7 13.7
Recursive Call %: 97.7 99.9 2.2
Rollback per transaction %: 0.0 0.0 0.0
Rows per Sort: 3,242.7 38.5 -3,204.2
Avg DB time per Call (sec): 0.0 0.0 -0.0
|
html 타입
리포트 수집 방법 및 AWR 관련 링크
오라클 19c AWR 란 ( https://positivemh.tistory.com/1013 )
오라클 19c AWR 리포트 수집 방법 ( https://positivemh.tistory.com/1012 )
오라클 19c ASH 리포트 수집 방법 ( https://positivemh.tistory.com/1011 )
오라클 19c AWR 비교 리포트 수집 방법 ( https://positivemh.tistory.com/1014 )
오라클 19c AWR 에 저장된 정보 확인(awrinfo.sql) ( https://positivemh.tistory.com/780 )
오라클 19c AWR 데이터 export 방법 ( https://positivemh.tistory.com/836 )
오라클 무료 모니터링 툴 ASH Viewer 설치 가이드 ( https://positivemh.tistory.com/966 )
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/gathering-database-statistics.html#GUID-56AEF38E-9400-427B-A818-EDEC145F7ACD
https://www.oracle.com/technetwork/oem/grid-control/overview/con9715-bestkeptsecrets-2806698.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/analyzing-sampled-data.html#GUID-A40B1029-D042-41B6-B596-C77BDCEC722B
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/measuring-database-performance.html#GUID-270E36D0-D7D7-4235-8280-CA3EDD68F8E6
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 인덱스 생성시 nosort 옵션(mssm 환경) (0) | 2024.02.02 |
|---|---|
| 오라클 19c 인덱스 생성시 nosort 옵션(assm 환경) (0) | 2024.02.02 |
| 오라클 19c AWR 란 (0) | 2024.01.30 |
| 오라클 19c AWR 리포트 수집 방법 (0) | 2024.01.30 |
| 오라클 19c ASH 리포트 수집 방법 (0) | 2024.01.30 |