Shared Cursor Issues and Flushing the SHARED_POOL (Doc ID 1288981.1)

Last updated on MAY 12, 2022

Applies to:

Symptoms

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

Changes

No changes required if this is new implementation
Changes in client, application or database version and/or patchset may introduce the problem

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.