행위

오라클 AWR

DB CAFE

Dbcafe (토론 | 기여)님의 2021년 9월 9일 (목) 23:48 판
thumb_up 추천메뉴 바로가기

1 AWR 리포트 출력 방법[편집]

1.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;

SQL> @?/rdbms/admin/awrrpt.sql

1.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

1.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

1.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

1.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
  • 종료하면 해당 폴더에 리포트를 확인 한다.


2 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
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.1 AWR 이용한 SQL 실행 계획 추출[편집]

SELECT SQL_ID
       ,PLAN_HASH_VALUE,
       ,ID
       ,PARENT_ID
       ,OPERATION
       ,OPTION
       ,OBJECT_NAME
  FROM DBA_HIST_SQL_PLAN
 WHERE (SQL_ID, PLAN_HASH_VALUE) IN (SELECT SQL_ID, PLAN_HASH_VALUE FROM 튜닝검토대상);
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_AWR&oldid=6368"