Use of Cursor_Sharing parameter in Oracle

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;

2 thoughts on “Use of Cursor_Sharing parameter in Oracle

  1. Zubair Ahmed Mohammed

    Please check for force…

    –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=EXACT SCOPE=BOTH;

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.