Question: I just turned-on
cursor_sharing=similar in Oracle 9i and now some of my large SQL
statements get the error "ORA-03113 no more data to read from the
socket". 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 MetaLink
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.
My tips for using cursor_sharing include:
