System Statistics - Troubleshooting Oracle Performance - page 199

Formula 7-2. The overall costs are based on the I/O costs and the CPU costs .

cpu_cost
cpuspeed sreadtim 1000

cost ≈ io_cost +

⋅ ⋅

To compute the overall cost with noworkload statistics, in Formula 7-2 cpuspeed is replaced by cpuspeednw ,
and sreadtim is computed using Formula 7-3. Simply put, to compute sreadtim , Formula 7-3 adds the time needed to
locate a block on disk with the time needed to transfer it to the database instance.

Formula 7-3. If necessary, sreadtim is computed based on noworkload statistics and the default block size of the
database .

db_block_size
iotfrspeed

sreadtim ioseektim ≈ +

Generally speaking, if workload statistics are available, the query optimizer uses them and ignores noworkload
statistics. You should be aware that the query optimizer performs several sanity checks that could disable or partially
replace workload statistics. You can look into this behavior through the system_stats_sanity_checks.sql script.
The following are some items to watch for:

mbrc isn't available or set to 0, the query optimizer ignores workload statistics.
The result is that noworkload statistics are used instead.

• When

sreadtim isn't available or set to 0, the query optimizer recomputes the value of
sreadtim and mreadtim using Formula 7-3 and Formula 7-4, respectively.

• When

mreadtim isn't available, or when it isn't greater than sreadtim , the query optimizer
recomputes the value of sreadtim and mreadtim using Formula 7-3 and Formula 7-4,
respectively.

• When

Formula 7-4. The computation of mreadtim based on noworkload statistics and the default block size of the database

mbrc db_block_size
iotfrspeed

⋅

mreadtim ioseektim ≈ +

A particular case where Formula 7-3 and Formula 7-4 are used is when only noworkload statistics gathered in
exadata mode are available. In fact, with this kind of statistics, all estimations are based on mbrc , ioseektim , and
iotfrspeed .
What's the role played by slavethr and maxthr for the estimations related to SQL statements that are considered
to be executed in parallel? Simply put, while the former can increase the cost of parallel executions, the latter can
cap the cost of parallel executions with a high degree of parallelism. I'll now discuss the impact of these two statistics
in detail.
When slavethr and maxthr aren't set, the query optimizer, as shown by Formula 7-5, considers that the cost of
an operation executed in parallel is inversely proportional to the degree of parallelism used to execute it. As a result,
the query optimizer considers that whatever the degree of parallelism is, every slave process running in parallel is able
to sustain the throughput computed by Formula 7-6.

Formula 7-5. The parallel I/O cost is inversely proportional to the degree of parallelism. Note that the constant 0.9 is a
fudge factor that probably takes into account the inevitable contention due to parallel processing .

serial_io_cost
dop 0.9

parallel_io_cost ≈