{"id":5009,"date":"2015-06-23T11:45:00","date_gmt":"2015-06-23T09:45:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/"},"modified":"2015-06-23T11:45:00","modified_gmt":"2015-06-23T09:45:00","slug":"buffer-pool-advisory-in-awr","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/","title":{"rendered":"Buffer pool advisory in AWR"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nIn Oracle memory advisors: how relevant ?<\/a> I said that advisors are calculating their recommendations from statistics cumulated since the begining of the instance, even in AWR which is supposed to cover only a short period. Here is a quick test on buffer pool advisory to validate that assumption.<\/p>\n

I’m running the following query to compare the ‘physical reads cache’ from DBA_HIST_SYSSTATS and the value from the advisor in DBA_HIST_DB_CACHE:<\/p>\n

SQL> column sysstat_value format 999G999G999G999\nSQL> column advisor_value format 999G999G999G999\nSQL> select snap_id,sysstat_value,advisor_value from\n-- physical reads cache\n(select snap_id,dbid,instance_number,stat_name,value sysstat_value from dba_hist_sysstat where stat_name like 'physical reads cache')\nnatural join\n--  ACTUAL_PHYSICAL_READS\n(select snap_id,dbid,instance_number,'ADVISOR',actual_physical_reads advisor_value from DBA_HIST_DB_CACHE_ADVICE where size_factor=1 and name='DEFAULT')\norder by 1 desc,2,3;\n<\/code><\/pre>\n
Here is the result where I can see that they match for all snapshots I have in history:<\/p>\n
   SNAP_ID    SYSSTAT_VALUE    ADVISOR_VALUE\n---------- ---------------- ----------------\n      3025      708,373,759      731,359,811\n      3024      708,364,027      731,350,072\n      3023      708,284,582      731,270,631\n      3022      708,281,965      731,268,020\n      3021      708,280,406      731,266,424\n      3020      708,252,249      731,238,240\n...\n      2133       45,538,775       46,930,580\n      2132       45,533,062       46,924,865\n      2131       30,030,094       31,423,247\n      2130          138,897          138,406\n      2129          125,126          124,637\n      2128          114,556          114,052\n      2127          113,455          112,959\n      2126          112,378          111,890\n      2125          111,179          110,682\n      2124          106,701          106,197\n      2123          104,782          104,287\n      2122           59,071           58,578\n      2121           57,972           57,476\n...\n<\/code><\/pre>\n
I’m not sure about the three columns available in that view: PHYSICAL_READS BASE_PHYSICAL_READS ACTUAL_PHYSICAL_READS so let’s check that the one I used is the one that is displayed in an AWR report. Here is the latest report for snapshots 3024 to 3025:<\/p>\n
\"CaptureBPA.PNG\"<\/a><\/p>\n
Here is how I verified my assumtions, on an instance that is running for a long time. When you read at the advisor recommendations, you should know whether the activity since instance startup is relevant or not. And I don’t know how to reset the counters (except with an instance restart).<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . In Oracle memory advisors: how relevant ? I said that advisors are calculating their recommendations from statistics cumulated since the begining of the instance, even in AWR which is supposed to cover only a short period. Here is a quick test on buffer pool advisory to validate that assumption. I’m running […]<\/p>\n","protected":false},"author":27,"featured_media":5010,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[96],"type_dbi":[],"class_list":["post-5009","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","category-oracle","tag-oracle"],"acf":[],"yoast_head":"\nBuffer pool advisory in AWR - 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\/buffer-pool-advisory-in-awr\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Buffer pool advisory in AWR\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In Oracle memory advisors: how relevant ? I said that advisors are calculating their recommendations from statistics cumulated since the begining of the instance, even in AWR which is supposed to cover only a short period. Here is a quick test on buffer pool advisory to validate that assumption. I’m running […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-06-23T09:45:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png\" \/>\n\t<meta property=\"og:image:width\" content=\"887\" \/>\n\t<meta property=\"og:image:height\" content=\"499\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Buffer pool advisory in AWR\",\"datePublished\":\"2015-06-23T09:45:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/\"},\"wordCount\":200,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png\",\"keywords\":[\"Oracle\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/\",\"name\":\"Buffer pool advisory in AWR - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png\",\"datePublished\":\"2015-06-23T09:45:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png\",\"width\":887,\"height\":499},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Buffer pool advisory in AWR\"}]},{\"@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":"Buffer pool advisory in AWR - 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\/buffer-pool-advisory-in-awr\/","og_locale":"en_US","og_type":"article","og_title":"Buffer pool advisory in AWR","og_description":"By Franck Pachot . In Oracle memory advisors: how relevant ? I said that advisors are calculating their recommendations from statistics cumulated since the begining of the instance, even in AWR which is supposed to cover only a short period. Here is a quick test on buffer pool advisory to validate that assumption. I’m running […]","og_url":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/","og_site_name":"dbi Blog","article_published_time":"2015-06-23T09:45:00+00:00","og_image":[{"width":887,"height":499,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png","type":"image\/png"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Buffer pool advisory in AWR","datePublished":"2015-06-23T09:45:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/"},"wordCount":200,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png","keywords":["Oracle"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/","url":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/","name":"Buffer pool advisory in AWR - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png","datePublished":"2015-06-23T09:45:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBPA.png","width":887,"height":499},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/buffer-pool-advisory-in-awr\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Buffer pool advisory in AWR"}]},{"@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\/5009","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=5009"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5009\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/5010"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5009"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5009"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5009"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5009"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}