Hi Rajeshwaran,
Let me address each of your questions.
1) "Why setting cursor_sharing=force in 12c doesn't replace literals with bind variables?"
There is no known issues with setting cursor_sharing=force in 12c. In my 12.2.0.1 environment it works just as expected:
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- -------
cursor_sharing string EXACT
SQL> alter session set cursor_sharing=force;
Session altered.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- -------
cursor_sharing string FORCE
SQL> select object_name from my_objects where object_id=100;
OBJECT_NAME
-----------
SEQ$
SQL>
SQL> select * from table(dbms_xplan.display_cursor(format=>'Typical'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID fu2bja6dgn1yg, child number 0
-------------------------------------
select object_name from my_objects where object_id=:"SYS_B_0"
Plan hash value: 880823944
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 397 (100)|
|* 1 | TABLE ACCESS FULL| MY_OBJECTS | 1 | 40 | 397 (1)| 00:00:01
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=:SYS_B_0)
2) "The explain plan command output from 11g and 12c were different after the SPM configuration. This seems to be that from 12c and above the cursors generated by the explain plan command are **Sharable**? is that correct?"
No, the cursors generated by the explain plan command are not **sharable** in 12c. They are still marked unsharable. If you execute your explain plan command 3 times and then query v$sql what you will see is the follows:
SQL> SELECT sql_id, sql_text, executions, child_number
2 FROM v$sql
3 WHERE sql_text like '%explain plan for select * %';
SQL_ID SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- -------------------------------------- ---------- ------------
bhnjby76zp21m explain plan for select * from t wher 1 0
e object_id = 100
bhnjby76zp21m explain plan for select * from t wher 1 1
e object_id = 100
bhnjby76zp21m explain plan for select * from t wher 1 2
e object_id = 100
There distinct cursors one for each execution.
The only reason you see the explain plan output showing the SQL plan baseline was used in your 12c environment is because you didn't do the literal replacement. So, your SQL plan baseline was created for the SQL statement with the literal value "select * from t where object_id = 100". Hence the explain plan found a match.
3) we have set the "optimizer_capture_sql_plan_baselines" = FALSE in both 11g and 12c. could you help me to understand why there is an entry in dba_sql_plan_baselines with ORIGIN = 'AUTO-CAPTURE' after the second execution of the cursor?
Once a SQL plan baseline exists for a SQL statement, any new plan found for that SQL statement during a hard parse will be automatically added to the SQL plan baseline with the ORIGIN of "AUTO-CAPTURE" and the ACCEPTED attribute set to NO. The automatic additional of new plans to an existing SQL plan baseline is not controlled by the parameter "optimizer_capture_sql_plan_baselines".
The parameter "optimizer_capture_sql_plan_baselines" only controls the automatic creation of a SQL plan baseline for any repeatable SQL statement.