Oracle Optimization Principles

First, explain that ORACLE has an Optimizer. The optimization mechanism of ORACLE begins with Optimizer.

There are two concepts clearly: Optimizer has two ways to optimize ORACLE. One is Rule-Based and we call it RBO (Rule-Based Optimization ), one is Cost-Based CBO (Cost-Based Optimization). We can basically understand the meaning of the two Optimization methods literally. Good, RBO is implemented according to ORACLE's internal rules, for example, as I mentioned in "ORACLE Performance Tuning principle", indexes are ORACLE's internal rules. For CBO, It is cost-based, that is, the resources of the ORACLE machine, such as CPU and memory, some friends asked at this time, since there are these two methods, how should ORACLE choose? In fact, half of this question should be answered by ourselves, and half should be answered by ORACLE. Since we have decided on how ORACLE is called when installing ORACLE, this is init. the OPTIMIZER_MODE parameter set in ora. If we set it to OPTIMIZER_MODE = RULE, it will be executed in RBO mode, and vice versa. Of course, we can also consider changing rules when developing programs, such as SQL:

SELECT/* RULE */* FROM TABLE_NAME, which forces ORACLE to use RBO rules. Of course, ORACLE is also "smart ", it selects an optimization method based on the statistical information of the user's retrieval table and the index of the table. Here, a problem occurs. If the statistical information is outdated or incorrect, we have to re-calculate the table and index with analyze manually. Http://yfqp.biz

ORACLE has the following optimization modes: RULE, CHOOSE, FIRSTROWS, and ALLROWS. These four modes must be explained together with the preceding optimization methods. CHOOSE is the meaning of selection, if statistical information is available for tables and indexes, ORACLE will optimize the choose cbo. For FIRSTROWS, I explained that it is similar to CHOOSE, but only returns the previous record rows in the fastest way. Of course, ALLROWS returns all rows in the quickest way.

The above describes the optimization methods and modes of ORACLE and how to choose ORACLE. Next I will talk about how we think ORACLE optimization is changed:

1. Modify the OPTIMIZER_MODE parameter of init. ora of ORACLE;

2. Manually specify the optimization mode when writing SQL statements, for example: SELECT/* RULE */* FROM TABLE_NAME

3. To prevent ORACLE from making mistakes, we need to use the following statement in daily maintenance to optimize tables and indexes:

Spool d: \ temp \ analyze_table. SQL

SELECT 'analyze table' | table_name | 'ute statistics; 'from user_tables;

Spool off

Spool d: \ temp \ analyze_index. SQL

SELECT 'analyze Index' | index_name | 'ute statistics; 'from user_indexes;

Spool off

The preceding SQL statements will generate batch SQL statements for our tables and indexes. Then we can execute these SQL statements.

OK. These are the ORACLE optimization mechanisms and our impact. I believe you understand them and hope they will help you.