Oracle AWR의 관리 및 유지보수에 대한 자세한 설명-MySQL 튜토리얼-php.cn

AWR은 자동 워크로드 저장소(Automatic Workload Repository)의 약자로, 중국어로 자동 워크로드 데이터 아카이브라고 합니다. AWR의 관리는 주로 스냅샷 및 기준선 관리를 위한 것입니다. 예를 들어 스냅샷 간격을 설정하고, 스냅샷을 삭제하고, 스냅샷 보존 시간을 설정합니다. 기준선의 경우 기준선의 생성, 삭제, 자동 생성 등을 말합니다. 이 기사에서는 주로 이 두 부분의 내용을 설명합니다. 이 글은 주로 Oracle AWR(Automatic Workload Data Archive)의 관리 및 유지 관리에 대한 관련 정보를 샘플 코드를 통해 자세히 소개합니다. 이는 학습이나 업무가 필요한 모든 사람에게 확실한 참고 학습 가치를 제공합니다. 아래 에디터와 함께 배워보겠습니다.

1. 스냅샷 관리

기본적으로 Oracle 데이터베이스는 매시간 스냅샷을 생성하고 워크로드 라이브러리에 8일간의 통계 정보를 보관합니다. 필요한 경우 dbms_workload_repository 프로그램을 사용하여 스냅샷 등을 수동으로 생성, 삭제 또는 수정할 수 있습니다. 스냅샷은 OEM 및 dbms_workload_repository 패키지를 사용하여 관리할 수 있습니다.

1. 스냅샷 수동 생성

스냅샷 생성이 완료되면 데이터 딕셔너리 DBA_HIST_SNAPSHOT에서 스냅샷 관련 정보를 모두 확인할 수 있습니다.

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

SELECT snap_id,
   dbid,
   begin_interval_time,
   end_interval_time
FROM  dba_hist_snapshot
ORDER BY end_interval_time DESC;

 SNAP_ID  DBID BEGIN_INTERVAL_TIME   END_INTERVAL_TIME
---------- ---------- ---------------------------- --------------------------
  164 41924548 14-APR-17 09.38.19.467 AM 14-APR-17 10.51.21.886 AM
  163 41924548 14-APR-17 09.00.10.470 AM 14-APR-17 09.38.19.467 AM
  162 41924548 14-APR-17 08.00.07.242 AM 14-APR-17 09.00.10.470 AM
  161 41924548 14-APR-17 07.00.04.120 AM 14-APR-17 08.00.07.242 AM

로그인 후 복사

2. 스냅샷 삭제

다음 프로세스는 snap_id가 162에서 164인 스냅샷을 삭제합니다. 41924548이 지정되지 않으면 현재 기본 데이터베이스가 사용됩니다

BEGIN
 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 162,
       high_snap_id => 164, dbid => 41924548);
END;
/

로그인 후 복사

3. 스냅샷 보존 간격 수정

--查看当前的保留策略，如下，为缺省值，采集间隔为1小时，保留8天
SQL> select * from dba_hist_wr_control;

  DBID SNAP_INTERVAL  RETENTION   TOPNSQL
---------- -------------------- ------------------ ----------
 41924548 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

--下面将其修改为保留5天，采集间隔为30分钟,topnsql为50条
BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 10*24*60,
     interval => 30, topnsql => 50, dbid => 41924548);
END;
/

--查看修改后的结果
SQL> select * from dba_hist_wr_control;

  DBID SNAP_INTERVAL  RETENTION   TOPNSQL
---------- -------------------- ------------------ ----------
 41924548 +00000 00:30:00.0 +00010 00:00:00.0 50

로그인 후 복사

2. 기준선 관리

AWR 기준선은 수동 또는 자동으로 생성 및 삭제할 수 있습니다. 생성 및 삭제합니다. 자동 기준선 생성의 경우 기준선 샘플을 먼저 생성하여 자동 기준선 생성을 달성해야 합니다. 주요 설명은 다음과 같습니다.

1. 기준선 생성

기준선 생성은 스냅샷에 의존하므로 생성 전에 필요한 시간 범위의 snap_id를 얻어야 하며, 동시에 DBA_HIST_SNAPSHOT 이후에 데이터 사전을 쿼리하여 얻을 수 있습니다. 베이스라인 생성이 완료되면 데이터 딕셔너리 DBA_HIST_BASELINE 정보에서 베이스라인 관련 정보를 조회할 수 있습니다.

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 160,
     end_snap_id => 161, baseline_name => 'peak_baseline',
     dbid => 41924548, expiration => 10);
END;
/

SQL> SELECT baseline_id,
 2   baseline_name,
 3   baseline_type,
 4   expiration,
 5   creation_time
 6 FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME   BASELINE_TYPE EXPIRATION CREATION_
