CURSOR_SHARING parameter vs High Version count in Oracle

~ CURSOR_SHARING parameter Vs High Version count  in Oracle
~ CURSOR_SHARING parameter setting in Oracle : boost your database performance
~ About the parameter:

Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects) cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter.

Note:  The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per MOSC Note 1169017.1

Best to Keep : cursor_sharing=EXACT in OLTPs

Other notes on cursor_sharing:
-------------------------------------------------
SQL that is generated by ad-hoc query tools with hard-coded literal values embedded within the SQL. As we know, hard-coded literal values make the SQL statements non-reusable unless cursor_sharing=force is set in the Oracle initialization file. Shops that are plagued with non-reusable SQL can set cursor_sharing=force.

SQL in Oracle to see if your SQL is using bind variables.  Systems that need cursor sharing are littered with SQL that contains literal values, like this:

select cust_id from customer where cust_name = 'JONES';

These system that benefit from cursor_sharing have low "executions" because the SQL cannot be re-used it is only executed once. (as seen in the v$sql view) and the need for cursor sharing van be seen by Oracle hard parse (sharing criteria) elapsed time.  Other symptoms indicating the need for cursor_sharing include:

> High CPU attributable to shared pool activity
> High hard parses
> Low number of executions for individual SQL statements

Parameters for CURSOR_SHARING :
-------------------------------------------------
CURSOR_SHARING determines what kind of SQL statements can share the same cursors. Values:

•FORCE : Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.

•SIMILAR : Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

•EXACT : Only allows statements with identical text to share the same cursor.


To Change cursor_sharing parameter value:
-------------------------------------------------

SQL> show parameter cursor_sharing ;

NAME                                 TYPE        VALUE
------------------       ------------------------ --------------------------------
cursor_sharing                   string        SIMILAR

SQL> alter system set cursor_sharing = 'EXACT' scope=spfile;

In RAC:
SQL> alter system set cursor_sharing = 'EXACT' scope=spfile sid='*';

Bounce the database to apply immediate effect.

Note: If you are (DBA) observing more counts coming against any sql_id ( e.g., see the query) then assume more version counts coming for that query. Confirm that more hard-coded values are used instead of bind variables. May be cursor_sharing parameter value is set as 'similar'. Change this value to 'exact' to maximize the performance.
e.g.,
select count(1) from v$SQL_shared_cursor where sql_id='2ht73m99gww6h'
--in RAC
select count(1) from gv$SQL_shared_cursor where sql_id='2ht73m99gww6h'

If values are in terms of 100 then more version counts are coming.

Hope this may help you. Always use oracle support to read more details.
Thanks