This was first published on https://blog.dbi-services.com/awrrpt-and-spreport-in-multitenant (2016-01-27)
Republishing here for new followers. The content is related to the the versions available at the publication date
Yesterday I was giving my ‘Interpreting AWR Reports’ Prezi at Oracle Midlands. There is a point where I explain that I always check the ‘Captured SQL account for …% of Total DB Time (s)’ in order to know whether I’ll get all details in the report or not. There are two main reasons why the most important statements are not captured: report covering a time window too short, or lot of non shareable SQL statements that are aged out from library cache quickly. Both cases because the report show only the statements that remained in the shared pool at the end snapshot. But in multitenant, there is another reason.
Before going to the detail, I would like to say here that Oracle Midland is a great meetup. Speakers should not hesitate to: http://oraclemidlands.com/present
In multitenant, AWR collects statistics at CDB level. Some of the statistics are instance-wide, with CON_ID=0. Some others are related to one pluggable database identified by its with CON_ID. The problem comes when we run the awrrpt.sql from a pluggable database. Here is an excerpt from an AWR report at CDB level:
SQL ordered by Gets DB/Inst: CDB/CDB Snaps: 139-143 … -> Total Buffer Gets: 24,958,807 -> Captured SQL account for 88.9% of Totaland then two ones from two active pluggable databases:
SQL ordered by Gets DB/Inst: CDB/CDB Snaps: 139-143 … -> Total Buffer Gets: 24,958,807 -> Captured SQL account for 21.6% of Totaland
SQL ordered by Gets DB/Inst: CDB/CDB Snaps: 139-143 … -> Total Buffer Gets: 24,958,807 -> Captured SQL account for 60.3% of Total
Here we see that the total logical reads, 24,958,807 buffer gets, comes from instance statistics even when the report is run at PDB level. But only the SQL statements with CON_ID of the PDB are displayed in the report, which explains the low percentage on some PDB. Difficult to know then if the statements were not captured (for the reasons above) or just not reported.
From that, my opinion is that we always need an AWR report gathered at CDB level.
I’ve installed Statspack in the same database and have taken snapshots at the same time as AWR ones. Documentation (spdoc.txt) says that Statspack can be installed only at PDB level, but I did it also on the CDB$ROOT. Here are the spreports:
SQL ordered by Gets DB/Inst: CDB/CDB Snaps: 2-6 -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 24,956,570 -> Captured SQL accounts for 10.5% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer GetsAt CDB level the total is the total of instance (CON_ID=0) but the statements are only those that run in CDB$ROOT (CON_ID=1)
SQL ordered by Gets DB/Inst: CDB/CDB Snaps: 1-5 -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 5,709,168 -> Captured SQL accounts for 112.5% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer GetsThe percentage is higher than 100% because Statspack can count two times the recursive SQL called from PL/SQL. What is important is that the percentage hre is calculated on the PDB logical reads: This is 22% of the total 24,956,570 which is egual to the AWR report.
SQL ordered by Gets DB/Inst: CDB/CDB Snaps: 1-5 -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 17,138,586 -> Captured SQL accounts for 102.0% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer GetsThis is 68% of the total 24,956,570 which is similar to the AWR report.
At PDB level, Statspack counts only the logical reads that come from sessions connected to the PDB. This is very different than the AWR behaviour. Here we can really consider each pluggable database as an isolated database.
If, like me, you like to see if the numbers match in an AWR or Statspack report, then you will probably do that on
But multitenant is new and I’ve not yet the same experience as I have on non-CDB AWR or Statspack reports, so please share your opinion. Generally, consolidation do not make tuning easier. In some cases you will have to look at the PDB, then at the CDB, then at the OS, the storage, and maybe at the hypervisor level…
Hi Franck,
I’ve been trying to install Statspack in a PDB. I can install it in there (with spcreate) but when I run spreport it’s missing pretty much everything. I can install and then run spreport in the cdb but it doesn’t pick up pdb stats. So, how are we to monitor performance issues in the pdb?
I have an SR open with Oracle and was advised:
“Hi
I just verified with my tech lead.
Statspack report can only be installed on CDB not on PDB. Same as awr report. ”
I find that very hard to believe, how in that case would we diagnose performance issues in a PDB?! I’ve scoured MOS and other sites and while I understand that Statspack isn’t really supported as such, I can’t find any install guide specific to a multitenant environment. Any advice you could provide would be great.
Thanks, David
Hi, The support engineer tech lead should read spdoc.txt: o Multitenant Database Support – Added support for Statspack installation and reporting at the Pluggable Database (PDB) level. However, some data sources in the report are for the entire instance and may not be restricted to the PDB. – Statspack installation and reporting is not supported at the root level (CDB$ROOT) Please can you explain what is missing in spreport. You should get everything about the PDB.
Thanks for the reply Franck,
So, when I run spreport in the PDB, it returns:
etc…Also, at the very beginning it gives the DBID & SID of the CDB.
It gives the Instance Efficiency Stats and Top 5 Timed Events but there is no SQL Ordered by CPU / Gets sections. The final section is “Ordered by Event” and then it outputs:
Really it looks like it doesn’t accept the inputs of values for Begin & End snap even.With regard to Oracle Support, I sent them back the following:
“Statspack report can only be installed on CDB not on PDB. Same as awr report. ”
That’s not correct, awr reports can be generated for a pdb. see Doc ID 2295998.1 :
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 <<— Choose AWR_ROOT(default) to create CDB AWR report. Choose "AWR_PDB" to create a PDB AWR report.
Can you please escalate this, we need someone to help us create an spreport purely for PDB data.