Storage outline and SQL plan baseline

The storage outline and SQL plan baseline storage outline are designed to provide stable execution plans to eliminate the impact of changes in the execution environment or object statistics. Therefore, this feature is also called Plan stability. Specifically, the storage outline is a collection of prompts. More precisely, all these prompts force the query optimizer to generate a Special execution plan for a given SQL statement. However, in practice, even if you use the storage outline, you may still be able to observe the change in the execution plan. The storage outline does not always provide a stable execution plan. Oracle 11g itself confirms this. From this version, it does not approve of using the storage outline, but recommends the SQL plan baseline. The main usage of www.2cto.com storage outline: stored outline is to fix the execution plan of an SQL statement. For example, if your SQL Execution Plan runs well in the test database, but another execution plan is adopted in the production database, you can export the stored outline of the test database to the official database! Let the SQL statements of the official database follow the execution plan of the test database! 1. to avoid serious performance degradation in some SQL statements after upgrade and cannot be optimized in a short time, we can use the outline function to implement the SQL Execution Plan in the original production database on the new database. 2. To avoid performance degradation caused by changes in SQL Execution plans due to inaccurate statistical data (such as the failure to collect statistics on tables or indexes in a timely manner. 3. The optimizer generated by database versions, configurations, and other differences in applications with large-scale distribution and implementation should be prevented from generating different execution plans. 4. Some bugs cause the optimizer to generate poor execution plans. Before fixing the bug, we can use outline to force the SQL Execution Plan to be correct. Outline stores the hint of the execution plan in the outline table. When an SQL statement is executed, Oracle compares it with the SQL statement in outline. If the SQL statement contains an outline statement, the execution plan is generated using the hint. Precautions for using Outline 1. Outln is a very important system user, and its importance is the same as sys and system. We do not recommend that you delete outln under any circumstances. Otherwise, database errors may occur. 2. The optimizer generates an execution plan through Outline, provided that all hints in outline are valid. For example, if an index is not created, the hint of the index is invalid, so that the SQL outline plan will not be used. 3. You cannot use outline when Cursor_sharing is set to force. 4. literial SQL is not highly shared, and Outline is better at binding variables to SQL statements. For literial SQL, each SQL statement must generate an outline. 5. You must have the create any outline permission to create an outline. 6. From the CBO perspective, the statistical information of database tables and indexes is constantly changing with the change of data volume. A fixed execution plan is not necessarily the best execution plan in some periods. Therefore, the use of outline depends on the specific situation. Outline maintenance stop db use outline function: alter system set use_stored_outlines = false; disable/enable specific outline: alter outline ol_name disable; alter outline ol_name enable; Delete: outline category: outline related views: dba_outlines dba_outline_hints this view lists the hints of the outline to check whether the outline exists: select name, category, owner from dba_outlines; the SQL plan baseline can be considered as an improved version of the storage outline, in fact, the SQL plan baseline not only has many identical features with the storage outline, but is also designed to provide stable execution plans like the storage outline to prevent execution environments and objects. Changes in statistics have an impact on the execution plan. In addition, similar to the storage outline, you can tune down the application without modifying the statement. Note that in the Orade document, maintaining the execution plan stability is the only purpose mentioned in the SQL plan baseline. for some unknown reason, it is not mentioned that you can use it to change the current execution plan (involving a given SQL statement) without modifying the application. What is an SQL plan baseline? An SQL plan baseline is an object associated with an SQL statement. It is designed to affect the decision when the query optimizer generates an execution plan. Specifically, the SQL plan baseline is a collection of prompts. Basically, the SQL plan baseline is used to force the query optimizer to generate a specific and stable execution plan for a given SQL statement. One of the advantages of an SQL plan baseline is that it is applied to a specific SQL statement, but it does not need to be modified when used. In fact, the SQL plan baselines are stored in the data dictionary and the query optimizer automatically selects them. first, SQL statements are executed in a traditional way. In other words, the query optimizer must generate an execution plan without the SQL plan baseline support. Then, standardize SQL statements so that they are case insensitive and are not affected by white spaces. Generate a signature for standardized SQL statement calculation. Then, query the data dictionary based on the signature. As long as an acceptable (trusted) SQL plan baseline with the same signature is available, check it to determine whether the SQL statement to be optimized is consistent with the SQL statement referenced in the SQL plan baseline. This step is necessary because the signature is a hash value, so there may be Hash conflicts. If the check succeeds, the prompt in the SQL plan baseline will be placed into the execution plan generation process. NOTE: If there are multiple available SQL plan baselines, the query optimizer selects the one with the lowest cost. You can use multiple methods to capture SQL plan baselines at www.2cto.com. Basically, they are automatically created by the database engine or manually created by the database administrator. The three main methods are described in the following sections. Automatic capture when the dynamic initialization parameter optlmlzer_capture_ SQL _plan_baselines is set to true, the query optimizer automatically creates a new SQL scheduler baseline. This initialization parameter is set to FALSE by default and can be modified at the system level and session level. When automatic capture is enabled, the query optimizer stores a new SQL plan baseline for each SQL statement that has been repeatedly executed (that is, at least twice. Therefore, it inserts the signature of each SQL statement into a log for ease of management. This means that when an SQL statement is executed for the first time, only its signature is inserted into the log. Then, when the same statement is executed for the second time, if the SQL plan baseline corresponding to the statement does not exist, a new one is created and stored. If the SQL plan baseline corresponding to the SQL statement already exists, the query optimizer will still compare the current execution plan with the execution plan based on the SQL plan baseline. If they do not match, the new SQL plan baseline describing the current execution plan will be stored. However, as you saw earlier, the current execution plan cannot be used directly. The query optimizer is forced to use the execution plan generated with the assistance of the SQL plan baseline.