Skip to Main Content
  • Questions

  • cursor sharing exact VS force in 11g

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, yair.

Asked: March 26, 2012 - 2:07 pm UTC

Last updated: September 14, 2015 - 2:24 am UTC

Version: 11.0.2

Viewed 10K+ times! This question is

You Asked

hi tom
I know you recommend to use cursor_sharing=exact (default)
when the app doesn't has big problem of binds.
In 11g the cursor_sharing=force seems like the best thing to have
so why i shouldn't change the default even if i don't have big problem of binds?
thanks.

and Tom said...

Why does it seem like the "best thing"

It is a horrible thing.


First of all - I purposely do not bind sometimes - if I have a low cardinality column (like a status code or something - with say five values) and I know that column is skewed - so that "status = 1" should use an index whereas "status = 2" should not - I might well NOT use a bind on purpose.

You would defeat that - you would force a bind on me. True, adaptive cursor sharing would kick in and *might* solve the problem - but ONLY AFTER making a mistake at least once.

and adaptive cursor sharing might not be able to fix a "wrong plan" issue in any case (it is not perfect)

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:599762300346943686

in particular:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:599762300346943686#4607385100346567193


So, there is the problem that a well written application will sometimes not use bind variables on purpose (and you would remove their ability to do that

There is also the problem that if you *need* cursor_sharing=force to remove a hard parse problem - you have by definition a SQL INJECTION problem.

http://asktom.oracle.com/Misc/all-about-security-sql-injection.html
http://asktom.oracle.com/Misc/all-about-security-sql-injection-redux.html

cursor_sharing = force will not fix that.


Additionally, you have the fact that cursor_sharing=force changes the way certain things happen. For example:


ops$tkyte%ORA11GR2> create table t
  2  as
  3  select *
  4    from all_users
  5   where rownum = 1;

Table created.

ops$tkyte%ORA11GR2> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA11GR2> select substr(username,1,10) uname,
  2         to_char(user_id,'999,999') u_id,
  3         to_char(created,'Dy Mon DD, YYYY' ) created
  4    from t cs_exact;

UNAME      U_ID     CREATED
---------- -------- ----------------
SYS               0 Sun Sep 05, 2010


nicely formatted, everything is nice, then you do this to me:

ops$tkyte%ORA11GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA11GR2> select substr(username,1,10) uname,
  2         to_char(user_id,'999,999') u_id,
  3         to_char(created,'Dy Mon DD, YYYY' ) created
  4    from t cs_force;

UNAME
------------------------------
U_ID
-------------------------------------------------------------------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
SYS
       0
Sun Sep 05, 2010


ops$tkyte%ORA11GR2>


what happened? well, look at the sql:

ops$tkyte%ORA11GR2> column sql_text format a80
ops$tkyte%ORA11GR2> select sql_text
  2    from v$sql
  3   where sql_text like 'select substr(username%';

SQL_TEXT
--------------------------------------------------------------------------------
select substr(username,1,10) uname,        to_char(user_id,'999,999') u_id,
   to_char(created,'Dy Mon DD, YYYY' ) created   from t cs_exact

select substr(username,:"SYS_B_0",:"SYS_B_1") uname,        to_char(user_id,:"SY
S_B_2") u_id,        to_char(created,:"SYS_B_3" ) created   from t cs_force


ops$tkyte%ORA11GR2>



it bound *everything*, including substr lengths, formats, etc. We no longer have a clue what the lengths, precision and scale of things are anymore. reports break, maybe some programs break altogether - because of this change in behavior.



So, I'll put the burden back on you - why does this seem like a *good* idea? To me, it is just about the worse idea possible.


If you HAVE to use cursor_sharing=force, apply it using the smallest scope possible - around the code or application that has this serious bug in it - and only use it until you get the massive security hole in the code fixed.


Note that I did not mention performance, scalability, memory or anything technical. it is all about functional and SECURITY things - things that seem to motivate people more than performance, scalability and the like. Code that needs cursor_sharing force is code that is not secure and code that doesn't work the way it should.Why does it seem like the "best thing"

It is a horrible thing.


First of all - I purposely do not bind sometimes - if I have a low cardinality column (like a status code or something - with say five values) and I know that column is skewed - so that "status = 1" should use an index whereas "status = 2" should not - I might well NOT use a bind on purpose.

You would defeat that - you would force a bind on me. True, adaptive cursor sharing would kick in and *might* solve the problem - but ONLY AFTER making a mistake at least once.

and adaptive cursor sharing might not be able to fix a "wrong plan" issue in any case (it is not perfect)

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:599762300346943686

in particular:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:599762300346943686#4607385100346567193


So, there is the problem that a well written application will sometimes not use bind variables on purpose (and you would remove their ability to do that

There is also the problem that if you *need* cursor_sharing=force to remove a hard parse problem - you have by definition a SQL INJECTION problem.

http://asktom.oracle.com/Misc/all-about-security-sql-injection.html
http://asktom.oracle.com/Misc/all-about-security-sql-injection-redux.html

cursor_sharing = force will not fix that.


Additionally, you have the fact that cursor_sharing=force changes the way certain things happen. For example:


ops$tkyte%ORA11GR2> create table t
  2  as
  3  select *
  4    from all_users
  5   where rownum = 1;

Table created.

ops$tkyte%ORA11GR2> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA11GR2> select substr(username,1,10) uname,
  2         to_char(user_id,'999,999') u_id,
  3         to_char(created,'Dy Mon DD, YYYY' ) created
  4    from t cs_exact;

UNAME      U_ID     CREATED
---------- -------- ----------------
SYS               0 Sun Sep 05, 2010


nicely formatted, everything is nice, then you do this to me:

ops$tkyte%ORA11GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA11GR2> select substr(username,1,10) uname,
  2         to_char(user_id,'999,999') u_id,
  3         to_char(created,'Dy Mon DD, YYYY' ) created
  4    from t cs_force;

UNAME
------------------------------
U_ID
-------------------------------------------------------------------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
SYS
       0
Sun Sep 05, 2010


ops$tkyte%ORA11GR2>


what happened? well, look at the sql:

ops$tkyte%ORA11GR2> column sql_text format a80
ops$tkyte%ORA11GR2> select sql_text
  2    from v$sql
  3   where sql_text like 'select substr(username%';

SQL_TEXT
--------------------------------------------------------------------------------
select substr(username,1,10) uname,        to_char(user_id,'999,999') u_id,
   to_char(created,'Dy Mon DD, YYYY' ) created   from t cs_exact

select substr(username,:"SYS_B_0",:"SYS_B_1") uname,        to_char(user_id,:"SY
S_B_2") u_id,        to_char(created,:"SYS_B_3" ) created   from t cs_force


ops$tkyte%ORA11GR2>



it bound *everything*, including substr lengths, formats, etc. We no longer have a clue what the lengths, precision and scale of things are anymore. reports break, maybe some programs break altogether - because of this change in behavior.



So, I'll put the burden back on you - why does this seem like a *good* idea? To me, it is just about the worse idea possible.


If you HAVE to use cursor_sharing=force, apply it using the smallest scope possible - around the code or application that has this serious bug in it - and only use it until you get the massive security hole in the code fixed.


Note that I did not mention performance, scalability, memory or anything technical. it is all about functional and SECURITY things - things that seem to motivate people more than performance, scalability and the like. Code that needs cursor_sharing force is code that is not secure and code that doesn't work the way it should.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Force v/s Exact - Identical & Similar Statement

VLS, September 09, 2015 - 11:33 am UTC

Thanks for the wonderful answer. I was going through the Oracle Documentation of 12.1 and came across Chapter 15 Section 15.3 (Sharing Cursors for Existing Applications).

http://docs.oracle.com/database/121/TGSQL/tgsql_cursor.htm#TGSQL94748

With Non-Default Cursor_Sharing, the documentation says, it has to search for an Identical Statement in the Shared Pool. If not found, then a Similar Statement. In this context, wanted to know, what is the difference between the two ?

For example, if I execute (Cursor_Sharing=Force)

select * from emp where empno=7369;
then
select * from emp where empno=7844;

Both of these will be hashed to a same parent cursor. What is the extra work that can be caused by non-default value ?

Thanks in advance.
Chris Saxon
September 14, 2015 - 2:24 am UTC

Consider the following example:

SQL> select * from t1 where owner = 'QWE';

no rows selected

SQL> alter session set cursor_sharing = force;

Session altered.

SQL> select * from t1 where owner = 'QWE';

no rows selected

SQL> select sql_text from v$sql
  2  where lower(sql_text) like 'select * from t1%';

SQL_TEXT
----------------------------------------------------------------
select * from t1 where owner = 'QWE'


Even though cursor sharing was force, notice we looked for an exact match first...and we were done. 

SQL> select * from t1 where owner = 'ABC';

no rows selected

SQL> select sql_text from v$sql
  2  where lower(sql_text) like 'select * from t1%';

SQL_TEXT
----------------------------------------------------------------
select * from t1 where owner = :"SYS_B_0"
select * from t1 where owner = 'QWE'


This time we didnt find an exact match, so we did the extra work in converting it to bind variables and then searching for a match
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.