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@xxxxxxxxx <mailto:andrew.kerber@xxxxxxxxx>> 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@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> 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@xxxxxxxxxx
<mailto:l.flatz@xxxxxxxxxx> 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
>
--
//www.freelists.org/webpage/oracle-l
-- Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
