CURSOR_SHARING=SIMILAR available in Oracle 12c, or not ?

It was announced before Oracle Database 12c was released that CURSOR_SHARING=SIMILAR will be deprecated (MOS Note 1169017.1). Moreover, according to the same note the ability to set this value will be removed.

And indeed, when I looked into 12c documentation, I found EXACT and FORCE being the only available values where the former is default value. (link)

I decided to play around a bit and immediately figured that SIMILAR could still be assigned to CURSOR_SHARING.

test[12,133]> ALTER SESSION SET CURSOR_SHARING=SIMILAR;

Session altered. 

When I tried to assign an invalid value to CURSOR_SHARING using SQL*Plus I got an error message that says EXACT, SIMILAR and FORCE are acceptable values for CURSOR_SHARING parameter.

test[12,133]> ALTER SESSION SET CURSOR_SHARING=foo;
ERROR:
ORA-00096: invalid value FOO for parameter cursor_sharing, must be 

from among SIMILAR, EXACT, FORCE 

Couple of reasons I can think of as why SIMILAR is still allowed. The first, it may be some sort of backward compatibility (so that applications that explicitly set this value don't break when migrated to 12c); or it may be that CURSOR_SHARING=SIMILAR is still a valid option if COMPATIBLE parameter is set to an earlier release where the value was supported. (didn't have time to play with COMPATIBLE parameter)

Anyway, my main question was how Oracle will behave if CURSOR_SHARING is set to SIMILAR in 12c.

I ran a quick test. I created one table, named TAB1 and then executed one sub-optimally shareable statement 5 times passing 5 different literal values for each of the executions.

When CURSOR_SHARING is set to SIMILAR and a suboptimal statement using literals is executed, Oracle would not share any of the existing cursors and will perform a hard parse.

CREATE TABLE tab1 AS (SELECT * FROM user_objects);

ALTER SESSION SET CURSOR_SHARING=SIMILAR;

SELECT COUNT(1) FROM tab1 WHERE object_id>1;

SELECT COUNT(1) FROM tab1 WHERE object_id>2;

SELECT COUNT(1) FROM tab1 WHERE object_id>3;

SELECT COUNT(1) FROM tab1 WHERE object_id>4;

SELECT COUNT(1) FROM tab1 WHERE object_id>5; 

For each of these statements the literals were replaced with bind variables hence the SQL that was parsed has sql id c73v21bgp4956 and text like the one below:

SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"

After running the statements from above, I observed the content in v$sqlarea, v$sql and v$sql_shared_cursor views to see if Oracle did a hard parsing.

 
set line 200
col sql_text format a80
SELECT sql_id, sql_text, executions, child_number FROM v$sql WHERE sql_id='c73v21bgp4956';

SQL_ID        SQL_TEXT                                                                         EXECUTIONS CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------- ------------
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             5            0

As you can see Oracle reused the same child cursor 5 times, which means even though CURSOR_SHARING was set to SIMILAR it behaved as it was set to FORCE.

I ran the same example from above in 11.2.0.2 database. This time Oracle behaved as expected when CURSOR_SHARING is set to SIMILAR and used 5 different child cursors.

 
set line 200
col sql_text format a80
SELECT sql_id, sql_text, executions, child_number FROM v$sql WHERE sql_id='c73v21bgp4956';

SQL_ID        SQL_TEXT                                                                         EXECUTIONS CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------- ------------
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            0
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            1
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            2
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            3
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            4

When I checked v$sql_shared_cursor I found that all of the child cursors had Y for HASH_MATCH_FAILED column which is expected when suboptimal statement is executed with CURSOR_SHARING=SIMILAR.

Conclusion

Even though setting SIMILAR to CURSOR_SHARING initialization parameter is still acceptable value, it looks Oracle ignore it and behave as if FORCE is used. (this may not be true if compatible parameter is set to an earlier release, like 11.2.0.2 for example. I didn't have time to test this scenario)

Hope this helps.