RE: cost
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Apr 2004 12:13:56 -0500
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
(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 = 'RESEARCH';
Execute EXPLAIN PLAN for the query 3 times as follows:
1) add hint use_hash (d e)
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
. . .
. . .
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 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"="D"."DEPTNO")
2 - filter("D"."DNAME"='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
. . .
. . .
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 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"='RESEARCH')
3 - filter("E"."DEPTNO"="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
. . .
. . .
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 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"="D"."DEPTNO")
2 - filter("D"."DNAME"='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
lowest cost plan. Although, I'm not really sure why at this point.
Karen Morton
Hotsos Enterprises, Ltd.
http://www.hotsos.com
~~~~
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
Sent: Monday, April 05, 2004 9:47 AM
To: oracle-l@xxxxxxxxxxxxx
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:
- re-calculate statistics for tables and indexes
- add histograms for some skewed columns
- adjust the OPTIMIZER_ parameters
- Are you on 9i? Implement system statistics
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: thump604@xxxxxxxxxxx [mailto:thump604@xxxxxxxxxxx]=20
Sent: Monday, April 05, 2004 10:32 AM
To: oracle-l@xxxxxxxxxxxxx
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=20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
