CPU Based Optimizer Costing
The new CPU Costing feature, controlled by the
_optimizer_cost_model=cpu
hidden parameter, enhances the CBO capabilities by
allowing it to estimate the number of machine cycles necessary
for an operation. This cost subsequently counts in the
execution plan calculation. The CPU costs
affiliated with servicing an Oracle query hinge on the current
server load, which Oracle cannot see. Generally, CPU costs are
not considered significant unless the entire Oracle instance is
using excessive CPU resources.
I/O Costing
While the CBO is now enhanced to figure the number of
physical block reads required for an operation, it has not yet
been quite perfected. For example, the CBO is not yet aware of
the percentage of a table’s blocks that reside in the data
buffer.
The I/O cost is proportional to the number of physical data
blocks read by the operation. However, the CBO has no
prior information on the data buffer contents and cannot
distinguish between a logical read (in-buffer) and a physical
read. Due to this shortcoming, the CBO cannot know if the data
blocks are already in the RAM data buffers.
The best environment for using CPU costing is for all_rows
execution plans, where cost is more noteworthy than with
first_rows
optimization.
External costing does not take into account the number of data
blocks residing in the RAM data buffers; however, a future
release of the CBO is likely to incorporate this
element. Additionally, costs are a function of the number of
reads and the relative read times plus the CPU cost estimate for
the query.
In evaluating the execution plan , Oracle
uses both the CPU and I/O cost estimations. This equation
becomes even more complex when parallel querying is factored in
when several concurrent processes are servicing the query.
Oracle Network affects Oracle performance and the AWR data can
help users detect and fix data transmission issues.
SEE CODE DEPOT FOR FULL SCRIPTS
