By Franck Pachot

.
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

AWR

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 Total

and 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 Total

and


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.

Statspack

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 Gets

At 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 Gets

The 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 Gets

This 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.

So what?

If, like me, you like to see if the numbers match in an AWR or Statspack report, then you will probably do that on

  • AWR reports at CDB level because they collect instance statistics
  • Statspack reports at PDB level because they collect container statistics

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…