{"id":8402,"date":"2016-06-20T18:15:59","date_gmt":"2016-06-20T16:15:59","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/"},"modified":"2016-06-20T18:15:59","modified_gmt":"2016-06-20T16:15:59","slug":"when-changing-cursor_sharing-takes-effect","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/","title":{"rendered":"When changing CURSOR_SHARING takes effect?"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nI usually don’t advice to set CURSOR_SHARING=FORCE but imagine your application requires it, you forgot it (or tried to do without) on migration and then everything is slow. You want to change it, but when does it take effect? New execution? New parsing? New session?
\n<\/p>\n

EXACT<\/h3>\n

I have the default value where parent cursor is shared only when sql_text is the same:<\/p>\n

\nSQL> show parameter cursor_sharing\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ncursor_sharing                       string      EXACT\n<\/code><\/pre>\n
And I check with a query that the predicate is not changed:<\/p>\n
\nSQL> select * from dual where dummy='X';\n \nD\n-\nX\n \nSQL> select * from table(dbms_xplan.display_cursor) where plan_table_output like '%filter%';\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\n   1 - filter(\"DUMMY\"='X')\n<\/code><\/pre>\n
FORCE<\/h3>\n
I change at system (=instance) level<\/p>\n
\nSQL> alter system set cursor_sharing=force;\nSystem altered.\n \nSQL> show parameter cursor_sharing\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ncursor_sharing                       string      FORCE\n<\/code><\/pre>\n
I tested without session cached cursors:<\/p>\n
\nSQL> alter session set session_cached_cursors=0;\nSession altered.\n<\/code><\/pre>\n
and even from another session<\/p>\n
\nSQL> connect \/ as sysdba\nConnected.\n<\/code><\/pre>\n
But the predicate still has its predicate:<\/p>\n
\nSQL> select * from dual where dummy='X';\n \nD\n-\nX\n \nSQL> select * from table(dbms_xplan.display_cursor) where plan_table_output like '%filter%';\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\n   1 - filter(\"DUMMY\"='X')\n<\/code><\/pre>\n
No invalidation, no new cursor. Same old statement.<\/p>\n
FLUSH SHARED_POOL<\/h3>\n
Only when I flush the shared_pool I can execute the statement with literals replaced:<\/p>\n
\nSQL> alter system flush shared_pool;\nSystem altered.\n \nSQL> select * from dual where dummy='X';\n \nD\n-\nX\n \nSQL> select * from table(dbms_xplan.display_cursor) where plan_table_output like '%filter%';\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------\n   1 - filter(\"DUMMY\"=:SYS_B_0)\n<\/code><\/pre>\n
If you fear a hard parse fest, you can flush specific cursors. I’ve documented the procedure in a previous post<\/a>.<\/del><\/p>\n
Update 27-JUN-2016<\/h3>\n
The previous sentence was wrong. Unfortunately, dbms_shared_pool.purge purges only the library cache object heaps and not the object handles. So this is not a solution to get cursor_sharing change immediate effect.<\/p>\n
Autotrace<\/h3>\n
As a side note, do not rely on autotrace for that<\/p>\n
\nSQL> set autotrace on explain\nSQL> select * from dual where dummy='X';\n \nD\n-\nX\n \nExecution Plan\n----------------------------------------------------------\nPlan hash value: 272002086\n \n--------------------------------------------------------------------------\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n--------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |\n|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |\n--------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   1 - filter(\"DUMMY\"='X')\n<\/code><\/pre>\n
Just one more thing that is special with autotrace…<\/p>\n
Conclusion<\/h3>\n
I don’t know exactly how cursor_sharing=force is managed. I thought that the literal replacement occurred before searching for parent cursor. Don’t hesitate to comment here if you know the ‘why’ behind this behavior. My goal here was just to test what has to be done in order to have immediate effect of cursor_sharing change.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . I usually don’t advice to set CURSOR_SHARING=FORCE but imagine your application requires it, you forgot it (or tried to do without) on migration and then everything is slow. You want to change it, but when does it take effect? New execution? New parsing? New session?<\/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":[229],"tags":[767,96],"type_dbi":[],"class_list":["post-8402","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-cursor-sharing","tag-oracle"],"acf":[],"yoast_head":"\nWhen changing CURSOR_SHARING takes effect? - 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\/when-changing-cursor_sharing-takes-effect\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"When changing CURSOR_SHARING takes effect?\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . I usually don’t advice to set CURSOR_SHARING=FORCE but imagine your application requires it, you forgot it (or tried to do without) on migration and then everything is slow. You want to change it, but when does it take effect? New execution? New parsing? New session?\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-20T16:15:59+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=\"3 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\\\/when-changing-cursor_sharing-takes-effect\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-changing-cursor_sharing-takes-effect\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"When changing CURSOR_SHARING takes effect?\",\"datePublished\":\"2016-06-20T16:15:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-changing-cursor_sharing-takes-effect\\\/\"},\"wordCount\":277,\"commentCount\":1,\"keywords\":[\"cursor sharing\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-changing-cursor_sharing-takes-effect\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-changing-cursor_sharing-takes-effect\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-changing-cursor_sharing-takes-effect\\\/\",\"name\":\"When changing CURSOR_SHARING takes effect? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2016-06-20T16:15:59+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-changing-cursor_sharing-takes-effect\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-changing-cursor_sharing-takes-effect\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-changing-cursor_sharing-takes-effect\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"When changing CURSOR_SHARING takes effect?\"}]},{\"@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":"When changing CURSOR_SHARING takes effect? - 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\/when-changing-cursor_sharing-takes-effect\/","og_locale":"en_US","og_type":"article","og_title":"When changing CURSOR_SHARING takes effect?","og_description":"By Franck Pachot . I usually don’t advice to set CURSOR_SHARING=FORCE but imagine your application requires it, you forgot it (or tried to do without) on migration and then everything is slow. You want to change it, but when does it take effect? New execution? New parsing? New session?","og_url":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/","og_site_name":"dbi Blog","article_published_time":"2016-06-20T16:15:59+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"When changing CURSOR_SHARING takes effect?","datePublished":"2016-06-20T16:15:59+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/"},"wordCount":277,"commentCount":1,"keywords":["cursor sharing","Oracle"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/","url":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/","name":"When changing CURSOR_SHARING takes effect? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-06-20T16:15:59+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/when-changing-cursor_sharing-takes-effect\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"When changing CURSOR_SHARING takes effect?"}]},{"@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\/8402","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=8402"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8402\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8402"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8402"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8402"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8402"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}