Re: Excessive child cursors

Terry,

I'd clarify that all of the children that concern you are being reused over a period of time. I.e. take couple samples and clarify trends of the following characteristics: total number of children, present(with loaded context heap), pinned, invalidated - basically all relevant to cursor life cycle attributes which you can find in v$sql or v$sqlarea.

I'd also double check that all children that are being reused over time are all bind mismatched.

If you find that significant portion of the cursors is not used, than you might want to think about flushing the shared pool. If such theory doesn't apply, then you might want to go deeper into specifics of the cursors to see if bind mismatch happened due to the presence of the histograms.

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

On 9/27/07, Terry Sutton <terrysutton_at_usa.net> wrote:
>
> Vlad,
>
> Thanks for your imput. Bug 3406977 doesn't seem relevant because I'm
> seeing bind_mismatch in v$sql_shared_cursor, which the bug says wouldn't be
> the case. Note 285447.1 doesn't seem to apply because they're not using
> PL/SQL. Note 261020.1 doesn't sem to apply, again because I'm seeing
> bind_mismatch. Note 364845.1 seems to say that cursor_sharing = force
> would result in fewer children, which is what I expect.
>
> Since we changed to force, we haven't seen a reduction in the number of
> children, and that's what I'm trying to make happen. I'm suspecting I may
> have to flush the shared pool to get this to happen, but don't want to flush
> unless I'm pretty confident that will fix the problem.
>
> --Terry
>
> Terry,
>
> After changing sharing to force do you see a change in the number of
> children and in the reasoning? I'm asking because there were instances of
> 'force' being a problem by itself besides its a little cryptic meaning.
> I.e. Bug 3406977 (not your release level though). There is still room for
> cursors not being shared after you set the parameter to 'force'.
>
> Go through the notes 285447.1, 261020.1 and 364845.1 if you didn't do so
> yet.
>
> On the side note. Did you try to alleviate the issue by increasing
> session_cached_cursors? Also check the alert log.
>
> Vlad Sadilovskiy
> Oracle Database Tools
> http://www.fourthelephant.com
>
> On 9/26/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