How to force cursor sharing for the sqls performance

We have seen that similar looking sqls are running but each has a different explain plan or has a hash value. We tune one and the other one complains about something similar or same.

The sqls are same but database thinks they are different and even after tuning the values where state name is different from IL for Illinois to OK for Oklahoma the plan will change.

For this oracle has provided cursor sharing where we can allow similar looking sqls which only has different values or upper and lower case to be ignored and share improved performance tuned plans.

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

sql> show parameter cursor_sharing;

sql> alter system set cursor_sharing='FORCE' scope=both sid='*';

sql> show parameter cursor_sharing;

Should show the force value from exact or similar. 

Hope this helps!

Ahmad