System Statistics Gathered in Exadata Mode – When Are They Relevant?

The aim of this post isn’t to explain what the “exadata mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only thing I would like to add is that the “exadata mode” is available as of 11.2.0.4 or when a patch implementing the enhancement associated to bug 10248538 is installed.

The key information I would like to share with you is that, in some situations, gathering system statistics in “exadata mode” is pointless. Let me explain why… But, before doing so, it’s important to review how the query optimizer computes the cost of full scans.

The key formula used by the query optimizer to compute the I/O cost of a full scan is the following:

io_cost = ceil ( blocks / mbrc * mreadtim / sreadtim ) + 1

Where:

  • blocks is the number of blocks to be read
  • mbrc, mreadtime and sreadtim are the values provided by the workload system statistics

To check the formula let’s have a look to an example with a specific set of workload system statistics:

  • Set workload system statistics
SQL> BEGIN
  2    dbms_stats.delete_system_stats();
  3    dbms_stats.set_system_stats(pname => 'CPUSPEED', pvalue => 2832);
  4    dbms_stats.set_system_stats(pname => 'SREADTIM', pvalue => 1.3);
  5    dbms_stats.set_system_stats(pname => 'MREADTIM', pvalue => 7.8);
  6    dbms_stats.set_system_stats(pname => 'MBRC',     pvalue => 42);
  7  END;
  8  /
  • Get the cost associated to the full scan
SQL> DELETE plan_table;

SQL> EXPLAIN PLAN FOR SELECT * FROM t;

SQL> SELECT io_cost
  2  FROM plan_table
  3  WHERE id = 1;

   IO_COST
----------
       148
  • Compute the cost with the formula
SQL> SELECT ceil(blocks / 42 * 7.8 / 1.3) + 1 AS io_cost_formula
  2  FROM user_tables
  3  WHERE table_name = 'T';

IO_COST_FORMULA
---------------
            148

Good, the two costs match!

Since the formula uses the workload system statistics, what does it happen when only noworkload system statistics are available?

For sreadtim the situation is quite simple. In fact, it can be derived by the noworkload statistics with the following formula.

sreadtim = ioseektim + db_block_size / iotfrspeed

For mreadtim and mbrc it’s a bit more complicated. In fact, the noworkload statistics doesn’t provide information about the average size of multiblock reads. To overcome this issue, the query optimizer sets mbrc equal to the value of the db_file_multiblock_read_count initialization parameter. In addition, it can derive mreadtim with the following formula:

mreadtim = ioseektim + db_file_multiblock_read_count * db_block_size / iotfrspeed

Also in this case let’s have a look to an example:

  • Set noworkload system statistics and db_file_multiblock_read_count
SQL> BEGIN
  2    dbms_stats.delete_system_stats();
  3    dbms_stats.set_system_stats(pname => 'CPUSPEEDNW', pvalue => 1618);
  4    dbms_stats.set_system_stats(pname => 'IOSEEKTIM',  pvalue => 9);
  5    dbms_stats.set_system_stats(pname => 'IOTFRSPEED', pvalue => 88419);
  6  END;
  7  /

SQL> ALTER SESSION SET db_file_multiblock_read_count = 42;
  • Get the cost associated to the full scan
SQL> DELETE plan_table;

SQL> EXPLAIN PLAN FOR SELECT * FROM t;

SQL> SELECT io_cost
  2  FROM plan_table
  3  WHERE id = 1;

   IO_COST
----------
        36
  • Compute the cost with the formula
