Oracle's optimization Principles

First, Oracle has an optimizer (Optimizer), and Oracle's optimization mechanism starts with Optimizer.

Clear two concepts: there are two ways to optimize Oracle Optimizer, one is rule-based, we call RBO (rule-based optimization), One is the cost-based CBO (cost-based optimization), we can literally understand the meaning of these two optimizations, yes, RBO is based on Oracle's default rules, such as I mentioned in the Oracle Performance Tuning principle: Index, The index is Oracle's default rules, and for the CBO, because it is based on the cost, that is, Oracle machine resources, such as CPU and memory, this time a friend asked, since there are these two ways, that Oracle exactly how to choose it, in fact, Half of the question needs to be answered by us, and half by Oracle. Because when we installed Oracle, we decided what Oracle called, this is the Optimizer_mode parameter set in Init.ora, and if we set it to Optimizer_mode=rule, it will be rbo. Instead, press CBO. Of course, we can also think of changing rules when developing a program, such as SQL:

Select/*rule*/* FROM TABLE_NAME, which makes Oracle enforce RBO rules, of course, Oracle is also more "smart", it will be based on the user to retrieve the table and the table's index statistics to choose the optimization method, there is a problem here, If the statistics are stale or wrong, we have to analyze the tables and indexes manually. Http://yfqp.biz

Oracle's optimization model is as follows: Rule,choose,firstrows,allrows Four, these four modes must be explained with the optimization method described above, CHOOSE is the meaning of choice, if there are statistics on the table and index, Oracle will choose the CBO's optimization approach. For Firstrows, I explained that it was about the same as choose, just returning the previous record line in the quickest way, and of course allrows was the quickest way to return all rows.

The above describes how Oracle optimizes and optimizes patterns, and how Oracle chooses, and I'll talk about what we consider to be a change to Oracle's optimizations:

1, modify the Optimizer_mode parameters of Oracle's Init.ora;

2, in the writing of SQL when the specified optimization mode, such as: SELECT/*rule*/* FROM table_name

3, in order not to let Oracle make mistakes, we need to use the following statements in daily maintenance to optimize tables and indexes:

Spool D:\temp\analyze_table.sql

SELECT ' Analyze table ' | | table_name| | ' compute statistics; ' from User_tables;

Spool off

Spool D:\temp\analyze_index.sql

SELECT ' analyze index ' | | index_name| | ' compute statistics; ' from user_indexes;

Spool off

The SQL above will produce a lot of SQL for our tables and indexes, and we can execute the SQL again.

OK, these are the optimization mechanism of Oracle and we have a human impact, I believe you understand, and hope to help you.