RE: Optimizer

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Thu, 30 Sep 2004 23:59:23 +0200
Message-ID: <2CF83791A616BB4DA203FFD13007824A018D0C22@MSXVS02.trivadis.com>

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.=20
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 =3D =

dept.deptno;

14336 rows selected.

Execution Plan

SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D18 Card=3D14336 = Bytes=3D817152)
HASH JOIN (Cost=3D18 Card=3D14336 Bytes=3D817152) TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432) TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D4 Bytes=3D80)

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 =3D = dept.deptno;

14336 rows selected.

Execution Plan

SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D13 Card=3D14336 = Bytes=3D817152)
HASH JOIN (Cost=3D13 Card=3D14336 Bytes=3D817152) TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D4 Bytes=3D80) TABLE ACCESS (FULL) OF 'EMP' (Cost=3D10 Card=3D14336 Bytes=3D530432)

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 =3D = dept.deptno;

14336 rows selected.

Execution Plan

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_at_freelists.org =

[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Freeman
>Robert - IL
>Sent: 30 September 2004 23:17
>To: 'oracle-l_at_freelists.org '
>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=3Db.deptno
>and a.empid < 1000;
>
>In playing with this statement, this is the execution path the =
optimizer
>takes:
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D40 Card=3D1000 =
Bytes=3D22000)
> 1 0 HASH JOIN (Cost=3D40 Card=3D1000 Bytes=3D22000)
> 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D1 =
Bytes=3D10)
> 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3D37 Card=3D1000 =
Bytes=3D12000)
>
>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=3DCHOOSE (Cost=3D40 Card=3D1000 =
Bytes=3D22000)
> 1 0 HASH JOIN (Cost=3D40 Card=3D1000 Bytes=3D22000)
> 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3D37 Card=3D1000 =
Bytes=3D12000)
> 3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3D2 Card=3D1 =
Bytes=3D10)
>
>
>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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 30 2004 - 16:55:00 CDT