Use intelligent optimizer to improve Oracle performance limits
The time consumed in preparing a new SQL statement is the most important component of the Oracle SQL statement execution time. But by understanding the mechanism of the execution plan inside Oracle, you can control Oracle costs in assessing the number of times of connection sequence, and can generally improve query performance.
Prepare SQL statement
When the SQL statement enters the Oracle library cache, the following steps are performed before the statement is prepared:
1) Syntax check: Check if the SQL statement spell is correct and correct.
2) Semantic analysis: Verify all the names and columns of the inconsistency with the data dictionary.
3) Contour Storage Check: Check the data dictionary to determine if the contour of the SQL statement already exists.
4) Generate an execution plan: Use statistics based on cost-based optimization rules and data dictionaries to determine the best implementation plan.
5) Establish binary code: Based on the execution plan, Oracle generates binary execution code.
Once the SQL statement is prepared for execution, the subsequent execution will occur quickly, because Oracle approves the same SQL statement and reuses the execution of the statement. However, the generation time of SQL execution plans is important for generating a special SQL statement, or an SQL statement embedded in a text variable, and the SQL execution plan is important, and the previous execution plan is usually not reused. For those who connect a lot of tables, Oracle takes a lot of time to detect the appropriate order of connecting these tables.
Evaluation form connection order
In the preparation of SQL statements, the steps spending the most feeding are generating an execution plan, especially in processing a query with multiple table connections. When the connection order of the Oracle evaluation table, it must take into account all possible connections between the tables. For example: between the six tables is connected with 720 (6th step, or 6 * 5 * 4 * 3 * 2 * 1 = 720) possible connection line. When a query contains more than 10 tables, the problem will become more remarkable. For 15 tables, the possible query arrangements that need to be evaluated will be more than 1 trillion (accurate numbers are 1, 307, 674, 368,000).
Set limit using the Optimizer_Search_limit parameter
By using an Optimizer_Search_Limit parameter, you can specify the maximum number of connection combinations used to be evaluated by the optimizer. Using this parameter, we will be able to prevent the optimizer from consuming to consume any number of times to evaluate all possible connection combinations. If the number of tables in the query is less than the value of Optimizer_Search_Limit, the optimizer will check the possible connection combinations.
For example, there are five table connections will have 120 (5! = 5 * 4 * 3 * 2 * 1 = 120) Possible connection combinations, so if Optimizer_Search_Limit is equal to 5 (default), the optimizer will evaluate all 120 possible. The Optimizer_Search_Limit parameter also controls the threshold for calling the connection prompt with an asterisk. When the number of tables in the query is higher than the Optimizer_Search_Limit hour, the tips of the star number will be prioritized.
Another tool: Parameter Optimizer_Max_Permutations
Initialization Parameters Optimizer_max_permutations defines the upper limit of the number of combined numbers considered, depending on the initial parameter Optimizer_Search_limit. The default value of Optimizer_Max_Permutations is 80,000.
Parameters Optimizer_Search_limit and Optimizer_Max_Permutations together to determine the upper limit of the number of combinations considered by the optimizer: Unless (table or combined number) exceeds the value set by the parameter Optimizer_Search_Limit or Optimizer_Max_Permutations, the optimizer will generate all possible connection combinations. Once the optimizer stops the connection combination of the evaluation form, it will select the lowest combination of cost. Use the Ordered prompt to specify the connection order
You can set the upper limit of the number of evaluations performed by the optimizer. But even with high-value arrangements, we still have an important opportunity to make the optimizer to give up complex queries as early as possible. Recalling an example of 15 connection queries, it will have more than 1 trillion connection combinations. If the optimizer stops after evaluating 80,000 combinations, then it only evaluates the possible combination of 0.000006%, and may have not found the best connection order for this huge query.
The best way to solve this problem in Oracle SQL is to manually specify the connection order. In order to create the minimum solution set as soon as possible, the rules followed here are combining tables and typically use limiting the most stringent WHERE clauses to connect tables.
The following code is an example of a query execution plan that enforces the nested loop connection on the associated query of the EMP table. Note that I have used Ordered Tips to directly optimize the evaluation order of the table, and ultimately they appear on the WHERE clause.
SELECT / * Ordered Use_nl (Bonus) Parallel (E, 4) * /
E.ename,
HIREDATE,
B.COMM.
From
EMP E,
Bonus B
WHERE
e.ename = B.ename
This example requires an optimizer to connect to the table specified in the FROM clause of the SQL statement in order, and specifies the drive table in the first table in the FROM clause. ORDERED prompts are often used to join other prompts to ensure multiple tables in the correct order. Its use is more in terms of inquiries in the number of data warehouses in the torsion connection.
Another example, the following query uses the Ordered prompt to connect to the table: EMP, DEPT, SAL, and finally Bonus. I use the hash connection and Sal to Bonus using the nesting loop connection by specifying EMP to DEPT to use the nesting loop connection to the Bonus to further refine the implementation plan.
SELECT / * Ordered Use_hash (EMP, DEPT) USE_NL (SAL, BONUS) * /
From
EMP,
DEPT,
Sal,
Bonus
..
Practice suggestion
In fact, more efficient practices are to reduce the size of the Optimizer_Max_Permutations parameter in the product environment, and always prevent the occurrence of a large number of connected queries using a stable optimization plan or storage profile. Once you find the best connection order, you can use the ORDERED prompt to the current query and save its storage outline to manually specify the connection order for these tables to persist.
When you plan to use an optimizer to stabilize the plan, you can use the following method to perform plan persistence, temporarily set the Optimizer_Search_Limit to the number of tables in the query, allowing the optimizer to consider the possible connection order. Then, by rearrange the name of the table in the WHERE clause, and use the Ordered prompt, the query will be adjusted with the storage contour. When you contain more than four tables in the query, the Ordered prompt and storage profile will exclude tasks that take time-consuming SQL connection sequence resolution, thereby increasing the speed of the query.
Once the optimal connection order is detected, we can use the Ordered prompt to overload the Optimizer_Search_Limit and Optimizer_Max_Permutations parameters. Ordered Tips The table is connected in the order in which they appear in the FROM clause, so the optimizer does not join the description. As an Oracle professionals, you should know that there may be significant start-up delays when the SQL statement enters the library cache. However, smart Oracle DBAs and developers can change the search restriction parameters of the table or use the Ordered prompt to manually specify the connection order of the table, thereby significantly reduced the time required to optimize and perform new queries.
