{"id":418,"date":"2020-11-24T19:02:03","date_gmt":"2020-11-24T18:02:03","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=418"},"modified":"2021-01-30T14:02:13","modified_gmt":"2021-01-30T13:02:13","slug":"is-pga_aggregate_limit-set","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2020\/11\/24\/is-pga_aggregate_limit-set\/","title":{"rendered":"Why PGA_AGGREGATE_LIMIT appears to be set, when it is not?"},"content":{"rendered":"\n<p>Today I had to explain why the pga_aggregate_target was showing a value, but one does not have to care about it most of the cases. Here the same explanation.<\/p>\n\n\n\n<p>When doing:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; show parameter pga\n\nNAME                                 TYPE                                         VALUE\n------------------------------------ -------------------------------------------- ----------------------\npga_aggregate_limit                  big integer                                  3000M\npga_aggregate_target                 big integer                                  1G\n\n<\/pre><\/div>\n\n\n<p>One would think that pga_aggregate_target is set to 3000M. However when one checks on the spfile, there is nothing defined:<\/p>\n\n\n\n<!--more-->\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; show spparameter pga_aggregate_limit\n\nSID      NAME                          TYPE                                         VALUE\n-------- ----------------------------- -------------------------------------------- --------------------\n*        pga_aggregate_limit           big integer\n\n<\/pre><\/div>\n\n\n<p>The answer comes from the documentation. When PGA_AGGREGATE_LIMIT is not defined on spfile, or if this value is not big enough, then it calculated based on PGA_AGGREGATE_TARGET or PROCESSES parameter, as it is clearly described on the <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/refrn\/PGA_AGGREGATE_LIMIT.html\" target=\"_blank\">documentation<\/a>.<\/p>\n\n\n\n<p>The value used will be the <strong>greatest of:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>value of PGA_AGGREGATE_LIMIT parameter<\/li><li>2 times the value of PGA_AGGREGATE_TARGET parameter<\/li><li>3 times the number of PROCESSES parameter (= 3MB\/process)<\/li><\/ul>\n\n\n\n<p>One can use the following query to show what value will be used by Oracle as PGA_AGGREGATE_LIMIT:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nselect PGA_AGGREGATE_LIMIT,\n       PROCESSES_x_3,\n       PGA_AGGREGATE_TARGET_x_2, \n       greatest(nvl(PGA_AGGREGATE_LIMIT,0), PROCESSES_x_3, PGA_AGGREGATE_TARGET_x_2) USED_VALUE \nfrom (select value\/(1024*1024) PGA_AGGREGATE_LIMIT from v$spparameter where name=&#039;pga_aggregate_limit&#039;),\n     (select value*3 PROCESSES_x_3 from v$spparameter where name=&#039;processes&#039;),\n     (select value\/(1024*1024)*2 PGA_AGGREGATE_TARGET_x_2 from v$spparameter where name=&#039;pga_aggregate_target&#039;);\n\nPGA_AGGREGATE_LIMIT PROCESSES_X_3 PGA_AGGREGATE_TARGET_X_2 USED_VALUE\n------------------- ------------- ------------------------ ----------\n                             3000                     1024       3000\n<\/pre><\/div>\n\n\n<p>If you still use Automatic Memory Management (MEMORY_TARGET parameter), then the PGA_AGGREGATE_TARGET will be the value of MEMORY_MAX_SIZE, which I don&#8217;t mention on the SQL.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I had to explain why the pga_aggregate_target was showing a value, but one does not have to care about it most of the cases. Here the same explanation. When doing: One would think that pga_aggregate_target is set to 3000M. However when one checks on the spfile, there is nothing defined:<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,7,39],"tags":[],"class_list":{"0":"post-418","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-oracle-12c","8":"category-oracle-19c","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/418","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\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=418"}],"version-history":[{"count":9,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/418\/revisions"}],"predecessor-version":[{"id":448,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/418\/revisions\/448"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=418"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}