A statement's cost is the amount of resources consumed at execution time. That's impossible to calculate exactly and for all time, but we can make a stab at it.

  • One CPU unit is the amount of time needed to execute one operation.

  • One I/O unit is the amount of time needed to read or write one page.

  • One Com unit is the amount of time needed to send one network message.

Oracle Has Two Optimizers

In Oracle and only in Oracle, one must distinguish the capitalized terms Rule-Based Optimizer and Cost-Based Optimizer, because Oracle uses those terms to refer to separate tools. The Oracle Cost-Based Optimizer is an enhanced alternative to the Rule-Based Optimizer, which is no longer being maintained .

The relative unit weights vary with time, but here's an approximation , based on Oracle's CBO weights for disk accesses versus cached accesses .

  • One I/O unit equals 1000 CPU units.

  • One Com unit equals 1.5 I/O units.

When a DBMS estimates cost for one statement, it lacks full information about what statements will occur before and after at execution time. Therefore, it can only guess what will be in cache, whether processors will be available, how much locking will be going on, and so forth; all of these items depend on activity, and the optimizer doesn't base cost estimates on activity. It bases them, as we've seen, on rules and database contents. (That's why DBAs have to monitor activity all the time.)

When executing SQL statements, then, the decisions made by the DBMS are strongly influenced by the optimizer's picture of the database contents. This picture, or data model, is used to estimate the costs of the various access plans that could be used to resolve a specific SQL statement. A key element in the data model is a set of statistics about the data, stored in the system catalog. A change in the data statistics can result in a change in the access plan selected as the most efficient method of getting the data you need.

Optimizer Terminology

Here are some common terms you'll come across when you're examining your DBMS's optimizer documentation.

Access plan: the plan used by the optimizer to resolve an SQL statement.

Cardinality: the number of rows in an object (table or result set). For a table, this is decided by INSERT statements.

Cost: the amount of resources consumed during the execution of an SQL statement (that is, the total execution time required to resolve the statement).

Degree: the number of attributes. This is decided by the CREATE statement for the object.

Heuristic: an educated guess. Well, everything is an educated guess, but quality optimizers have more information available.


SQL Performance Tuning
SQL Performance Tuning
ISBN: 0201791692
EAN: 2147483647
Year: 2005
Pages: 125

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: