Re: Multiple SQL version count with cusror_sharing=similar
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 01 Jul 2009 10:41:04 +0200
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�
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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
