Re: Number 1 gripe about CBO: 0 <Cardinality< 1 (?????)

On Apr 4, 10:10 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Apr 4, 8:16 pm, "Kevin Kirkpatrick" <kvnkrkpt..._at_gmail.com> wrote:
>
>
>
>
>
> > On Apr 4, 5:16 pm, sybra..._at_hccnet.nl wrote:
>
> > > On 4 Apr 2007 14:58:47 -0700, "Kevin Kirkpatrick"
>
> > > <kvnkrkpt..._at_gmail.com> wrote:
> > > >Not trying to pick on you Sybrand, but given the sample database and
> > > >the desired query, what steps should be taken to avoid the atrocious
> > > >plan?
>
> > > The plan isn't atrocious. The plan is to be expected.
>
> > Are you even able to read/understand execution plans? Your response
> > that the plan isn't atrocious indicates you are not so enabled. For a
> > good plan, see what happens when the CBO is prevented from using a
> > cardinality of < 1 on the kk_small table with a hint:
>
> > /*+ cardinality(kk_small,1) */
>
> > > You
> > > - force a full table scan on kk_small
>
> > What do you mean by "force"? No hints specified; but aside from that,
> > why would I want anything but a full scan on kk_small? Your comment
> > is nonsensical.
>
> > > - have no join clause between kk_small and kk_big, yet you list them
> > > in this order, which is obeyed by CBO.
>
> > Did I just step into the twighlight zone? Syband, this comment makes
> > so little sense that I'm literally stumped as to how to reply. Do you
> > know even know what the CBO is?
>
> > > What else do you expect?
>
> > Certainly not a response from a "Senior Oracle DBA" who thinks that
> > the CBO chooses join order based on the order in which tables are
> > listed in a query...
>
> I know a bit about execution plans. I ran your SQL statement through
> a 10053 trace and a 10046 trace to see what is going on. What you
> will find is that histograms cannot be used with the SQL statement as
> you have written it. Why? Take a close look at the structure of the
> predicates in the WHERE clause. Oracle's first attempted join order
> (as will be indicated in a 10053 trace file) is in the order of
> increasing predicted cardinality. What have you done to confuse the
> cost based optimizer regarding cardinality?
>
> First, from the 10053 trace:
> SINGLE TABLE ACCESS PATH
> Table: KK_SMALL Alias: KK_SMALL
> Card: Original: 500 Rounded: 1 Computed: 0.00 Non Adjusted:
> 0.00
> Access Path: TableScan
> Cost: 3.06 Resp: 3.06 Degree: 0
> Cost_io: 3.00 Cost_cpu: 236657
> Resp_io: 3.00 Resp_cpu: 236657
> Best:: AccessPath: TableScan
>
> The predicate restrictions on just the KK_SMALL table is leading
> Oracle to believe that fewer than one row of the 500 will be
> returned. Why? My guess if that each restriction on the KK_SMALL
> table is evaluated with a 5% selectivity just as if it were a bind
> variable being evaluated.
> 1) SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND
> NVL(END_DATE_ACTIVE,SYSDATE)
> 2) AND UPPER(KK_SMALL.HUGE_VAL) LIKE '%ALU%'
> 3) AND UPPER(KK_SMALL.HUGE_VAL) LIKE '%AL%'
> 4) AND KK_HUGE.HUGE_VAL = KK_SMALL.HUGE_VAL
> 5) AND KK_BIG.ID = KK_HUGE.ID;
>
> Selectivity:
> 1) 0.05 * 0.05
> 2) * 0.05
> 3) * 0.05
> = 0.05^4 = 0.00000625
>

Right - Everything is perfectly sensible up until this point. The selectivity problem is mostly tied to the condition "SYSDATE BETWEEN NVL(START_DATE,SYSDATE) AND NVL(END_DATE,SYSDATE)" which can be expressed as

"(START_DATE IS NULL OR START_DATE < SYSDATE) AND (END_DATE IS NULL OR END_DATE > SYSDATE)" to give identical results, but without confusing the CBO (which can use stats/histograms on the latter to give accurate selection estimates but, given the former condition, will ignore all stats/ histograms and yield a selectivity of (0.05 * 0.05) for the condition).

My objection is not how the CBO gets the excessive selectivity, but rather how it applies the number:

>
> Expected number of rows = 0.00000625 * 500
>

At this point, I think the CBO approach is flawed. We all know no WHERE clause, no matter how small the starting cardinality and no matter how many conditionals, can ever return a fraction of a row. I believe the logic of the CBO should be:

Expected number of rows = ceil(0.00000625 * 500)

That is my gripe - the CBO uses floating point values to represent cardinality estimates when, based on a proper understanding of the problem domain, it ought to be using discrete integral values of the most likely non-zero cardinality. The latter approach would avoid bizarre decision making as exemplified in the above example. In fact, the CBO developers seemed to know that fractional cardinality estimates are flawed - notice that on the explain plan displayed to the world, the value "1" is displayed even though behind the scenes CBO is using "0.00003"

<snip>

> It is still taking about 49 seconds to execute, so the MERGE JOIN
> CARTESIAN was not as damaging as initially expected.
>

Your hint replaced the MERGE JOIN cartesian product with a NESTED LOOP cartesian product (tomayto / tomahto). Try this hint: /*+ CARDINALITY(kk_small,1) */, which will force the CBO to use "1" as the expected rows after applying conditions to kk_small (as I believe it ought to do). Also, to see the real damage, make kk_big 100,000 rows and kk_huge 1,000,000 rows.

> Slamming someone who is trying to help you will likely do nothing to
> help you resolve the issue that you are experiencing.
>

I have little sympathy. To me, the tone of sybra's responses #4 and #6 came across as somebody dishing out canned responses based on a quick skim, not that of somebody who has made a genuine effort to read and contemplate the problem - this helps nobody and wastes everyone's time. Furthermore, as he seems compelled to sign off posts to a newsgroup with "Senior Oracle DBA", he darned-well better expect to be called out when he makes bizarred statements like "[you] have no join clause between kk_small and kk_big, yet you list them in this order, which is obeyed by CBO". Look back at the query and you'll see that this is wrong on so many levels (kk_small joins kk_huge joins kk_big; but since I list kk_small then kk_big, I should expect that join order???) that any response I could give would necessarily include a definition of the "cost based optimizer".

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -
Received on Fri Apr 06 2007 - 09:31:19 CDT