Re: Excessive child cursors

  • From: "LS Cheng" <exriscer@xxxxxxxxx>
  • To: terrysutton@xxxxxxx
  • Date: Thu, 27 Sep 2007 08:33:48 +0200


if you have histograms for those columns in your predicates then for each
cursor you would have X cursor per different column value, this is with

with FORCE you still have 40-60 child cursors?



On 9/27/07, Terry Sutton <terrysutton@xxxxxxx> wrote:
>  I'm trying to resolve a client situation which involves a lot of hard
> parsing and library cache latch waits, and I'm hoping to bounce some ideas
> off people.
> The database is on Solaris.  cursor_sharing was set to
> 'SIMILAR'.  No, they don't use bind variables, and that's not going to
> change soon.
> I've found that we have a lot (40-60) of child cursors for some SQL
> statements.  When I look at v$sql_shared_cursor I find that most of the
> child cursors have Y for bind_mismatch.  But when I look at
> v$sql_bind_metadata the DATATYPE and MAX_LENGTH are identical for all the
> cursors.  So I'm concluding that the bind mismatches are due to the
> cursor_sharing='SIMILAR' parameter causing new child cursors due to
> different values for the literals which are being converted to bind
> variables (please correct me if my conclusion is unwarranted).
> So I'm trying to figure how to solve this issue (short of having the
> client use bind variables properly).  It seems that cursor_sharing='FORCE'
> would eliminate the bind mismatch problem, since the values of the literals
> would all convert to the same bind variables.  We've changed the
> cursor_sharing setting, but we're still having library cache latch waits.
> I'm wondering if the problem is that we still have the child cursors in the
> shared pool, and each soft parse has to go through the list of child cursors
> for a given parent to find a matching one.  Would flushing the shared pool
> solve this part of the problem?  Or am I missing something else obvious?
> I'm a bit wary of flushing the shared pool on a DB that is already having
> contention problems, unless I'm very confident of having it resolve the
> issue.
> Any input is welcome, even if it's "hey idiot, you missed this". :-)
> --Terry

Other related posts: