Stable (fixed) execution plan

Why does the SQL execution plan change?

Why does our SQL statement execution plan change? How can I stabilize the execution plan of an SQL statement? To answer the 2 questions above, we need to first know how the SQL statement execution plan is generated, and those factors that affect the generation of the execution plan, only to understand these factors we can remedy the situation, stabilize our SQL statement execution plan.

We know an SQL statement. His execution plan may be more than one, how is the database determined to use that execution plan? This is determined by the database optimizer, and its role is to choose the most ' optimal ' execution plan among all possible execution plans. There are 2 types of optimizer for the current database: The rule-based Rbo optimizer and the cost-based CBO optimizer. The Rbo Optimizer uses a set of internal rules to determine the execution plan of an SQL statement, regardless of whether the execution plan is the best choice, while the CBO optimizer calculates the execution cost of each execution plan for that SQL by a rigorous conversion mechanism and then chooses the most cost Small ' that is the execution plan for the SQL statement. As can be seen from the description, it is more scientific to use the CBO optimizer to decide the execution plan of SQL statements in an ideal environment.

Currently our users are generally using Oracle 10g database, Oracle 10g default optimizer CBO is a cost-based optimizer, but it is because of this choice mechanism of the CBO, for various reasons, may lead to the CBO calculated the most ' small ' cost execution plan changes, This causes the SQL execution plan to change. The factors that affect CBO's cost include IO, CPU, network, statistics, initialization parameters, and so on, if these environments change, it will cause the CBO to change the execution plan cost value of the SQL statement, which may eventually lead to the change of the execution plan's choice.

In addition to the fact that the optimizer can cause changes to the SQL statement execution plan, if the data structure changes, it can also lead to changes in the execution plan, most commonly such as the creation or invalidation of the index, the normal table becomes the partition table, and so on, the changes of these data structures can also lead to changes in the SQL execution plan.

common ways to stabilize SQL execution plans

Through the previous introduction we understand the SQL statement execution plan change reasons, in order to make the SQL statement execution plan fixed, there are several ways to stabilize the SQL statement execution plan, although they can stabilize the SQL statement execution plan, but each method has a certain flaw, To be based on the actual situation of the validation.

1. Selection rules (RBO) optimizer

We know from the previous introduction that the execution plan of SQL statements under RBO is generated by certain rules, that is, once the SQL statement is determined, its execution plan is confirmed, and its execution plan will not change as long as the data structure is not changed.

defect : The same is because RBO SQL statement execution plan is based on rule generation, first of all, the SQL writer has a certain degree of performance optimization knowledge, the preparation of the full consideration of the RBO rules, otherwise it may lead to the efficiency of the execution plan is not high, the ability of the program writer is higher than the requirements , and many of our program personnel do not have the ability to do so; again, Oracle has clearly stated that RBO is no longer maintained, meaning that it may later abandon the optimizer and replace it with a CBO, such as the 10g after the default optimizer is the CBO model, for the sustainable development of products, Also forced us to choose a more reasonable CBO model.

2. Add hint character/*+hints */

Although the database optimizer chooses the CBO mode, we can specify that the statement adopt the RBO mode for a separate SQL statement by using the hint word/*+rule*/, so that it does not violate the schema of the whole database using the CBO, but also uses RBO for the specified SQL to stabilize these The execution plan of the SQL statement, which is also a common way for our programmers to stabilize the SQL statement execution plan.

flaw : At present, we use a lot of/*+rule*/in our program to stabilize the execution plan of the specified SQL statement, which is simple and effective, but it also exposes a lot of problems. The first is the same because the writing of SQL is not a standard cause rbo generation may not be the most efficient execution plan, at the user often a performance problem, and SQL adjustment due to the prompt word, must be modified by the program staff to adjust the process, the cycle of the problem is longer In addition, late zlhis may use table partitioning technology, Oracle has a rule is that once the SQL statement has a partitioned table, the hint word using the Rbo method will be invalidated, which is bound to bring performance hidden trouble.

3. Statistical information locking

As we said earlier, in the CBO environment, the factors that affect the execution plan are I/O, CPU, database parameters, and statistics, in which the most likely change in our user environment is statistics, while other factors generally do not change easily once the server is deployed. Since the change in statistical information is a major factor in the instability of the implementation plan, we will try to lock up the statistical information. Oracle provides DBMS_STATS packets to lock statistics so that statistics do not change with actual data growth and are locked in a specified range, e.g. we lock the hospitalization expense record table and its index statistics

sql>exec dbms_stats.lock_table_stats (ownname = ' zlhis ', tabname = ' Hospital Fee Records ')

Once the statistics are locked, we will not be in a role to collect statistics with dbms_stats,analyze, and if it is to be collected, it must be unlocked by the following statement.

sql>exec dbms_stats.unlock_table_stats (ownname = ' zlhis ', tabname = ' Hospital Fee Records ')

flaw : The flaw in locking statistics is also obvious, the locked statistics can not reflect the real situation of the data table, so the cost of the calculated execution plan is certainly untrue, and if we want the real statistics, we must first unlock, And then collect the statistics of the previously locked objects again, which is bound to cost a lot of resources.

4. Using the storage profile

The storage profile (stored outlines) is the feature of a stable SQL statement execution plan that Oracle8.1 began with, and is further refined and enhanced in oracle10g, followed by a detailed introduction to it in a separate section.

Storage Profile

1. What is a storage profile

Oracle 8i begins with a mechanism to capture the execution plan of the SQL statement and save it as a storage profile, a storage profile that is actually an SQL statement that is optimized by the DBA or developer, stored in the ' summary ' scenario, when an SQL statement is executed and it exactly matches the storage profile. Oracle replaces the SQL statement used with the storage profile so that the SQL statement is no longer parsed and uses the information stored in the storage profile directly, including the execution plan. The storage profile is flexible enough that it can be staged or restricted to only those matching sessions to execute.

2. How the Storage profile works

In fact, through the previous definition, we have probably known how the storage profile works, and then we have a flowchart more intuitive understanding of how the storage profile is a stable execution plan.

From the flowchart above we can see that if the storage profile is deployed, the SQL statement first goes to match the storage profile before execution, verifying that the storage profile has a statement that matches the execution SQL, and if there is a direct execution of SQL in the Extract storage profile, if no execution plan is generated for the normal process, Execute SQL again. Because the execution plan of the SQL statement is saved in the storage profile, and each time the SQL statement is executed, it is no longer generated and extracts the saved execution plan directly, so it plays a role in stabilizing the SQL execution plan.

3. Storage Profile Deployment

With so many storage profiles, how does the storage profile be created and deployed? Next we will explain in a case, we prefer to know that the storage profile saves the execution plan of the SQL statement, and the execution plan is parsed, so the storage profile must be parsed when the SQL statement can be created and applied, if you want to create a storage profile of the SQL has been cached, I will find that SQL is unable to use our newly created storage profile, we can use the storage profile to stabilize all SQL execution plans for the database, or to stabilize some of the specified SQL statements, which need to be considered according to different needs, we have a separate demonstration

3.1. Database all SQL statements Create storage profiles

To stabilize all the S QL statements of the database, we only need to modify the database's Create_stored_outlines parameters as follows:

Sql> alter system set Create_stored_outlines = true;

Or

Sql> alter system set create_stored_outlines = Zlhis_outline;

The storage profile is in category (class), and when create_stored_outlines = True, Oracle uses the default "defaults" category when Create_stored_outlines = Zlhis_ Outline, Oracle will use our custom "zlhis_outline" category, and when we set the Create_stored_outlines parameter, Oracle will automatically create outline for all subsequent compiled SQL Create_stored_outlines the class specified, when we set Create_stored_outlines to False, Oracle stopped creating outline, a mechanism for classifying SQL. Here we no longer narrate that, with the above setting, as long as the SQL of the database is parsed, the SQL corresponding storage profile is created, but the following actions are needed to make the SQL statement use the storage profile the next time it executes.

