Re: comparing tkprof results between cursor_sharing = exact or similar

On Mar 5, 9:37 am, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> On Sun, 04 Mar 2007 19:34:18 -0800, Charles Hooper wrote:
> > You might want to try CURSOR_SHARING=FORCE, fix the application, add
> > hints to the SQL statment so that Oracle does not need to spend as much
> > time resolving execution plans, or see if the complex view can be
> > simplified.
>
> The setting of CURSOR_SHARING to "FORCE" will cause optimizer to start
> peeking into bind variables frantically, sometimes with the interesting
> results. In other words, it introduces uncertainty principle to your
> database and, in conjunction with histograms, can lead to really
> interesting plans. God may play with dice, but I don't like to do so.
> When Turing created the world some time ago, he created constants and
> there are variables. That was an intelligent design. Blurring the
> difference between them is not a good idea from the philosophical point
> of view.
>
> --http://www.mladen-gogala.com

Are you sure that you are not thinking of CURSOR_SHARING=SIMILAR? SIMILAR potentially causes Oracle to peek at bind variables on every execution call (the EXEC lines in a 10046 trace will show this). If you pass in 20 different sets of bind variables with CURSOR_SHARING=SIMILAR set, you could see a (hard) parse call (mis=#) on each of the EXEC calls in a 10046 trace. With CURSOR_SHARING=FORCE set, Oracle 9i and 10g can peek at the original values when the bind variables are generated in order to create a suitable execution plan for the _original_ values passed in on the parse call. Once the execution plan is created, Oracle does not attempt to hard parse the SQL statement as new bind variables are provided. If out of the ordinary constants were provided on the initial hard parse, Oracle could end up with a very bad execution plan for the future executions of the same SQL statement submitted with different constants. 10g can overcome some of the problems when a very bad execution plan is generated.

I could be confused about the CURSOR_SHARING parameter. We ran 8i and 10g with CURSOR_SHARING=FORCE set for at least five years, and only changed that setting due to problems encountered after applying the October 2006 critical update patch for 10.2.0.2.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Mar 05 2007 - 10:02:04 CST