This discussion is archived
9 Replies Latest reply: Jan 14, 2009 10:48 AM by 448778 RSS

How to calculate table scan cost with CPU costing enabled.

448778 Journeyer
Currently Being Moderated
System statistics on DB version 10.2.0.3 on AIX 5.3 looks like this:-
SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME PVAL1
-----
----------
CPUSPEEDNW 850.243902
IOSEEKTIM 10
IOTFRSPEED 4096
How to calculate the table scan cost as the CPUSPEED is missing?What will be formula for calculating table scan cost in this case.
  • 1. Re: How to calculate table scan cost with CPU costing enabled.
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED are noworkload statistics and
    SREADTIM, MREADTIM, CPUSPEED, MBRC, MAXTHR, and SLAVETHR represent workload statistics.
    Note :- If both workload and noworkload statistics are available, the optimizer uses workload statistics.

    The formula to calculate cost using a CPU Costing methodology is :

    Cost = (#SRds * SReadtim + #MRds * MReadtim + #CPUCycle / CPUSpeed ) / SReadtim

    where #MRds = Num_Blocks / MBRC

    How to calculate the table scan cost as the CPUSPEED is missing
    exec dbms_stats.gather_system_stats('START');
    after some time
    exec dbms_stats.gather_system_stats('STOP');

    You will get Updated the results. CPU is the main thing which deals with processing time, number fo Cycle per transactions done on your system.

    - Jonathan Lewis Given the Very insight picture on Costing. you must go through the Book (Cost Based ORACLE Fundamentals.)

    Adding Further, Its better you post the version of DB, since Optimizer forumals changes based on that

    SReadtim = IO Seektime + (db_block_size/IO Transfer Speed)
    MReadtim = IO Seektime + db_file * (db_block_size/IO Transfer Speed)

    - Pavan Kumar N

    Edited by: Pavan Kumar on Jan 9, 2009 3:01 PM
  • 2. Re: How to calculate table scan cost with CPU costing enabled.
    448778 Journeyer
    Currently Being Moderated
    Tnx Pawan,

    I know all this,what happens when only network load statistics is present, and workload statistics are missing?. as the formula provided in Jonathan's book only talks about CPUSPEED.

    4 digit oracle version is mentioned in the OP.

    P.S:- I am referring the same book(Cost basesd fundamemtal)
  • 3. Re: How to calculate table scan cost with CPU costing enabled.
    448778 Journeyer
    Currently Being Moderated
    I am not sure if we can use CPUSPEEDNW to calculate the table scan cost..may be Jonathan need to confirm this.
  • 4. Re: How to calculate table scan cost with CPU costing enabled.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user445775 wrote:
    I am not sure if we can use CPUSPEEDNW to calculate the table scan cost..may be Jonathan need to confirm this.
    When system statistics have not been collected in 10g, Oracle "creates" and sreadtim and mreadimg from the ioseektim and iotfrspeed, according to the formula given earlier and then uses those figures in my original formula for the IO_COST in the plan table:
    SReadtim = ioseektim +  db_block_size/iotfrspeed
    MReadtim = ioseektim + (db_file_multiblock_read_count * db_block_size)/iotfrspeed
    If you haven't set the db_file_mulitblock_read_count (which, ideally, you should not do in 10g2) then Oracle uses the dbfile_optimizer_read_count which defaults to 8.

    The CPUSPEEDNW is used in exactly the same was as the CPUSPEED to derive an extra component to add to the IO_COST value to produce the COST value.

    Note that the CPU_COST column records the predicted number of "CPU unit " it will require for Oracle run the query, and the CPUSPEED and CPUSPEEDNW give the speed of the CPU in "millions of units per second". So Oracle takes the CPU_COST, converts to seconds using the CPUSPEED(NW) then converts to cost by factoring in the sreadtim - and adds the result the IO_COST to get the total COST.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 5. Re: How to calculate table scan cost with CPU costing enabled.
    448778 Journeyer
    Currently Being Moderated
    Thanks for your response Jonathan.

    One more question in your book the formula for calculating the
    cost of table scan :- I/O cost + cpu_cost

    and cpu_cost := #cpucycles/(cpuspeed*sreadtim)

    How would I calculate the #cpucycles? once I have the #cpucycles then only I can find the CPU_COST and can add to I/O cost to get actual cost of the table scan.
  • 6. Re: How to calculate table scan cost with CPU costing enabled.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user445775 wrote:
    Thanks for your response Jonathan.

    One more question in your book the formula for calculating the
    cost of table scan :- I/O cost + cpu_cost
    There's a point of confusion in typing there - cpu_cost should read "CPU cost", it's not meant to be a reference to the "cpu_cost" column in the plan table.
    and cpu_cost := #cpucycles/(cpuspeed*sreadtim)
    Again, this should be "CPU cost", not cpu_cost.
    How would I calculate the #cpucycles? once I have the #cpucycles then only I can find the CPU_COST and can add to I/O cost to get actual cost of the table scan.
    Page 22 of the book, where these notes appear, points out the #cpucycles is the value which is recorded in the cpu_cost column of the plan table. There are also comments about how you could do a load of experiments to work out the formula that Oracle uses to calculate the #cpucycles/cpu_cost based on the number of block visits, which rows in the block, which columns in the rows, and what comparisons will be done.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 7. Re: How to calculate table scan cost with CPU costing enabled.
    448778 Journeyer
    Currently Being Moderated
    Thanks Jonathan,

    I am trying to find the cost of table scan and I have the following data, but the cost in the explain plan doesn't match after putting all those values in the your formula.
    PNAME PVAL1
    =========================
    CPUSPEEDNW     850.243902
    IOSEEKTIM         10
    IOTFRSPEED     4096
    
    _db_file_optimizer_read_count =16
    db_file_multiblock_read_count=16
    
    # of blocks in the table =1927029(blocks column for user_tables)
    
    db_block_size=8192
    
    select cpu_cost from plan_Table where statement_id='TEST';
    
    CPU_COST from plan_table=28028013068.
    
    Can you please let me know what will be table scan cost with the data above.
  • 8. Re: How to calculate table scan cost with CPU costing enabled.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user445775 wrote:

    Can you please let me know what will be table scan cost with the data above.
    Assuming no other crtiical information has been omitted: 424,387 plus or minus 2.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "The temptation to form premature theories upon insufficient data is the bane of our profession."
    Sherlock Holmes (Sir Arthur Conan Doyle) in "The Valley of Fear".
  • 9. Re: How to calculate table scan cost with CPU costing enabled.
    448778 Journeyer
    Currently Being Moderated
    Thanks Jonathan,

    The explain plan shows cost= 424K, is this figure rounded ?

    Also what could be other crtiical information we need to ascertain the table scan cost?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points