What is the difference between a bind-sensitive and bind-aware cursor?

The terms "bind-sensitive" and "bind-aware" are used when explaining Oracles Adaptive Cursor Sharing (ACS) feature.

Adaptive cursors sharing makes it possible for a single statement to use different execution plans, depending on the values of its bind variables.

Oracle explains the difference between bind-sensitive and bind-aware in their documentation like this:


"A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable."

and

"A bind-aware cursor is a bind-sensitive cursor that is eligible to use different plans for different bind values."


In other words, when a bind-sensitive cursor have been executed, the database saves the execution statistics for the new values, and compares them with the values used previously.

If (and only if) there is a significant change, Oracle marks the cursor as bind-aware.
During future executions of the cursor, the CBO will generate a plan based on the bind values and their cardinality estimates.

How to purge a cursor from the shared pool using dbms_shared_pool

Find the address and hash value for the particular SQL you're looking for:

select address, hash_value from v$sqlarea where sql_id like 'bjkd7pcyfns86';

ADDRESS	HASH_VALUE
0000000085CD2838	1021993222

Execute the purge procedure:

SQL> exec dbms_shared_pool.purge('0000000085CD2838, 1021993222','C');

Note that the name of the object is the combined values of address and hash value from v$sqlarea.
The value C is what you want to use for cursors.

Note that even if an SQL Plan Baseline is dropped from the SMB, it doesn't mean it won't get used if it is still in the cache.

If you have dropped a plan from your SMB that you would like the optimizer to ignore, you must clear out the cursor that previously using the now dropped SQL Plan baseline from the shared pool using the command above. 

sources:

Oracle Documentation