{"id":4382,"date":"2023-12-08T20:52:35","date_gmt":"2023-12-08T20:52:35","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=4382"},"modified":"2023-12-08T20:52:36","modified_gmt":"2023-12-08T20:52:36","slug":"performance-improvement-dictionary-views-oracle-21c","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/","title":{"rendered":"Performance Improvement on Dictionary Views in Oracle 21c"},"content":{"rendered":"<h1>V$SQL<\/h1>\n<p>The V$SQL view is queried to retrieve information about SQLs in the shared pool. Let&#8217;s examine the execution plan that retrieves SQL data for a specific SQL_ID:<\/p>\n<pre><code>select * from v$sql where <span style=\"color:red\">sql_id = 'a'<\/span>;\n\n-----------------------------------------------------------------------------------------------------\n| Id  | Operation\t\t| Name\t\t\t    | Rows  | Bytes | Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT\t|\t\t\t    |\t  1 | 20404 |\t  0   (0)| 00:00:01 |\n|*  1 |  <span style=\"color:red\">FIXED TABLE FIXED INDEX<\/span>| <span style=\"color:red\">X$KGLCURSOR_CHILD (ind:2)<\/span> |\t  1 | 20404 |\t  0   (0)| 00:00:01 |\n-----------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   1 - filter(<span style=\"color:red\">\"KGLOBT03\"='a'<\/span> AND \"INST_ID\"=USERENV('INSTANCE'))<\/code><\/pre>\n<p>From the above execution plan, the following conclusions can be drawn:<\/p>\n<ul>\n<li>V$SQL selects from the fixed table X$KGLCURSOR_CHILD.<\/li>\n<li>The view column SQL_ID is mapped to the column KGLOBT03 in the fixed table.<\/li>\n<li>The access path over SQL_ID is indexed.<\/li>\n<\/ul>\n<p>In conclusion, it is advisable to access V$SQL over SQL_ID whenever possible, and the optimizer should access it over the index.<\/p>\n<h1>Oracle 19c and lower versions<\/h1>\n<h2>Suboptimal plan<\/h2>\n<p>To illustrate the issue, I&#8217;m performing a join between the table VSESSION and the view V$SQL. I generated the VSESSION table using the following command:<\/p>\n<pre><code>create table vsession as select * from v$session<\/code><\/pre>\n<p>The rationale behind utilizing the table VSESSION instead of the view V$SESSION is that the latter involves a join across three fixed tables, introducing unnecessary complexity to the execution plan.<\/p>\n<p>When joining VSESSION and V$SQL, the optimizer doesn&#8217;t use the indexed path:<\/p>\n<pre><code>SELECT sq.sql_fulltext\nFROM vsession s, v$sql sq\nWHERE\n  <span style=\"color:red\">sq.sql_id(+)   = s.sql_id<\/span>\n  AND sq.child_number(+) = s.sql_child_number\n;\n\n----------------------------------------------------------------------------------------\n| Id  | Operation\t   | Name\t       | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |\t\t       |    62 |   994K|     3\t (0)| 00:00:01 |\n|*  <span style=\"color:red\">1 |  HASH JOIN OUTER<\/span>   |\t\t       |    62 |   994K|     3\t (0)| 00:00:01 |\n|   2 |   TABLE ACCESS FULL| VSESSION\t       |    62 |  1302 |     3\t (0)| 00:00:01 |\n|   3 |   VIEW\t\t   | V$SQL\t       |     1 | 16405 |     0\t (0)| 00:00:01 |\n|*  4 |    <span style=\"color:red\">FIXED TABLE FULL<\/span>| X$KGLCURSOR_CHILD |     1 | 16418 |     0\t (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   <span style=\"color:red\">1 - access(\"SQ\".\"SQL_ID\"(+)=\"S\".\"SQL_ID\"<\/span> AND\n\t      \"SQ\".\"CHILD_NUMBER\"(+)=\"S\".\"SQL_CHILD_NUMBER\")\n   4 - filter(\"INST_ID\"=USERENV('INSTANCE'))<\/code><\/pre>\n<h2>Bypassed JPPD transformation<\/h2>\n<p>The index wasn&#8217;t used because the join predicate <span style=\"color:red\">SQ.SQL_ID(+) = S.SQL_ID<\/span> was applied during the join (step 1). The optimizer didn&#8217;t push the join predicate into the view, which would have been more efficient. This transformation is called Join Predicate Pushdown (JPPD), and can be tracked in the optimizer trace. The optimizer decided to bypass the transformation without providing a reason:<\/p>\n<pre><code>JPPD:  Considering Cost-based predicate pushdown from query block SEL$1 (#1)\nCost-based predicate pushdown (JPPD)\nJPPD: Checking validity of push-down in query block SEL$1 (#1)\nJPPD:   Checking validity of push-down from query block SEL$1 (#1) to query block SEL$335DD26A (#2)\nJPPD:     <span style=\"color:red\">JPPD bypassed: No valid join condition found.<\/span>\nJPPD:   No valid views found to push predicate into.\nJPPD: Applying transformation directives\nJPPD: Checking validity of push-down in query block SEL$1 (#1)\nJPPD:   No valid views found to push predicate into.<\/code><\/pre>\n<h2>Workaround<\/h2>\n<p>The transformation can be enforced with the PUSH_PRED hint:<\/p>\n<pre><code>SELECT \/*+ <span style=\"color:red\">push_pred(sq)<\/span> *\/ sq.sql_fulltext\nFROM vsession s, v$sql sq\nWHERE\n  sq.sql_id(+)   = s.sql_id\n  AND sq.child_number(+) = s.sql_child_number\n;\n\n-------------------------------------------------------------------------------------------------------\n| Id  | Operation\t\t  | Name\t\t      | Rows  | Bytes | Cost (%CPU)| Time     |\n-------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT\t  |\t\t\t      |    62 |   993K|     3\t(0)| 00:00:01 |\n|   1 |  NESTED LOOPS OUTER\t  |\t\t\t      |    62 |   993K|     3\t(0)| 00:00:01 |\n|   2 |   TABLE ACCESS FULL\t  | VSESSION\t\t      |    62 |  1302 |     3\t(0)| 00:00:01 |\n|   3 |   <span style=\"color:red\">VIEW PUSHED PREDICATE<\/span>   | V$SQL\t\t      |     1 | 16388 |     0\t(0)| 00:00:01 |\n|*  <span style=\"color:red\">4 |    FIXED TABLE FIXED INDEX<\/span>| X$KGLCURSOR_CHILD (ind:2) |     1 | 16418 |     0\t(0)| 00:00:01 |\n-------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   <span style=\"color:red\">4 - filter(\"KGLOBT09\"=\"S\".\"SQL_CHILD_NUMBER\" AND \"KGLOBT03\"=\"S\".\"SQL_ID\"<\/span> AND\n\t      \"INST_ID\"=USERENV('INSTANCE'))<\/code><\/pre>\n<p>The plan now shows that the filter is pushed into the V$SQL view, allowing the index to be used.<\/p>\n<h1>Oracle 21c<\/h1>\n<h2>Optimal plan<\/h2>\n<p>The situation improved in Oracle 21c. A good plan with the index is achieved even without the hint:<\/p>\n<pre><code>\n-------------------------------------------------------------------------------------------------------\n| Id  | Operation\t\t  | Name\t\t      | Rows  | Bytes | Cost (%CPU)| Time     |\n-------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT\t  |\t\t\t      |    61 |   973K|     3\t(0)| 00:00:01 |\n|   1 |  NESTED LOOPS OUTER\t  |\t\t\t      |    61 |   973K|     3\t(0)| 00:00:01 |\n|   2 |   TABLE ACCESS FULL\t  | VSESSION\t\t      |    61 |  1281 |     3\t(0)| 00:00:01 |\n|*  3 |   <span style=\"color:red\">VIEW PUSHED PREDICATE<\/span>   | V$SQL\t\t      |     1 | 16317 |     0\t(0)| 00:00:01 |\n|*  4 |    FIXED TABLE <span style=\"color:red\">FIXED INDEX<\/span>| X$KGLCURSOR_CHILD (ind:2) |     1 | 16336 |     0\t(0)| 00:00:01 |\n-------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   3 - filter(\"SQ\".\"CHILD_NUMBER\"(+)=\"S\".\"SQL_CHILD_NUMBER\")\n   4 - filter(\"KGLOBT03\"=\"S\".\"SQL_ID\" AND \"INST_ID\"=USERENV('INSTANCE'))\n<\/code><\/pre>\n<h2>JPPD transformation<\/h2>\n<p>The optimizer trace confirms that Join Predicate Pushdown (JPPD) transformation has been performed:<\/p>\n<pre><code>\nJPPD: Checking validity of push-down in query block SEL$1 (#1)\nJPPD:   Checking validity of push-down from query block SEL$1 (#1) to query block SEL$335DD26A (#2)\nJPPD:     Passed validity checks\nJPPD: JPPD:   Pushdown from query block SEL$1 (#1) passed validity checks.\nJPPD: Using search type: linear\n<span style=\"color:red\">JPPD: Considering join predicate push-down\nJPPD: Starting iteration 1, state space = (2) : (0)<\/span>\n...\nJPPD: Performing join predicate push-down (final phase) from query block SEL$1 (#1) to query block SEL$335DD26A (#2)\nJPPD:   Pushing predicate \"SQ\".\"SQL_ID\"(+)=\"S\".\"SQL_ID\"\n<\/code><\/pre>\n<h2>Improvement<\/h2>\n<p>To identify what led to the improvement, we can list all the bug fixes related to JPPD:<\/p>\n<pre><code>\nselect bugno, value, sql_feature, description, optimizer_feature_enable \n  from v$system_fix_control \n  where sql_feature like '%JPPD%' and optimizer_feature_enable like '2%' ;\n\n     BUGNO\tVALUE SQL_FEATURE\t\t\t\t\t\t       DESCRIPTION\t\t\t\t\t\t\tOPTIMIZER_FEATURE_ENABLE\n---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------\n  28173995\t    1 QKSFM_JPPD_28173995\t\t\t\t\t       prevent Cartesian Merge Join with partial join predicate pushdow 20.1.0\n  28776811\t    1 QKSFM_JPPD_28776811\t\t\t\t\t       no replacement by transitive predicate for pushed join predicate 20.1.0\n  <span style=\"color:red\">29302565<\/span>\t    1 QKSFM_JPPD_29302565\t\t\t\t\t       <span style=\"color:red\">JPPD index access heuristics correction and fixed index support<\/span>\t20.1.0\n  29867728\t    1 QKSFM_JPPD_29867728\t\t\t\t\t       do not unset partial JPPD info in additional phase\t\t20.1.0\n  30786641\t    1 QKSFM_JPPD_30786641\t\t\t\t\t       relax restriction on\n<\/code><\/pre>\n<p>Bug fix 29302565 &#8220;JPPD index access heuristics correction and fixed index support&#8221; is the main suspect: the JPPD transformation was discarded without calculating the cost, and only the fixed tables are affected. (We can easily prove that JPPD on regular tables are working fine.)<\/p>\n<p>To verify this, the improvement can be temporarily switched off:<\/p>\n<pre><code>ALTER SESSION SET \"_FIX_CONTROL\"='29302565:OFF';<\/code><\/pre>\n<p>This results in the suboptimal plan, confirming the improvement with the bug fix.<\/p>\n<pre><code>\n----------------------------------------------------------------------------------------\n| Id  | Operation\t   | Name\t       | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |\t\t       |    61 |   973K|     3\t (0)| 00:00:01 |\n|*  1 |  HASH JOIN OUTER   |\t\t       |    61 |   973K|     3\t (0)| 00:00:01 |\n|   2 |   TABLE ACCESS FULL| VSESSION\t       |    61 |  1281 |     3\t (0)| 00:00:01 |\n|   3 |   VIEW\t\t   | V$SQL\t       |     1 | 16323 |     0\t (0)| 00:00:01 |\n|*  4 |    <span style=\"color:red\">FIXED TABLE FULL<\/span>| X$KGLCURSOR_CHILD |     1 | 16336 |     0\t (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   1 - access(\"SQ\".\"SQL_ID\"(+)=\"S\".\"SQL_ID\" AND\n\t      \"SQ\".\"CHILD_NUMBER\"(+)=\"S\".\"SQL_CHILD_NUMBER\")\n   4 - filter(\"INST_ID\"=USERENV('INSTANCE'))\n<\/code><\/pre>\n<h1>Summary<\/h1>\n<p>In summary, Oracle&#8217;s heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD. The improvement can be attributed to bug fix 29302565, which corrected JPPD fixed index heuristics.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle&#8217;s heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD.  <a href=\"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/\" class=\"more-link\">Continue Reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[46,11,28,5],"tags":[],"class_list":["post-4382","post","type-post","status-publish","format-standard","hentry","category-21c","category-cost-based-optimizer","category-join-predicate-push-down-jppd","category-oracle"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Performance Improvement on Dictionary Views in Oracle 21c - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Oracle&#039;s heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Performance Improvement on Dictionary Views in Oracle 21c - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Oracle&#039;s heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2023-12-08T20:52:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-08T20:52:36+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\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:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Performance Improvement on Dictionary Views in Oracle 21c\",\"datePublished\":\"2023-12-08T20:52:35+00:00\",\"dateModified\":\"2023-12-08T20:52:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/\"},\"wordCount\":467,\"commentCount\":0,\"articleSection\":[\"21c\",\"cost based optimizer\",\"join predicate push-down (JPPD)\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/\",\"name\":\"Performance Improvement on Dictionary Views in Oracle 21c - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-12-08T20:52:35+00:00\",\"dateModified\":\"2023-12-08T20:52:36+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Oracle's heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/performance-improvement-dictionary-views-oracle-21c\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance Improvement on Dictionary Views in Oracle 21c\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Performance Improvement on Dictionary Views in Oracle 21c - All-round Database Topics","description":"Oracle's heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD.","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:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/","og_locale":"en_US","og_type":"article","og_title":"Performance Improvement on Dictionary Views in Oracle 21c - All-round Database Topics","og_description":"Oracle's heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD.","og_url":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/","og_site_name":"All-round Database Topics","article_published_time":"2023-12-08T20:52:35+00:00","article_modified_time":"2023-12-08T20:52:36+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Performance Improvement on Dictionary Views in Oracle 21c","datePublished":"2023-12-08T20:52:35+00:00","dateModified":"2023-12-08T20:52:36+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/"},"wordCount":467,"commentCount":0,"articleSection":["21c","cost based optimizer","join predicate push-down (JPPD)","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/","url":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/","name":"Performance Improvement on Dictionary Views in Oracle 21c - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2023-12-08T20:52:35+00:00","dateModified":"2023-12-08T20:52:36+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Oracle's heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD.","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/performance-improvement-dictionary-views-oracle-21c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Performance Improvement on Dictionary Views in Oracle 21c"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/4382","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=4382"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/4382\/revisions"}],"predecessor-version":[{"id":4391,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/4382\/revisions\/4391"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=4382"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=4382"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=4382"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}