只有12.2才有这个功能
http://docs.oracle.com/database/122/TGDBA/gathering-database-statistics.htm#TGDBA-GUID-D64AEB01-18FF-47EF-BB5C-A0611117D180
参考oracle官方博客
https://blogs.oracle.com/database4cn/122-%e5%a6%82%e4%bd%95%e5%8d%95%e4%b8%bapdb%e5%88%9b%e5%bb%baawr%e6%8a%a5%e5%91%8a
步骤如下:
1)设置PDB的awr_pdb_autoflush_enabled=true:
alter session set container=PDB1;
alter system set awr_pdb_autoflush_enabled=true;
2)设置AWR snpashot时间间隔为1小时:
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 3
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 3
3)同时注意AWR_SNAPSHOT_TIME_OFFSET这个参数建议设置成1000000 以防止过多PDB同时启动snapshot收集导致性能问题
http://docs.oracle.com/database/122/REFRN/AWR_SNAPSHOT_TIME_OFFSET.htm#REFRN10325
alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;
4)等一段时间,等snapshot生成之后就可以生成AWR报告了:
SQL> select * from awr_pdb_snapshot;
或者,您也可以手工创建snapshot:
SQL> connect / as sysdba
SQL> alter session set container=PDB1;
SQL> exec dbms_workload_repository.create_snapshot();
执行生成AWR报告:
@?/rdbms/admin/awrrpt
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB <<<<<<<
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26964624/viewspace-2646979/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26964624/viewspace-2646979/