Re: Multiple SQL version count with cusror_sharing=similar
Date: Wed, 01 Jul 2009 10:41:04 +0200
Message-Id: <693995020_at_web.de>
Some comments:
- 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�
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-lReceived on Wed Jul 01 2009 - 03:41:04 CDT