Re: Excessive child cursors
- From: "LS Cheng" <exriscer@xxxxxxxxx>
- To: terrysutton@xxxxxxx
- Date: Thu, 27 Sep 2007 08:33:48 +0200
hi
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
SIMILAR
with FORCE you still have 40-60 child cursors?
thanks
--
LSC
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 10.2.0.3 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:
