oracle| optimization
The purpose of this article:
1. Say something about Oracle's optimizer and its related knowledge.
2, answer why sometimes a table of a field is clearly indexed, when the observation of some SQL execution plan, found that the problem does not go to the index.
3, if you have doubts about First_rows, all_rows These two modes, you can also look at this article.
Let's go:
Before Oracle executes a SQL, it first analyzes the execution plan of the statement and then executes the execution plan. The execution plan for parsing statements is performed by the optimizer (Optimizer). In different cases, a single SQL may have multiple execution plans, but at some point there must be only one execution plan that is optimal and spends the least time. I believe you will use Pl/sql Developer, Toad and other tools to see the execution plan of a statement, but you may have questions about rule, Choose, first rows, and all rows, because I was like this, At that time I also wondered why the different items were chosen, and the execution plan changed.
1. Optimization method of Optimizer
Oracle's optimizer has a total of two optimization methods, i.e. rule-based optimization (rule-based optimization, short rbo) and cost based optimization (cost-based optimization, referred to as CBO).
A, Rbo Way: 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.
B, CBO way: according to the meaning of the word, it is to look at the cost of the statement, where the cost of mainly refers to the CPU and memory. 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 in the library at first, it is you do analyze after the appearance, a lot of time and the expiration of statistics will make the optimizer made a wrong execution plan, because we should update this information in a timely manner. In Oracle8 and later versions, the Oracle column recommends a CBO approach.
We want to understand, not necessarily go index is excellent, such as a table with only two rows of data, one IO can complete the whole table retrieval, and then go to the index when the need for two times IO, then the table to do a full table scan (scan) is the best.
2, optimizer optimization mode (Optermizer mode)
The optimization model includes the four Rule,choose,first rows,all rows, which we mentioned above. I would like to explain the following:
Rule: Needless to say, it's a rule-based approach.
Choolse: That's what we should be watching, and that's the way Oracle uses it by default. Refers to when a table or index has statistics, the way of the CBO, if the table or index does not have statistical information, the table is not particularly small, and the corresponding columns 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.
All rows: That is what we mean by cost, when a table has statistics, it returns all the rows of the table in the quickest way, increasing the throughput of the query as a whole. There is no statistical information to go the rule-based approach.
3, how to set the choice of which optimization mode
A, instance level
We can set Optimizer_mode=rule, Optimizer_mode=choose, Optimizer_mode=first_rows, OPTIMIZER_ in the Init<sid>.ora file Mode=all_rows to choose 3 Four ways, if you do not set the Optimizer_mode parameter is the default choose this way.
B, Sessions level
Set by sql> ALTER session set optimizer_mode=<mode>;.
C, statement level
These need to use hint, such as:
sql> SELECT/*+ Rule * * A.userid,
2 B.name,
3 B.depart_name
4 from Tf_f_yhda A,
5 Tf_f_depart B
6 WHERE A.userid=b.userid;
4, why sometimes a table of a field is clearly indexed, when the observation of some language implementation plan does not take the index? How to solve it?
A, do not go to the index generally have the following several reasons
♀ you're using the All_rows method at the instance level.
♀ the statistics of your table (most probable cause)
♀ your table is small, as mentioned above, the Oracle optimizer does not think it is worthwhile to go to the index.
B, Solution
♀ can modify the Optimizer_mode parameter in Init<sid>.ora, change it to rule or choose, and restart the database. You can also use the hint mentioned in 4.
♀ deletion of statistical information
Sql>analyze table table_name Delete statistics;
♀ table Small not go index is right, do not need to tune.
5, Other related
A, how to see whether a table or index is statistical information
Sql>select * from User_tables
2 WHERE table_name=<table_name>
3 and num_rows is not null;
Sql>select * from User_indexes
2 WHERE table_name=<table_name>
3 and num_rows is not null;
B, if we first use the CBO approach, we should update the table and index statistics in time to avoid the form of unrealistic implementation plans.
sql> ANALYZE TABLE table_name COMPUTE STATISTICS;
sql> ANALYZE INDEX index_name estimate STATISTICS;
For specific analyze statements, refer to the refrence documentation for oracle8i/9i.
(End of full text)
