{"id":4797,"date":"2014-01-29T09:45:53","date_gmt":"2014-01-29T08:45:53","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=4797"},"modified":"2019-09-13T13:56:21","modified_gmt":"2019-09-13T12:56:21","slug":"limiter-la-pga-totale-en-12c","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/","title":{"rendered":"Limiter la PGA totale en 12c"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

La PGA (Program Area) a souvent pos\u00e9 probl\u00e8me parce qu’il \u00e9tait tr\u00e8s difficile de limiter sa taille totale. De ce fait, la saturation de la m\u00e9moire sur une machine h\u00e9bergeant une instance Oracle est un ph\u00e9nom\u00e8ne qui peut survenir, avec des cons\u00e9quences d\u00e9sastreuses (au mieux probl\u00e8me de performance, au pire probl\u00e8me de stabilit\u00e9 du syst\u00e8me).<\/p>\n

Petit rappel du fonctionnement de la PGA:<\/h2>\n

Sous Oracle (en mode de connexion d\u00e9di\u00e9, ce qui correspond \u00e0 la quasi totalit\u00e9 du parc Oracle d\u00e9ploy\u00e9), un processus est cr\u00e9\u00e9 pour chaque session. Ce processus dispose d’un acc\u00e8s \u00e0 la zone m\u00e9moire partag\u00e9e, la SGA, dans laquelle sont entre autres stock\u00e9s les blocs de donn\u00e9es (Buffer Cache) et les plans d\u2019ex\u00e9cution r\u00e9cents (Library Cache). Il dispose aussi d’une zone m\u00e9moire d\u00e9di\u00e9e, qui va h\u00e9berger tout ce qui est relatif \u00e0 son contexte d’ex\u00e9cution, sa zone de compilation des ordres SQL, et sa zone de tri (qui repr\u00e9sente la plus grosse portion de la PGA).<\/p>\n

Donc, pour faire court, une session = (au moins) un processus = une zone m\u00e9moire en plus de la SGA. La m\u00e9moire consomm\u00e9e par une instance d\u00e9pend donc du nombre de sessions ouvertes, et principalement de la somme des zones de tri allou\u00e9es aux sessions ouvertes.<\/p>\n

 <\/p>\n

Les m\u00e9canismes de limitation de cette PGA sont les suivants:<\/h2>\n

Limitation de la zone de tri par processus, via SORT_AREA_SIZE\u00a0<\/strong><\/p>\n

Limitation de la PGA par processus, via _PGA_MAX_SIZE<\/strong><\/p>\n

Ces deux param\u00e8tres (dont l’un est un param\u00e8tre cach\u00e9…) ont pour port\u00e9e un processus. Limiter la PGA totale est donc possible (il s’agit ici de “PROCESSES x _PGA_SIZE”), mais tr\u00e8s restrictif. C’est bien entendu le nombre de sessions actives effectuant des tris qu’il faut quantifier, par le nombre de sessions maximales sur l’instance.<\/p>\n

Il est possible \u00a0d’essayer de borner la PGA globale via le param\u00e8tre PGA_AGGREGATE_TARGET<\/strong> (qui va concr\u00e8tement g\u00e9rer de mani\u00e8re dynamique SORT_AREA_SIZE). Il s’agit ici d’une cible, pas d’une valeur maximale. Vous pouvez donc consid\u00e9rer que la PGA ne d\u00e9passera pas “souvent” cette valeur.<\/p>\n

A noter que la gestion automatique de la m\u00e9moire propos\u00e9e en 11g (via MEMORY_TARGET<\/strong> et MEMORY_MAX_TARGET<\/strong>) consiste entre autres \u00e0 g\u00e9rer dynamiquement PGA_AGGREGATE_TARGET<\/strong>, elle ne prot\u00e8ge donc pas d’un d\u00e9bordement de la PGA.<\/p>\n

 <\/p>\n

Avec la version 12c, il est enfin possible de poser une r\u00e9elle limite \u00e0 cette zone, via le param\u00e8tre PGA_AGGREGATE_LIMIT<\/strong>. Si la PGA globale atteint ce seuil, la session consommant le plus de PGA est termin\u00e9e.<\/p>\n

 <\/p>\n

Plus de d\u00e9tails sur cette nouvelle fonctionnalit\u00e9 dans cet article de Peter H\u00e4usler<\/a>, et un compl\u00e9ment d’informations concernant _PGA_MAX_SIZE<\/strong> dans cet article de Christian Bilien<\/a>.<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"

La PGA (Program Area) a souvent pos\u00e9 probl\u00e8me parce qu’il \u00e9tait tr\u00e8s difficile de limiter sa taille totale. De ce fait, la saturation de la m\u00e9moire sur une machine h\u00e9bergeant une instance Oracle est un ph\u00e9nom\u00e8ne qui peut survenir, avec… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":14,"featured_media":7846,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[236,245,246],"class_list":["post-4797","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-memoire","tag-pga","tag-pga_aggregate_limit"],"yoast_head":"\nLimiter la PGA totale en 12c - Capdata TECH 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:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Limiter la PGA totale en 12c - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"La PGA (Program Area) a souvent pos\u00e9 probl\u00e8me parce qu’il \u00e9tait tr\u00e8s difficile de limiter sa taille totale. De ce fait, la saturation de la m\u00e9moire sur une machine h\u00e9bergeant une instance Oracle est un ph\u00e9nom\u00e8ne qui peut survenir, avec… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-29T08:45:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T12:56:21+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2014\/01\/oracle_pga.gif\" \/>\n\t<meta property=\"og:image:width\" content=\"287\" \/>\n\t<meta property=\"og:image:height\" content=\"297\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/gif\" \/>\n<meta name=\"author\" content=\"Benjamin VESAN\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin VESAN\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/\"},\"author\":{\"name\":\"Benjamin VESAN\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\"},\"headline\":\"Limiter la PGA totale en 12c\",\"datePublished\":\"2014-01-29T08:45:53+00:00\",\"dateModified\":\"2019-09-13T12:56:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/\"},\"wordCount\":500,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"m\u00e9moire\",\"PGA\",\"PGA_AGGREGATE_LIMIT\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/\",\"name\":\"Limiter la PGA totale en 12c - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2014-01-29T08:45:53+00:00\",\"dateModified\":\"2019-09-13T12:56:21+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Limiter la PGA totale en 12c\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e\",\"name\":\"Benjamin VESAN\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Limiter la PGA totale en 12c - Capdata TECH 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:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/","og_locale":"fr_FR","og_type":"article","og_title":"Limiter la PGA totale en 12c - Capdata TECH BLOG","og_description":"La PGA (Program Area) a souvent pos\u00e9 probl\u00e8me parce qu’il \u00e9tait tr\u00e8s difficile de limiter sa taille totale. De ce fait, la saturation de la m\u00e9moire sur une machine h\u00e9bergeant une instance Oracle est un ph\u00e9nom\u00e8ne qui peut survenir, avec… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2014-01-29T08:45:53+00:00","article_modified_time":"2019-09-13T12:56:21+00:00","og_image":[{"width":287,"height":297,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2014\/01\/oracle_pga.gif","type":"image\/gif"}],"author":"Benjamin VESAN","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Benjamin VESAN","Dur\u00e9e de lecture estim\u00e9e":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/"},"author":{"name":"Benjamin VESAN","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e"},"headline":"Limiter la PGA totale en 12c","datePublished":"2014-01-29T08:45:53+00:00","dateModified":"2019-09-13T12:56:21+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/"},"wordCount":500,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["m\u00e9moire","PGA","PGA_AGGREGATE_LIMIT"],"articleSection":["Oracle"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/","url":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/","name":"Limiter la PGA totale en 12c - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2014-01-29T08:45:53+00:00","dateModified":"2019-09-13T12:56:21+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/limiter-la-pga-totale-en-12c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Limiter la PGA totale en 12c"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/ae56d1d3d5680d95ccc2c927e44bdc3e","name":"Benjamin VESAN","url":"https:\/\/blog.capdata.fr\/index.php\/author\/bvesan\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/4797","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=4797"}],"version-history":[{"count":7,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/4797\/revisions"}],"predecessor-version":[{"id":7847,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/4797\/revisions\/7847"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7846"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=4797"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=4797"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=4797"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}