RE: Optimizer
- From: Freeman Robert - IL <FREEMANR@xxxxxxxx>
- To: 'Wolfgang Breitling ' <breitliw@xxxxxxxxxxxxx>
- Date: Thu, 30 Sep 2004 16:47:39 -0500
Sitting here thinking about it, I think my point is this....
Does it matter what it is really doing? The question is, whatever it's
doing, why is it not doing the best doing it can be doing UNLESS I give it a
hint that causes it to do something different?
Just practicing my illeteration skills on ya all...
Is the optimizer so stupid, that even for a simple join I have to give it a
hint to get it to perform optimally? Is it data related? Volume related?
Does the hint change the way Oracle handles the hash join perhaps? I dunno.
10046 and v$sql_plan output tomorrow.... perhaps that will shed some light
on the subject. For now, I'm off to celebrate a birthday with my kiddos!
Robert
-----Original Message-----
From: Wolfgang Breitling
To: Freeman Robert - IL
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxx>:
> 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
>
--
regards
Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
