Re: Multiple SQL version count with cusror_sharing=similar

Some comments:

1. Without any statistics and enabled dynamic sampling with 
CURSOR_SHARING=SIMILAR, one could argument that every distinct literal value 
passed potentially leads to a different execution plan due to the dynamic 
sampling performed, therefore this cursor is marked as unsafe to share and 
you'll get a different child cursor per different literal value passed.

So CURSOR_SHARING=SIMILAR and dynamic sampling will cause a lot of child 
cursors, since it's by definition unsafe to share. Note that CURSOR_SHARING 
similar doesn't mean that you get only a new child cursor if the plans are 
actually different. You get different child cursors since you *potentially* get 
different execution plans. It's perfectly valid with CURSOR_SHARING=similar to 
have dozens of child cursors that share the same plan.

What you're looking for is (partly) implemented by the Adaptive Cursor Sharing 
(ACS) introduced in 11g, that attempts to minimize the number of child cursors 
generated. In fact, with Adaptive Cursor Sharing it's recommended to use 
CURSOR_SHARING=FORCE instead of SIMILAR to minimize the number of child cursor 
generated.

Note however that the ACS doesn't work very well if you have dramatic 
differences in execution runtime and high aging rate of the SQLs as Kerry 
Osborne reports here:

http://kerryosborne.oracle-guy.com/2009/06/oracle-11g-adaptive-cursor-sharing-acs/

Its current implementation might however work very well if the SQLs stay in the 
shared pool and you don't suffer from "killer queries" that are using the 
"wrong" plans.

2. The case that you've had when changing the CURSOR_SHARING to FORCE and a 
version of the statement without replaced literals popped up might be the same 
as observed here:

http://forums.oracle.com/forums/thread.jspa?messageID=3573497&#3573497

Try to flush the shared pool twice after changing the CURSOR_SHARING setting.

3. What I can't answer is why you get no literal replacement when switching 
bind variable peeking off. I couldn't reproduce in 10.2.0.4 Win32, depending on 
the order of the steps executed it might be a issue and described above (2.)

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

> Hi,
> 
> First of all thanks a lot Cheng for the clue.
> 
> I just turned-off dynamic sampling and it behaved as expected. Means, 1
> version_count in case of similar and exact cursor_sharing setting.
> 
> 1) But, even in case of dynamic sampling, table data is not skewed and
> histogram is of no use in this case. Thi can be checked even from
> plan_hash_value of child cursors. Execution plan is same. Then why Oracle is
> creating multiple child.
> 
> 2) Disabling the bind value peeking, resulting in three parent cursors. I
> didn't know any reason for that.
________________________________________________________________
Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate
für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/

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

Other related posts: