Home » RDBMS Server » Performance Tuning » Execution plan cost (11.2.0.3)
Execution plan cost [message #645067] Wed, 25 November 2015 05:13 Go to next message
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 #645069 is a reply to message #645067] Wed, 25 November 2015 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. No.
2. Roughly.

If you want to know where does the cost come then activate a 10053 trace... as Jonathan says.

Re: Execution plan cost [message #645071 is a reply to message #645067] Wed, 25 November 2015 08:58 Go to previous messageGo to next message
John Watson
Messages: 8919
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
Re: Execution plan cost [message #645084 is a reply to message #645071] Thu, 26 November 2015 00:25 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thank you John and Micheal.

Regards,
Pointers
Previous Topic: dbms_xplan
Next Topic: Making Oracle generate and use different execution plans for any sql
Goto Forum:
  


Current Time: Mon Mar 18 21:22:22 CDT 2024