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

From: <oracle10_at_gmail.com>
Date: 21 Sep 2006 16:46:49 -0700
Message-ID: <1158882409.010328.285060@k70g2000cwa.googlegroups.com>

This query returns 126 rows
Largetable has 4 Million Rows
Smalltable has 130K rows only

First_Rows deliver results in less than 3 seconds

Here is the full stats for /*+ FIRST_ROWS */

---

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
------------------------------

And now the bad /*+ ALL_ROWS */ with fullscan of both tables!

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

Jonathan Lewis wrote:
> <oracle10_at_gmail.com> wrote in message
> news:1158861514.550243.206350_at_m73g2000cwd.googlegroups.com...
> > We have a query joining large and small tables. Small table has 130K
> > rows. LargeTable has 4M (4 million) rows
> > Both have PK column called pk_id with Index on PK
> >
> > SELECT ST.pk_id
> > FROM smalltable ST, largetable SL
> > WHERE ST.pk_id = LT.pk_id
> > AND LT.code_tp = 'maybe'
> > AND LT.trans_date IS NULL
> > AND LT.status <> 'Incomplete'
> > Table and Index were not analyzed for 6 months and CBO was picking
> > correct plan where small table drives large table
> > i.e. full scan on small table
> > for each row in smalltable, oracle uses PK index on large table to
> > locate matching join row on largetable
> >
> > This happened with all_rows or first_rows optimizer goal/mode
> >
> > Few days ago, we updated / analyzed all tables and indexes
> >
> > After analysis CBO started picking wrong plan and do FULL scan on large
> > and small tables both and do Hash Join instead of Nested Loops in case
> > of ALL_ROWS
> > FIRST_ROWS still works after analysis
> >
> > Are there cases when following Oracle Recommendation by frequent
> > ANALYZE/stats gathering causes CBO to go astray?
> >
> > Thanks
> >
> > Good Plan:
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=170900 Car
> > d=42699 Bytes=1067475)
> >
> > 1 0 NESTED LOOPS (Cost=170900 Card=42699 Bytes=1067475)
> > 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
> > =3830901 Bytes=61294416)
> > 4 3 INDEX (RANGE SCAN) OF 'PK_ID' (UNIQUE) (Cost=3 Card
> > =3830901)
> >
> > Bad Plan:
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=19388 Card=4
> > 2699 Bytes=1067475)
> >
> > 1 0 HASH JOIN (Cost=19388 Card=42699 Bytes=1067475)
> > 2 1 TABLE ACCESS (FULL) OF 'SMALLTABLE' (Cost=104 Car
> > d=42699 Bytes=384291)
> > 3 1 TABLE ACCESS (FULL) OF 'LARGETABLE' (Cost=15214
> > Card=38309
> > 01 Bytes=61294416)
> >

>
>

> Looks like you're running 8i there, and have
> simplified the query a little. Should we assume
> you have some extra predicates on the small
> table, and that the primary key on the parent
> is a multi-column index of which the small table
> primary key is the first column ?

>

> The missing bit of the puzzle is the plan before
> you collected the statistics. Both plans seem
> to be good plans, according to the intention
> stated by the first_rows/all_rows hints and
> the statistics that are appearing.

>

> It is possible that your old statistics were
> sufficient misleading that the optimizer got
> a completely different impression either of
> how many times it would exercise the indexed
> access path or how much it would cost to
> use that index once.

>
>

> It is a sad fact of optimization that there are always
> cases where the optimizer really hasn't got a chance
> of coming to the right strategy from the available information.
> In those cases, you need to fabricate suitable statistics,
> or use the right hints to make the desired path appear.
> If you have to fabricate statistics, you may find that a
> one-off design will work, but you may find that you
> have to use a program to keep moving the stats
> in line with the data and queries.

>
>

> --
> Regards

>

> Jonathan Lewis
> http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

>

> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>

> Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Received on Thu Sep 21 2006 - 18:46:49 CDT