Tuning the database before entering the production phase

The main dilemma associated with the use of cost-based optimizer (CBO) is a SQL tuning before entering the database in production phase. Most of the development and test database contains much less data than the production database. It is therefore very likely that the CBO will take different decisions on the execution plan. Many sites can not afford the cost and inconvenience of copying the production database to the pre-production.

Oracle8i and later systems offer a variety of mechanisms to solve this problem, and the DBMS_STATS package and the mechanism of scenarios (outlines).

Internal cost-performance optimizer

In contrast to the rule-based optimizer cost optimizer does not have rigid rules that allow quick identification of execution path. CBO is a tool flexible and can adapt to the working environment. Such adaptations are possible if they are refreshed (re-examined) statistics concerning the required objects. Constant is the only method by which the cost optimizer calculates every possible plan and evaluate its cost (efficiency).

Operation can be cost-based optimizer (informal) break down into the following steps:

1. Parsing SQL statements (syntax checking, powers possessed by objects, etc.).
2. Create a list of all possible execution plans.
3. Calculate (estimate) the cost of each execution plans using available statistics objects.
4. Selecting the execution plan with the lowest cost.

Cost optimizer is used when even one table with SQL command has statistics (statistics for tables niezanalizowanych are estimated). If there are no statistics available for any of the tables that appear in the command, the database system uses rule-based optimizer, unless forced to use a cost-based optimizer earlier guidance levels of command (hints) or option ALL_ROWS or FIRST_ROWS optimizer.
Understanding of the cost-based optimizer - and consequently its ability to use - requires an understanding of how it is "thinking."

Equality primary key and (or) a unique index

The selectivity of a unique index (including in its definition of the UNIQUE keyword) is 100%. No other method of access to data using an index could not be better. Therefore, a unique index is used whenever possible.

Equality non-unique Index

If indexes are calculated  non-unique their selectivity. Cost optimizer assumes that the table (and the corresponding indices) has data distributed evenly, unless the ANALYZE command option is used INDEXED COLUMNS FOR ALL. This option causes the optimizer receives information about possible asymmetry data in indexed columns.

Calculation of the range

In the case of execution plans for ranges of index values ​​calculated their selectivity. These calculations are based on the values ​​of the upper and lower recently created statistics. Cost optimizer again assumes a uniform distribution of the data in the table (and the relevant index), except that during the process of analyzing the table option is specified INDEXED COLUMNS FOR ALL.

Calculation based on the scope of bind variables

In this case, the execution plans for a selectivity index ranges guessed. Before the introduction of Oracle9i - due to the fact that the values ​​of bind variables were not available at the time of parsing (values ​​are passed to the cursor after determining the execution plan) - optimizer can not make decisions based on these values. According to a rule of thumb therefore assumed 25% selectivity for any range of bind variables (for example, WHERE dept_no =: b1) and 50% selectivity to the limited range (for example, WHERE dept_no>: b1 AND dept_no