Sql> alter system set Use_stored_outlines =true;

Or

Sql> alter system set Use_stored_outlines =zlhis_outline;

How do I see if my storage profile is in effect? We can review the User_outlines view by querying the following statements:

Names are the name of the store profile, which is defined by the database itself, Sql_text is the script for the SQL statement, and the basis for storing the profile match, used indicates whether the storage profile is used, ununsed indicates that the storage profile has not been used since it was created. The state becomes used only when its corresponding SQL executes again to match the storage profile.

3.2. Specify SQL to create a storage profile

It is simpler to create a storage generalization for all SQL statements in the entire database, but most of the time we don't need to do this, wasting resources and not using management. We just need to create a storage profile for some of the specified SQL statements, and here we'll use the Dbms_ outln package from the database, and Dbms_ outln.create_outline to create the storage profile based on the existing SQL cursors, Let's do the exact operation.

If we want to create a storage profile that specifies the SQL statement " SELECT * from department table Where id=1584", we first need to find the Hash_value, Child_number value of the statement in the SQL cursor, which can be done by v$ SQL view to find ( Note: The SQL statement in this view is the premise that SQL has already been executed, and if the SQL statement is never executed, the cursor for the SQL statement cannot be found in the view), the lookup statement is as follows

After locating the Hash_value and Child_number values for the corresponding SQL statement, take these 2 values into the Dbms_ outln.create_outline package and create the corresponding storage profile as follows:

sql>exec dbms_ outln.create_outline (hash_value = ' 3345575829 ', child_number = 0,category = ' Test _ Storage profile ');

Then also execute the command to enable the storage profile, as follows

Sql>alter system Set Use_stored_outlines = Test _ storage profile;

Finally, query the User_outlines view to see if the storage profile you just created takes effect, as follows

4. Test Storage profile Effects

Having talked so much about storage profiles and about how the storage profile was created, is the storage profile really as good as the execution plan of the SQL statement that it describes? Let's do a simple experiment to see how the storage profile works in a stable SQL statement execution plan.

Experimental ideas: We write a SQL statement, and confirm that the SQL statement execution plan is different under the CBO and rbo conditions, then we create the SQL storage profile in the CBO environment and take it into effect, and finally compare the implementation plan in the CBO and RBO environment to see what changes are made to the execution plan.

1. We look at the same SQL statements below that differ in the execution plan under CBO and RBO.

2. We then create the profile of the SQL statement and make it effective in the manner described earlier, with detailed steps to create a storage profile.

3. We then repeat the first step, looking at the execution plan of the SQL statement under the CBO and Rbo, resulting in the following results:

As you can see clearly, the execution plan for the SQL statement is the same now, either in the CBO or RBO, and it turns out that the storage profile has played a role in stabilizing the SQL execution plan to the effect we want.

5. Deficiencies in existence

As with other stable SQL statement execution plans, there is a certain flaw in the storage profile.

He is matched by SQL text, and as soon as the SQL statement changes, a new corresponding storage profile must be generated, so management maintenance is more troublesome;

The storage itself has a separate additional overhead, and the newly parsed SQL statement Oracle will check for an associated storage profile. This is a big price, and the table for that information is created and saved in the system table space. For a production system, when you start creating a storage profile, you will find that it uses a lot of space in the system table space.

However, since 8i, Oracle has continuously optimized the storage profile and improved its functionality, for example, in addition to the stable execution plan, as well as the ability to add storage profile hints, and the storage profile has one of the biggest features is the ability to migrate, It is believed that the application of the products in the United product is promising as long as it is fully verified.

Summary

Through the introduction of this article, we should grasp the following knowledge:

1. Understand the factors that affect the SQL statement execution plan.

2. Common ways to stabilize SQL statement execution plans and their pros and cons.

3. Have a certain understanding and understanding of the storage profile, know how it stabilizes the SQL statement execution plan, and be able to do simple deployment.

In the end, this article is only a rough introduction to the storage profile, interested colleagues can come down to the further research, I believe there will be a gratifying harvest.

Stable (fixed) execution plan