----------- ----------------------- ------------- ---------- ---------
   1 peak_baseline   STATIC    10 14-APR-17
   0 SYSTEM_MOVING_WINDOW MOVING_WINDOW   07-APR-17

--基于特定时间创建基线，如下示例
BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
  start_time  => TO_DATE ('2017-04-14 6:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  end_time  => TO_DATE ('2017-04-14 8:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  baseline_name => 'peak_baseline2',
  expiration  => 10);
END;
/

로그인 후 복사

위의 예에서는 peak_baseline이라는 기준선이 160~161 범위와 10일의 보존 시간으로 생성되었습니다. 10일이 지나면 기준선이 삭제되고 해당 스냅샷도 삭제됩니다. 만료 기간이 지정된 경우 기준 및 해당 스냅샷이 영구적으로 보존됩니다.

2. 기준선 삭제

BEGIN
 DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak_baseline',
     cascade => FALSE, dbid => 41924548);
END;
/

로그인 후 복사

위의 예에서는 peak_baseline이라는 기준선이 삭제되고, cascade가 false로 지정되어 기준선이 삭제되면 해당 스냅샷이 삭제되지 않습니다.

3. 기준선 이름 변경

BEGIN
 DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
     old_baseline_name => 'peak_baseline',
     new_baseline_name => 'peak_mondays',
     dbid => 41924548);
END;
/

--验证结果
SQL> SELECT baseline_id,
 2   baseline_name,
 3   baseline_type,
 4   expiration,
 5   creation_time
 6 FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME   BASELINE_TYPE EXPIRATION CREATION_
----------- ----------------------- ------------- ---------- ---------
   1 peak_mondays   STATIC    10 14-APR-17
   0 SYSTEM_MOVING_WINDOW MOVING_WINDOW   07-APR-17

로그인 후 복사

위 예에서는 기준선 이름을 peak_baseline에서 peak_mondays로 변경합니다.

4. 기본 이동 기간 기준 보존 값 수정

--查看缺省的window_size
SELECT baseline_name, baseline_type, moving_window_size
FROM dba_hist_baseline
WHERE baseline_name = 'SYSTEM_MOVING_WINDOW';

BASELINE_NAME   BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW  MOVING_WINDOW     8

BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
     window_size => 7,
     dbid => 41924548);
END;
/

--window_size为天，只能够小于等于当前快照保留时间，否则报错，如下：

ERROR at line 1:
ORA-13541: system moving window baseline size (864000)
greater than retention (691200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 686
ORA-06512: at line 2

로그인 후 복사

5. 기준 샘플 관리

단일 기준 템플릿 만들기

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
  start_time  => TO_DATE ('2017-04-14 17:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  end_time  => TO_DATE ('2017-04-14 19:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  baseline_name => 'baseline_140414',
  template_name => 'template_140414',
  expiration  => 10,
  dbid   => 41924548);
END;
/

--如果创建基线样本时间小于当前时间，则收到如下错误
ERROR at line 1:
ORA-13537: invalid input for create baseline template (end_time, end_time is less than SYSDATE)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 768
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 818
ORA-06512: at line 2

로그인 후 복사

위의 예에서는 단일 기준 템플릿을 만들었습니다. 기준 샘플을 지정하고 해당 기간, 기준 이름, 보존 기간 등을 지정합니다. 그런 다음 이 시간 범위 내의 해당 스냅샷이 유지되며 이 기준은 성능 문제가 발견될 때 후속 비교에 사용될 수 있습니다.

반복 기준 샘플 생성

반복 기준 샘플은 미래의 특정 시간 범위를 나타냅니다. Oracle은 이 세트 샘플을 참조하여 자동으로 기준을 생성합니다. 예를 들어, 2017년 매주 월요일 오전 9시부터 11시까지 기준선이 자동으로 생성되도록 반복 기준선 샘플을 생성할 수 있습니다.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
  day_of_week   => 'monday',
  hour_in_day   => 9,
  duration    => 2,
  expiration    => 30,
  start_time    => '2017-04-14 09:00:00',
  end_time    => '2017-12-31 11:00:00',
  baseline_name_prefix => 'baseline_2017_mondays_',
  template_name   => 'template_2017_mondays',
  dbid     => 41924548);
END;
/

-- Author  : Leshami
-- Blog  : http://blog.csdn.net/leshami
-- QQ(Weixin) : 645746311

--查看已经创建的基线样本
SQL> select t.template_name,
 2  t.template_type,
 3  t.start_time,
 4  t.end_time,
 5  t.day_of_week,
 6  t.hour_in_day,
 7  t.duration
 8 from dba_hist_baseline_template t;

