Oracle introduced CURSOR_SHARING option for legacy applications that were not written to use bind variables. This was more of temporary fix. Oracle assumed that customers/vendors would rewrite their applications to use bind variables. The assumption seems to be proving wrong; Today CURSOR_SHARING has become more of a positive feature and solution for all shared pool issues.  This is not true. As Per Oracle Metalink document 957431.1, Oracle recommends setting CURSOR_SHARING to EXACT. The consequences of not setting this parameter to EXACT are following:

1. Errors with cache algorithm;
2. Migration issues when upgrading from 9i to 10g or higher;
3. Bugs related to cursor sharing.

Until Oracle 11gR1, Oracle limited the number child cursors for a given parent to 1024. Once this number is reached, Oracle invalidated the parent and all related child cursors. The 1024 limit was introduced to avoid performance issues associated with soft parsing. In other words hard parsing performs better than soft parsing when there are too many dependent child cursors.

With 11gR2, Oracle removed the 1024 limit knowing that there are issues with soft parsing. The reasoning behind this is the improvements made to adaptive Cursor Sharing feature.  However adaptive Cursor Sharing does not work well with CURSOR_SHARING=SIMILAR as it is very restrictive and overrides improvements made to adaptive cursor sharing . Therefore it will be deprecated in Oracle version 12.

Tagged: 5155885, 8453245, adaptive Cursor Sharing, CURSOR_SHARING, CURSOR_SHARING 10g, CURSOR_SHARING 11g, CURSOR_SHARING 9i, CURSOR_SHARING=EXACT, CURSOR_SHARING=FORCE, CURSOR_SHARING=SIMILAR