01 December, 2016

12.2 New Features -- 4 : AWR for Pluggable Database

12.2 now allows AWR Snapshots and Reports to be created at the PDB level.

Here I demonstrate a Manual Snapshot.  Although Automatic PDB AWR Snapshots are possible (with the AWR_PDB_AUTOFLUSH_ENABLED parameter) , they are disabled by default and Oracle recommends Manual Snapshots.

[timestamps in UTC]

SQL> connect / as sysdba
Connected.
SQL> alter session set container=PDB1;

Session altered.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL>


I then proceed to create an AWR Report, still in the PDB1 container.

SQL> @?/rdbms/admin/awrrpt

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: text

Type Specified:  text

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




Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 3774315809     HKCORCL                     1 HKCORCL        PDB1


Root DB Id      Container DB Id AWR DB Id
--------------- --------------- ---------------
    947935822      3774315809      3774315809









Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  3774315809     1      HKCORCL      HKCORCL      HKCORCL.comp

Using 3774315809 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  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

HKCORCL      HKCORCL              1  01 Dec 2016 08:48    1
                                  2  01 Dec 2016 08:49    1
                                  3  01 Dec 2016 08:52    1


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

Enter value for end_snap: 3
End   Snapshot Id specified: 3




Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_3.txt.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_1_3.txt


Here's a look at the header of the AWR report.

WORKLOAD REPOSITORY PDB report (PDB snapshots)

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
HKCORCL       3774315809 HKCORCL     PRIMARY          EE      12.2.0.1.0 NO  NO

Instance     Inst Num Startup Time
------------ -------- ---------------
HKCORCL             1 16-Nov-16 06:13

    PDB Name PDB Id PDB DB Id        Open Time
------------ ------ ---------- ---------------
PDB1               3  3774315809 25-Nov-16 14:11

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
HKCORCL.compute- Linux x86 64-bit                    2     2       1       7.05

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:         1 01-Dec-16 08:48:46         0       4.0
  End Snap:         3 01-Dec-16 08:52:08         1      12.0
   Elapsed:                3.36 (mins)
   DB Time:                0.29 (mins)

Load Profile                    Per Second   Per Transaction  Per Exec  Per Call
~~~~~~~~~~~~~~~            ---------------   --------------- --------- ---------
             DB Time(s):               0.1               4.3      0.00      0.06
              DB CPU(s):               0.1               2.9      0.00      0.04
      Background CPU(s):               0.0               0.0      0.00      0.00
      Redo size (bytes):         138,443.8       6,976,599.0
  Logical read (blocks):           1,798.4          90,625.0
          Block changes:             282.3          14,224.3
 Physical read (blocks):              21.0           1,055.8
Physical write (blocks):               0.7              34.5
       Read IO requests:              20.9           1,051.8
      Write IO requests:               0.3              12.5
           Read IO (MB):               0.2               8.3
          Write IO (MB):               0.0               0.3
           IM scan rows:               0.0               0.0
Session Logical Read IM:               0.0               0.0
             User calls:               1.5              77.5
           Parses (SQL):              17.9             904.0
      Hard parses (SQL):               3.2             161.5
     SQL Work Area (MB):               2.5             123.5
                 Logons:               0.0               1.0
         Executes (SQL):              45.7           2,302.3
              Rollbacks:               0.0               0.0
           Transactions:               0.0

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait
Event                                Waits Time (sec)      Wait   time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU                                           11.5             66.9
db file sequential read              3,758        2.5  667.74us   14.6 User I/O
direct path write                       23         .7   29.08ms    3.9 User I/O
flashback log file sync                 36         .6   16.98ms    3.6 User I/O
local write wait                        12         .4   37.17ms    2.6 User I/O
acknowledge over PGA limit               9         .1    9.50ms     .5 Schedule
control file sequential read           189         .1  293.42us     .3 System I
PGA memory operation                 3,687          0   11.02us     .2 Other
db file scattered read                   4          0    8.32ms     .2 User I/O
log file sync                            3          0    9.35ms     .2 Commit


The Header identifies the PDB being reported on.Note that Snapshots 1 to 3 are local to the PDB and are not in the Root.  PDB Snapshots can be maintained (create or drop snapshot) in the same manner as CDB snapshots.  (Note : PDB AWR Snapshots are in the view AWR_PDB_SNAPSHOT,  not DBA_HIST_SNAPSHOT).




In contrast, this below is the Header for a CDB where Automatic Snapshots have meant Snap IDs are already at 379,380.  Thus, the CDB snapshots are different from the PDB snapshots.

WORKLOAD REPOSITORY report for

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
HKCORCL        947935822 HKCORCL     PRIMARY          EE      12.2.0.1.0 NO  YES

Instance     Inst Num Startup Time
------------ -------- ---------------
HKCORCL             1 16-Nov-16 06:13

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
HKCORCL.compute- Linux x86 64-bit                    2     2       1       7.05

              Snap Id      Snap Time      Sessions Curs/Sess  PDBs
            --------- ------------------- -------- --------- -----
Begin Snap:       379 01-Dec-16 08:00:47        51        .6     2
  End Snap:       380 01-Dec-16 09:00:09        62        .9     2
   Elapsed:               59.36 (mins)
   DB Time:                1.14 (mins)



Note how it doesn't identify a PDB.

You need to be explicitly connected to a PDB before awrrpt shows you the option to generate PDB-level AWR report.
.
.
.

1 comment:

Foued said...

Thanks Hemant for sharing this post.