RE: Optimizer
- From: Freeman Robert - IL <FREEMANR@xxxxxxxx>
- To: 'Christian Antognini ' <Christian.Antognini@xxxxxxxxxxxx>, "'oracle-l@xxxxxxxxxxxxx '" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Sep 2004 17:14:40 -0500
>> For hash joins the optimizer creates the hash table on the smaller row
>> source. In this case it's obviously DEPT.
If this is true, then why isn't the LIO the same regardless of the order of
the rows in the FROM clause with or without the ORDERED hint? If a hash join
ALWAYS uses the smallest table as the hash table, shouldn't the LIO's be the
same in this simple join? It appears to me that the hint overrides this rule
somehow.
As for array size, I don't think that is an issue. While it can be used to
reduce LIO's, that is not the point of the question. The point is, with the
array size being the same, why didn't the optimizer take the better path?
What changed when I used the /*+ ORDERED */ hint? Why didn't Oracle use
whatever changed to get me the smaller numbers of LIO's to begin with.
This isn't about tuning the statement, it's about understanding why the
optimizer does what it does.
Thanks a bunch for your thoughts!
RF
-----Original Message-----
From: Christian Antognini
To: Freeman Robert - IL; oracle-l@xxxxxxxxxxxxx
Sent: 9/30/2004 4:59 PM
Subject: RE: Optimizer
Hi Robert
For hash joins the optimizer creates the hash table on the smaller row
source. In this case it's obviously DEPT.
Now, the difference with LIO depends on the number of fetches that are
performed.
Just an example (notice the "set arraysize" statements)...
SQL> set autotrace trace exp stat
SQL> set arraysize 15
SQL> select /*+ ordered */ * from emp, dept where emp.deptno =
dept.deptno;
14336 rows selected.
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18 Card=14336 Bytes=817152)
HASH JOIN (Cost=18 Card=14336 Bytes=817152)
TABLE ACCESS (FULL) OF 'EMP' (Cost=10 Card=14336 Bytes=530432)
TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
98 consistent gets
0 physical reads
0 redo size
607686 bytes sent via SQL*Net to client
11000 bytes received via SQL*Net from client
957 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14336 rows processed
SQL> select /*+ ordered */ * from dept, emp where emp.deptno =
dept.deptno;
14336 rows selected.
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=14336 Bytes=817152)
HASH JOIN (Cost=13 Card=14336 Bytes=817152)
TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80)
TABLE ACCESS (FULL) OF 'EMP' (Cost=10 Card=14336 Bytes=530432)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1051 consistent gets
0 physical reads
0 redo size
794713 bytes sent via SQL*Net to client
11000 bytes received via SQL*Net from client
957 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14336 rows processed
SQL> set arraysize 5000
SQL> select /*+ ordered */ * from dept, emp where emp.deptno =
dept.deptno;
14336 rows selected.
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=14336 Bytes=817152)
HASH JOIN (Cost=13 Card=14336 Bytes=817152)
TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=80)
TABLE ACCESS (FULL) OF 'EMP' (Cost=10 Card=14336 Bytes=530432)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
98 consistent gets
0 physical reads
0 redo size
718473 bytes sent via SQL*Net to client
517 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14336 rows processed
Chris
>-----Original Message-----
>From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Freeman
>Robert - IL
>Sent: 30 September 2004 23: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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
