CBO Configuration Policy

"The cost of configuring the query optimizer is expensive, but it is worth the effort ." (Note 1) In fact, I have seen many sites that underestimate the importance of good configuration. Sometimes, I even had a heated discussion with some people who thought that "there is no need to bother configuring the query optimizer for each database separately. We already have a set of initialization parameters, and we have tried and tested them all over the database." Generally, I will first answer this question as follows: "If a configuration set applies to all databases, why does Oracle introduce more than 20 initialization parameters dedicated to the query optimizer? Oracle knows what they are doing. If such a 10 thousand magic configuration exists, it will be provided by default, and you can save a lot of formal instructions on initialization parameters ." Next, I will explain in detail the reason why this magic configuration does not exist: each application has its own requirements and load summary, and each system composed of different hardware and software has its own characteristics. If the customer is in trouble, I will also remind them that "you are looking for me because you have encountered performance problems, right? For some reason, the application has not achieved the best performance, but the database must be partly responsible for the current situation... ." It is said that at least from Oracle9iR2, the query optimizer will be able to run well, which means it can generate an efficient execution plan for most 2SQL statements. More accurately, however, this is true only when the query optimizer is correctly configured and the database design gives full play to all its features. I have already stressed this, and remember that the configuration of the query optimizer includes not only initialization parameter settings, but also system statistics and object statistics. Since there is no such magical configuration in the configuration roadmap, a reliable step is needed to help us. Figure 5-1 summarizes the main steps I have taken. Figure 5-1. Main steps for configuring the roadmap The following describes the steps marked with numbers in the figure 1. There are always two initialization parameters to be adjusted: optimizer_mode and db_file_multiblock_read_count. As you will see later in this chapter, the latter is no longer so important to the query optimizer itself. However, the performance of individual operations will be heavily dependent on it. 2. the initialization parameters to be adjusted in this step are usually set as appropriate values by default, so this step is not very important. However, in any case, this step aims to enable or disable certain features of the query optimizer. 3. Since system statistics and object statistics provide crucial information for the query optimizer, they must be collected. 4. Depending on the initialization parameter workarea_size_policy, choose whether to manually or automatically adjust the memory usage when storing data in the memory. After the selection is made, the other initialization parameter values are set in step 5 or step 6. 5. If you select to automatically set the amount of memory used for work, you need to set the initialization parameter pga_aggregate_target. 6. If you select manual settings, the actual amount of memory used depends on the threshold value set for each different operation. Basically, a special initialization parameter must be set for each different operation. 7. Test the application after the preceding settings take effect. During the test, collect execution plans for those parts that do not show the expected performance. By analyzing the execution plan, you should be able to guess where the problem is. In this phase, you must note that the key is to identify common and non-special phenomena. For example, check whether the query optimizer uses too many or too few indexes, or whether the query optimizer does not properly recognize the constraints. 8. If the query optimizer can provide an efficient execution plan for most SQL statements, the configuration is successful. Otherwise, the ninth step is required. 9. If the query optimizer tends to use too many or too few indexes or nested loops. You usually need to adjust the initialization parameters optimizer_index_caching and optimizer_index_cost_adj to fix this problem. If the query optimizer incorrectly estimates the cardinality ). Some histograms may be lost or need to be adjusted. In Oracle 11g, extended statistics (extended statistic) can also provide some help. According to Figure 5-1, the initialization parameters set from step 1 to step 2 should not be changed. Of course, this is not an absolute deal. If the index-related initialization parameters and histograms are adjusted in step 9, the expected results have not been achieved. It is necessary to start all over again. Another point is to mention that, since some initialization parameter settings have an impact on system statistics, after adjusting them, you must recalculate the system statistical value.