Excessive child cursors
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Sep 2007 19:51:53 -0700
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:
