.
\nDo you look at memory advisors? I usually don’t but I still show them during the performance tuning workshop because there is no easy way to get an idea about memory sizing. This morning, while giving the training, I’ve shown on our workshop environment what the problem is: they are based on statistics cumulated from instance startup, which can cover months of heterogeneous activity, and then the result is probably meaningless.<\/p>\n
If you want to trust advisors, then you need to know on which measures it is based. Let’s check it.<\/p>\n
Here is how we query the V$DB_CACHE_ADVISOR in order to see the estimated physical reads for different buffer cache size:<\/p>\n
SQL> \nSELECT a.size_for_estimate \"Buffer size MB\",\n a.size_factor \"Factor size\",\n round(a.estd_physical_read_time\/1000,2) \"Estim. time (s)\",\n a.estd_physical_read_factor \"Estim. time factor\",\n a.estd_physical_reads \"Estim. nb physical read\"\nFROM sys.v$db_cache_advice a\nWHERE a.name='DEFAULT'\nORDER BY a.size_for_estimate;\n\nBuffer size MB Factor size Estim. time (s) Estim. time factor Estim. physical read\n-------------- ----------- --------------- ------------------ --------------------\n 24 .0909 1.31 8.4871 23424349\n 48 .1818 1.16 7.5612 20868825\n 72 .2727 .37 2.3838 6579289\n 96 .3636 .31 1.9787 5461235\n 120 .4545 .26 1.6831 4645325\n 144 .5455 .23 1.4912 4115679\n 168 .6364 .21 1.3713 3784848\n 192 .7273 .2 1.2564 3467715\n 216 .8182 .18 1.1418 3151277\n 240 .9091 .16 1.0568 2916629\n 264 1 .16 1 2759998\n 288 1.0909 .15 .9351 2580935\n 312 1.1818 .14 .8736 2411003\n 336 1.2727 .13 .8291 2288418\n 360 1.3636 .12 .7918 2185486\n 384 1.4545 .12 .7537 2080272\n 408 1.5455 .11 .7035 1941706\n 432 1.6364 .1 .6479 1788252\n 456 1.7273 .09 .6021 1661696\n 480 1.8182 .09 .554 1529086\n\n<\/code><\/pre>\nLook at the factor 1 – the current values. The advisor is based on 2.7 million physical reads. Let’s see if it is based on statistics since instance startup or a shorter period.<\/p>\n
V$SYSTAT<\/h3>\n
I’ll display the instance statistics (cumulative since instance startup) that measure physical reads:<\/p>\n
SQL> select value,name from v$sysstat where name like 'physical reads %';\n\n VALUE NAME\n---------- ----------------------------------------------------------------\n 2760403 physical reads cache\n 86342292 physical reads direct\n 33656 physical reads direct temporary tablespace\n 76909 physical reads cache prefetch\n 13105 physical reads prefetch warmup\n 0 physical reads retry corrupt\n 3428 physical reads direct (lob)\n 0 physical reads for flashback new\n 0 physical reads cache for securefile flashback block new\n 0 physical reads direct for securefile flashback block new\n\n<\/code><\/pre>\nHere it’s clear: the advisor was based on the 2.7 million physical reads to cache. Those values are cumulated from instance startup. If the instance have been started a long time ago then there is nothing relevant here: activity is not regular, memory component have been resized several times, etc. And if the instance has been started recently, then the cache activity is not significant: you did lot of physical reads to load the cache.<\/p>\n
AWR \/ Statspack<\/h3>\n
Ok. we know that V$ views are cumulative from instance start. When we want to look at statistics on a shorter period of time we have AWR or Statspack. Here is the Buffer Cache advisor section:<\/p>\n
Buffer Pool Advisory DB\/Inst: DB1\/DB1 Snap: 61\n-> Only rows with estimated physical reads >0 are displayed\n-> ordered by Block Size, Buffers For Estimate\n\n Est\n Phys Estimated Est\n Size for Size Buffers Read Phys Reads Est Phys % DBtime\nP Est (M) Factor (thousands) Factor (thousands) Read Time for Rds\n--- -------- ------ ------------ ------ -------------- ------------ -------\nD 24 .1 3 8.5 23,422 1 1307.0\nD 48 .2 6 7.6 20,866 1 1164.0\nD 72 .3 9 2.4 6,579 1 368.0\nD 96 .4 12 2.0 5,461 1 306.0\nD 120 .5 15 1.7 4,645 1 260.0\nD 144 .5 18 1.5 4,116 1 231.0\nD 168 .6 21 1.4 3,785 1 213.0\nD 192 .7 24 1.3 3,468 1 195.0\nD 216 .8 26 1.1 3,151 1 177.0\nD 240 .9 29 1.1 2,917 1 164.0\nD 264 1.0 32 1.0 2,760 1 155.0\nD 288 1.1 35 0.9 2,581 1 145.0\nD 312 1.2 38 0.9 2,411 1 136.0\nD 336 1.3 41 0.8 2,289 1 129.0\nD 360 1.4 44 0.8 2,186 1 123.0\nD 384 1.5 47 0.8 2,080 1 118.0\nD 408 1.5 50 0.7 1,942 1 110.0\nD 432 1.6 53 0.6 1,788 1 101.0\nD 456 1.7 56 0.6 1,662 1 94.0\nD 480 1.8 59 0.6 1,529 1 87.0\n ------------------------------------------------------\n<\/code><\/pre>\nthis looks like the cumulative values from instance startup. But I want to be sure – not guess. The instance statistics section can show get the number of physical reads to cache during this period of time:<\/p>\n
Instance Activity Stats DB\/Inst: DB1\/DB1 Snaps: 60-61\n-> Ordered by statistic name\n\nStatistic Total per Second per Trans\n-------------------------------- ------------------ -------------- -------------\n\nphysical reads 1,973 22.9 140.9\nphysical reads cache 1,863 21.7 133.1\n<\/code><\/pre>\nonly few thousand of them here. This confirms that the advisor is not based on delta values.<\/p>\n
Conclusion<\/h3>\n
From what we see, the memory advisors are based on values cumulated since instance startup. I always advise to focus the performance analysis on a short period where activity is regular. Then I can’t advise to use those advisors. You can look at it in the few following days after instance startup, just to have an idea, but don’t rely only on that. In my opinion, that a bug. there is no reason to show cumulative values in a Statspack \/ AWR report. I think that the advisor can do similar estimations on delta values. Anyone volunteer to open a bug ?<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . Do you look at memory advisors? I usually don’t but I still show them during the performance tuning workshop because there is no easy way to get an idea about memory sizing. This morning, while giving the training, I’ve shown on our workshop environment what the problem is: they are based […]<\/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":[198,59],"tags":[96,67],"type_dbi":[],"class_list":["post-4900","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-oracle","tag-performance"],"acf":[],"yoast_head":"\n
Oracle memory advisors: how relevant ? - 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\/oracle-memory-advisors-how-relevant\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle memory advisors: how relevant ?\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . Do you look at memory advisors? I usually don’t but I still show them during the performance tuning workshop because there is no easy way to get an idea about memory sizing. This morning, while giving the training, I’ve shown on our workshop environment what the problem is: they are based […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-06-12T06:10:45+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=\"3 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\/oracle-memory-advisors-how-relevant\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle memory advisors: how relevant ?\",\"datePublished\":\"2015-06-12T06:10:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/\"},\"wordCount\":447,\"commentCount\":0,\"keywords\":[\"Oracle\",\"Performance\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/\",\"name\":\"Oracle memory advisors: how relevant ? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-06-12T06:10:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle memory advisors: how relevant ?\"}]},{\"@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":"Oracle memory advisors: how relevant ? - 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\/oracle-memory-advisors-how-relevant\/","og_locale":"en_US","og_type":"article","og_title":"Oracle memory advisors: how relevant ?","og_description":"By Franck Pachot . Do you look at memory advisors? I usually don’t but I still show them during the performance tuning workshop because there is no easy way to get an idea about memory sizing. This morning, while giving the training, I’ve shown on our workshop environment what the problem is: they are based […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/","og_site_name":"dbi Blog","article_published_time":"2015-06-12T06:10:45+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle memory advisors: how relevant ?","datePublished":"2015-06-12T06:10:45+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/"},"wordCount":447,"commentCount":0,"keywords":["Oracle","Performance"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/","name":"Oracle memory advisors: how relevant ? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-06-12T06:10:45+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-memory-advisors-how-relevant\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle memory advisors: how relevant ?"}]},{"@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\/4900","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=4900"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4900\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4900"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4900"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4900"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4900"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}