![]() ![]() Possible values are ALL_ROWS, FIRST_ROWS_ n, and FIRST_ROWS.Ĭontrols the cost analysis of an index probe with a nested loop. Sets the optimizer mode at database instance startup. You can view the report by using DBMS_XPLAN.DISPLAY_CURSOR. For example, an adaptive plan always choose the default plan, but the database collects information about which plan the database would use if the parameter were set to false. ![]() In this case, the database gathers information required for an adaptive optimization, but takes no action to change the plan. If set to true, then adaptive optimizations run in reporting-only mode. By default, reporting mode is off ( false), which means that adaptive optimizations are enabled. If the number of sessions is extremely large, then the multiblock read count value decreases to avoid the buffer cache getting flooded with too many table scan buffers.Ĭontrols the reporting mode for automatic reoptimization and adaptive plans (see " Adaptive Query Plans"). Because the parameter is expressed in blocks, it is set to a value equal to the maximum I/O size that can be performed efficiently divided by the standard block size. This value is platform-dependent and is 1 MB for most platforms. The default value of this parameter corresponds to the maximum I/O size that the database can perform efficiently. Larger values result in a lower cost for full table scans, which may result in the optimizer choosing a full table scan over an index scan. The optimizer uses the value of this parameter to calculate the cost of full table scans and index fast full scans. Specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan. Set to EXACT to allow only statements with identical text to share the same cursor. Set to FORCE to enable the creation of a new cursor when sharing an existing cursor, or when the cursor plan is not optimal. The optimizer generates the execution plan based on the presence of the bind variables and not the actual literal values. Converting the values improves cursor sharing and can affect the execution plans of SQL statements. Table 14-1 Initialization Parameters That Control Optimizer Behavior Initialization ParameterĬonverts literal values in SQL statements to bind variables. This chapter does not cover SQL plan management. SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans. This chapter does not cover SQL profiles. A SQL profile can correct suboptimal optimizer estimates discovered during SQL tuning. Conceptually, a SQL profile is to a SQL statement what a set of object-level statistics is to a table or index. See " Managing Optimizer Statistics: Basic Topics".Ī SQL profile is a database object that contains auxiliary statistics specific to a SQL statement. The more accurate the statistics, the better the optimizer estimates. This package updates and manages optimizer statistics. See " Influencing the Optimizer with Hints". The most important parameters are covered in " Influencing the Optimizer with Initialization Parameters".Ī hint is a commented instruction in a SQL statement. Parameters influence many types of optimizer behavior at the database instance and session level. You can use the following techniques to influence the optimizer: SQL profiles also technically include hints. The overlapping squares in the preceding diagram show that SQL plan management uses both initialization parameters and hints. Figure 14-1 Techniques for Influencing the Optimizerĭescription of "Figure 14-1 Techniques for Influencing the Optimizer" ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |