Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Alfred.

Asked: May 25, 2018 - 12:03 pm UTC

Last updated: January 06, 2020 - 3:08 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Does the "Host CPU" in the PDB AWR mean the CPU usage for the PDB, CDB or the host?

and Connor said...

Thanks for your patience.

I'm not sure what you mean - the AWR report for the PDB only reflects the PDB.

Anyway - I did the following experiment

1) In PDB, take snapshot pre and post experiment

2) In CDB, pummel the machine with a high cpu process for 2 mins

SQL> conn / as sysdba
Connected.
SQL> declare
  2    x int;
  3  begin
  4    for i in 1 .. 1000
  5    loop
  6      select max(sqrt(object_id)) into x from dba_objects;
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.


3) Take an AWR report from the PDB

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:
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new   1: select 'Type Specified: ',lower(nvl('','html')) report_type from dual

Type Specified:  html

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

Location of AWR Data Specified: AWR_PDB

Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 4088113180     CDB122                      1 cdb122         PDB122A

Root DB Id      Container DB Id AWR DB Id
--------------- --------------- ---------------
   3342713002      4088113180      4088113180

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  4088113180     1      CDB122       cdb122       LAPTOP

Using 4088113180 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: 1

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

cdb122       CDB122               2  12 Jun 2018 11:10    1
                                  3  12 Jun 2018 11:12    1

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

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

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_2_3.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: c:\temp\awrrpt_1_2_3.html


and the resulting AWR report contains no reference to the activity from the CDB - it shows the PDB as "idle"

            Per Second Per Transaction Per Exec Per Call
DB Time(s):        0.0             0.3     0.00     0.15
DB CPU(s):         0.0             0.3     0.00     0.15


And just some addenda from one of the PM's on this

There are a few PDB level metrics, but the vast majority of them are Instance wide stats. As from 12.2, instance wide statistics are not shown to PDBs due security concerns. Hence these won’t be flushed in AWR and will always be shown as 0 in the AWR report.

You can check the differences between the two by querying v$sysmetric and v$con_sysmetric at a PDB


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

few more questions about AWR in MultiTenants

Rajeshwaran, Jeyabal, December 07, 2019 - 2:24 pm UTC

Questions:

1) Let's say i run a workload in a PDB, want to know what was the memory (PGA) utilization at the PDB level during the workload - in that case how do we get that from PDB level AWR's (dont see "memory statistics" portion available in PDB level AWR's, where should we need to focus) ?

2) let's say we got the event "SGA: allocation forcing component growth" as the top wait in the PDB level AWR's - since the "memory statistics" portion and ADDM is not available at PDB level AWR's - so how can we address them using PDB level AWR's ?

3) lets say we got the event "Library cache: mutex X" wait as top wait in PDB level AWR's - which section in the PDB level AWR's should we focus to resolve this? and ADDM is not available to us from PDB.

4) also what is the different between AWR_PDB_SNAPSHOT and AWR_PDB_ASH_SNAPSHOT tried in the below link, but don't find - please help us to understand

https://docs.oracle.com/apps/search/search.jsp?q=AWR_PDB_ASH_SNAPSHOT&category=database&product=en/database/oracle/oracle-database/18

Kindly advice.
Connor McDonald
January 06, 2020 - 3:08 am UTC

Anything I can't get from the PDB level in AWR, I think your recourse is hunt around in

DBA_HIST_RSRC_PDB_METRIC
V$RSRCPDBMETRIC
V$RSRCPDBMETRIC_HISTORY
V$RSRC_PDB
V$RSRC_PDB_HISTORY

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library