Oracle Server Metrics and SQL Execution

When determining the best execution plan for an SQL query, the newly enhanced cost-based optimizer (CBO) considers external influences.  Since the Oracle database does not run in a vacuum, the CBO must have the ability to factor in the costs of external disk I/O as well as the cost of CPU cycles for each SQL operation.  This is a significant step forward in making the CBO one of the most sophisticated software packages in the world.  Choosing the best execution plan for any SQL statement is always the job of the CBO and is no small challenge.

In accordance with Oracle documentation, the I/O and CPU costs are estimated as shown below:

Cost =  (#SRds * sreadtim + #MRds * mreadtim + #CPUCycles

         ------------------------------------------------

                          cpuspeed )

         -------------------------------------------------

                          sreadtim

where:

§       #SRDs: number of single block reads

§       #MRDs: number of multi block reads

§       #CPUCycles: number of CPU Cycles

§       sreadtim: single block read time

§       mreadtim: multi block read time

§       cpuspeed:  CPU cycles per second

The external costing is markedly influenced by the calculated cost of disk reads as measured by the v$ tables and the estimated CPU costs associated with each internal operation. By storing the details regarding the costs of many components of SQL execution, Oracle can use these average costs to influence the choices made by the cost-based SQL optimizer. Here are some examples:

Hash join costs: Oracle records the average amount of RAM memory consumed by a hash join.

Sort costs: Oracle tracks the RAM necessary for sorting and aggregation operations.

Table scan costs: Oracle keeps information about the amount of time that is essential to performing a multiblock read such as db file scatter reads .

Index block access costs: Oracle stores the average time required to fetch a single block such as db file sequential reads.

Depending on the optimizer_mode choice, Oracle costs are weighed differently.  If the all_rows optimizer mode is utilized for a data warehouse, the CBO will be heavily influenced by external factors due to the fact that the all_rows mode is designed to minimize resource consumption. On the other hand, if there is an OLTP system with the first_rows optimizer mode, the CBO deems it more imperative to return rows quickly than to minimize resource costs.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm