CURSOR_SHARING explained

In Oracle8i, release 2 (version 8.1.6), Oracle introduced a new feature called CURSOR_SHARING. Cursor sharing is an 'auto binder' of sorts. It causes the database to rewrite your query using bind variables before parsing it. This feature will take a query such as:

select * from emp where ename = 'KING'; 

and will automatically rewrite it as: 

select * from emp where ename = :SYS_B_0

CURSOR_SHARING was introduced to help relieve pressure put on the shared pool, specifically the cursor cache, from applications that use literal values rather than bind variables in their SQL statements. It achieves this by replacing the literal values with system generated bind variables thus reducing the number of (parent) cursors in the cursor cache.

In this great article by Maria Colgan the possible values of CURSOR_SHARING are explained.

Also check out this great collection of blog posts related to cursor sharing from the Oracle Optimizer blog.

Enjoy!
Oracle Learner.