RE: Optimizer
- From: Freeman Robert - IL <FREEMANR@xxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx '" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Sep 2004 16:30:08 -0500
Interesting thought but if that were the case, then shouldn't the LIO's be
the same for both plans??
RF
-----Original Message-----
From: Lex de Haan
To: Freeman Robert - IL; oracle-l@xxxxxxxxxxxxx
Sent: 9/30/2004 4:26 PM
Subject: RE: Optimizer
I think you are seeing dynamic role reversal at work.
with hash joins, Oracle is able to look at the partition sizes at run
time,
and then make the smallest partition driving -- with one of your tables
containing a single row,
it is rather obvious which one will be driving -- regardless the ORDERED
hint you specify.
additions/corrections welcome,
Kind regards,
Lex.
-------------------------------
visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>
-------------------------------
skype me <callto://lexdehaan>
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Freeman Robert - IL
Sent: Thursday, September 30, 2004 22:17
To: 'oracle-l@xxxxxxxxxxxxx '
Subject: Optimizer
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
--
//www.freelists.org/webpage/oracle-l
<<Lex de Haan.vcf>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
