{"id":2340,"date":"2018-10-11T00:01:09","date_gmt":"2018-10-11T05:01:09","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=2340"},"modified":"2026-02-07T15:05:26","modified_gmt":"2026-02-07T21:05:26","slug":"odc-appreciation-day-two-useful-parameters-in-18c-thanksodc","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2018\/2340\/","title":{"rendered":"ODC Appreciation Day: Two Useful Parameters in 18c #ThanksODC"},"content":{"rendered":"\n<p>Happy to be part of the #ThanksODC campaign in 2018. Often I learn something new related to Oracle from a forum or blog or a social media post or an article written by someone who loves to help the Oracle community. The Oracle community has helped me tremendously to do my job better. Kudos to the Oracle Development Community! Keep growing!!<\/p>\n\n\n\n<p>I like two parameters introduced in Oracle Database 18c -optimizer_ignore_hints and\u00a0\u00a0optimizer_ignore_parallel_hints. Maybe I have seen too many SQL statements with hints when trying to fix performance issues after upgrading to a newer release of the database!<\/p>\n\n\n\n<p>In earlier releases the optimizer_ignore_hints parameter was an underscore parameter (_optimizer_ignore_hints). Now we can set the parameter at the database level (ALTER SYSTEM) or at the session level (ALTER SESSION) to disable hints. This parameter makes the Oracle optimizer\u00a0ignore all embedded hints in the SQL statement. By default, the value for this parameter is FALSE, meaning, all embedded hints will be recognized in 18c.\u00a0<\/p>\n\n\n\n<p>The related parameter is optimizer_ignore_parallel_hints. Here the optimizer only ignores the PARALLEL hints.\u00a0<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"532\" height=\"307\" src=\"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2018\/10\/image-4.png?resize=532%2C307&#038;ssl=1\" alt=\"\" class=\"wp-image-2342\" srcset=\"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2018\/10\/image-4.png?w=532&amp;ssl=1 532w, https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2018\/10\/image-4.png?resize=300%2C173&amp;ssl=1 300w\" sizes=\"auto, (max-width: 532px) 100vw, 532px\" \/><figcaption>Properties for both OPTIMIZER_IGNORE_HINTS and OPTIMIZER_IGNORE_PARALLEL_HINTS<\/figcaption><\/figure><\/div>\n\n\n\n<p>My preference would be to set optimizer_ignore_parallel_hints in the init file (at <g class=\"gr_ gr_7 gr-alert sel gr_gramm gr_replaced gr_inline_cards gr_disable_anim_appear Grammar only-ins replaceWithoutSep\" id=\"7\" data-gr-id=\"7\">the <\/g>database level) for OLTP databases if the application uses many SQLs with hints embedded. If there is a need to set optimizer_ignore_hints, I would set it for the applications (or users) using a <g class=\"gr_ gr_4 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del\" id=\"4\" data-gr-id=\"4\">logon<\/g> trigger at the session level (ALTER SESSION).\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Happy to be part of the #ThanksODC campaign in 2018. Often I learn something new related to Oracle from a forum or blog or a social media post or an article written by someone who loves to help the Oracle community. The Oracle community has helped me tremendously to do my job better. Kudos to &#8230; <a title=\"ODC Appreciation Day: Two Useful Parameters in 18c #ThanksODC\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2018\/2340\/\" aria-label=\"Read more about ODC Appreciation Day: Two Useful Parameters in 18c #ThanksODC\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":2341,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133],"tags":[],"class_list":["post-2340","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracledb"],"acf":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2018\/10\/ThanksODC.png?fit=560%2C315&ssl=1","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2340","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=2340"}],"version-history":[{"count":3,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2340\/revisions"}],"predecessor-version":[{"id":2345,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/2340\/revisions\/2345"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media\/2341"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=2340"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=2340"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=2340"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}