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

The purpose of this article is to share with you about the optimization of CBO in SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The processing of a query includes three stages: syntax analysis, sentence optimization and query execution. In the sentence optimization phase, Oracle uses the cost-based optimizer (CBO,cost based optimizer) by default to select the best execution plan, and the best criterion is that it consumes the least system resources such as CPU and Imando.

The optimizer based on CBO has three optimization ways to meet different query requirements, namely, all_rows, first_rows_n and first_rows.

The default way of all_rows:Oracle, the optimization goal is to achieve maximum query throughput.

First_rows_n: optimizes the output of the first n rows of the query to meet the query requirements of fast response.

The old version of first_rows:first_rows to output the first few rows of data from the query as soon as possible.

Oracle provides optimizations at different levels, namely, instance level, session level, and statement level. CBO optimization method for querying the current database

Show parameter optimizer_mode

NAME TYPE VALUE

-

Optimizer_mode string ALL_ROWS

Set CBO optimization mode at the instance level

Alter system set optimizer_mode = first_rows_10

Set CBO optimization mode at the session level

Alter session set optimizer_mode = all_rows

To set the CBO optimization mode at the statement level, you need to use the hint prompt

Select / * + first_rows_10*/ ename, sal, mgr from scott.emp

The working process of the optimizer includes the steps of SQL conversion according to statistical data, selecting access path according to resource consumption, selecting connection method according to statistical data and determining connection order, and finally selecting the best execution plan.

Thank you for reading! This is the end of this article on "what are the ways to optimize CBO in SQL?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.