Database Reference
In-Depth Information
CHAPTER 7
■ ■ ■
Event 10053 and the
Cost Based Optimizer
T
here is no better way to comprehend decisions and cost calculations of the cost based optimizer
(CBO) than to read an optimizer trace file generated with the undocumented event 10053.
Oracle Support will usually request such a trace file if you intend to file a technical assistance
request against the optimizer in situations where it fails to find an execution plan that yields an
acceptable response time.
Essentially, the CBO is a mathematical model for calculating estimates of SQL statement
response times. It receives initialization parameters, object statistics pertaining to tables and
indexes, as well as system statistics that represent the capabilities of the hardware, as input.
According to
Oracle9i Database Performance Tuning Guide and Reference Release 2
, the cost
calculation formula for serial execution used by the optimizer is as follows:
CPU Cycles
cpuspeed
SRds
sreadtim
+
MRds
mreadtim
+
-----------------------------
---------------------------------------------------------------------------------------------------------------------------------
sreadtim
Thus, the unit of cost is the time it takes to complete a single block read. Table 7-1 explains
the placeholders used in the formula. In case you are familiar with system statistics gathering
using
DBMS_STATS.GATHER_SYSTEM_STATS
, the three placeholders—sreadtim, mreadtim, and
cpuspeed—will be familiar. All three are part of so-called workload statistics, which are derived
from measurements of the system on which the DBMS runs. The documented interfaces for
setting these parameters (and a few more) are the packaged procedures
DBMS_STATS.SET_
SYSTEM_STATS
and
DBMS_STATS.IMPORT_SYSTEM_STATS
. The current settings may be retrieved
with a call to the packaged procedure
DBMS_STATS.GET_SYSTEM_STATS
. System statistics were
optional in Oracle9
i
. Oracle10
g
uses so-called
noworkload statistics
if actual measurements
have not been imported into the data dictionary table
SYS.AUX_STATS$
by one of the interfaces
cited above.
63
