Re: Excessive child cursors

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