Re: Optimizer Choices - Part Two

At 12:59 PM 10/1/2004, Freeman Robert - IL wrote:

>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)'
>
>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)'
>
>The question was, why didn't Oracle choose the plan with the smaller numbers
>of IO's.

Robert, was the second (in the order you list them here) test, the one with the ordered hint, run before the first, or why has the 2nd sql 1423 reads and takes 10 times as long as the 1st (with 0 reads). If I look at this data in isolation I would have to come to the conclusion that the optimizer chose the better, i.e. faster, plan and that your hint made things worse. I care about elapsed time more than I do about LIOs.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

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