Re: CBO picks wrong plan after analyze. FIRST_ROWS hint is workaround. ALL_ROWS causes wrong plan

Frank van Bortel wrote:
> oracle10_at_gmail.com schreef:
> >
> > Good Plan:
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=170900 Car
> > d=42699 Bytes=1067475)
>
> [snip]
> > Bad Plan:
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=19388 Card=4
> > 2699 Bytes=1067475)
>
> CBO does *not* agree: your "good" plan has a cost of 170900, whereas
> your "bad" plan has a cold of about 1/10th of it: 19388.
>

Cost reported by CBO is meaningless! Look at the "consistent gets" difference between good plan (first_rows) and bad plan (all_rows)

===> Also, notice that Nested Loops seems to be better than hash join in most cases!

1. Good ********************

126 rows selected.

Elapsed: 00:00:02.84

Execution Plan

   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=170900 Card=4269
          9 Bytes=1152873)

   1    0   NESTED LOOPS (Cost=170900 Card=42699 Bytes=1152873)
   2    1     TABLE ACCESS (FULL) OF 'SMALLTABLE' (Cost=104 Car
          d=42699 Bytes=384291)

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'LARGETABLE' (Cost=4
Card
          =3837539 Bytes=69075702)

   4    3       INDEX (RANGE SCAN) OF 'PK_ID' (UNIQUE) (Cost=3 Card
          =3837539)

Statistics

          7  recursive calls
          4  db block gets
       1260  consistent gets
         88  physical reads
          0  redo size
       1933  bytes sent via SQL*Net to client
        887  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        126  rows processed
--=========================================================================================

2) BAD ***********************

126 rows selected.

Elapsed: 00:02:38.03

Execution Plan

   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=19722 Card=4
          2699 Bytes=1152873)

   1    0   HASH JOIN (Cost=19722 Card=42699 Bytes=1152873)
   2    1     TABLE ACCESS (FULL) OF 'SMALLTABLE' (Cost=104 Car
          d=42699 Bytes=384291)

   3    1     TABLE ACCESS (FULL) OF 'LARGETABLE' (Cost=15251
Card=38375
          39 Bytes=69075702)

Statistics

          7  recursive calls
         93  db block gets
     101196  consistent gets
      99941  physical reads
          0  redo size
       2012  bytes sent via SQL*Net to client
        887  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        126  rows processed

> If I could have the same car for 11% of the price, I'd go for it!
>
> What are the execution times? What values for the optimizer_cost*
> parameters? multiblock_read_count?
>
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
Received on Fri Sep 22 2006 - 13:18:43 CDT