Improving Oracle database Performance with intelligent optimization constraints

The most important component of Oracle SQL uptime is the time spent preparing new SQL statements for execution. However, if you understand the intrinsic mechanism of the executable plan, you can control the time that Oracle spends in assessing the connection order of the tables and improve the performance of the query overall.

Prepare the SQL statement provided for execution

After an SQL statement enters the cache of the Oracle library and is actually executed, the following events occur sequentially:

Grammar check--check that the SQL statement is spelled and the correct word order.

Semantic parsing--oracle verifies all tables (table) and columns (column) based on the Data Dictionary (dictionary).

Saved outline check--oracle check dictionary to confirm that there is a saved outline (Stored Outline) for whether the SQL statement should exist.

Generate execution plan--oracle determines how to generate an optimal execution plan based on a penalty value (cost-based) optimization algorithm and statistical data in the data dictionary.

Produces binary code--oracle generates executable binaries based on the execution plan.

Once you start preparing to execute the SQL statement, the above procedure will be executed very quickly because Oracle can identify the same SQL statement and reuse the corresponding executable code for the same SQL statement. However, for systems that produce ad hoc SQL and embedded text values (literal value) in SQL, the generation time of the SQL execution plan becomes quite long, and the previous execution plans are often not used again. For queries that involve many tables, Oracle may take a long time to decide the order in which to connect the tables.

Evaluate the order of the connection tables

The time to build an executable plan is often the largest cost component of the SQL preparation process, especially if you are working with queries with connections with multiple tables. When Oracle evaluates the connection order for a table, it must consider each possible sort. For example, when there are six tables that need to be connected, Oracle needs to consider the possible connection ordering of 720 kinds (6 of permutations, that is, 6x5x4x3x2x1=720). When the number of tables that need to be connected is more than 10 o'clock, this permutation problem becomes very prominent: if there are 15 tables that need to be connected, the possible query order is more than 1 trillion (the exact value is 1,307,674,368,000).

Setting limits in the Optimizer_search_limit parameter

You can use the Optimizer_search_limit parameter to control the occurrence of the above problem, which specifies the maximum number of table join orders that the optimizer evaluates. With this parameter, you can prevent the optimizer from estimating the extra time that is spent in evaluating all possible table join orders. If the number of tables in the query is less than or equal to Optimizer_search_limit, the optimizer examines how all possible tables are connected.

For example, there are 120 kinds of queries involving five tables (5!). =5X4X3X2X1=120) The possible connection order, so if the value of the parameter optimizer_search_limit is set to 5 (the default), then the optimizer takes into account all of the 120 possible connection sequences. The Optimizer_search_limit parameter is also used to control thresholds for starting the Start connection indication (star join hint). When the query involves less than the number of tables in the parameter optimizer_search_limit, the start connection indication is set.

Another tool: Optimizer_max_permutations parameters

The optimizer_max_permutations initial parameter is used to set the upper bounds of the optimizer's range (that is, the maximum number of table join orders to consider), which relies on the initial parameter optimizer_search_limit. The default value for the parameter optimizer_max_permutations is 8000.

The parameters Optimizer_search_limit and optimizer_max_permutations are used together to set the upper limit of the number of permutations considered by the optimizer. The optimizer constantly produces possible permutations of the table's connections until the number of permutations reaches the parameter optimizer_search_limit or optimizer_max_permutations. Once the optimizer stops producing a new possible connection arrangement, it will select the least expensive arrangement from it.