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.

4 Comments

Martin Preiss

11 October 2013 - 14:50

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?

1. Christian Antognini Author
  
  11 October 2013 - 15:21
  
  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
  
Dennis

30 January 2015 - 17:19
What do you make of an Exadata configured (from day 1) like this? (a V2 quarter rack)
I speculate that it is not configured as an Exadata!
```
select  * from sys.aux_stats$;

SYSSTATS_INFO	STATUS		COMPLETED
SYSSTATS_INFO	DSTART		03-21-2013 21:08
SYSSTATS_INFO	DSTOP		03-21-2013 21:08
SYSSTATS_INFO	FLAGS	1	
SYSSTATS_MAIN	CPUSPEEDNW	2938.05309734513	
SYSSTATS_MAIN	IOSEEKTIM	10	
SYSSTATS_MAIN	IOTFRSPEED	4096	
SYSSTATS_MAIN	SREADTIM		
SYSSTATS_MAIN	MREADTIM		
SYSSTATS_MAIN	CPUSPEED		
SYSSTATS_MAIN	MBRC		
SYSSTATS_MAIN	MAXTHR		
SYSSTATS_MAIN	SLAVETHR
```
1. Christian Antognini Author
  
  2 February 2015 - 07:01
  
  Hi
  
  that is the default configuration (which is not Exadata specific).
  If it works fine since 2013, I would not change it. If you have problems related to suboptimal execution plans, that is another story…
  
  Cheers,
  Chris

Striving for Optimal Performance

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

4 Comments

Leave a Reply Cancel reply