Re: strange cost in explain plan
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Oct 2007 08:41:18 +0100
The statement: "the cost of the parent should be equal or larger than the
child" is not strictly true.
Your basic plan is for
select {} from PATH_RELATIONSHIP_TEMP x;
You then have a scalar subquery in the select list.
Oracle therefore shows you two independent plans
in the plan table. One for your driving query - which
as a cost of two, and one for (each) scalar subquery,
which has a cost of 2083 EACH TIME IT EXECUTES.
But Oracle does not know how many times the scalar subquery
will run, and does not make any allowance for multiple executions
in the total cost of the query.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
Date: Fri, 12 Oct 2007 10:10:51 +0800
From: "qihua wu" <staywithpin@xxxxxxxxx>
Subject: strange cost in explain plan
Hi, everyone,
For the plan, the cost of the parent should be equal or larger than the
child. But in the following plan, the total cost (Id=0) is 2, but it's child
"HASH UNIQUE "(ID=1) has a cost of 2083 which is much larger than 2, how
could it be like that?
Plan hash value: 3028553145
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 482 | 2 (0)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 48 | 2083 (1)| 00:00:25 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | ATTRIBUTE_VALUE | 1 | 24 | 3 (0)|
00:00:01 |
| 4 | NESTED LOOPS | | 11 | 528 | 2082 (1)| 00:00:25 |
|* 5 | TABLE ACCESS BY INDEX ROWID| ATTRIBUTE_VALUE | 11 | 264 | 2049 (1)|
00:00:25 |
|* 6 | INDEX RANGE SCAN | IDX_ATTRIBUTE_VALUE_2 | 19155 | | 59 (0)| 00:00:01
|
|* 7 | INDEX RANGE SCAN | IDX_ATTRIBUTE_VALUE_1 | 1 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | PATH_RELATIONSHIP_TEMP | 1 | 482 | 2 (0)| 00:00:01
|
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
5 - filter("A"."ATTR_STR"=:B1)
Select (select distinct b.attr_str
from ATTRIBUTE_VALUE a,
ATTRIBUTE_VALUE b
where a.locale_lang_id = :a
and a.attr_id = :b
and a.attr_str = x.product_line_name
and a.node_id = b.node_id
and b.locale_lang_id = :c
and b.attr_id = :d
and a.change_lifecycle_id = 0
and b.change_lifecycle_id = 0
and rownum = 1)
from PATH_RELATIONSHIP_TEMP x;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
