Configuring the Query Optimizer - Troubleshooting Oracle Performance - page 274

In Formula 9-1, with noworkload statistics, the variables are replaced as follows:
Provided that the •

db_file_multiblock_read_count initialization parameter is explictly set,
mbrc is replaced by the value of the db_file_multiblock_read_count initialization parameter;
otherwise, 8 is used.

•

sreadtim is replaced with the value computed by Formula 7-3.

mreadtim is replaced with the value computed by Formula 7-4.
This means that the db_file_multiblock_read_count initialization parameter has a direct impact on the cost of
multiblock read operations only when noworkload statistics are used. This also means values that are too high may
lead to excessive full scans or at least an underestimation of the cost of multiblock read operations. Further, this is
another situation where workload statistics are superior to noworkload statistics.
Now that you've seen the costing formulas, you need to know how to find out the value the db_file_multiblock_
read_count initialization parameter should be set to. The most important thing is to recognize that multiblock reads
have a big impact on performance. Therefore, the db_file_multiblock_read_count initialization parameter should
be carefully set to achieve best performance. Even though values that lead to a disk I/O size of 1MB usually provide
performance close to the best, sometimes higher or lower values are better. In addition, higher values usually require
less CPU to issue the disk I/O operations. A simple full table scan with different values gives useful information about
the impact of this initialization parameter and, therefore, helps find an optimal value. The following PL/SQL code
snippet, which is an excerpt of the assess_dbfmbrc.sql script, could be used for that purpose:

•

BEGIN
dbms_output.put_line('dbfmbrc blocks seconds cpu');
FOR i IN 0..10
LOOP
l_dbfmbrc := power(2,i);
EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count = '||l_dbfmbrc;
EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH BUFFER_CACHE';
SELECT sum(decode(name, 'physical reads', value)),
sum(decode(name, 'CPU used by this session', value))
INTO l_starting_blocks, l_starting_cpu
FROM v$mystat ms JOIN v$statname USING (statistic#)
WHERE name IN ('physical reads','CPU used by this session');

l_starting_time := dbms_utility.get_time();

SELECT count(*) INTO l_count FROM t;

l_ending_time := dbms_utility.get_time();
SELECT sum(decode(name, 'physical reads', value)),
sum(decode(name, 'CPU used by this session', value))
INTO l_ending_blocks, l_ending_cpu
FROM v$mystat ms JOIN v$statname USING (statistic#)
WHERE name IN ('physical reads','CPU used by this session');

l_time := round((l_ending_time-l_starting_time)/100,1);
l_blocks := l_ending_blocks-l_starting_blocks;
l_cpu := l_ending_cpu-l_starting_cpu;
dbms_output.put_line(l_dbfmbrc||' '||l_blocks||' '||to_char(l_time)||' '||to_char(l_cpu));
END LOOP;