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.
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?
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
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!
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
I read this interesting article on system statistics. We have a new hardware and decided to recreate the system statistics with option WORKLOAD.
Then we wanted to investigate the effects of the new system statistics with a test run.We determine in a sql script the values ( “I/O formular”) according to your article
and with your informations :
<< Good, the costs computed by the formula are identical to the ones computed by the query optimizer.
the 2 values (I/O Cost PlanTable vs I/O Formular ) must be identical, right.
And maybe I"m missing something with the following information?
<< 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.
The database parameter db_file_multiblock_read_count is a dynamic value and between 64 and 128.
The system statistics creation with option WORKLOAD is performed at a DWH database, when the highest workload and at time interval of e.g.: 12 h becomes MBRC entry in SYS.AUX_STATS$ (SYSSTATS_MAIN.MBRC) to a very low value of e.g.: 21.
Then the costs computed by the formula are not identical !!! to the ones computed by the query optimizer. The formular shows huge I/O costs.
oracle documents shows :
<< When gathering workload statistics, the database may not gather the mbrc and mreadtim values if no table scans occur during serial workloads, as is typical of OLTP systems.
<< However, full table scans occur frequently on DSS systems. These scans may run parallel and bypass the buffer cache. In such cases, the database still gathers the sreadtim because index lookups use the buffer cache.
<< If the database cannot gather or validate gathered mbrc or mreadtim values, but has gathered sreadtim and cpuspeed, then the database uses only sreadtim and cpuspeed for costing.
<< In this case, the optimizer uses the value of the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT to cost a full table scan. However, if DB_FILE_MULTIBLOCK_READ_COUNT is 0 or is
<< not set, then the optimizer uses a value of 8 for calculating cost.
So, maybe in the system collection interval aren't many FTS ?
I set (with dbms_stats.set_system_stats ) the MBRC value (SYS.AUX_STATS), which is displayed as a database parameter DB_FILE_MULTIBLOCK_READ_COUNT (e.g.: 64 )
Then the costs computed by the formula are identical !!! to the ones computed by the query optimizer. The formular shows low I/O costs.
So you should set the MBRC value (SYS.AUX_STATS) according to the database parameter DB_FILE_MULTIBLOCK_READ_COUNT (absolutely?) ?
Hi Tom
In case the formula does not return the same value as the plan table, I would start by checking two things:
– Is the FTS executed in parallel?
– Are the system statistics consistent? (share the output of “select * from sys.aux_stats$”)
The former is relevant because for FTS executed in parallel the cost has to be adjusted based on the PX degree and on the available system statistics.
The latter is relevant because the query optimizer checks the available system statistics and in case they don’t fullfil some rule, it can ignore them.
When workload system statistics are computed through DBMS_STATS, it’s common to see MBRC lower than db_file_multiblock_read_count. And that isn’t per se an issue.
Best,
Chris