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

This article introduces the knowledge that "the values of oracle optimizer parameters can be divided into several". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The values of the optimizer parameter optimizer_mode can be divided into the following categories: RULE,CHOOSE,FIRST_ROWS_n (nasty 1, 10, 10, 100 and 1000), first_rows,all_rows

1,rule

SQL > show parameter optimizer_mode

NAME TYPE VALUE

-

Optimizer_mode string RULE

Indicates that oracle will use RBO to parse the target SQL, and the statistics of the various objects involved in the target SQL have no effect on RBO.

2.choose

SQL > show parameter optimizer_mode

NAME TYPE VALUE

-

Optimizer_mode string CHOOSE

Depending on whether there are statistics in the target SQL, RBO will not be used if not, and CBO will be used if there is one.

3firstworthy rowsgiving n (nasty 1meme 10pr 100pr 1000)

SQL > show parameter optimizer_mode

NAME TYPE VALUE

-

Optimizer_mode string FIRST_ROWS_10

When there is any value in the first_rows_n, the oracle uses CBO to parse the target SQL. In this case, the focus of CBO in calculating the cost values of each execution path of the SQL is to return n records with the fastest response speed, when the first_rows_n is satisfied. Oracle changes the cost value of all corresponding execution steps to a very small value (less than the cost calculated by CBO for the same execution step by default) so that oracle does not violate CBO's principle of selecting an execution plan (minimum cost value).

4,first_rows

SQL > show parameter optimizer_mode

NAME TYPE VALUE

-

Optimizer_mode string FIRST_ROWS

Indicates that oracle uses CBO and RBO together when parsing SQL, and here CBO is still used in most cases.

5,all_rows

SQL > show parameter optimizer_mode

NAME TYPE VALUE

-

Optimizer_mode string ALL_ROWS

The default value for subsequent versions of oracle 10g. When calculating the cost of each execution path of SQL, CBO focuses on the best throughput (minimum consumption of Imax O and CPU resources)

The content of "the values of oracle optimizer parameters can be divided into several" is introduced here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.