Optimizer Choices

From: Freeman Robert - IL <FREEMANR_at_tusc.com>
Date: Fri, 1 Oct 2004 13:59:37 -0500
Message-ID: <F5E885BEF9540D47A7BDC03CF16880870A7D5157@tuscil_ex1>

Good afternoon folks...

Continuing our discussion on the optimizer from yesterday. To review:

I have the following statement:

select a.empid, a.ename, b.dname
from emp a, dept b
where a.deptno=b.deptno
and a.empid < 1000

And the optimizer chooses this plan (this is from the 10046 for that statement):

STAT #1 id=1 cnt=999 pid=0 pos=1 obj=0 op='HASH JOIN (cr=1607 r=0 w=0 time=277989 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=71010 op='TABLE ACCESS FULL OBJ#(71010)
(cr=3 r=0 w=0 time=104 us)'

STAT #1 id=3 cnt=999 pid=1 pos=2 obj=71009 op='TABLE ACCESS FULL OBJ#(71009)
(cr=1604 r=0 w=0 time=274428 us)'

and I get these run stats (from autotrace):

          0  db block gets
       1607  consistent gets
          0  physical reads
        999  rows processed

I change the statement thusly:

select /*+ ORDERED */ a.empid, a.ename, b.dname from emp a, dept b
where a.deptno=b.deptno
and a.empid < 1000;

I get this plan (from the 10046):

STAT #1 id=1 cnt=999 pid=0 pos=1 obj=0 op='HASH JOIN (cr=1540 r=1423 w=0 time=2595993 us)'

STAT #1 id=2 cnt=999 pid=1 pos=1 obj=71009 op='TABLE ACCESS FULL OBJ#(71009)

(cr=1537 r=1422 w=0 time=2565364 us)'

STAT #1 id=3 cnt=1 pid=1 pos=2 obj=71010 op='TABLE ACCESS FULL OBJ#(71010)
(cr=3 r=1 w=0 time=26645 us)'

and these stats:

          0  db block gets
       1540  consistent gets
          0  physical reads
        999  rows processed

The question was, why didn't Oracle choose the plan with the smaller numbers of IO's.

It was suggested that I send the 10046 and 10053 traces of each of these plans, which are attached to this message.

It was suggested that the execution plan from autotrace and explain plan was incorrect. The plans you see above are from the 10046 trace file. The V$SQL_PLAN results are the same as well.

Several folks indicated that the hash join will always take the smallest table as the has join, even dynamically switching the table at run time. Looking at the 10053, I think this is NOT the case if an /*+ ORDERED */ hint is used. Based on the 10053 traces, it seems to me that if an ORDERED hint is used, that Oracle does not dynamically change the join order for a hash join. I say this because of this line in the 10053 trace files:

For join with no hint:
Hash join one ptn Resc: 1 Deg: 1 (sides swapped)

I'm guessing that (sides swapped) indicates a re-ordering of the join. In BOTH cases, the join with the ORDERED hint does not have the (sides swapped) syntax for this line (but the rest of the Hash join line is there). If oracle were to dynamically swap the join order, I would expect to see
(sides swapped) in one of the ORDERED plans. Anyone have any thoughts on
this?

I appologize if I'm missing something basic here.... please help me understand why the optimizer is choosing a plan that, to me, seems less efficient.

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

Received on Fri Oct 01 2004 - 13:55:11 CDT

Optimizer Choices - Part Two