RE: AUTO_SAMPLE_SIZE is every row

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 30 Jan 2018 07:15:33 -0500
Message-ID: <051901d399c4$203f1080$60bd3180$_at_rsiz.com>


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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Monday, January 29, 2018 7:47 PM
To: Jared Still
Cc: Jonathan Lewis; oracle-l_at_freelists.org 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?

Oracle 12.1.0.2

>
> Also, what were some of the crippling bugs?
Bug 17632286 - ORA-600 [qksdsUpdTabStatsCbk:0] when dynamic_sampling=11 (Doc ID 17632286.8)

Patch 20586191: ORA-600 [QESMAGETDOMIDXSEG1] FOR A QUERY WHEN OPTIMIZER_DYNAMIC_SAMPLING = 11

Also there was a problem with latches when parsing.

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

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 30 2018 - 13:15:33 CET

Original text of this message