Shulou(Shulou.com)06/01 Report--

Official document

Http://docs.oracle.com/database/122/TGDBA/gathering-database-statistics.htm#TGDBA-GUID-D64AEB01-18FF-47EF-BB5C-A0611117D180

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/AWR_PDB_AUTOFLUSH_ENABLED.html#GUID-08FA21BC-8FB1-4C51-BEEA-139C734D17A7

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/AWR_SNAPSHOT_TIME_OFFSET.html#GUID-90CD8379-DCB2-4681-BB90-0A32C6029C4E

Summary

1. The AWR report created by CDB in 12c is generated in the same way as in the previous version. The AWR of CDB records the information of PDB. For example, there is an extra column of PDB Name in top sql.

2. 12.2.0.1 you can start to create a separate AWR for PDB

3. Create a separate AWR for the PDB. You can directly enter the PDB without making any settings. When creating an AWR, you can only select AWR_ROOT when creating a location of AWR Data. In this case, the AWR of the PDB uses the snapshot dba_hist_snapshot of the CDB, and the generated AWR only contains the information of the PDB.

4. Create a separate AWR for the PDB. You can set the PDB separately, set the parameters AWR_PDB_AUTOFLUSH_ENABLED and AWR_SNAPSHOT_TIME_OFFSET of the PDB, and reset the snapshot generation mode of the PDB (the value of the cdb_hist_wr_control.SNAP_INTERVAL field no longer appears 40150). Enter the PDB directly, and select AWR_ROOT or AWR_PDB when creating the AWR. To select AWR_ROOT, the AWR of the PDB uses the snapshot dba_hist_snapshot of CDB, and the generated AWR has only the information of the PDB. When you select AWR_PDB, the AWR of the PDB uses PDB's own snapshot awr_pdb_snapshot, and the generated AWR has only the information of the PDB.

The method of creating AWR by CDB

Method 1

Execute under the container CDB$ROOT

SQL > @? / rdbms/admin/awrrpt.sql

Method 2

Execute under the container CDB$ROOT

Select * from dba_hist_snapshot order by 1 desc

Select * from table (dbms_workload_repository.awr_report_html (DBID, INSTANCE_NUMBER, startsnapid,endsnapid))

The method of creating AWR by PDB

Method 1

No need to make any settings for PDB

SQL > alter session set container=PDB_NAME

SQL > @? / rdbms/admin/awrrpt.sql

Note: the following prompt appears during this period. Only AWR_ROOT can be selected.

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:

Method 2

You need to set up PDB first.

Alter session set container=PDB_NAME

Alter system set awr_pdb_autoflush_enabled=true

The value of the select * from cdb_hist_wr_control;SNAP_INTERVAL field appears as 40150, which is 40150 01purl 00.0 + 00008.

Execute dbms_workload_repository.modify_snapshot_settings (interval = > 30)

The value of select * from cdb_hist_wr_control;SNAP_INTERVAL field no longer appears 40150, specifically + 00000 00000 30 purl 00.0 + 00008

Alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000

SQL > alter session set container=PDB_NAME

SQL > @? / rdbms/admin/awrrpt.sql

Note: during this period, the following prompt appears. You can choose AWR_ROOT or AWR_PDB.

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: select AWR_ROOT to use the snapshot of CDB, that is, dba_hist_snapshot to export the awr of this PDB, and select AWR_PDB to use the snapshot of PDB, that is, awr_pdb_snapshot, to export the awr of this PDB. The AWR_ROOT export is not the awr of the entire CDB and all the PDB, but only the awr of the PDB, but uses the snapshot of CDB

Method 3

You need to set up PDB first.

Alter session set container=PDB_NAME

Alter system set awr_pdb_autoflush_enabled=true

The value of the select * from cdb_hist_wr_control;SNAP_INTERVAL field appears as 40150, which is 40150 01purl 00.0 + 00008.

Execute dbms_workload_repository.modify_snapshot_settings (interval = > 30)

The value of select * from cdb_hist_wr_control;SNAP_INTERVAL field no longer appears 40150, specifically + 00000 00000 30 purl 00.0 + 00008

Alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000

SQL > alter session set container=PDB_NAME

SQL > select * from awr_pdb_snapshot order by 1 desc

SQL > select * from table (dbms_workload_repository.awr_report_html (DBID, INSTANCE_NUMBER, startsnapid,endsnapid))

The AWR_PDB_AUTOFLUSH_ENABLED initialization parameter enables you to specify whether to enable or disable automatic snapshots for all the PDBs in a CDB or for individual PDBs in a CDB. The automatic snapshot operations are enabled by default for a CDB, but are disabled by default for a PDB. To enable automatic snapshots for a PDB, the PDB administrator must connect to that PDB, set the value for the AWR_PDB_AUTOFLUSH_ENABLED parameter to true, and set the snapshot generation interval to a value greater than 0.

Using the AWR_PDB_AUTOFLUSH_ENABLED initialization parameter, you can specify whether to enable or disable automatic snapshots for all PDB in CDB or a single PDB in CDB. Automatic snapshot operations are enabled by default for CDB, but disabled by default for PDB. To enable automatic snapshots for PDB, the PDB administrator must connect to the PDB, set the value of the AWR_PDB_AUTOFLUSH_ENABLED parameter to true, and set the snapshot generation interval to a value greater than 0.

AWR_PDB_AUTOFLUSH_ENABLED enables you to specify whether to enable or disable automatic Automatic Workload Repository (AWR) snapshots for all the PDBs in a CDB or for individual PDBs in a CDB.

The default value of AWR_PDB_AUTOFLUSH_ENABLED is false. Thus, by default, automatic AWR snapshots are disabled for all the PDBs in a CDB.

When you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root, the new value takes effect in all the PDBs in the CDB.

Therefore, if you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root to true, the value of AWR_PDB_AUTOFLUSH_ENABLED is also changed to true in all of the PDBs, so that automatic AWR snapshots are enabled for all the PDBs.

You can also change the value of AWR_PDB_AUTOFLUSH_ENABLED in any of the individual PDBs in a CDB, and the value that is set for each individual PDB will be honored. This enables you to enable or disable automatic AWR snapshots for individual PDBs.

When a new PDB is created, or a PDB from a previous database release is upgraded to the current database release, automatic AWR snapshots are enabled or disabled for the PDB based on the current value of AWR_PDB_AUTOFLUSH_ENABLED in the root.

AWR_PDB_AUTOFLUSH_ENABLED enables you to specify whether automatic automatic workload Repository (AWR) snapshots are enabled or disabled for all PDB in CDB or for a single PDB in CDB.

The default value for AWR_PDB_AUTOFLUSH_ENABLED is false. Therefore, automatic AWR snapshots are disabled by default for all PDB in CDB.

When you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the root directory of CDB, the new value takes effect in all PDB in CDB.

Therefore, if you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the root directory of CDB to true, the value of AWR_PDB_AUTOFLUSH_ENABLED in all PDB will also change to true to enable automatic AWR snapshots for all PDB.

You can also change the value of AWR_PDB_AUTOFLUSH_ENABLED in any single PDB in CDB, and the value set for each individual PDB will be respected. This allows you to enable or disable automatic AWR snapshots for a single PDB.

When you create a new PDB or upgrade a previous database version of PDB to the current database version, automatic AWR snapshots are enabled or disabled for PDB based on the current value of AWR_PDB_AUTOFLUSH_ENABLED in the root directory.

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

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 (1000000), 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.

AWR_SNAPSHOT_TIME_OFFSET specifies the offset of the start time of the automatic workload repository (AWR) snapshot.

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

This is a useful parameter to avoid CPU spikes from multiple instances starting their AWR snapshots at the same time. This parameter can be useful if you have many instances on your large system (for example, many Exadata installations) and encounter such CPU peaks.

Specify parameters in seconds. Typically, it is set to a value less than 3600. If you set the special value to 1000000 (1000000), you get an automatic mode in which the offset is based on the database name.

Automatic mode is an effective way to allocate offset time when you are running a large number of instances on the same node.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.