Shulou(Shulou.com)05/31 Report--

This article mainly introduces how Oracle uses Optimizer_mode parameters to control the optimizer, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Oracle uses Optimizer_mode parameters to control optimizer preferences. Several parameters commonly used in 9i are: first_rows,all_rows,first_rows_N,rule

, choose et al. While 10g without rule and choose.oracle, there are two optimization methods when executing SQL statements: rule-based RBO and cost-based CBO. When SQL is executed, what kind of optimization method is used is determined by the Oracle parameter optimizer_mode.

Rule Based Optimizer (RBO) is based on rules

Cost Based Optimizer (CBO) is based on cost, or statistics.

SQL > show parameter optimizer_mode

NAME TYPE VALUE

-

Optimizer_mode string ALL_ROWS

Oracle10g starts with all_rows by default.

With or without statistical information, ALL_ROWS uses a cost-based optimization method to return the maximum number of quantitative data.

With or without statistical information, FIRST_ROWS_n adopts a cost-based optimization method and returns the first N rows of records as quickly as possible.

FIRST_ROWS uses a combination of cost and heuristics to find a way to return the first few rows as quickly as possible

The first is the minimum cost of returning all rows, the second is the minimum cost of returning the first N rows, and the last is the best plan to return the previous few rows.

Modify the global situation

Alter system set optimizer_mode=all_rows

Modify the current session

Alter session set optimizer_mode=all_rows

Specific explanation of several parameters:

Optimizer_mode = choose

This is the default value for Oracle. When using this value, Oracle can use either rule-based RBO or cost-based CBO, which value depends on whether statistics are available in the accessed table of the current SQL. If there are multiple accessed tables, one or more of them have statistics, then Oralce will sample the tables without statistics (that is, not all of them), and then use the cost-based optimization method CBO when the statistics are complete. If all the accessed tables do not have statistics, Oracle uses the rule-based optimization method RBO.

Optimizer_mode=First_rows

This option is obsolete after oracle 9i and is retained for backward compatibility purposes to find an execution plan that returns the first row of the result set in the shortest possible time. This rule tends to prompt the optimizer to use index access paths, and occasionally very inappropriate access paths occur.

When set to this CBO mode, the SQL statement returns results as quickly as possible, regardless of whether all queries in the system take a long time or consume too much system resources. Because the use of indexes speeds up queries, first_rows optimization mode performs index scans on full table scans. This optimization mode is generally suitable for some OLTP systems and meets the requirement that users can see a smaller query result set in a short time.

Optimizer_mode=all_rows

The optimizer will look for an execution plan that can complete the statement in the shortest possible time.

When set to this CBO mode, all computing resources consumed are guaranteed to be minimized, although sometimes no results are returned after the query ends. The optimization mode of all_rows is more inclined to full table scanning than full index scanning and index sorting, so this optimization mode is suitable for data warehouses, decision support systems and batch-oriented databases (batch-oriented databases) where the real-time performance of data viewing is not so strong.

Optimizer_mode=first_rows_N

The value of N can be 1, 10, 10, 100, and 1000. The optimizer first estimates the total number of rows returned by thoroughly analyzing the first join order. In this way, you can know the fragments of the entire data set that the query may get, and restart the entire optimization process, with the goal of finding an execution plan that can return the entire data fragment with minimal resource consumption.

Oracle 9i strengthens some SQL statement optimization patterns with a small amount of data expected to return the result set, adding four parameter values: first_rows_1, first_rows_10, first_rows_100, and first_rows_1000. CBO determines the cardinality of the number of result sets returned by the n value in first_rows_n. We may only need to query a portion of the result set, based on which CBO decides whether to use index scanning.

Optimizer_mode = rule

The rule-based optimizer pattern, RBO, is an optimization pattern used in earlier versions of Oracle. Since RBO does not support new features such as bitmap indexes,table partitions,function-based indexes since the 1994 Oracle version, RBO has not been updated in future Oracle versions, and users are no longer recommended to use RBO as an optimization mode.

Thank you for reading this article carefully. I hope the article "how Oracle uses Optimizer_mode parameters to control the optimizer" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.