Re: cursor_sharing setting

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 10 Feb 2020 20:59:15 -0500
Message-ID: <b036cd0f-48cd-e1db-0ec0-a827e643a220_at_gmail.com>



Hi!

Oracle optimizer gets in its initial plan from playing peekaboo with the bind variables. It checks the values of the bind variables and uses histogram and statistics to optimize the statement for those particular values. That is called bind variable peekaboo. In Oracle 11g, that's all. In oracle 12c and newer, there are so called "adaptive plans". Oracle re-evaluates the plan based on the cardinality feedback mechanism and can change the plan dynamically, using what Jonathan has aptly named "adaptive mayhem". However, the plan change is restricted to changing join method. The article in which the historic phrase was coined is here:

https://jonathanlewis.wordpress.com/2016/08/02/adaptive-mayhem/

There are also plan directives which add to the mess. And baselines and SQL profiles. Of course, you don't have to worry, the database is completely autonomous and doesn't need a DBA who understands all those mechanisms. Here is a little something about plan directives from Tim Hall: https://oracle-base.com/articles/12c/sql-plan-directives-12cr1. Essentially, there are so many mechanisms which influence execution plans that every plan is a little surprise and life of a DBA is never boring.

Regards

On 2/10/20 2:16 PM, Cee Pee wrote:
> Thanks everyone.
>
> Andrew:
>
> NAME                                          VALUE            DEFLT
> --------------------------------------------- ------------------------
> ------------------------
> _optim_peek_user_binds                        TRUE         TRUE
>
>
> "Bind varaibles for a partition key should inhibit from partion stats
> being used on parsing." I think the optimizer may not know to use
> indexes or not, etc. which applies even to an OLTP system. I
> remember reading about those things in Christian's TOP book. (Thank
> you Christian if you are reading this thread, a great book).
>
> what does that parameter do? I couldnt get any good results searching.
> Does it let the optimizer peek at bind variable values once a while?
>
>
> On Mon, Feb 10, 2020 at 11:24 AM Andrew Kerber
> <andrew.kerber_at_gmail.com <mailto:andrew.kerber_at_gmail.com>> wrote:
>
> What is the _optim_peek_user_binds setting?
>
> In any case, if the warehouse has been running for some time
> without serious problem, you probably dont want to change that
> setting without understanding why it was set the way it was to
> begin with.  Of course, the answer is likely to be that it was set
> that way 10 years ago in oracle 9i and never changed it, but it is
> still not something to change without testing.
>
> On Mon, Feb 10, 2020 at 11:13 AM Mladen Gogala
> <gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com>> wrote:
>
> I would not set it anywhere. CS = FORCE is a source of many
> optimizer
> bugs and is only recommended in case of an emergency, where
> one needs to
> break the glass first.
>
> Regards
>
>
> On 2/10/20 7:54 AM, l.flatz_at_bluewin.ch
> <mailto:l.flatz_at_bluewin.ch> wrote:
> > Hi,
> >
> > I would not set cs=forced in a DWH. These a logrunning
> queries and the
> > plans should be as  good as possible.
> > Parse time will normally be a lesser concern.
> > Just to give an example: Bind varaibles for a partition key
> should
> > inhibit from partion stats being used on parsing.
> >
> > Regards
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 11 2020 - 02:59:15 CET

Original text of this message