SQL> SELECT ceil(blocks / 42 * ( 9 + 42 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula
  2  FROM user_tables
  3  WHERE table_name = 'T';

IO_COST_FORMULA
---------------
             36

Good, also in this case the two costs match!

Another question we have to ask is… What does it happen when db_file_multiblock_read_count is not explicitly set? Does the query optimizer use the value which is automatically set by the database engine at the instance startup? Let’s check it…

  • Show that db_file_multiblock_read_count isn’t set in the spfile. I.e. it’s automatically set to 128 when the instance is started
SQL> SELECT nvl(value,'*NULL*') AS value
  2  FROM v$spparameter
  3  WHERE name = 'db_file_multiblock_read_count';

VALUE
------
*NULL*

SQL> SELECT value
  2  FROM v$parameter
  3  WHERE name = 'db_file_multiblock_read_count';

VALUE
------
128
  • Check the cost associated to the full scan
SQL> DELETE plan_table;

SQL> EXPLAIN PLAN FOR SELECT * FROM t;

SQL> SELECT io_cost
  2  FROM plan_table
  3  WHERE id = 1;

   IO_COST
----------
       139
  • Compute the cost with the formula
SQL> SELECT ceil(blocks / 128 * ( 9 + 128 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula
  2  FROM user_tables
  3  WHERE table_name = 'T';

IO_COST_FORMULA
---------------
             20

The two costs don’t match! What’s going on? The “problem” is that if db_file_multiblock_read_count is not explicitly set, in the costing formula the query optimizer does not use the value automatically set at the instance startup. Instead, it uses 8! Let’s check it…

SQL> SELECT ceil(blocks / 8 * ( 9 + 8 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula
  2  FROM user_tables
  3  WHERE table_name = 'T';

IO_COST_FORMULA
---------------
            139

Now the two costs match!

Ok, now that we have seen how the query optimizer uses the system statistics for computing the cost of full scans, let’s discuss what happens when system statistics are gathered in “exadata mode”.

First of all it’s essential to point out that the gathering of “exadata mode” system statistics is identical to the gathering of noworkload system statistics. The only additional step executed in “exadata mode” is that mbrc is set to the value of db_file_multiblock_read_count. And that, independently on how it is set (explicitly or automatically).

The query optimizer uses “exadata mode” system statistics as regular noworkload statistics. The only difference is that, since mbrc is available, it doesn’t check the value of db_file_multiblock_read_count. Let’s have a look to two examples with two different values for mbrc…

  • mbrc = 128
SQL> BEGIN
  2    dbms_stats.delete_system_stats();
  3    dbms_stats.set_system_stats(pname => 'CPUSPEEDNW', pvalue => 1618);
  4    dbms_stats.set_system_stats(pname => 'IOSEEKTIM',  pvalue => 9);
  5    dbms_stats.set_system_stats(pname => 'IOTFRSPEED', pvalue => 88419);
  6    dbms_stats.set_system_stats(pname => 'MBRC',       pvalue => 128);
  7  END;
  8  /

SQL> DELETE plan_table;

SQL> EXPLAIN PLAN FOR SELECT * FROM t;

SQL> SELECT io_cost
  2  FROM plan_table
  3  WHERE id = 1;

   IO_COST
----------
        20

SQL> SELECT ceil(blocks / 128 * ( 9 + 128 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula
  2  FROM user_tables
  3  WHERE table_name = 'T';

IO_COST_FORMULA
---------------
             20
  • mbrc = 42
SQL> BEGIN
  2    dbms_stats.delete_system_stats();
  3    dbms_stats.set_system_stats(pname => 'CPUSPEEDNW', pvalue => 1618);
  4    dbms_stats.set_system_stats(pname => 'IOSEEKTIM',  pvalue => 9);
  5    dbms_stats.set_system_stats(pname => 'IOTFRSPEED', pvalue => 88419);
  6    dbms_stats.set_system_stats(pname => 'MBRC',       pvalue => 42);
  7  END;
  8  /

SQL> DELETE plan_table;

SQL> EXPLAIN PLAN FOR SELECT * FROM t;

SQL> SELECT io_cost
  2  FROM plan_table
  3  WHERE id = 1;

   IO_COST
----------
        36

SQL> SELECT ceil(blocks / 42 * ( 9 + 42 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula
  2  FROM user_tables
  3  WHERE table_name = 'T';

IO_COST_FORMULA
---------------
             36

Good, the costs computed by the formula are identical to the ones computed by the query optimizer.

Back to the original question… When does it make sense to gather “exadata mode” system statistics?

Based on what I described above… Only when db_file_multiblock_read_count is not explicitly set! In fact, if it’s explicitly set, the costs doesn’t change.

2 Comments

  1. October 11, 2013    

    Christian,
    that’s perhaps the most concise description of the CBOs calculation of FTS cost I have ever seen – I think I will refer to it more than once…

    I assume the standard value 8 is the hidden parameter _db_file_optimizer_read_count – is this correct?

    • Christian Antognini's Gravatar Christian Antognini
      October 11, 2013    

      Hi Martin

      > I assume the standard value 8 is the hidden parameter _db_file_optimizer_read_count – is this correct?

      I didn’t check it. But, I also expect to be the default value…

      Best,
      Chris

Leave a Reply

Your email address will not be published. Required fields are marked *