Histograms

Before the entering of the system, Oracle 7.3 histograms, cost optimizer was not able to distinguish strongly uneven distribution of key data.

Use of system resources

By default, the cost optimizer assumes that the user is the only one that works with the database. In Oracle9i can store information on the use of system resources, allowing you to make much better decisions on the basis of load (DBMS_STATS procedure. GATHER_SYSTEM_STATS).

Relevant statistics are current

Cost optimizer may incorrectly choose a plan, if the table was analyzed, but the indexes were analyzed, as well as when the indexes are analyzed, and the table does not.

One should not impose cost-based optimizer to use the system via included instructions when for any table with SQL commands are not available statistics. Using the old (outdated) statistics could pose a greater danger than the estimation of statistics at runtime. But we must remember that the statistics change frequently and may not be desirable (especially for mission critical systems used by many users). Always back up statistics with DBMS_STATS procedures. EXPORT_SCHEMA_STATS before re-analysis.

Analysis of large tables and indexes associated with the COMPUTE option takes time, requires a lot of CPU, input-output system, and temporary tablespaces. It is often unnecessary. Analysis of a consistent value (for example ESTIMATE 3%) usually allows the optimizer to undertake optimal decisions.
The combination of the information received by the principles of selectivity with other information about the system input-output database allows the optimizer to calculate the cost of an execution plan.

EXPLAIN PLAN option cost-based optimizer

Oracle can provide information on the cost of a query when you use EXPLAIN PLAN feature. EXPLAIN PLAN feature can be used to display the calculated cost of execution after certain extensions. In particular, the COST column of the table contains the value of the plan, which reflects a decrease or increase the relative cost of a query.

For example:

EXPLAIN PLAN FOR SELECT COUNT (*)
FROM winners, horses WHERE winners.owner = horses.owner
Winners.horse_name AND LIKE 'Mr.%';
COLUMN 'SQL' FORMAT A56
SELECT lpad ('', 2 * level) | operation | |''
| [Options '' jobject_namej | decode (OBJECT "TYPE, • '•'
1 ('| | v ect_type | |') ') "SQL" cost "Cost", cardinality "Num Rows" FROM plan_table
CONNECT BY PRIOR id = parent_id START WITH id = 0;

By manipulating the optimizer cost effective (for example, through the included instructions, by creating or deleting indexes, by adjusting how are analyzed indexes or tables)

You can see the difference in the cost of performance calculated by the optimizer.

It used EXPLAIN PLAN to compare different versions of the query and select the version with the lowest relative cost.

In order to achieve the absolute best performance in many centers analyzed most of the tables and indexes, and a small number of tables, which are used separately, are not subject to analysis. This is usually to provide rules-based behavior of the tables which are not analyzed. However, the important issue is to ensure that the tables were Connectors Not analyzed with tables analyzed.