RE: Optimizer

What version of Oracle?
If >=3D9i, are system stats gathered?

-----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 5:17 PM 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.=20

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:20:51 CDT