Two ways to optimize Oracle Optimizer

There are two ways to optimize Oracle's optimizer:

Rule-based optimization method: rule-based optimization (RBO)

Optimization method based on cost or statistic information (cost-based OPTIMIZATION:CBO)

Rbo: When the optimizer analyzes SQL statements, it follows some rules that are predetermined within Oracle. For example, we usually go to the index when a column in a WHERE clause has an index.

CBO approach: The CBO was introduced in ORACLE7, but matured in the oracle8i. ORACLE has declared that in the Oracle9i version, RBO will no longer support it. It is the cost of reading statements, where the cost is mainly CPU and memory. The CPU costing is now default to the sum of CPU+I/O. Available through Dbms_xplan. Display_cursor observes more detailed implementation plans. When the optimizer determines whether to use this method, the main reference is the statistics of tables and indexes. Statistics give information about the size of the table, the number of rows, the length of each line, and so on. These statistics are not at first in the library, is done after the analyze, many of the time delay statistics will cause the optimizer to make a wrong execution plan, because some should update the information in a timely manner. Presumably, the CBO should automatically collect, but not actually, sometimes in the CBO case, you must regularly analysis of the large table.

Note: Indexing is not necessarily excellent, such as a table with only two rows of data, one IO can complete the whole table retrieval, and this time to go to the index requires two times IO, when the full table scan (scan) is the best.

Oracle uses Optimizer_mode parameters to control the preferences of the optimizer, and several 9i commonly used parameters are: First_rows,all_rows,first_rows_n,rule,choose and so on. and 10g less rule and choose.

Rule: a rule-based approach.

Choolse: Refers to when a table or index has statistics, the way of the CBO, if the table or index no statistics, the table is not particularly small, and the corresponding column indexed, then go to the index, walk Rbo way.

First rows: It's similar to the Choose approach, and the difference is that when a table has statistics, it will be the quickest way to return the initial rows of the query, reducing the response time overall.

This column more highlights: http://www.bianceng.cn/database/Oracle/

The default value in all rows:10g, which is what we call cost, when a table has statistics, it returns all rows of the table in the quickest way, increasing the throughput of the query as a whole. There is no statistical information on the way to go rbo.

To set the optimizer mode:

(1) Instance level we can set in the Initsid.ora file

Optimizer_mode=rule/choose/first_rows/all_rows

Sql> Show Parameter Optimizer_mode

NAME TYPE VALUE

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

Optimizer_mode string All_rows

(2) The sessions level is set by alter session set Optimizer_mode=rule/choose/first_rows/all_rows.

(3) The statement level is set with hint (/*+ ... * *).

Optimizer_index_cost_adj parameters

Parameter Optimizer_index_cost_adj can understand the cost of performing multiple block (multiblock) I/O (such as full table scans) for Oracle and the relative proportion of the execution of Tan (single-block) I/o costs. Optimizer_index_cost_adj affects the behavior of the CBO by indicating the relative ratio of index I/O costs to the scan full table I/O costs, and the smaller the CBO, the more inclined it is to use the index, and the larger the value, the more likely it is to scan the whole table. The default value of 100 indicates a lack of savings, the price of which is equal.

This parameter is described in the official document (Reference) as follows:

Optimizer_index_cost_adj

Property

Description

Parameter type

Integer

Default value

100

Modifiable

Alter session, ALTER SYSTEM

Range of values

1 to 10000

Optimizer_index_cost_adj lets you tune OPTIMIZER behavior for access path selection to is more or less INDEX friendly-that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter was percent, at which the optimizer evaluates index access paths at the regular. Any other value makes the optimizer evaluate the access path at this percentage of the regular cost. For example, a setting of makes the index access path look half as expensive as normal.

Note:

The adjustment does not apply to user-defined cost functions for domain indexes.

Originated from: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams160.htm#REFRN10143

Considerations for writing SQL statements under the CBO:

1, Rbo since Oracle 6 edition has been adopted, there is a strict rule of use, so long as you follow it to write SQL statements, no matter what the contents of the data table, it will not affect your "execution plan", that is, not "sensitive" to the data, the CBO calculates a variety of possible "implementation Plan" of the "price", Cost, from which the lowest cost of the scheme, as the actual operation of the program. The calculation of the cost of each "execution plan" depends on the statistical distribution of the data in the data table, the Oracle database itself is not clear about the statistical distribution, and the tables and associated indexes (using the Analyze command) must be analyzed to gather the data required by the CBO.

2. When using the CBO, when you write an SQL statement, you do not have to consider the order of the table or view following the "from" clause and the order of the conditions following the "WHERE" clause; many of the new technologies that Oracle has adopted since version 7 are based on CBO, such as star-linked array queries, hash-join queries, function and parallel queries.

3. In general, the CBO chooses the "Implementation plan" is not worse than the Rbo "execution plan", and relatively, the CBO's requirements for programmers is not rbo so harsh, saving the programmer to choose from a number of possible "execution plan" to select an optimal solution to spend the debugging time, But there are also problems in some situations. Some of the more typical problems are: Sometimes it shows that there are indexes in Ming Dynasty, but the query process obviously does not use the relevant indexes, which results in a lengthy and resource-intensive query process that requires careful analysis of the execution plan to find out why. For example, you can see whether the connection order permits the use of related indexes. Assuming that there is an index on the Deptno column of the table EMP, there is no index on the column deptno the table dept and the WHERE statement has emp.deptno=dept.deptno conditions. In the NL connection, the EMP as the appearance, first accessed, because of the connection mechanism, the appearance of the data access is a full table scan, the index on the Emp.deptno is obviously not used, at most on its index full scan or index fast full scan.