Understanding cost for each step in Oracle execution plan

Question

With the following execution plan (oracle database), can someone explain me how are cost computed for each step relative to another ? I don't need to what the cost mean, just what is the relation for cost between steps.

I'm following a lesson about oracle database, and the teacher tells us that to compute the cost of the request, we need to add the cost of each line except for the first one (for this plan, he tells us that the total cost is 348).

However, it would make much more sense if this was computed like a tree where the cost of each parent node is the sum of the cost of its children and if needed the cost of the parent operation.

Your teacher is wrong; the statement plan costs shown are cumulatvie, so 112 is the final cost of the statement in question. — mustaccio, Dec 18 '18 at 14:17
It **is** a tree, your SQL client simply fails to display it like that — , Dec 18 '18 at 14:28
Please learn how to post `execution plan` in text format - you may find some hints [here](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) — Marmite Bomber, Dec 18 '18 at 14:30
The way the cost is calculated is documented in [the manual](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/query-optimizer-concepts.html#GUID-9D0BF31B-7215-4BD8-B45D-A8BF2B4DB7E5) — , Dec 18 '18 at 14:32

Marmite Bomber · Accepted Answer · 2018-12-18T14:46:17.290

You may quickly validate, that your tutor is wrong.

Perform an explain plan for some statement, and check the cost in the top line (line = 0 is equal 7)

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR
select * 
from ACCOUNTS a 
left outer join ACCOUNTS b 
on a.ACC = b.ACC;

---    
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL'));

Plan hash value: 1881186757

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     4 |    24 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |          |     4 |    24 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| ACCOUNTS |     4 |    12 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| ACCOUNTS |     4 |    12 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$9E43CB6E
   2 - SEL$9E43CB6E / A@SEL$2
   3 - SEL$9E43CB6E / B@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ACC"="B"."ACC"(+))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "A"."ACC"[CHARACTER,1], "B"."ACC"[CHARACTER,1]
   2 - "A"."ACC"[CHARACTER,1]
   3 - "B"."ACC"[CHARACTER,1]

Note
-----
   - dynamic sampling used for this statement (level=2)

Now run the statement and get the OPTIMIZER_COST from V$SQL

select 
  OPTIMIZER_COST,
  sql_text
from v$sql 
where sql_text like 'select%ACCOUNTS%';

OPTIMIZER_COST SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------- ---------
            7 select *  from ACCOUNTS a  left outer join ACCOUNTS b  on a.ACC = b.ACC

So you se that the cost are calculated cummulative and the top line contains the total costs

Understanding cost for each step in Oracle execution plan

1 Answers1