| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Optimizer
the dynamic role reversal does not mean that Oracle ignores your ORDERED
hint --
the hash join algorithm starts with the row source you ask for, doing the
hash partitioning;
the role reversal happens later in the game, so I am not surprised to see a
small difference in LIOs.
if you want to see specific details of the hash join at work, you might want
to use event 10104;
the trace file output might be useful in combination with 10053 output.
Kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Freeman Robert - IL
Sent: Thursday, September 30, 2004 22:40
To: 'Wolfgang Breitling '
Cc: 'oracle-l_at_freelists.org '
Subject: 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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 01 2004 - 02:45:33 CDT
 |
 |