Re: Different execution plans for same query with same cost

From: <jo_holvoet_at_amis.com>
Date: Thu, 03 Mar 2005 18:54:12 +0100
Message-id: <OFDAA1AFDD.DF58832E-ONC1256FB9.0061EF9B@eu.amis.com>

Gladly :)

First off, the query : (the 2 tables in the from clause are actually views)

SELECT

  DRS_AME.OUTS.FACILITY,
  DRS_AME.OUTS.TECH,
  DRS_AME.OUTS.OWNER,
  DRS_AME.OUTS.QUANTITY,

  lpad(DRS_AME.OUTS.WEEK,2,'0'),
  DRS_AME.OUTS.YEAR,
  CONCAT(( DRS_AME.OUTS.YEAR ),CONCAT(' ',( lpad(DRS_AME.OUTS.WEEK,2,'0') )))
FROM
  DRS_AME.OUTS,
  DRS_AME.CURR_DATE_SHIFT_DAY_JHO
WHERE
  (

  DRS_AME.OUTS.TRANSACTION  =  'SHLT'
  AND  DRS_AME.OUTS.WEEK  =  DRS_AME.CURR_DATE_SHIFT_DAY_JHO.CURR_WK_WEEK
  AND  DRS_AME.OUTS.FACILITY  =  'FABII'
  AND  DRS_AME.OUTS.YEAR  =  DRS_AME.CURR_DATE_SHIFT_DAY_JHO.CURR_WK_YEAR
  AND  DRS_AME.OUTS.OWNER  IN

('BASE','ENGE2','ENGM2','ENGS2','ENGT2','PROD2','PROTO2','PROTOF2') )

Here's the STAT lines from a 10046 trace.

Run 1 : (slowest run)

STAT #3 id=1 cnt=46 pid=0 pos=0 obj=0 op='FILTER '
STAT #3 id=2 cnt=46 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=3 cnt=47 pid=2 pos=1 obj=0 op='NESTED LOOPS '
STAT #3 id=4 cnt=4891 pid=3 pos=1 obj=0 op='NESTED LOOPS '
STAT #3 id=5 cnt=2 pid=4 pos=1 obj=7769 op='TABLE ACCESS BY INDEX ROWID

PLAN_CALENDAR '

STAT #3 id=6 cnt=2 pid=5 pos=1 obj=28481 op='INDEX UNIQUE SCAN '
STAT #3 id=7 cnt=4891 pid=4 pos=2 obj=0 op='INLIST ITERATOR '
STAT #3 id=8 cnt=4906 pid=7 pos=1 obj=3796 op='TABLE ACCESS BY INDEX ROWID

WIPLTH '
STAT #3 id=9 cnt=4908 pid=8 pos=1 obj=104840 op='INDEX RANGE SCAN ' STAT #3 id=10 cnt=4936 pid=3 pos=2 obj=7769 op='TABLE ACCESS BY INDEX ROWID PLAN_CALENDAR '
STAT #3 id=11 cnt=9520 pid=10 pos=1 obj=55255 op='INDEX RANGE SCAN ' STAT #3 id=12 cnt=46 pid=2 pos=2 obj=31709 op='INDEX RANGE SCAN '

Run 2 : (fastest)

STAT #3 id=1 cnt=46 pid=0 pos=0 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=2 cnt=47 pid=1 pos=1 obj=0 op='HASH JOIN '
STAT #3 id=3 cnt=168 pid=2 pos=1 obj=0 op='NESTED LOOPS '
STAT #3 id=4 cnt=2 pid=3 pos=1 obj=7769 op='TABLE ACCESS BY INDEX ROWID

PLAN_CALENDAR '
STAT #3 id=5 cnt=2 pid=4 pos=1 obj=28481 op='INDEX UNIQUE SCAN ' STAT #3 id=6 cnt=168 pid=3 pos=2 obj=7769 op='TABLE ACCESS BY INDEX ROWID PLAN_CALENDAR '

STAT #3 id=7 cnt=169 pid=6 pos=1 obj=41297 op='INDEX RANGE SCAN '
STAT #3 id=8 cnt=4890 pid=2 pos=2 obj=0 op='INLIST ITERATOR '
STAT #3 id=9 cnt=4890 pid=8 pos=1 obj=3796 op='TABLE ACCESS BY INDEX ROWID

WIPLTH '
STAT #3 id=10 cnt=4908 pid=9 pos=1 obj=104840 op='INDEX RANGE SCAN ' STAT #3 id=11 cnt=46 pid=1 pos=2 obj=31709 op='INDEX RANGE SCAN '

mvg/regards

Wolfgang Breitling <breitliw_at_centrexcc.com> 03/03/2005 18:39

        To:     jo_holvoet_at_amis.com
        cc:     oracle-l_at_freelists.org
        Subject:        Re: Different execution plans for same query with same cost

The optimizer IS deterministic. If it generates a different plan - even with the same cost - then something that affects its decision path was different - even if you think everything is the same.

How about posting some details.

jo_holvoet_at_amis.com wrote:
> Hi all,
>
> Oracle 8.1.7.4 on 64-bit Solaris 8.
> I've run into a situation where the same query run on the same instance
> from the same session gets a different execution plan from one run to
the
> next (confirmed this with the STAT-lines in a 10046 trace). Both
execution
> plans apparently get the same cost from CBO. Am I right in thinking that

> in such a case the CBO is not "deterministic" (i.e. it will not arrive
at
> the same plan every time) but randomly arrive at one of the plans with
the
> same lowest cost ?
>

"Both execution plans apparently get the same cost from CBO"

How do you know?

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com



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

Received on Thu Mar 03 2005 - 13:10:40 CST