Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS I have the following scenario:


Oracle 11.2.0.3
A 3rd party application, that requires cursor_sharing = TRUE
A table (T2310) with a indexed field (C1000000161) that has no duplicated values
Result Cache Mode = force (don't believe this is relevant in this case, but it's here to justify its appearance in the explain)


The 3rd party application is running queries like the one below (I have to use the hint here, since the explain won't show the FTS):

    SQL&gt; select /*+ GATHER_PLAN_STATISTICS */ C1000000161 
    &gt; from aradmin.T2310 
    &gt; where C1000000161 = 'INC000002137945' or ('a' = 'b');


This kind of query ends up forcing a full table scan:

SQL&gt; SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=&gt;'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  f3njwvkb0yf1x, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ C1000000161 from
aradmin.T2310 where C1000000161 = :"SYS_B_0" or (:"SYS_B_1"
= :"SYS_B_2")

Plan hash value: 1862236576

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |      1 |        |      0 |00:03:00.20 |    3964K|    161K|
|   1 |  RESULT CACHE      | bkhyzz7n945syc4numpbs27y20 |      1 |        |      0 |00:03:00.20 |    3964K|    161K|
|*  2 |   TABLE ACCESS FULL| T2310                      |      1 |  44030 |      0 |00:03:00.20 |    3964K|    161K|
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((:SYS_B_1=:SYS_B_2 OR "T2310"."C1000000161"=:SYS_B_0))

Result Cache Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
------------------------------------------------------

   1 -


As we can see in the explain, the literals are replaced by bind variables, and I believe this is what is forcing the FTS. If you remove the literal comparison, the index is used as expected.

So, my question is: is there any way to avoid this behavior from the Database side?
You can not correct a bad implemented application from the database side in such a way that it will always work as expected. With cursor sharing the database tries to prevent parsing. This works when the application uses bind variables when working on item/person level and literals when working on group/class level.
You could try cursor_sharing force. This could correct some queries but at the same time hurt others.
You could try sql plan management but in my experience, when you really need it, it fails. Especially with literal littered applications. Sometimes you have luck but don't underestimate the amount of work you need to put in them. Auto sampling of plans is not the best idea here ....