AWR이란?
Automatic Workload Repository
시간 주기별로 DB status snapshot을 찍어서 통계치를 낸다
성능 정보에 대한 내장 Repository이다
Performance Data Dictonary의 내용을 snapshot을 찍느다
AWR Script 위치
$ORACLE_HOME/rdbms/admin 위치로 가면 AWR sql 파일들을 확인할 수 있다
접속
DBA 권한이 있는 User로 접속한다
[oracle@sua-oracle dbhome_1]$ sqlplus '/as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 16 02:28:10 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
awrrpt.sql 실행
> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
1. Enter value for report type : report 형식 선택
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type: text <----------------------- 입력
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('text','html')) report_type from dual
Type Specified: text
old 1: select '&&report_type' report_type_def from dual
new 1: select 'text' 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
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
1693790396 ORCL 1 ORCL ORCL
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1693790396 1 ORCL ORCL sua-oracle.n
Using 1693790396 for database Id
Using 1 for instance number
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 <return> without
specifying a number lists all completed snapshots.
AWR report의 결과물을 html, text , active-html 중에 선택할 수 있다
> 터미널에서 보기위해 text로 선택해보겠다
2. Enter value for num_days : 며칠동안의 데이터를 볼 것인가?
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
ORCL ORCL 805 15 May 2024 01:00 1
806 15 May 2024 02:00 1
807 15 May 2024 03:00 1
808 15 May 2024 04:00 1
809 15 May 2024 05:00 1
810 15 May 2024 06:00 1
811 15 May 2024 07:00 1
812 15 May 2024 08:00 1
813 15 May 2024 09:00 1
814 15 May 2024 10:00 1
815 15 May 2024 11:00 1
816 15 May 2024 12:00 1
817 15 May 2024 13:00 1
818 15 May 2024 14:00 1
819 15 May 2024 15:00 1
820 15 May 2024 16:00 1
821 15 May 2024 17:00 1
822 15 May 2024 18:00 1
823 15 May 2024 19:00 1
824 15 May 2024 20:00 1
825 15 May 2024 21:00 1
826 15 May 2024 22:00 1
827 15 May 2024 23:00 1
828 16 May 2024 00:00 1
829 16 May 2024 01:00 1
830 16 May 2024 02:00 1
831 16 May 2024 03:00 1
832 16 May 2024 04:00 1
833 16 May 2024 05:00 1
834 16 May 2024 06:00 1
835 16 May 2024 07:00 1
...
...
default로 한시간 간격씩 찍히고 있다
3. Enter value for begin_snap: end_snap : 분석할 기간을 정하기
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 824
Begin Snapshot Id specified: 824
Enter value for end_snap: 835
End Snapshot Id specified: 835
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_824_835.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
4. Enter value for report_name: report 이름 정하기
5. 생성완료
Analysis Target
---------------
Database 'ORCL' with DB ID 1693790396.
Database version 19.0.0.0.0.
ADDM performed an analysis of instance ORCL, numbered 1 and hosted at
sua-oracle.novalocal.
Activity During the Analysis Period
-----------------------------------
Total database time was 0 seconds.
The average number of active sessions was 0.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
The database's maintenance windows were active during 100% of the analysis
period.
End of Report
Report written to awrrpt_1_824_835.txt
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@sua-oracle dbhome_1]$ ls -al awrrpt_1_824_835.txt
-rw-rw-r--. 1 oracle oracle 252766 May 16 07:47 awrrpt_1_824_835.txt
결과의 head -n 40으로 해서 보면 아래와 같다
[oracle@sua-oracle dbhome_1]$ head -n 40 awrrpt_1_824_835.txt
WARNING: Since the DB Time is less than one second, there was
minimal foreground activity in the snapshot period.
Some of the percentage values will be invalid.
WORKLOAD REPOSITORY report for
DB Name DB Id Unique Name DB Role Edition Release RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
ORCL 1693790396 ORCL PRIMARY EE 19.0.0.0.0 NO NO
Instance Inst Num Startup Time
------------ -------- ---------------
ORCL 1 11-Apr-24 11:42
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
sua-oracle.noval Linux x86 64-bit 8 8 8 7.64
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 824 15-May-24 20:00:39 58 17.0
End Snap: 835 16-May-24 07:00:41 58 17.4
Elapsed: 660.02 (mins)
DB Time: 0.00 (mins)
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 0.0 0.0 0.00 0.00
DB CPU(s): 0.0 0.0 0.00 0.00
Background CPU(s): 0.0 0.2 0.00 0.00
Redo size (bytes): 3,796.9 36,153.8
Logical read (blocks): 83.6 795.6
Block changes: 22.1 210.6
Physical read (blocks): 0.2 1.5
Physical write (blocks): 0.7 7.1
Read IO requests: 0.1 0.9
Write IO requests: 0.3 2.8
Read IO (MB): 0.0 0.0
Write IO (MB): 0.0 0.1
snapshot 수동으로 생성하는 법
SQL> execute dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type: text
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('text','html')) report_type from dual
....
...
..
.
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
ORCL ORCL 805 15 May 2024 01:00 1
...
829 16 May 2024 01:00 1
830 16 May 2024 02:00 1
831 16 May 2024 03:00 1
832 16 May 2024 04:00 1
833 16 May 2024 05:00 1
834 16 May 2024 06:00 1
835 16 May 2024 07:00 1
836 16 May 2024 08:00 1
837 16 May 2024 08:03 1
838 16 May 2024 08:04 1
snapshot을 수동으로 찍을 수도 있다.
DBA_HIST_SNAPSHOT 으로 확인하기
DBA_HIST_SYSSTAT 으로 어떤 state 정보가 있는지 확인할수 있다
'DBMS > Oracle' 카테고리의 다른 글
| [Oracle] AWR Report 보는 법 - Load Profile 항목은 어떻게 보는가 (0) | 2024.06.06 |
|---|---|
| [Oracle] AWR- report HTML 형식으로 보는 법 (0) | 2024.05.17 |
| [Oracle] Wait Event - Library Cache 란? (0) | 2024.05.15 |
| [Oracle] Wait Event - Log Buffer Space , Log File Switch Completion, Log File Switch란? (0) | 2024.05.15 |
| [Oracle] Wait Event - Log File Sync, Log File Parallel Write 란? (0) | 2024.05.15 |