Alter Cursor_Sharing parameter to use bind variables instead of literal value in Oracle

Cursor_sharing parameter is used for the application which cause high hard parsing in Oracle.
Oracle suggest to use bind variables in application SQL Queries instead of literal value.
It’s good for performance point of view.
To overcome this problem of application, Oracle have cursor_sharing parameter.

Cursor_sharing parameter has two values:

EXACT: Statements which are only identical including literal value in where condition to share the same cursor. Otherwise it will go for new parsing.
FORCE: It replace the literal to bind variables which cause different SQL to use same cursor. it avoid parsing by replacing literal to bind variables.

Default value of Cursor_Sharing parameter is EXACT.

Change the Cursor sharing parameter value

Note: Don’t require reboot the database to change the parameter value in effects.

--Change the value to EXACT
SQL> ALTER SYSTEM SET CURSOR_SHARING=EXACT SCOPE=BOTH;

-- Change the value to FORCE
SQL> ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=BOTH;