Cost Based Optimizer - Common Misconceptions and Issues
Cost-based optimizer - general error concept and problem
Introduction introduction
~~~~~~~~~~~~
This short message intentionally eliminates some of the cost-based optimizer (CBO) error, emphasizing general errors and problems.
Background background ~~~~~~~~~~ In order to perform any SQL statement, Oracle must first export a "Execution Plan". It is basically ORACLE how to retrieve the execution plan that meets the data required for a given SQL statement.
Oracle7 and Oracle8 have two optimizers that can be executed for SQL statements:
- Rule-based optimizer (RBO)
Inherit from Oracle6, it uses a series of strict rules to determine the execution plan of each SQL statement. If you know these rules, you can construct a SQL query to access data in a specified manner. The content of the table has no effect on the execution plan.
This optimizer is no longer enhanced, so many Oracle8 can not be used.
- Cost-based optimizer (CBO)
Be introduced from Oracle7 that attempts to find the lowest cost access data, for maximum throughput or the fastest initial response time. Calculate the cost of using different execution plans and select the lowest cost. The statistics on the data content of the table are used to determine the execution plan.
Fundamental Points Basic Point
~~~~~~~~~~~~~~~~~~
There are many possible execution plans for each SQL statement.
"Best Plan" is always the "best plan", no matter how it arrives.
Best plan can be made by two:
1 This plan uses the smallest resource to handle all rows involved in this statement. [Called all_ROWS]
2 This plan returns the first row of this statement at the shortest time. [Called first_ROWS]
CBO does not understand the related characteristics of the application, and it does not fully understand the impact of complex relationships between association tables. Only limited information can be used to determine the best plan.
CBO determines the best plan by calculating the estimation cost of different implementations and selects the lowest cost plan.
Because this relationship is related to the assumption, the selected plan is not necessarily the best plan.
This situation is often used as a bug report to Oracle technical support because CBO does not select a best plan for a designated scheme.
People can usually be confirmed because the given input statistics are effective and the default "cost" is involved. The selected plan is calculated as the best plan, although it is not. Regardless of how CBO improves, there will always be the best plan. So, you must often prepare the optimization statement.
The function of RBO is no longer enhanced. This means that some execution plans only valid for CBO. However, RBO will continue to exist in Oracle 8.
Before you continue before you continue
~~~~~~~~~~~~~~~~~~~
It is not recommended to use CBO in Oracle Releases 7.0.x.
The information in this article applies to Oracle Releases 7.1 or more (including Oracle 8.0).
Base Statistics Basic Statistics
~~~~~~~~~~~~~~~
in order to
To give CBO's most information (have a chance to choose a good execution plan), you must do what will be queried.
Analyze operations with Estimate options can generate incorrect results for some tables, especially those with smaller sampling. This is not a bug, but the characteristics of each statistical sampling method. If the selected sample does not represent the entire data set, you cannot expect the correct statistics.
In Oracle 7.1 and 7.2, the value of the column is assumed to be uniform distribution. This is an important limit on these versions, complete and accurate statistics, can not point out the distribution of actual data. This limitation is partially solved in Oracle Release 7.3 or later, which can save the distribution information of the column value - But these additional information may have no practical help to some types of queries, please see the attention of Bind Variables in the following chapters. matter. Consider the following important issues when considering using Analyze:
- An Analyze of a table with an index will analyze its correlation index. (Possible value analysis table in Oracle 7.3 without analyzing the index.)
- If you perform Analyze ... Estimate analysis for a table, then INALYZE COMPUTE analysis is wise on its related index. This ensures that the statistics of the index field are accurate.
- Analysis indexes do not have to be temporary table space
- If an index is analyzed without analyzing its base table, CBO will not be selected on this single basis.
- If you need to use Estimate- Estimation (for example, due to time limit), it is recommended that you perform Analyze ... Estimate on several different sampling sampling to determine the ideal sampling size of each object. The overall goal is to find a sampling size that produces accurate statistics in the shortest time. The better start point is 10% - 15%.
- A more than 50% of Analyze ... Estimate will result in / become analogize ... compute.
- 7.1.6 Previous versions When performing Analyze ... Estimate, ORA 600 is wrong.
- Do not analyze the data dictionary table (SYS table) unless you have sufficient reasons. About this is contradictory with some documents or readme files, they say dbms_utility.Analyze_schema can be used to analyze the SYS table. Although dbms_utility.analyze_schema can analyze SYS users, Oracle does not decline for these analytical tables, which may cause deadlocks or efficiency.
Optimizer Goal / Mode optimization goals and patterns
~~~~~~~~~~~~~~~~~~~~~
What kind of optimizer and its operation are determined by the following factors:
Object Type object type
- Some object types are based on rule optimization. For example, the index table (IoT) Rbo is not aware that CBO will be automatically used in the inquiry involving the IoT.
Parallel Degree> 1 On A Table Table is greater than 1
- If the parallelism of a table in the query is greater than one, CBO will be adopted regardless of the prompt, whether the value of Optimizer_Mode or Optimizer_Goal is "Rule". Suitable for Oracle 7.3 or more.
- In Oracle 8.0.5 and Oracle 8.1.5 Releases If any index is more than 1, CBO will also be adopted. Only for Oracle 8.0.5 and Oracle 8.1.5.
Hints prompt
- In addition to any tips other than Rule, it will cause CBO. Hint can't be turned off by any parameters, this is very important.
Session Level Session level Optimizer_Goal
- If there is no given condition, the optimizer is determined by the session stage parameter Optimizer_Goal. If one of the above conditions is given, Optimizer_Goal does not work.
If Optimizer_Goal is set to Rule, RBO will be used without the statistics of any table.
If Optimizer_Goal is set to Choose, you will use all_rows for as long as there is a query being analyzed. Init.ora
Optimizer_Mode parameter
- The default setting of the session stage Optimizer_Goal parameter is the value of Optimizer_Mode in the init.ora file.
The PL / SQL block (including anonymous block and stored procedure) should use an explicit prompt (hint) to determine the actual optimization method. Without specifying a prompt, parallel or "cbo-only" object, the optimizer in the SQL statement in the PL / SQL block is shown in:
Init.ora Optimizer_Mode
Mode Used in PLSQL
Rule
Rule
Choose
All_Rows
All_Rows
All_Rows
First_ROWS
All_Rows
Summary Optimizer Mode: Summary of Optimization Mode
~~~~~~~~~~~~~~~~~~~~~~
For the above article, we will clearly determine some of the adoption of some optimers, summarize as follows:
Description
Table Statistics
Mode Used
NON-RBO Object (EG: IOT)
N / a
#1
Parallelism> 1
N / a
#1
Rule Hint
N / a
Rule
All_Rows Hint
N / a
All_Rows
FIRST_ROWS HINT
N / a
First_ROWS
* Other Hint
N / a
#1
Optimizer_Goal = Rule
N / a
Rule
Optimizer_Goal = all_ROWS
N / a
All_Rows
Optimizer_Goal = first_ROWS
N / a
First_ROWS
Optimizer_Goal = choose
NO
Rule
Optimizer_Goal = choose
YES
All_Rows
# 1 All_Rows will be adopted unless Optimizer_Goal is set to first_ROWS. ALL_ROWS will be used in PLSQL.
* Other Hint other tips means that in addition to the prompts other than Rule, All_Rows, or First_Rows.
General Optimizer Notes Optimizer General Precautions
~~~~~~~~~~~~~~~~~~~~~~
When you look at the optimizer problem, you should consider the following:
- All_ROWS tends to full table scanning (Full Table Scans).
- first_ROWS tends to index access (index access).
- CBO default use all_ROWS computing costs.
- CBO does not adjust costs in order to meet Parallel Queries prior to Oracle 7.3.
- Before Oracle 7.3, CBO believes that the value of the field is evenly distributed between the value of the field; then, after this, it can be stored according to the request storage column statistics.
- All situations equal to RBO, in the form of a DRIVING ORDER, from right to left in the FROM clause.
The CBO determines the join order based on the cost that is derived from the collected statistics.
If there is no statistics, the CBO will be driven from left to right in the process of left to right in the process of the FROM clause, just in contrast to RBO.
- CBO will use Analyze information in conjunction with the high water level information of the current table. Therefore, the execution plan of a statement may change due to time.
- Note: Truncate resets the "high water level" of the table, but does not modify the statistics of the table, but left the old CBO information of the table. - When performing various connections, some connection combinations will be excluded to reduce the overall time required to determine the implementation plan. In summary, each connection order must be compared with the best one, and it is clear that some optimized scheme will be excluded.
Problem SQL Statements Question SQL Statement
~~~~~~~~~~~~~~~~~~~~~~
If CBO returns a partially optimized execution plan, for such a problem SQL statement, the following processing steps should be taken:
a) Check if you really become the table involved, such CBOs can be based on useful information.
b) Check if the information is accurate
For example: using the Compute option, and compare statistics on the table before and after.
c) Check if you involve a dictionary table. Dictionary table is not analyzed when default, so SQL accesses them may have a very poor plan in CBO mode.
d) Determine if you want to use RBO, All_Rows, or First_ROWS as your optimization target (Optimizer_goal).
e) Use the prompt (hint) to help guide the optimization.
If you have a problematic SQL statement, and the attempt to guide CBO does not have a result, the first action should be an isolation problem statement, the simplest form of the statement in SQL * Plus. Then use the explain command or TKPROF to determine the actual execution plan and improve it.
If it suddenly runs well in SQL * Plus, then pay attention to the general error indicated in the subsequent chapter.
Explain Plan and Tkprof
~~~~~~~~~~~~~~~~~~~~~~
Explain Plan A SQL statement and TkProf Some trace files are very useful for determining the execution plan of the given statement, but pay attention to the following limitations:
a) They use current information to derive the implementation plan, and cannot display the originally used programs when necessary.
For example: If you analyze any table or create / delete an index, the plan will not be the same. If Optimizer_Mode is different, there will be different plans.
b) They don't know the type of bind variable, assume all binding variables as character types. Therefore, you may get the error prompt or misleading.
EG: SELECT 1 from dual where sysdate <: b2 1
This will result in an error because the ': b2' is explained as a character type.
To avoid such problems, you should use the corresponding type conversion function to package such a binding variable.
EG: SELECT 1 from dual where sysdate This is a good way to avoid implicit type conversion. Making Comparisons ~~~~~~~~~~~~~~~~~~ Comparison of SQL statements and PL / SQL: PL / SQL uses a bind variable (Bind Variables) when referenced to a PL / SQL than variables in the SQL statement. This is very important because the interpretation of the binding variable affects the determination of the execution plan. EG: Some statements in PL / SQL: SELECT ENAME FROM EMP Where Empno> MyemPno; Among them, 'myempno' is a PL / SQL variable, so the statement is actually equivalent: SELECT ENAME FROM EMP WHERE Empno>: bind1; Bind variables: usually we will get the problem SQL statement in the SQL * Plus and replace the bound variable with constant. This is an invalid comparison in most cases. EG: Comparison: SELECT ENAME FROM EMP WHERE Empno> 9999; with SELECT ENAME FROM EMP WHERE Empno>: bind1; Assume that the table has been analyzed, CBO knows the maximum minimum of EMPNO. For the first sentence, CBO can determine the selectivity of the clause 'WHERE EMPNO> 9999', and for the second sentence, CBO does not know if ': bind1' is between 1-9999, so use the default Selectability - for example: he assumes that the clause will return a quarter of the table. This is also true even for the version of Oracle 7.3 or more, if CBO does not know the value of the bound variable. Therefore, the execution plan is therefore completely different. In general, if bind variables are used, all Range Scans and Like compare operations use default selectivity. Comparison of invalidation. You should also use Bind Variable in SQL * Plus. E.g: Variable bind1 varchar2 (10) Explain Plan for SELECT ENAME FROM EMP Where Empno>: BIND1; Instead: Explain Plan for SELECT ENAME FROM EMP Where Empno> 9999; Comparison between databases: If you have separate two databases, the basic statistics of the table will be different. You can't pass the statistics from a database to another database. The main factor affecting database comparison is: - The size of the database block (DB_BLOCK_SIZE). This is the foundation of CBO calculations, so you can't compare DB_BLOCK_SIZE values different databases. -DB_FILE_MULTIBLOCK_READ_COUNT. This is Oracle 7.1 or more The init.ora parameter is used to determine the cost of the full table scan. - DB_FILE_MULTIBLOCK_READ_COUNT. This init.ora parameter is used in Determining The Cost of Full Table Scans in Oracle 7.1 OnWards. - Statistics of the table / index. Removing the reproduction data will cause tables and indexes to occupy different numbers of data blocks. This affects the calculated or estimated statistics, and thus affects the CBO execution plan. (Oracle8i allows exporting actual statistics to solve this problem.) INIT.ORA parameters: The following init.ora parameters affect cost calculations: Between the different versions of Oracle, cost algorithms and default cost goals are still fewer changes. Therefore, the implementation plan may change when upgrading. For example, the default selective change of the LIKE clause with binding variable is a good example. Compared with RBO: Compared with the RBO program, the implementation plan is only the same, and you will get a different implementation plan. Fortunately, it is now clear: CBO uses the relevant costs of each implementation plan to choose the execution plan. There is little point comparing an execution plan to the RBO plan and being surprised if you get a different execution plan. Hopefully it is fairly clear by now that the CBO uses relative 'costs' of execution plans to determine an execution plan. E.g: For RBO, an index exists, it is to use it enough reason. (If it is related to inquiry) For CBO, only the index exists is not sufficient, and it is also necessary to compare the cost of accessing the amount of data from the expected number of expectations and other methods (such as full table scans). If the RBO has given the best plan, you should use the 'Rule' prompt or other form of prompt to specify the access path. The main difference between RBO and CBO is: CBO does not use indexes. It is: a) The index does not have 'selectivity. By calculating the cost of accessing the desired number of data, CBO finds that the cost of finding this method using the index block is higher than the cost of multi-block reads. b) The value of the value of CBO expects to return 'and the corresponding record number is incorrect, which may be due to incorrect statistics or use bind value to limit the scope. Note: If the result data returned by the RBO and CBO is physically different, it is a serious problem, please report Oracle as a bug. Comparison of parsing time: If the parsing time accounts for a big proportion of execution time, it is recommended that you check if a query that connects a lot of tables. If so, use the Ordered prompt to set the order of the access table to reduce the number of tables considered when connecting, is a very wise practice. Once you have a good execution plan, you can quickly achieve the same plan by rearrangement of the order of the FROM clause and using the Ordered prompt. HINTS optimization tips ~~~~~ Tip Let you provide CBO to provide information about how to access data. Unfortunately, invalid tips will make prompts to be ignored without any warning, so everyone must pay attention to its grammar. When prompted, the main points that need to be remembered are: - Tips must be in this strict format in this strict format / * ... * / - All tips (except Rule) will make you use CBO. Therefore, unless these tables have been analyzed or you give a complete prompt, use prompt is not a good way. - All hints (Except rule) cause you to use the cbo. Hence, it is not a good idea to use hints unless the tables area ie query. - Do not quote the mode (user) name in the prompt. For example: SELECT / * index (Scott.emp EMP1) * / is wrong, it should be used to use alias in the prompt. - If the table uses an alias, you must use an alias in the prompt. E.g: Error writing: SELECT / * FULL (EMP) * / Empno from Emp Myalias WHERE Empno> 10; Correct writing : SELECT / * FULL (MyAlias) * / Empno from Emp Myalias WHERE Empno> 10; - Tips in the PL / SQL block, you must follow a space behind ' '. This is important, otherwise the prompt may be ignored, because some versions of PL / SQL will ignore the first character behind the ' ' when passing the query to the SQL engine. For example: Use "SELECT / * FULL (A) * /" instead of "SELECT / * FULL (A) * /" . - The prompted access path must be a valid access path. E.g: Tables A and B have indexes on the airless Ind_col. A given index suggestions should use these indexes. The Value field in the two tables is not indexed. Tables A & B Both Have Indexes on The Nullable Ind_col Column. A Hint Has Been Supplied Suggesting That Sesting. The value column in Both Tables is unindexed. SELECT / * INDEX (a) index (b) * / * From A, B WHERE A.ind_col = B.ind_col And A.Value = 1 And b.value = 2 Query Plan Executive Plan -------------------------------------------------- -------- SELECT Statement [choose] COST = 83 NESTED LOOPS Table Access Full A Table Access by Rowid B Index Range Scan B1 If this query droves from A, we cannot use the index on A.ind_col, because the airline index will make us drop some records. So prompt 'index (a)' is invalid, and thus is ignored. The index on B can be used because it is an effective way to access the B table. IF WE Drive The Query from A, Then We cannot USE The index on a.ind_col as the column is nullable as we may miss some rows. Hence the 'index (a)' hint is invalid and is ignored. The index on b CAN BE Used as it is a Valid Way to Access Table B. - Invalid tips may not be explicitly expressed immediately. For example: use the first_rows prompt in the statement with the Order By clause. (You think that there is no act " - In a third-party tool (and old version of the PLSQL V1) that does not support HINTS, the method of solving this problem is to embed the prompt into the view and reference the view in the tool. For example: For statements: SELECT / * FULL (MyTab) * / COL1, COL2 From myTab WHERE col1> var1; You can create a view: Create or Replace View MyView AS SELECT / * FULL (MyTab) * / * From myTab; And change the statement to: Select Col1, Col2 from MyView Where Col1> VAR1; Summary summary ~~~~~~~ To use CBO, we must: - Regular analysis of all tables - Set the requirements for optimizing target Optimizer_goal (first_rows or all_rows). - Use the prompt to guide CBO in the necessary place - Use the prompt in PL / SQL to make the desired optimizer to be used. - Pay attention to using binding variables For the un-hoc query, CBO works very well. For hard-coded, repeated execution SQL statements, tuning should be tuned to obtain a repeatable optimization plan. You must regularly monitor execution efficiency, take serious testing when the RDBMS version is upgraded. It is very important to know the key online statement of your application and what is the implementation plan you expect.
