"오라클 AWR"의 두 판 사이의 차이
DB CAFE
90번째 줄: | 90번째 줄: | ||
DBA_HIST_WAITSTAT | DBA_HIST_WAITSTAT | ||
DBA_HIST_WR_CONTROL | DBA_HIST_WR_CONTROL | ||
+ | |||
+ | == AWR 리포트 출력 방법 == | ||
+ | |||
+ | === awrrpt.sql 실행 === | ||
+ | * awr*.sql 파일 위치($ORACLE_HOME/rdbms/admin) 으로 이동 | ||
+ | <source lang=sql> | ||
+ | cd $ORACLE_HOME/rdbms/admin | ||
+ | ls awr* | ||
+ | |||
+ | -- awrrpt.sql 은 현재 노드 리포트 출력 | ||
+ | -- awrrpti.sql 은 RAC 에서 다른 노드(DB서버)의 리포트 출력 | ||
+ | |||
+ | </source> | ||
+ | |||
+ | <source lang=sql> | ||
+ | sqlplus / as sysdba; | ||
+ | |||
+ | @?/rdbms/admin/awrrpt.sql | ||
+ | </source> | ||
+ | === 리포트 저장 타입 === | ||
+ | * 리포트 저장 타입 입력 : 기본 html | ||
+ | <source lang=sql> | ||
+ | Current Instance | ||
+ | ~~~~~~~~~~~~~~~~ | ||
+ | |||
+ | DB Id DB Name Inst Num Instance | ||
+ | ----------- ------------ -------- ------------ | ||
+ | 1632937457 BAP 1 BAP1 | ||
+ | |||
+ | |||
+ | 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' | ||
+ | Enter value for report_type: html | ||
+ | </source> | ||
+ | |||
+ | === 리포트 일수 === | ||
+ | * 리포트 일수 입력 | ||
+ | <source lang=sql> | ||
+ | Instances in this Workload Repository schema | ||
+ | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | ||
+ | |||
+ | DB Id Inst Num DB Name Instance Host | ||
+ | ------------ -------- ------------ ------------ ------------ | ||
+ | * 1632937457 1 BAP BAP1 acc1 | ||
+ | 1632937457 2 BAP BAP2 acc2 | ||
+ | |||
+ | Using 1632937457 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. | ||
+ | |||
+ | -- 리포트할 일자 입력 | ||
+ | Enter value for num_days: 1 | ||
+ | </source> | ||
+ | |||
+ | === 시작~종료시간 스냅 ID 선택 === | ||
+ | * 시작 ~ 종료 스냅 id 입력(시간) | ||
+ | <source lang=sql> | ||
+ | Listing the last day's Completed Snapshots | ||
+ | |||
+ | Snap | ||
+ | Instance DB Name Snap Id Snap Started Level | ||
+ | ------------ ------------ --------- ------------------ ----- | ||
+ | BAP1 BAP 2665 13 May 2021 00:00 1 | ||
+ | 2666 13 May 2021 01:00 1 | ||
+ | 2667 13 May 2021 02:00 1 | ||
+ | 2668 13 May 2021 03:00 1 | ||
+ | 2669 13 May 2021 04:00 1 | ||
+ | 2670 13 May 2021 05:00 1 | ||
+ | 2671 13 May 2021 06:00 1 | ||
+ | 2672 13 May 2021 07:00 1 | ||
+ | 2673 13 May 2021 08:00 1 | ||
+ | 2674 13 May 2021 09:00 1 | ||
+ | 2675 13 May 2021 10:00 1 | ||
+ | 2676 13 May 2021 11:00 1 | ||
+ | 2677 13 May 2021 12:00 1 | ||
+ | 2678 13 May 2021 13:00 1 | ||
+ | 2679 13 May 2021 14:00 1 | ||
+ | 2680 13 May 2021 15:00 1 | ||
+ | 2681 13 May 2021 16:00 1 | ||
+ | 2682 13 May 2021 17:00 1 | ||
+ | |||
+ | |||
+ | |||
+ | Specify the Begin and End Snapshot Ids | ||
+ | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | ||
+ | Enter value for begin_snap: 2665 | ||
+ | Begin Snapshot Id specified: 2665 | ||
+ | |||
+ | Enter value for end_snap: 2682 | ||
+ | End Snapshot Id specified: 2682 | ||
+ | </source> | ||
+ | === 리포트명 입력 및 종료 === | ||
+ | * 리포트 명 입력 및 종료 | ||
+ | <source lang=sql> | ||
+ | Specify the Report Name | ||
+ | ~~~~~~~~~~~~~~~~~~~~~~~ | ||
+ | The default report file name is awrrpt_1_2665_2682.html. To use this name, | ||
+ | press <return> to continue, otherwise enter an alternative. | ||
+ | |||
+ | Enter value for report_name: BAP_AWR_20210513.html | ||
+ | |||
+ | |||
+ | Report written to BAP1_AWR_20210513.html | ||
+ | SQL> exit | ||
+ | </source> | ||
+ | |||
+ | * 종료하면 해당 폴더에 리포트를 확인 한다. | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
[[Category:oracle]] | [[Category:oracle]] |
2021년 5월 13일 (목) 16:46 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 AWR 뷰[편집]
- DBA_HIST_SNAPSHOT
- DBA_HIST_SQLSTAT
- DBA_HIST_SYSSTAT
- DBA_HIST_SEG_STAT
- DBA_HIST_SEG_STAT_OBJ
- DBA_HIST_SQLTEXT
- DBA_HIST_ACTIVE_SESS_HISTORY
———
Oracle Database » Useful Tables and Views » ASH Views VIEW_NAME
DBA_HIST_ACTIVE_SESS_HISTORY DBA_HIST_BASELINE DBA_HIST_BG_EVENT_SUMMARY DBA_HIST_BUFFERED_QUEUES DBA_HIST_BUFFERED_SUBSCRIBERS DBA_HIST_BUFFER_POOL_STAT DBA_HIST_COMP_IOSTAT DBA_HIST_CR_BLOCK_SERVER DBA_HIST_CURRENT_BLOCK_SERVER DBA_HIST_DATABASE_INSTANCE DBA_HIST_DATAFILE DBA_HIST_DB_CACHE_ADVICE DBA_HIST_DLM_MISC DBA_HIST_ENQUEUE_STAT DBA_HIST_EVENT_NAME DBA_HIST_FILEMETRIC_HISTORY DBA_HIST_FILESTATXS DBA_HIST_INSTANCE_RECOVERY DBA_HIST_INST_CACHE_TRANSFER DBA_HIST_JAVA_POOL_ADVICE DBA_HIST_LATCH DBA_HIST_LATCH_CHILDREN DBA_HIST_LATCH_MISSES_SUMMARY DBA_HIST_LATCH_NAME DBA_HIST_LATCH_PARENT DBA_HIST_LIBRARYCACHE DBA_HIST_LOG DBA_HIST_METRIC_NAME DBA_HIST_MTTR_TARGET_ADVICE DBA_HIST_OPTIMIZER_ENV DBA_HIST_OSSTAT DBA_HIST_OSSTAT_NAME DBA_HIST_PARAMETER DBA_HIST_PARAMETER_NAME DBA_HIST_PGASTAT DBA_HIST_PGA_TARGET_ADVICE DBA_HIST_PROCESS_MEM_SUMMARY DBA_HIST_RESOURCE_LIMIT DBA_HIST_ROWCACHE_SUMMARY DBA_HIST_RULE_SET DBA_HIST_SEG_STAT DBA_HIST_SEG_STAT_OBJ DBA_HIST_SERVICE_NAME DBA_HIST_SERVICE_STAT DBA_HIST_SERVICE_WAIT_CLASS DBA_HIST_SESSMETRIC_HISTORY DBA_HIST_SESS_TIME_STATS DBA_HIST_SGA DBA_HIST_SGASTAT DBA_HIST_SGA_TARGET_ADVICE DBA_HIST_SHARED_POOL_ADVICE DBA_HIST_SNAPSHOT DBA_HIST_SNAP_ERROR DBA_HIST_SQLBIND DBA_HIST_SQLSTAT DBA_HIST_SQLTEXT DBA_HIST_SQL_BIND_METADATA DBA_HIST_SQL_PLAN DBA_HIST_SQL_SUMMARY DBA_HIST_SQL_WORKAREA_HSTGRM DBA_HIST_STAT_NAME DBA_HIST_STREAMS_APPLY_SUM DBA_HIST_STREAMS_CAPTURE DBA_HIST_STREAMS_POOL_ADVICE DBA_HIST_SYSMETRIC_HISTORY DBA_HIST_SYSMETRIC_SUMMARY DBA_HIST_SYSSTAT DBA_HIST_SYSTEM_EVENT DBA_HIST_SYS_TIME_MODEL DBA_HIST_TABLESPACE_STAT DBA_HIST_TBSPC_SPACE_USAGE DBA_HIST_TEMPFILE DBA_HIST_TEMPSTATXS DBA_HIST_THREAD DBA_HIST_UNDOSTAT DBA_HIST_WAITCLASSMET_HISTORY DBA_HIST_WAITSTAT DBA_HIST_WR_CONTROL
2 AWR 리포트 출력 방법[편집]
2.1 awrrpt.sql 실행[편집]
- awr*.sql 파일 위치($ORACLE_HOME/rdbms/admin) 으로 이동
cd $ORACLE_HOME/rdbms/admin
ls awr*
-- awrrpt.sql 은 현재 노드 리포트 출력
-- awrrpti.sql 은 RAC 에서 다른 노드(DB서버)의 리포트 출력
sqlplus / as sysdba;
@?/rdbms/admin/awrrpt.sql
2.2 리포트 저장 타입[편집]
- 리포트 저장 타입 입력 : 기본 html
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1632937457 BAP 1 BAP1
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'
Enter value for report_type: html
2.3 리포트 일수[편집]
- 리포트 일수 입력
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1632937457 1 BAP BAP1 acc1
1632937457 2 BAP BAP2 acc2
Using 1632937457 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.
-- 리포트할 일자 입력
Enter value for num_days: 1
2.4 시작~종료시간 스냅 ID 선택[편집]
- 시작 ~ 종료 스냅 id 입력(시간)
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
BAP1 BAP 2665 13 May 2021 00:00 1
2666 13 May 2021 01:00 1
2667 13 May 2021 02:00 1
2668 13 May 2021 03:00 1
2669 13 May 2021 04:00 1
2670 13 May 2021 05:00 1
2671 13 May 2021 06:00 1
2672 13 May 2021 07:00 1
2673 13 May 2021 08:00 1
2674 13 May 2021 09:00 1
2675 13 May 2021 10:00 1
2676 13 May 2021 11:00 1
2677 13 May 2021 12:00 1
2678 13 May 2021 13:00 1
2679 13 May 2021 14:00 1
2680 13 May 2021 15:00 1
2681 13 May 2021 16:00 1
2682 13 May 2021 17:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2665
Begin Snapshot Id specified: 2665
Enter value for end_snap: 2682
End Snapshot Id specified: 2682
2.5 리포트명 입력 및 종료[편집]
- 리포트 명 입력 및 종료
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_2665_2682.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: BAP_AWR_20210513.html
Report written to BAP1_AWR_20210513.html
SQL> exit
- 종료하면 해당 폴더에 리포트를 확인 한다.