I don't have my specific bug notes handy (I'll try to find them later), but one
of the several things that can go wrong that a particular patch fixes goes like
this:
1) Some degree of parallelism is chosen.
2) All the children are spawned in a "mode" where Oracle reparses rather than
grabs from memory the parent plan (guessing perhaps quicker for each to parse
than each to look up parent's plan, ignoring the possible costs of the dynamic
sampling)
3) The dynamic sample is near a tipping point to a different plan, and some of
the children don't match the parent hash, which causes them to try again
(disaster loop resulting in possibly conversion to serial by timeout or a
blow-up error somewhere)
IF memory serves, the patch is to do a lookup of the hash after one parse try
(still ignoring the dynamic sampling cost, I think, in making that decision to
parse rather than look up) instead of a try again to match in a loop.
Without my notes and the exact bug number and patch handy, please treat this as
slightly suspect. It is probably worth a quick look if you have the MOS note,
etc. in front of you.
Cynical remark: Seems like we need a recursive cost estimate to decide whether
the cost of dynamic stats exceed the likely cost of the query with "any old
plan" before we do dynamic query.
This is sort of the old argument about "let's just price the rule plan and if
the cost is less than the likely cost to search for a better plan, just use it."
That leads to a consideration of how many times the query might be run, so even
if it costs a bunch to get a slightly better (lower cost predicted) plan, if
the plan will be used thousands or millions of times it is a win.
okay, logic chain getting long, but stay with me:
Dynamic sampling says, hey, forget that we may have spent time already getting
a good plan, I'm going to go spend more on each execution checking whether
maybe there is an even better plan.
So there needs to be some floor on the cost of such a plan already parsed
before we spend time trying to get an even better plan that takes longer to
arrive at than the existing plan takes to execute. (Assuming the goal is
minimum cost, not finding the "perfect plan.")
Sigh. Hope this helps.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mladen Gogala
Sent: Monday, January 29, 2018 7:47 PM
To: Jared Still
Cc: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: Re: AUTO_SAMPLE_SIZE is every row
Replies in-line
On 01/29/2018 10:26 AM, Jared Still wrote:
Mladen,
Which version of Oracle was this?
Bug 17632286 - ORA-600 [qksdsUpdTabStatsCbk:0] when dynamic_sampling=11 (Doc ID
Also, what were some of the crippling bugs?
