Re: Optimizer Choices - Part Two
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: FREEMANR@xxxxxxxx
- Date: Sat, 02 Oct 2004 00:22:05 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
