Difference between CBO and RULE in Oracle optimizer

Differences between the Oracle optimizer CBO and RULE 1. When the Rule-Based Optimization (RBO) optimizer analyzes SQL statements, oracle rules are followed and are not sensitive to Data. It uses only a small amount of information to determine the execution plan of an SQL statement, including: 1) SQL statement itself 2) basic information about the table, view, and index involved in SQL 3) information in the data dictionary of the local database (remote database data dictionary information is invalid for RBO). For example, we usually use indexes when a column in a where clause has an index. However, it should be noted that indexing is not necessarily optimal. For example, if a table has only two rows of data, the entire table can be searched at one IO, at this time, the index requires two I/O operations. In this case, full table scan is more efficient. Www.2cto.com 2. The Cost-Based Optimization method (CBO) is the Cost of statements (Cost). The Cost engine is used to estimate the Cost of each execution plan, this cost quantifies the resources consumed by each execution plan. CBO selects the optimal execution plan based on this cost.
The resources consumed by a query can be divided into three parts: I/O cost, CPU cost, and NETWORK cost. I/O refers to the price required to read data from the disk into the memory (this price is the most important for queries, so a basic principle during optimization is to reduce the total number of I/O operations ); CPU cost refers to the cost required to process data in the memory. Once the data is read into the memory, when we identify the data we want, we will execute the sort (sort) on the data) or join operations, which consumes CPU resources. For remote nodes, network costs are also high.
When determining whether this method is used, the optimizer mainly refers to the statistical information of tables and indexes. Statistics show the table size, number of rows, and length of each row. These statistics are not available in the database at first, but only after analyze. In many cases, the Optimizer may make an incorrect execution plan when the statistics expire, this information should be updated in a timely manner (dbms_stat.analyze ). Technologies such as star join arrangement query, hash join query, function index, and parallel query are based on the CBD. 3. The optimization mode includes Rule, Choose, First rows, and All rows: Rule-based. Choose: Oracle uses this method by default. When a table or index has statistical information, it adopts the CBO method. If the table or index does not have statistical information, the table is not particularly small, in addition, when the corresponding column has an index, the index is adopted and the RBO method is adopted. Www.2cto.com First Rows: It is similar to the Choose method. The difference is that when a table has statistics, it returns the First few Rows of the query in the fastest way, the overall response time is reduced. All Rows: This is what we call the Cost method. When a table has statistics, it returns All Rows of the table in the fastest way, improves the query throughput in general. If no statistical information is available, RBO is used. 4. Set which optimization mode to use: A. In initSID. set OPTIMIZER_MODE = RULE/CHOOSE/FIRST_ROWS/ALL_ROWS in ora (the default is Choose) Selection Method: If the table has statistical information (outside the Partition Table) When optimizer_mode = choose ), the optimizer selects CBO. Otherwise, select RBO. B. Run the statement Hint (/* +... */) to set 5. Some Common Problems: A. Why does A table field clearly have an index, but the execution plan does not go through the index? 1. The optimization mode is the all_rows mode. 2. The table has been analyzed with statistical information. 3. The table is small. As mentioned above, the Oracle optimizer does not think it is worthwhile to take an index. B. Why can't system data dictionary tables or views be referenced in SQL statements when CBO is used? 1. Because the system data dictionary tables have not been analyzed, it may lead to a very poor "Execution Plan ". 2. Unauthorized Analysis of Data Dictionary tables may lead to deadlocks or a severe reduction in system performance. C. How do I select the table connection mode when using CBO? Www.2cto.com 1. CBO sometimes focuses on SMJ and HJ, but in OLTP systems, NL is generally better because it uses indexes efficiently. 2. Even if an index is built on the relevant column, SMJ can only avoid data sorting because of the existence of the index. 3. Due to HASH calculation, the existence of Indexes has almost no impact on the data query speed. D. What should I pay attention to when using CBO? 1. Make sure that sufficient statistics are collected for tables and related indexes. It is best to analyze tables and indexes on a regular basis for tables with frequent addition, deletion, and modification of data. 2. Available SQL statements: analyze table xxx compute statistics for all indexesE. Why is it slow to use CBO sometimes? 1. When Analyze2 and SQL are not performed on tables or views for CBO, Analyze is automatically performed on objects without Analyze, resulting in slow running. Author wiseboyloves