This was first published on https://blog.dbi-services.com/when-changing-cursor_sharing-takes-effect (2016-06-20)
Republishing here for new followers. The content is related to the the versions available at the publication date
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?
I have the default value where parent cursor is shared only when sql_text is the same:
SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string EXACTAnd I check with a query that the predicate is not changed:
SQL> select * from dual where dummy='X'; D - X SQL> select * from table(dbms_xplan.display_cursor) where plan_table_output like '%filter%'; PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - filter("DUMMY"='X')
I change at system (=instance) level
SQL> alter system set cursor_sharing=force; System altered. SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string FORCE
I tested without session cached cursors:
SQL> alter session set session_cached_cursors=0; Session altered.
and even from another session
SQL> connect / as sysdba Connected.
But the predicate still has its predicate:
SQL> select * from dual where dummy='X'; D - X SQL> select * from table(dbms_xplan.display_cursor) where plan_table_output like '%filter%'; PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - filter("DUMMY"='X')
No invalidation, no new cursor. Same old statement.
Only when I flush the shared_pool I can execute the statement with literals replaced:
SQL> alter system flush shared_pool; System altered. SQL> select * from dual where dummy='X'; D - X SQL> select * from table(dbms_xplan.display_cursor) where plan_table_output like '%filter%'; PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - filter("DUMMY"=:SYS_B_0)
If you fear a hard parse fest, you can flush specific cursors. I’ve documented the procedure in a previous post.
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.
As a side note, do not rely on autotrace for that
SQL> set autotrace on explain SQL> select * from dual where dummy='X'; D - X Execution Plan ---------------------------------------------------------- Plan hash value: 272002086 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DUMMY"='X')
Just one more thing that is special with autotrace…
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.
Testing with ALTER SESSION, I tried : a. COMMENT ON TABLE .. b. TRUNCATE TABLE … c. DROP TABLE .. CREATE TABLE and none of them caused the SQL to switch to a forced bind. Of course, changing the SQL (such that the SQL_ID changes) resulted in a forced bind.
Hi Hemant, Good point. It’s not the flush of the parent cursor that is responsible for new cursor sharing matching method.
Hi Franck
Here’s the post I mentioned this morning: https://antognini.ch/2016/06/when-cursor_sharingforce-does-literal-replacement-always-take-place/
Best, Chris
Thanks a lot Chris, good that you always test in all patchsets, that explains why I’ve seen different behavior on other environment. It you have other posts in draft, don’t wait to publish them! Cheers, Franck.