{"id":6983,"date":"2016-01-27T21:36:32","date_gmt":"2016-01-27T20:36:32","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/"},"modified":"2016-01-27T21:36:32","modified_gmt":"2016-01-27T20:36:32","slug":"awrrpt-and-spreport-in-multitenant","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/","title":{"rendered":"awrrpt and spreport in multitenant"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nYesterday 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.<\/p>\n

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<\/a>
\n<\/p>\n

AWR<\/h3>\n

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.
\nHere is an excerpt from an AWR report at CDB level:<\/p>\n

\nSQL ordered by Gets                           DB\/Inst: CDB\/CDB  Snaps: 139-143\n\u2026\n-> Total Buffer Gets:      24,958,807\n-> Captured SQL account for   88.9% of Total\n<\/code><\/pre>\n
and then two ones from two active pluggable databases:<\/p>\n
\nSQL ordered by Gets                           DB\/Inst: CDB\/CDB  Snaps: 139-143\n\u2026\n-> Total Buffer Gets:      24,958,807\n-> Captured SQL account for   21.6% of Total\n<\/code><\/pre>\n
and<\/p>\n
\nSQL ordered by Gets                           DB\/Inst: CDB\/CDB  Snaps: 139-143\n\u2026\n-> Total Buffer Gets:      24,958,807\n-> Captured SQL account for   60.3% of Total\n<\/code><\/pre>\n
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.
\nBut 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.<\/p>\n
From that, my opinion is that we always need an AWR report gathered at CDB level. <\/p>\n
Statspack<\/h3>\n
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:<\/p>\n
\nSQL ordered by Gets  DB\/Inst: CDB\/CDB  Snaps: 2-6\n-> End Buffer Gets Threshold:     10000 Total Buffer Gets:      24,956,570\n-> Captured SQL accounts for   10.5% of Total Buffer Gets\n-> SQL reported below exceeded  1.0% of Total Buffer Gets\n<\/code><\/pre>\n
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)<\/p>\n
\nSQL ordered by Gets  DB\/Inst: CDB\/CDB  Snaps: 1-5\n-> End Buffer Gets Threshold:     10000 Total Buffer Gets:       5,709,168\n-> Captured SQL accounts for  112.5% of Total Buffer Gets\n-> SQL reported below exceeded  1.0% of Total Buffer Gets\n<\/code><\/pre>\n
The percentage is higher than 100% because Statspack can count two times the recursive SQL called from PL\/SQL.
\nWhat is important is that the percentage hre is calculated on the PDB logical reads:
\nThis is 22% of the total 24,956,570 which is egual to the AWR report.<\/p>\n
\nSQL ordered by Gets  DB\/Inst: CDB\/CDB  Snaps: 1-5\n-> End Buffer Gets Threshold:     10000 Total Buffer Gets:      17,138,586\n-> Captured SQL accounts for  102.0% of Total Buffer Gets\n-> SQL reported below exceeded  1.0% of Total Buffer Gets\n<\/code><\/pre>\n
This is 68% of the total 24,956,570 which is similar to the AWR report.<\/p>\n
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.<\/p>\n
So what?<\/h3>\n
If, like me, you like to see if the numbers match in an AWR or Statspack report, then you will probably do that on<\/p>\n
    \n
  • AWR reports at CDB level because they collect instance statistics<\/li>\n
  • Statspack reports at PDB level because they collect container statistics<\/li>\n<\/ul>\n
    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.
    \nGenerally, 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…<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[422,220,64,96,209,66,223,69],"type_dbi":[],"class_list":["post-6983","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-awr","tag-cdb","tag-multitenant","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases","tag-statspack"],"acf":[],"yoast_head":"\nawrrpt and spreport in multitenant - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"awrrpt and spreport in multitenant\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-27T20:36:32+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"awrrpt and spreport in multitenant\",\"datePublished\":\"2016-01-27T20:36:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/\"},\"wordCount\":562,\"commentCount\":0,\"keywords\":[\"AWR\",\"CDB\",\"multitenant\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\",\"Statspack\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/\",\"name\":\"awrrpt and spreport in multitenant - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-01-27T20:36:32+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"awrrpt and spreport in multitenant\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"awrrpt and spreport in multitenant - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/","og_locale":"en_US","og_type":"article","og_title":"awrrpt and spreport in multitenant","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/","og_site_name":"dbi Blog","article_published_time":"2016-01-27T20:36:32+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"awrrpt and spreport in multitenant","datePublished":"2016-01-27T20:36:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/"},"wordCount":562,"commentCount":0,"keywords":["AWR","CDB","multitenant","Oracle","Oracle 12c","PDB","Pluggable Databases","Statspack"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/","url":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/","name":"awrrpt and spreport in multitenant - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-01-27T20:36:32+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/awrrpt-and-spreport-in-multitenant\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"awrrpt and spreport in multitenant"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6983","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=6983"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6983\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6983"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}