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]
I then proceed to create an AWR Report, still in the PDB1 container.
Here's a look at the header of the AWR report.
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.
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.
.
.
.
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:
Thanks Hemant for sharing this post.
Post a Comment