TEMPLATE_NAME   TEMPLATE_ START_TIME   END_TIME   DAY_OF_WE HOUR_IN_DAY DURATION
--------------------- --------- ------------------- ------------------- --------- ----------- --------
template_140414  SINGLE 2017-04-14 17:00:00 2017-04-14 19:00:00
template_2017_mondays REPEATING 2017-04-14 09:00:00 2017-12-31 11:00:00 MONDAY    17  3

로그인 후 복사

위의 예에서는 2017년 4월 14일부터 매주 월요일(day_of_week) 반복을 만들었습니다. 그러면 시작 시간이 9시(hour_in_day)이고 기간이 2인 기준선이 자동으로 생성됩니다. 시간(기간), 유효 기간은 30일(만료)입니다. 전체 기준의 시작 및 종료 시간 범위는 2017-04-14 09:00:00 ~ 2017-12-31 11:00:00, 기준선도 지정됩니다. 샘플 이름과 기준선 접두사 이름도 지정됩니다.

기준 샘플 삭제

BEGIN
 DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
     template_name => 'template_140414',
     dbid => 41924548);
END;
/

로그인 후 복사

위의 예에서는 샘플 이름을 지정하여 이전에 생성된 기준 샘플을 삭제합니다.

3. AWR 공간 점유

AWR 스냅샷 데이터는 모두 SYSAUX 테이블 공간에 채워져 있습니다. 따라서 스냅샷을 장기간 보관하려면 더 많은 SYSAUX 공간을 차지해야 한다는 의미입니다. 동시에 awrinfo.sql을 통해 SYSAUX를 점유하고 있는 각 객체의 상세 정보를 볼 수 있다.

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

This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrinfo.txt
No errors.     -- Author : Leshami
No errors.     -- Blog : http://blog.csdn.net/leshami
~~~~~~~~~~~~~~~   -- QQ  : 645746311 
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
19:48:53 on Apr 14, 2017 ( Friday ) in Timezone +08:00


Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days


  DB_ID DB Name HOST_PLATFORM   INST STARTUP_TIME  LAST_ASH_SID PAR
------------ ------- ----------------------- ---- ----------------- ------------ ---
* 41924548 ORA11G ydq - Linux x86 64-bit  1 15:18:26 (04/07)  617410 NO

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size      617.4 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS   occupies   206.6 MB ( 33.5% )
| Schema XDB   occupies   157.7 MB ( 25.5% )
| Schema APEX_030200 occupies    85.4 MB ( 13.8% )
| Schema MDSYS  occupies    73.9 MB ( 12.0% )

로그인 후 복사

## 4. AWR 보고서 생성

--단일 인스턴스에서 AWR 보고서 생성

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

로그인 후 복사

--RAC 환경에서 AWR 보고서 생성

SQL> @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

로그인 후 복사

-- AWR 보고서를 생성할 데이터베이스 인스턴스 지정

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

로그인 후 복사

--SQL 문 AWR 보고서 생성

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

로그인 후 복사

--SQL 문 AWR 보고서를 생성할 인스턴스 지정

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

로그인 후 복사

--비교 AWR 보고서 생성

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

로그인 후 복사

--RAC 환경에서 비교 AWR 보고서 생성

@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

로그인 후 복사

5. AWR 관련 중요 뷰 및 데이터 사전

v$active_ 세션_히스토리 : 초당 한 번씩 샘플링된 활성 데이터베이스 세션 활동을 표시합니다.

v$metric 및 v$metric_history:

시스템 성능을 추적하기 위해 메트릭 데이터를 제공합니다. 뷰는 v$metricgroup 뷰

DBA_HIST_ACTIVE_SESS_HISTORY

에 정의된 여러 그룹으로 구성됩니다.

메모리의 활성 세션 기록 정보 표시

DBA_HIST_BASELINE
캡처된 기준선 정보 표시

DBA_HIST_BASELINE_DETAILS

특정 기준선의 세부 정보 표시

DBA_HIST_BASELINE_TEMPLATE

베이스라인 템플릿 관련 정보

DBA_HIST_DATABASE_INSTANCE

데이터베이스 환경

DBA_HIST_DB_CACHE_ADVICE

기록 데이터를 기반으로 다양한 캐시 크기에서의 물리적 읽기 예측

DBA_HIST_DISPATCHER

각 스냅샷의 예약 프로세스에 대한 정보

DBA_HIST_DYN_REMASTER_STATS

통계 정보 동적 리마스터링 프로세스

DBA_HIST_IOSTAT_DETAIL
누르기 유형이나 기능 없음 통계를 위해 찾았습니다. 기록 I/O 정보

DBA_HIST_SHARED_SERVER_SUMMARY

공유 서버 통계

DBA_HIST_SNAPSHOT

스냅샷 정보

DBA_HIST_SQL_PLAN

실행 계획

DBA_HIST_ WR_Control

AWR 제어 정보