在oracle 12c pdb下设置awr
可以按照以下详细步骤在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
-- The End --
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步