Oracle configuration Query Optimizer

I. background

The query optimizer is very important to the performance of SQL statements, because the SQL statements we write are finally executed by the database and are implemented through the query optimizer to generate an execution plan. If the execution plan generated by the query optimizer is inefficient, it will lead to poor performance. There are some parameter configurations that can affect the query optimizer to generate an efficient execution plan, but they are also risky. In short, it can be said that the configuration of the query optimizer is expensive, but it is worth the effort.

2. Configure the route map

We can configure Parameters Based on the routes shown in

The following describes the steps marked with numbers in the figure: (the meanings of each parameter are described in detail later)

1. These two parameters need to be adjusted: optimizer_mode and db_file_mutiblock_read_count

2. The default value of the parameter in this step is very suitable and usually there is no need to adjust it. These parameters are mainly used to enable or disable some features of the query optimizer.

3. Collect System statistics and object statistics. This step must be done, but Oracle10g has already done it by yourself.

4. When storing data in the memory, choose to manually or automatically adjust the memory usage.

5. The pga_aggregate_target parameter must be set for automatic adjustment.

6. manually adjust these parameters.

7. test whether the application achieves the expected performance

8. If the query optimizer can generate an efficient execution plan for most SQL statements, the configuration is successful. Otherwise, step 2 is required.

9. If the query optimizer tends to use too many or too few indexes, you must adjust the optimizer_index_caching and optimizer_index_cost_adj parameters.

1 2 3 Next Page