Execution plan cost [message #645067] |
Wed, 25 November 2015 05:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I created two tables with some data, collected stats i.e.
create table t_build as
select rownum id
, trunc(dbms_random.value(1,4)) c1
from dual
connect by level<=10000;
create table t_probe as
select trunc(dbms_random.value(1,8)) c1
from dual
connect by level<=1000000;
execute dbms_stats.gather_table_stats('SCOTT','T_BUILD')
execute dbms_stats.gather_table_stats('SCOTT','T_PROBE')
ran below query and verified the execution plan i.e.
select /*+ use_nl(b p) */ *
from t_build b
, t_probe p
where b.c1=p.c1
/
select * from table(dbms_xplan.display_cursor('913xat8gctujw',null,'allstats last +cost'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID 913xat8gctujw, child number 0
-------------------------------------
select /*+ use_nl(b p) */ * from t_build b
,t_probe p where b.c1=p.c1
Plan hash value: 4283443859
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4296K(100)| 36991 |00:00:00.03 | 2873 |
| 1 | NESTED LOOPS | | 1 | 1388M| 4296K (2)| 36991 |00:00:00.03 | 2873 |
| 2 | TABLE ACCESS FULL| T_BUILD | 1 | 10000 | 7 (0)| 1 |00:00:00.01 | 4 |
|* 3 | TABLE ACCESS FULL| T_PROBE | 1 | 138K| 430 (2)| 36991 |00:00:00.02 | 2869 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."C1"="P"."C1")
21 rows selected.
I am trying to understand the above execution plan at different levels
please correct my understanding.
1. As Jonahtan says, cost is roughly equals to number blocks (I know it is a figure of cpu etc.), can I say from line 2 of the plan the T_BUILD rows are in the 7 blocks
similary, T_PROBE in the 430 blocks.
2. Both the table blocks are read into memory and line 1 of the execution plan shows the cost as 4296k
can this 4296k cost be equated to 10000 rows from t_build (line 2)*430 + 7 i.e.
My understanding is that the total cost at line 1 is equal to cost of reading t_build (cost: 7) + reading the t_probe 10000 times (10000*430) to perform the nested loop join-
so, 10000*430+7=4300007 (4300k) almost 4297K
Regards,
Pointers
|
|
|
|
Re: Execution plan cost [message #645071 is a reply to message #645067] |
Wed, 25 November 2015 08:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
What Jonathan says (Cost-Based Oracle Fundamentals, 2006) is this,
Quote:According to the CPU costing model:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
where
#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second
Translated, this says the following:
The cost is the time spent on single-block reads, plus the time spent on multiblock reads,
plus the CPU time required, all divided by the time it takes to do a single-block read.
Which means the cost is the total predicted execution time for the statement, expressed
in units of the single-block read time. which is certainly not
Quote:1. As Jonahtan says, cost is roughly equals to number blocks
|
|
|
|