Oracle cursor_sharing=similar tips

Question:  I just turned-on cursor_sharing=similar in Oracle 9i and now some of my large SQL statements get the error:

When I remove cursor_sharing=similar, it works fine.

Answer:  First, note that Oracle9i was de-supported in 2007, and you may want to upgrade.  There are many possible causes for the ORA-03113 error.  See MOSC Note:17613.1 "ORA-03113":

Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects) cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter. This use of cursor_sharing=similar allows for bind variable "peeking", and in my opinion, it's too buggy to use until Oracle 11i, when you get adaptive cursor sharing.  See here for details:

The dynamic shop often has SQL that is generated by ad-hoc query tools with hard-coded literal values embedded within the SQL and the library cache is clogged with non-reentrant SQL (you can tell because the SQL has literals in the WHERE clause and it always has executions=1).

As we know, hard-coded literal values make the SQL statements non-reusable unless cursor_sharing=force is set in the Oracle initialization file.

Note:  The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per MOSC Note 1169017.1

Note:  For testing purposes, you may want to turn-off optimizer bind variable peeking and this can be done in several ways::

• Re-set the _optim_peek_user_binds hidden parameter.

• Re-analyze using dbms_stats with the argument:  method_opt=> 'for all columns size 1';

My other tips for using cursor_sharing include: