{"id":11685,"date":"2018-09-11T14:18:27","date_gmt":"2018-09-11T12:18:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/"},"modified":"2018-09-11T14:18:27","modified_gmt":"2018-09-11T12:18:27","slug":"oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/","title":{"rendered":"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore"},"content":{"rendered":"<p>With its 18th release Oracle comes with many improvements. Some of them are obvious and some of them more discrete.<br \/>\nThis is the case of the new buffer area (memory area) called the Memoptimize pool. This new area, part of the SGA, is used to store the data and metadata of standard Oracle tables (heap-organized tables) to improve significantly the performance of queries having filter on PKs.<\/p>\n<p><strong>This new MEMPTIMIZE POOL memory area is split in 2 parts:<\/strong><\/p>\n<ul>\n<li>Memoptimize buffer area: 75% of the space reserved to store table buffers the same way as they are store in the so-called buffer cache<\/li>\n<li>Hash index: 25% of the space reserved to store the hash index of primary key from table in the Memoptimize buffer area<\/li>\n<\/ul>\n<p>To manage this space a new parameter <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/refrn\/MEMOPTIMIZE_POOL_SIZE.html#GUID-D33BB2FE-94A7-475F-B8C8-CC9AC61B502F\">MEMOPTIMIZE_POOL_SIZE<\/a> is available, unfortunately not dynamic. This parameter is fixed at run time and it is not managed with the database automatic memory management. This parameter takes space from the SGA_TARGET so be careful when dimensioning it.<\/p>\n<p><strong>Before<\/strong> this new memory structure, clients who want to query a standard table with a filter on its PK (e.g: where COL_PK = X ) have to wait on I\/Os coming from the disk to the memory until reach the X value from the index. Then I\/Os again from disk to memory to fetch the table block containing the row from the table where COL_PK = X. This mechanism consumes I\/Os of course and also CPU cycles because it involves other processes of the instance who need to perform some tasks.<\/p>\n<p><strong>Now<\/strong> thanks to this new memory space, when a client does the exact same query where COL_PK = X, it can directly hash the value and walk through the Hash Index to find the row location in the Memoptimize buffer area. Then the result is directly picked up by the client process. It results in less CPU consumption and less I\/Os disk in most of case at the cost of memory space.<\/p>\n<p><strong>When to used?<\/strong><\/p>\n<p>It is only useful in case when queries are done on table with an equality filter on the PK. You can balance the need with the size of the requested table and the frequency of usage of such queries.<\/p>\n<p><strong>4 steps activation<br \/>\n<\/strong><\/p>\n<ol>\n<li>Check that the COMPATIBLE parameter is set to 18.0.0 or higher<\/li>\n<li>Set the parameter <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/refrn\/MEMOPTIMIZE_POOL_SIZE.html#GUID-D33BB2FE-94A7-475F-B8C8-CC9AC61B502F\">MEMOPTIMIZE_POOL_SIZE <\/a>to the desired value (restart required)<\/li>\n<li>Alter (or create) target table with the &#8220;MEMOPTIMIZE FOR READ&#8221; clause<\/li>\n<li>Then execute the procedure &#8220;<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/arpls\/DBMS_MEMOPTIMIZE.html#GUID-001ED359-0547-443D-A8D7-2ECB1F553B41\">DBMS_MEMOPTIMIZE.POPULATE( )<\/a>&#8221; to populate the MEMOPTIMIZE POOL with the target table<\/li>\n<\/ol>\n<p><strong>How to remove a table from the MEMOPTIMIZE POOL ?<\/strong><\/p>\n<p>With the procedure <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/arpls\/DBMS_MEMOPTIMIZE.html#GUID-504BEDE7-8CF8-418D-AA65-ACAC2DA256DA\">DROP_OBJECT()<\/a> from the <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/arpls\/DBMS_MEMOPTIMIZE.html#GUID-49F0E799-97F0-41E7-9CD3-24AE3CAA8105\">DBMS_MEMOPTIMIZE<\/a> package.<\/p>\n<p>You can disable the access to this new MEMPTIMIZE POOL by using the clause &#8220;NO MEMOPTIMIZE FOR READ&#8221;.<\/p>\n<p>&nbsp;<\/p>\n<p>I hope this helps and please do not hesitates to contact us should you want more details.<\/p>\n<p>Nicolas<\/p>\n","protected":false},"excerpt":{"rendered":"<p>With its 18th release Oracle comes with many improvements. Some of them are obvious and some of them more discrete. This is the case of the new buffer area (memory area) called the Memoptimize pool. This new area, part of the SGA, is used to store the data and metadata of standard Oracle tables (heap-organized [&hellip;]<\/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":[],"tags":[],"type_dbi":[],"class_list":["post-11685","post","type-post","status-publish","format-standard","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore - 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-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore\" \/>\n<meta property=\"og:description\" content=\"With its 18th release Oracle comes with many improvements. Some of them are obvious and some of them more discrete. This is the case of the new buffer area (memory area) called the Memoptimize pool. This new area, part of the SGA, is used to store the data and metadata of standard Oracle tables (heap-organized [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-09-11T12:18:27+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=\"2 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-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore\",\"datePublished\":\"2018-09-11T12:18:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/\"},\"wordCount\":496,\"commentCount\":0,\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/\",\"name\":\"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-09-11T12:18:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore\"}]},{\"@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 18c new feature] Quick win to improve queries with Memoptimized Rowstore - 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-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/","og_locale":"en_US","og_type":"article","og_title":"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore","og_description":"With its 18th release Oracle comes with many improvements. Some of them are obvious and some of them more discrete. This is the case of the new buffer area (memory area) called the Memoptimize pool. This new area, part of the SGA, is used to store the data and metadata of standard Oracle tables (heap-organized [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/","og_site_name":"dbi Blog","article_published_time":"2018-09-11T12:18:27+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore","datePublished":"2018-09-11T12:18:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/"},"wordCount":496,"commentCount":0,"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/","name":"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-09-11T12:18:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-18c-new-feature-quick-win-to-improve-queries-with-memoptimized-rowstore\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore"}]},{"@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\/11685","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=11685"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11685\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11685"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}