CURSOR_SHARING=SIMILAR

CURSOR_SHARING=SIMILAR

With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

Is the operator NEITHER of   ‘=’ or ‘!=’
OR
Are there Column Histograms present on the column.

If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, =, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see

SQL> select count(*) from dba_objects where object_id>3;

  COUNT(*)
----------
     50363

SQL> select count(*) from dba_objects where object_id>5;

  COUNT(*)
----------
     50361

SQL> select count(*) from dba_objects where object_id>6;

  COUNT(*)
----------
     50360

SQL> select count(*) from dba_objects where object_id>4;

  COUNT(*)
----------
     50362

 

SQL> select sql_id,child_number from v$sql where sql_text like '%dba_objects%';

SQL_ID        CHILD_NUMBER
------------- ------------
bg5mpjkmsjg7h            0
bg5mpjkmsjg7h            1
bg5mpjkmsjg7h            2
bg5mpjkmsjg7h            3
bg5mpjkmsjg7h            4
bg5mpjkmsjg7h            5
bg5mpjkmsjg7h            6
bg5mpjkmsjg7h            7
2r3tjryp06xfx            0: