| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: cost
Hmm....I should have known, Cary...;-)
I actually had the thought, as I was typing that reply "hmm....*always*, really? Are you sure? Yeah, of course you are, if it's not always choosing the lowest cost, what else could it do....?" Of course, Oracle found a way.....
My first thought, though, would be that this is a bug.....is that where you're going with this? Or is there something more going on? Did 10053 reveal anything interesting?
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]=20
Sent: Monday, April 05, 2004 1:14 PM
To: oracle-l_at_freelists.org
Subject: RE: cost
Mark,
Unfortunately, not *always*. :( See Karen Morton's test case below my sig.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 4/6 Seattle, 5/7 Dallas, 5/18 New Jersey - SQL Optimization 101: 4/19 Denver, 5/3 Boston, 5/24 San Diego - Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...
Given the following query using the SCOTT tables emp and dept=20 (note that each table has only a primary key index):
select e.ename, d.dname
from emp e inner join dept d
using (deptno)
where d.dname =3D 'RESEARCH';
Execute EXPLAIN PLAN for the query 3 times as follows:
1) add hint use_hash (d e)=20 2) add hint use_nl (d e) 3) run as is
09:50:26 SQL> explain plan for
09:50:26 2 select /*+ use_hash (d e) */ e.ename, d.dname
. . .
. . .
| 0 | SELECT STATEMENT | | 5 | 90 | 5 | |* 1 | HASH JOIN | | 5 | 90 | 5 | |* 2 | TABLE ACCESS FULL | DEPT | 1 | 11 | 2 | | 3 | TABLE ACCESS FULL | EMP | 14 | 98 | 2 | --------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - access("E"."DEPTNO"=3D"D"."DEPTNO") 2 - filter("D"."DNAME"=3D'RESEARCH')
Note: cpu costing is off
09:50:26 SQL> 09:50:26 SQL> explain plan for 09:50:26 2 select /*+ use_nl (d e) */ e.ename, d.dname
| 0 | SELECT STATEMENT | | 5 | 90 | 4 | | 1 | NESTED LOOPS | | 5 | 90 | 4 | |* 2 | TABLE ACCESS FULL | DEPT | 1 | 11 | 2 | |* 3 | TABLE ACCESS FULL | EMP | 5 | 35 | 2 | --------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("D"."DNAME"=3D'RESEARCH')
3 - filter("E"."DEPTNO"=3D"D"."DEPTNO")
Note: cpu costing is off
09:50:26 SQL> 09:50:26 SQL> explain plan for 09:50:26 2 select e.ename, d.dname
| 0 | SELECT STATEMENT | | 5 | 90 | 5 | |* 1 | HASH JOIN | | 5 | 90 | 5 | |* 2 | TABLE ACCESS FULL | DEPT | 1 | 11 | 2 | | 3 | TABLE ACCESS FULL | EMP | 14 | 98 | 2 | --------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - access("E"."DEPTNO"=3D"D"."DEPTNO") 2 - filter("D"."DNAME"=3D'RESEARCH')
Note: cpu costing is off
09:50:28 SQL>
Hmmmm....the optimizer chose the hash join plan, when not hinted, even
though the cost of the plan was 5 vs. 4 for the nested loops plan.
I don't think you can say the optimizer will "always" choose the=20 lowest cost plan. Although, I'm not really sure why at this point.
=20
=20
Karen Morton
Hotsos Enterprises, Ltd.
http://www.hotsos.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: Monday, April 05, 2004 9:47 AM
To: oracle-l_at_freelists.org
Subject: RE: cost
Yes, the CBO will ALWAYS choose the plan with the lowest cost. If you have SQLs where higher cost plans are actually more efficient than lower cost plans, then you ought to ask yourself why. One solution is always to simply add hints and convince the optimizer to do what you want, but, a better long-term approach is probably to try to understand WHY the CBO thinks a particular plan is lower cost than another and then give the CBO better information so that it can more accurately estimate plan costs.
Ways to do this may include:
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: thump604_at_comcast.net [mailto:thump604_at_comcast.net]=3D20
Sent: Monday, April 05, 2004 10:32 AM
To: oracle-l_at_freelists.org
Subject: cost
DB - 8174
Does the CBO choose always the path based solely on lowest cost of all
plans?
We have noticed that best plan is not always of lowest cost, which is
why I ask.
If that is the case, is that where hints, outlines, etc come in?
-- - David=3D20 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Apr 05 2004 - 12:20:06 CDT
 |
 |