| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Number 1 gripe about CBO: 0 <Cardinality< 1 (?????)
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
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
Expected number of rows = 0.00000625 * 500
Looking at the plan from the 10053 trace file:
+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------- +-----------------------------------+ | 0 | SELECT STATEMENT | | | | 24 | | | 1 | NESTED LOOPS | | 1 | 44 | 24 | 00:00:01 | | 2 | MERGE JOIN CARTESIAN | | 16 | 448 | 8 | 00:00:01 | | 3 | TABLE ACCESS FULL | KK_SMALL | 1 | 14 | 3 | 00:00:01 | | 4 | BUFFER SORT | | 5000 | 68K | 5 | 00:00:01 | | 5 | TABLE ACCESS FULL | KK_BIG | 5000 | 68K | 5 | 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID | KK_HUGE | 1 | 16 | 1 | 00:00:01 | | 7 | INDEX UNIQUE SCAN | SYS_C007864| 1 | | 0 | | --------------------------------------------------- +-----------------------------------+
The above plan does look reasonable, if the predicted cardinality of the KK_SMALL table is correct. Note the filter at ID 3.
The MERGE JOIN CARTESIAN is used in the plan because Oracle believes that it will be joining 1 row with 5000 rows, so this would be an efficient method. This might be causing the performance problem. Now, look at the row source lines from the 10046 trace file:
(Rows 441) NESTED LOOPS (cr=4410082 pr=0 pw=0 time=49346200
us)
(Rows 2205000) MERGE JOIN CARTESIAN (cr=50 pr=0 pw=0 time=13232069
us)
(Rows 441) TABLE ACCESS FULL KK_SMALL (cr=33 pr=0 pw=0 time=6284 us)
(Rows 2205000) BUFFER SORT (cr=17 pr=0 pw=0 time=4413952 us)
(Rows 5000) TABLE ACCESS FULL KK_BIG (cr=17 pr=0 pw=0
time=10032 us)
(Rows 441) TABLE ACCESS BY INDEX ROWID KK_HUGE (cr=4410032
pr=0 pw=0 time=42661584 us)
(Rows 2205000) INDEX UNIQUE SCAN SYS_C007864 (cr=2205032 pr=0
pw=0 time=20234238 us)
The SQL statement required 49.3 seconds to execute. Note how the actual number of rows differs from the predicted number of rows. Oracle calculated the cardinality wrong in this instance, and you are able to use hints to adjust the predicted cardinality, if desired.
What if, you can get the plan to look like this without the MERGE JOIN
CARTESIAN by using a hint - from the 10053 trace: --------------------------------------------------- +-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------- +-----------------------------------+ | 0 | SELECT STATEMENT | | | | 24 | | | 1 | NESTED LOOPS | | 1 | 44 | 24 | 00:00:01 | | 2 | NESTED LOOPS | | 16 | 448 | 8 | 00:00:01 | | 3 | TABLE ACCESS FULL | KK_SMALL | 1 | 14 | 3 | 00:00:01 | | 4 | TABLE ACCESS FULL | KK_BIG | 5000 | 68K | 5 | 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | KK_HUGE | 1 | 16 | 1 | 00:00:01 | | 6 | INDEX UNIQUE SCAN | SYS_C007864| 1 | | 0 | | --------------------------------------------------- +-----------------------------------+us)
>From the 10046 row source:
(Rows 441) NESTED LOOPS (cr=4417592 pr=0 pw=0 time=49161224
(Rows 441) TABLE ACCESS FULL KK_SMALL (cr=33 pr=0 pw=0
time=5825 us)
(Rows 2205000) TABLE ACCESS FULL KK_BIG (cr=7527 pr=0 pw=0
time=4462857 us)
(Rows 441) TABLE ACCESS BY INDEX ROWID KK_HUGE (cr=4410032
pr=0 pw=0 time=42379344 us)
(Rows 2205000) INDEX UNIQUE SCAN SYS_C007864 (cr=2205032 pr=0
pw=0 time=20102253 us)
It is still taking about 49 seconds to execute, so the MERGE JOIN CARTESIAN was not as damaging as initially expected.
Slamming someone who is trying to help you will likely do nothing to help you resolve the issue that you are experiencing.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Wed Apr 04 2007 - 22:10:10 CDT
 |
 |