Database Reference
In-Depth Information
inversely proportional to the degree of parallelism. In fact, because database servers don't scale infinitely, for high
degrees of parallelism the estimated costs are too low. This is exactly why
maxthr
is available. Figure
7-5
shows, for
the same case illustrated in Figure
7-4
, the impact of setting
maxthr
to prevent the costs from falling below a specific
threshold. Notice that although the minimum cost is independent of the
slavethr
value, the cap takes place at a
different degree of parallelism.
Figure 7-5.
Comparison of the estimated I/O costs with
maxthr
set (data generated by the
parallel_fts_costing.sql
script)
As illustrated in Figure
7-5
, the value of
maxthr
stops the cost of decreasing when the degree of parallelism is
getting too high. Simply put, the query optimizer computes, based on Formula 7-8, that the cost can't be lower than a
specific value.
Formula 7-8.
The ratio between the expected throughput of a single server process and the maximum disk I/O
throughput for the whole system limits the parallel I/O cost (note that k is defined in the note of the previous page)
minimum_parallel_io_cost erial_io_cost
mreadthr
maxthr k
≈
⋅
⋅
As discussed in this section, system statistics make the query optimizer aware of the system where the database
engine is running. This means they're essential for a successful configuration. I recommend freezing them in order to
have some stability in the generation of execution plans. In other words, I consider them as initialization parameters.
Of course, in case of major hardware or software changes, system statistics should be recomputed, and as a result,
the whole configuration should be checked. For checking purposes, it's also possible to regularly gather them in a
backup table (in other words, using the
statown
and
stattab
parameters of the
gather_system_stats
procedure)
and verify whether there's a major difference between the current values and the values stored in the data dictionary.
On to Chapter 8
This chapter describes what system statistics are and why the query optimizer needs them. Simply put, they provide
performance information about the CPU and the disk I/O subsystem. The chapter also coveres how to manage system
statistics with the
dbms_stats
package, and where to find them in the data dictionary.
System statistics aren't sufficient though, to fully describe the environment in which the query optimizer
operates. The query optimizer also needs insight about the data stored in the database. For that purpose, another type
of statistics is available: object statistics. The next chapter provides full coverage of this second type of statistics.
