Re: Excessive child cursors

Terry,

In that case flushing would hardly help at all (unless there is an Oracle bug of which I don't know). You might flush unpinned cursors, but the new children would be introduced over time.

I'm sorry if you feel bad when I'm trying to double check your findings. Happens to me too. Could you please paste a report of the delta of VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, LOADS, INVALIDATIONS, PARSE_CALLS from V$SQLAREA for top 10 parents (children count wise) and, if at all possible, same for all their children - V$SQL.

Cheers,

 Vlad Sadilovskiy
Oracle Database Tools
http://www.fourthelephant.com

On 9/27/07, Terry Sutton <terrysutton_at_usa.net> wrote:
>
> Yes, that's my problem. We still have too many child cursors after
> switching to force.
>
> --Terry
>
> 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_at_usa.net> 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
> >
>
>

--
http://www.freelists.org/webpage/oracle-l