RE: Optimizer

Good point.... I'll run a 10046 and dump the contents of the relevant SQL*Plan into the plan table and generate an execution plan off of it tomorrow....

Still, the point (in my mind) is that Oracle didn't seem to select the right plan. The LIO's were still higher, until I used the hint. Even if dynamic adjustment of the join order did occur, why would it result in higher LIO's?

RF

-----Original Message-----
From: Wolfgang Breitling
To: Freeman Robert - IL
Cc: oracle-l_at_freelists.org
Sent: 9/30/2004 4:28 PM
Subject: Re: Optimizer

OK, so those are the plans. But what is REALLY happening? You only can see that
if you run a sqltrace and look at the STAT records - or in v$sql_plan if on 9i
or later. Also, it is my understanding that in the case of a hash join, the
execution engine can switch the roles of inner and outer table on the fly,
while the hash join is happening, if it determines that the original order is
not the best way of doing the join.

Quoting Freeman Robert - IL <FREEMANR_at_tusc.com>:

> I can do backup and recovery in my sleep.... I can create databases,
and I
> am not a bad SQL tuning fellow I must say.. But, if there were to be
an
> Oracle inqusition, I would have to confess that the optimizer still
> befuddles me sometimes. I have two tables: EMP and DEPT. EMP has
15,000 rows
> and DEPT has 1 row. No indexes. Real simple.
>
> I have a simple SQL statement joining these tables:
>
> select a.empid, a.ename, b.dname
> from emp a, dept b
> where a.deptno=b.deptno
> and a.empid < 1000;
>
> In playing with this statement, this is the execution path the
optimizer
> takes:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
Bytes=22000)
> 1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
> 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
> 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
Bytes=12000)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 444 consistent gets
> 0 physical reads
> 0 redo size
> 21517 bytes sent via SQL*Net to client
> 1378 bytes received via SQL*Net from client
> 68 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 999 rows processed
>
> If I do an ORDERED hint and reverse the join order, I get these
results:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1000
Bytes=22000)
> 1 0 HASH JOIN (Cost=40 Card=1000 Bytes=22000)
> 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=1000
Bytes=12000)
> 3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=10)
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 377 consistent gets
> 0 physical reads
> 0 redo size
> 21517 bytes sent via SQL*Net to client
> 1378 bytes received via SQL*Net from client
> 68 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 999 rows processed
>
> Note that the plan the optimizer chooses results in more consistent
gets,
> than the plan using the ordered hint does. I would expect that for
something
> this basic, the optimizer would "get it right" and come up with the
better
> plan, which the later plan seems to be. Any thoughts on this? Did I
miss
> something basic in my statistics gathering? I gathered stats for all
> columns, and did 100 buckets for the histograms.
>
> I note that the cost for both plans is the same, so is there some tie
> breaking going on and if so, what are the rules for this tie breaking?
> Or...Is this just a "law of diminishing returns" thing, and the
difference
> is so slight that Oracle could just go either way? I'm going to add
more
> rows to both tables and see if that impacts the results....
>
> Thoughts anyone?
>
> RF
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l