Re: Excessive child cursors

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@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: