RE: cost

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 5 Apr 2004 12:13:56 -0500
Message-ID: <001101c41b31$665bdba0$6901a8c0@CVMLAP02>

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 . . .
. . .

|   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

. . .
. . .

|   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

. . .
. . .

|   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_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:

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_at_comcast.net [mailto:thump604_at_comcast.net]=20 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=20
----------------------------------------------------------------
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:10:47 CDT