Multitentant 환경에서 AWR Report 뽑기
In 12.2 and later
AWR Snapshot 및 Report 는 CDB level 또는 PDB level 에서 생성가능하며 AWR Snapshot 은 기본으로 CDB level 에서만 생성된다.
1. PDB AWR snapshot 수동 생성하는 경우.
SQL> connect <username>/<password> as sysdba
SQL> alter session set container=PDB1;
SQL> exec dbms_workload_repository.create_snapshot();
2. PDB AWR snapshots 의 자동생성 환경 구성.
SQL> alter session set container = CDB$ROOT;
SQL> alter system set AWR_PDB_AUTOFLUSH_ENABLED = TRUE;
SQL> alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000;
SQL> select * from cdb_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
---------- ---------------------------------------- ---------------------------------------- ---------- ----------
1793141417 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 0
4182556862 +40150 00:01:00.0 +00008 00:00:00.0 DEFAULT 3
PDB의 기본 snap_interval 은 너무 길기 때문에 변경해 준다.
SQL> alter session set container=PDB1;
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 30, dbid => 4182556862);
SQL> alter session set container = CDB$ROOT;
SQL> select * from cdb_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
---------- ---------------------------------------- ---------------------------------------- ---------- ----------
1793141417 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 0
4182556862 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT 3
3. cdb_hist_snapshot 을 조회해서 CDB, PDB 의 AWR snapshot 확인가능
SQL> alter session set container=CDB$ROOT;
SQL> select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;
CON_ID INSTANCE_NUMBER SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------- ---------- -------------------------------- --------------------------------
0 1 1 28-FEB-19 06.26.06.000 PM 28-FEB-19 07.00.14.425 PM
0 1 2 28-FEB-19 07.00.14.425 PM 28-FEB-19 08.00.30.362 PM
0 1 3 28-FEB-19 08.00.30.362 PM 28-FEB-19 09.00.46.286 PM
0 1 4 28-FEB-19 09.00.46.286 PM 28-FEB-19 10.00.02.598 PM
0 1 5 28-FEB-19 10.00.02.598 PM 28-FEB-19 11.00.15.351 PM
3 1 1 28-FEB-19 07.00.14.425 PM 28-FEB-19 07.30.36.225 PM <<--- PDB snapshot
3 1 2 28-FEB-19 07.30.36.225 PM 28-FEB-19 08.00.31.532 PM <<--- PDB snapshot
3 1 3 28-FEB-19 08.00.31.532 PM 28-FEB-19 08.30.10.270 PM <<--- PDB snapshot
4. CDB 에서 CDB AWR report 생성
SQL> alter session set container=CDB$ROOT;
SQL> @?/rdbms/admin/awrrpt
5. PDB에서 PDB AWR reportSQL> alter session set container=PDB1;
SQL> @?/rdbms/admin/awrrpt
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:
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB <<------- PDB Snapshot이 별도로 생성된 경우 선택가능
Enter value for awr_location:
AWR Snapshots and Reports from Oracle Multitentant Database(CDB, PDB) (Doc ID 2295998.1)