oracle 12c pdb版本,如何在Oracle 12.2或更高版本上为PDB级别创建AWR？

这是12.2或更高版本的新功能。

Oracle Database Performance Tuning Guide

12c Release 2 (12.1)

6.2.7 Managing Automatic Workload Repository in a Multitenant Environment

可以按照以下详细步骤在PDB级别启用和创建AWR报告。

1) 在PDB级别设置awr_pdb_autoflush_enabled=true：

alter session set container=PDB1;

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 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同时创建快照时出现性能问题。

Oracle Database 12c Release 2

Database Reference

1.26 AWR_SNAPSHOT_TIME_OFFSET

alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;

AWR_SNAPSHOT_TIME_OFFSET

AWR_SNAPSHOT_TIME_OFFSET specifies an offset for the Automatic Workload Repository (AWR) snapshot start time.

Property

Description

Parameter type

Integer

Default value

There is no offset by default.

Modifiable

ALTER SYSTEM

Modifiable in a PDB

No

Range of values

0 - 3599, or the special value 1000000

Basic

No

Oracle RAC

Multiple instances should use the same value

AWR snapshots normally start at the top of the hour (12:00, 1:00, 2:00, and so on). This parameter allows DBAs to specify an offset for the AWR snapshot start time.

This is a useful parameter to avoid CPU spikes from multiple instances all starting their AWR snapshots at the same time. If you have a large system with many instances on it (like many Exadata installations), and you are experiencing such CPU spikes, this parameter can be very useful.

The parameter is specified in seconds. Normally, you set it to a value less than 3600. If you set the special value 1000000 (1,000,000), you get an automatic mode, in which the offset is based on the database name.

The automatic mode is an effective way of getting a reasonable distribution of offset times when you have a very large number of instances running on the same node.

4) 等待1-2小时以自动生成快照：

select * from awr_pdb_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    ** Use the location AWR_PDB for a PDB level report

5)参考文档

如何在12.2或更高版本的PDB级别创建AWR报告 (文档 ID 2469637.1).docx