Shared Cursor Issues and Flushing the SHARED_POOL
Last updated on FEBRUARY 20, 2018
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
One of the more difficult problems to diagnose are regarding Shared or Child Cursor issues. Many different types of errors may occur due to the nature of this memory / cursor problem.
In older versions of Oracle changing the CURSOR_SHARING parameter to EXACT was a common resolution to many cursor problems.
Over time we now see many different RDBMS versions and patchset levels with their own unique cursor issues.
That stated, many of these problem share similar symptoms:
- The error appears or problem may be found to only reproduce through an application
- The problem/error may go away if the query is
- only slightly altered or
- if the Shared Pool is flushed
- If trace files are generated (including 10046 traces level 4 or errorstack for the error) we may find it is based on a common query shared by several different users
- The queries usually will show no explicit schema pre-pending the object name
- The application / code may alter the user dynamically before executing the queries
- You may find several object names and queries duplicated across several schemas
- Triggers, MVIEWS or packages may also be required for the problem to occur
- Bouncing the database, instance, application tier or front end may temporarily relieve the problem (but rarely completely)
- The problem may occur more often during load (this is not required however)
- ORA-942 Fixed By Flushing Shared Pool
No changes required if this is new implementation
Changes in client, application or database version and/or patchset may introduce the problem
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms