Oracle 19c收集AWR报告

1.AWR 报告是DBA 的一个重要辅助工具，用来帮助我们分析数据库的性能问题。 我们通常说的AWR 报告默认指的是awrrpt.sql 这个单实例的AWR 报告。

[oracle@node02:/u01/app/oracle/product/19.3.0/db/rdbms/admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@node02:/u01/app/oracle/product/19.3.0/db/rdbms/admin]$ ls -lrt awr*.sql
-rw-r--r-- 1 oracle oinstall   744 Jan 26  2017 awrrptinoop.sql
-rw-r--r-- 1 oracle oinstall  1730 Jan 26  2017 awrrptidc.sql
-rw-r--r-- 1 oracle oinstall  7657 May 29  2017 awrupd12.sql
-rw-r--r-- 1 oracle oinstall  3114 May 29  2017 awrsqrpt.sql
-rw-r--r-- 1 oracle oinstall 10161 May 29  2017 awrrpti.sql
-rw-r--r-- 1 oracle oinstall 14374 May 29  2017 awrinput.sql
-rw-r--r-- 1 oracle oinstall  1533 May 29  2017 awrgrpt.sql
-rw-r--r-- 1 oracle oinstall  8606 May 29  2017 awrgrpti.sql
-rw-r--r-- 1 oracle oinstall 12399 May 29  2017 awrginp.sql
-rw-r--r-- 1 oracle oinstall  3667 May 29  2017 awrgdrpt.sql
-rw-r--r-- 1 oracle oinstall  9384 May 29  2017 awrgdrpi.sql
-rw-r--r-- 1 oracle oinstall 11610 May 29  2017 awrextr.sql
-rw-r--r-- 1 oracle oinstall  3883 May 29  2017 awrddrpt.sql
-rw-r--r-- 1 oracle oinstall  1458 May 29  2017 awrblmig.sql
-rw-r--r-- 1 oracle oinstall 11458 Jul  6  2017 awrload.sql
-rw-r--r-- 1 oracle oinstall  2907 Jul 29  2017 awrinpnm.sql
-rw-r--r-- 1 oracle oinstall 26042 Jul 29  2017 awrgdinp.sql
-rw-r--r-- 1 oracle oinstall 30797 Jul 29  2017 awrddinp.sql
-rw-r--r-- 1 oracle oinstall  2539 Jan  4  2018 awrmacro.sql
-rw-r--r-- 1 oracle oinstall  8333 Aug  9  2018 awrrpt.sql
-rw-r--r-- 1 oracle oinstall 12153 Aug 16  2018 awrsqrpi.sql
-rw-r--r-- 1 oracle oinstall 10177 Aug 21  2018 awrddrpi.sql
-rw-r--r-- 1 oracle oinstall 49857 Aug 23  2018 awrinfo.sql

awrrpt.sql: 单实例AWR 报告
awrgrpt.sql: RAC 的AWR 报告
awrddrpt.sql: 单实例AWR 对比报告
awrgdrpt.sql: RAC 的AWR 对比报告
awrsqrpt.sql: 单个SQL 的报告

2.在之前的版本，AWR,ADDM, ASH 是三个独立的部分，有时在分析时我们需要分别收集， 在19c的AWR 报告中，包含了这3部分的内容。

3.针对CDB执行命令收集AWR，并且Oracle 19c 的PDB AWR 报告也包含了PDB 的ASH 和 ADDM 的内容。

[oracle@node02:/]$ sqlplus / as sysdba
sys@CXMTDB 17:26:23> @?/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

Type Specified:  html







Current Instance
~~~~~~~~~~~~~~~~
DB Id         DB Name        Inst Num       Instance      Container Name
-------------- -------------- -------------- -------------- --------------
 2538411202  CXMTDB          1 cxmtdb       cxmtdb









Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num  DB Name      Instance    Host
------------ ---------- ---------    ----------   ------
* 2538411202   1  CXMTDB       cxmtdb    node02

Using 2538411202 for database Id
Using         1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name    Snap Id  Snap Started  Snap Level
------------ ------------ ---------- ------------------ ----------

cxmtdb       CXMTDB    153  08 Oct 2021 09:21    1
        154  08 Oct 2021 10:00    1
        155  08 Oct 2021 11:00    1
        156  08 Oct 2021 12:00    1
        157  08 Oct 2021 13:00    1
        158  08 Oct 2021 14:00    1
        159  08 Oct 2021 15:00    1
        160  08 Oct 2021 16:00    1
        161  08 Oct 2021 17:00    1
        162  08 Oct 2021 18:00    1
        163  08 Oct 2021 19:00    1
        164  08 Oct 2021 20:00    1
        165  08 Oct 2021 21:00    1
        166  08 Oct 2021 22:00    1
        167  08 Oct 2021 23:00    1
        168  09 Oct 2021 00:00    1
        169  09 Oct 2021 01:00    1
        170  09 Oct 2021 02:00    1
        171  09 Oct 2021 03:00    1
        172  09 Oct 2021 04:00    1
        173  09 Oct 2021 05:00    1
        174  09 Oct 2021 06:00    1
        175  09 Oct 2021 07:00    1
        176  09 Oct 2021 08:00    1
        177  09 Oct 2021 09:00    1
        178  09 Oct 2021 10:00    1
        179  09 Oct 2021 11:00    1
        180  09 Oct 2021 12:00    1
        181  09 Oct 2021 13:00    1
        182  09 Oct 2021 14:00    1
        183  09 Oct 2021 15:00    1
        184  09 Oct 2021 16:00    1
        185  09 Oct 2021 17:00    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 154
Begin Snapshot Id specified: 154

Enter value for end_snap: 155

···········
···········
···········
Report written to awr_20211009.html

AWR报告默认在当前目录下。

4.针对PDB执行命令收集AWR