9 Replies Latest reply: Jan 14, 2009 12:48 PM by 448778 RSS

    How to calculate table scan cost with CPU costing enabled.

    448778
      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.
          Pavan Kumar
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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?