在oracle 12c pdb下设置awr

jiawang

可以按照以下详细步骤在PDB级别启用和创建AWR报告。
1) 在PDB级别设置awr_pdb_autoflush_enabled=true：
alter session set container=ORA12CPDB4;
alter system set awr_pdb_autoflush_enabled=true;
2) 正确设置AWR快照

select * from cdb_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
2580889417 +40150 00:01:00.0 +00008 00:00:00.0 DEFAULT 6
##这里修改为60分钟生成一次快照
execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

select * from cdb_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
2580889417 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 6

3) 另请将AWR_SNAPSHOT_TIME_OFFSET设置为1000000，以避免多个PDB同时创建快照时出现性能问题。
SQL> alter session set container=cdb$root;
SQL>alter system set awr_snapshot_time_offset=1000000 scope=both;

4) 等待1-2小时以自动生成快照：
select * from awr_pdb_snapshot;

或者您可以手动创建快照：
SQL> connect / as sysdba
SQL> alter session set container=ORA12CPDB4;
SQL> exec dbms_workload_repository.create_snapshot();

5.创建AWR报告：
sqlplus /nolog
SQL>connect / as sysdba
SQL>alter session set container=ORA12CPDB4
SQL>@/u01/app/oracle/product/12.2.0.1/db_1/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: html
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new   1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual

Type Specified:  html

Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB

Location of AWR Data Specified: AWR_PDB
我这里是做pdb的awr,若是做cdb的awr那么选择AWR_ROOT

6.两个时段做awr比较
awrddrpt比较两个awr报表的值
运行awrddrpt.sql脚本;
SQL>@/u01/app/oracle/product/12.2.0.1/db_1/rdbms/admin/awrddrpt.sql