We have a 1 gigabyte shared pool in our production instance (Oracle 8i), and some users are allowed to issue ad hoc queries. On a few occasions the users succeeded in bringing the instance to a halt by issuing statements with literals, i.e., by issuing 25,000 INSERT statements with literals. The instance hung, and it was not possible to establish any new connection, even with INTERNAL.
We do not want to set CURSOR_SHARING=FORCE due to the unpredictable behavior. Is it that easy to bring the Oracle instance down? What are we missing here? What parameter settings should we look at?
Your Shared Pool size is very large. It is rare that a 1 gigabyte Shared Pool is required. The large size might be causing your problems.
I understand the literals in the application is causing problems too. But you have taken one correct action in setting CURSOR_SHARING to FORCE. Another correct action would be to upgrade to Oracle 9i or 10g where you can set CURSOR_SHARING to SIMILAR.
I'm not fully convinced that inserting 25,000 rows, even with literal values, is bringing down your database. That shouldn't happen. You would need to look at your Alert Log at the time of the crash and examine any trace files that were generated at the time of the crash to be certain of the cause of the crash.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.