{"id":253,"date":"2019-07-22T16:20:15","date_gmt":"2019-07-22T14:20:15","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=253"},"modified":"2019-07-22T16:23:38","modified_gmt":"2019-07-22T14:23:38","slug":"warning-pga_aggregate_limit-value-is-too-high-for-the-amount-of-physical-memory-on-the-system","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2019\/07\/22\/warning-pga_aggregate_limit-value-is-too-high-for-the-amount-of-physical-memory-on-the-system\/","title":{"rendered":"WARNING: pga_aggregate_limit value is too high for the amount of physical memory on the system"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">On Oracle 12.2 I have this fancy message after the database mounts. It is a datawarehouse and there are quite some memory.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The full message is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>2019-07-02T17:41:33.036963+02:00\n WARNING: pga_aggregate_limit value is too high for the\n amount of physical memory on the system\n   PGA_AGGREGATE_LIMIT is 20480 MB\n   PGA_AGGREGATE_TARGET is 10240 MB.\n   physical memory size is 302063 MB\n   limit based on physical memory and SGA usage is 512 MB\n   SGA_TARGET is 276480 MB<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">There are 300GB of physical memory on the server.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SGA_TARGET is set to 270GB, PGA_AGGREGATE_LIMIT to 20GB. There should be 10GB available for the OS to run even when the PGA usage goes to the maximum it is defined.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So why does Oracle gives me this message?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The answer is in the documentation:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"350\" src=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2019\/07\/image-1024x350.png\" alt=\"\" class=\"wp-image-254\" srcset=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2019\/07\/image-1024x350.png 1024w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2019\/07\/image-300x102.png 300w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2019\/07\/image-768x262.png 768w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2019\/07\/image.png 1028w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption> <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/12.2\/refrn\/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3\">https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/12.2\/refrn\/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3<\/a> <\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Even though PGA_AGGREGATE_LIMIT is set to a value different from 0, Oracle calculates so that it does not overpass 90% of the physical memory.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">90% of 300GB is 270GB, minus 270GB from SGA size, means there is nothing left. Fortunately it is not in action, and I can see that by checking the maximum PGA used since instance start:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SQL&gt; select round(value\/1024\/1024\/1024) max_used from v$pgastat where name='maximum PGA allocated';\n  MAX_USED\n----------\n        16<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>On Oracle 12.2 I have this fancy message after the database mounts. It is a datawarehouse and there are quite some memory. The full message is: 2019-07-02T17:41:33.036963+02:00 WARNING: pga_aggregate_limit value is too high for the amount of physical memory on the system PGA_AGGREGATE_LIMIT is 20480 MB PGA_AGGREGATE_TARGET is 10240 MB. physical memory size is 302063 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-253","post","type-post","status-publish","format-standard","category-oracle-12c","czr-hentry"],"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/253","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=253"}],"version-history":[{"count":2,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/253\/revisions"}],"predecessor-version":[{"id":257,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/253\/revisions\/257"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=253"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=253"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=253"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}