In order to stop regressions in the performance of your SQL server, you need to deploy an effective SQL plan management for the business. This helps you to cater to the changes that occur when there is an execution plan of any SQL statement. This plan provides you with the components to capture, choose, and evolve the SQL plan for getting an optimal database system.
The objective of the SQL plan baselines for your business
The primary goal of a SQL plan baseline is to keep the performance of the existing SQL statements irrespective of their changes in the database system. Some examples of the above changes include-
- A new version for the administrator or optimizer
- Changes to the statistics and the parameters of the optimizer
- Changes to the metadata definitions and schema
- Changes to the settings of the system
- Creation of SQL profile
Note that an SQL plan baseline cannot help those instances where an event causes irreversible changes in the execution of the plan, like dropping an index.
The tuning features of an Oracle database system generates SQL profiles to help the optimizer to create plans that are tuned well. However, note this is a reactive mechanism and will not guarantee stability in performance when there are drastic changes in the system. Note, SQL tuning will only provide solutions to issues in the performance of the database after they take place and are noticed. For example, a SQL statement might get a high-load because there are changes in the plan; however, SQL tuning cannot fix this issue until changes in the plan take place.
Also Read: How Data Science Can Make You Tomorrow’s Leader
General scenarios where SQL plan management improves or preserves SQL database performance-
- Any upgrade to the database leading to the installation of a new optimizer version that results in plan changes for a small volume of SQL statements. Most of these changes in the plan result in negative improvement or no performance change in the plan. However, there are some changes in the plan that might cause regressions in the performance of the SQL server. The baselines created for the SQL plan significantly decreases the future regressions that result from upgrades.
- Regular data and system changes influence plans for a few SQL statements leading to performance regressions in the future. SQL plan baselines assist in the reduction of performance regressions and help to provide stability to SQL performance.
What information does SQL plan baselines contain?
- Using new application modules helps the business to introduce new SQL statements to the system. This application software might use the appropriate SQL execution plan created for standard test configurations for these new SQL statements. If the configuration in the system differs from the test configuration, then the system has the ability to make the SQL plan baselines evolve with the passage of time to boost performance.
Experts in SQL server consulting state SQL baseline plans contain the information as given below-
- The set of hints
- The hash value of the plan
- Information related to the plan
The history of the plan is the set of plans that have been accepted, along with those that have been not accepted. The database optimizer overtime creates these plans for the SQL statement. Accepted plans find a place in SQL plan baselines, and they form the sub-set of the history of the plan. For instance, after the optimizer has generated the first plan that has been accepted in the baseline, the subsequent plans become part of the history but not part of the baseline of the plan. Adding plans to any SQL plan baseline is known as plan evolution. In order for it to be evolved in the usage, it should be made enabled by the optimizer.
Capture phase of SQL base plan
This is the phase where the database identifies the changes in the plan and records a new plan so that the administrator of the database can verify it. The database maintains a history of the plan for the individual SQL statements. The random SQL statements are not repeated, and they do not suffer from the degradation of performance. The database maintains the history of the plan for relatable SQL statements.
Repeated SQL statements and how they function
To identify the repeated SQL statements, the database keeps a log of the statements that contain the ID of the different SQL statements that the optimizer evaluates. The database identifies the SQL statement as a repeatable one when it is executed again or analyzed after being logged in.
For every repeated SQL statement, the database keeps a history of the plan that has all the plans created by the optimizer. The set of all the plans that have been accepted remain in the plan history of the SQL plan baseline.
The business can configure the capture phase of the SQL plan baseline for the automatic capture of its plan history as well as SQL plan baselines for repeating these SQL statements. You have the option to load these plans as SQL plan baselines manually.
Automatically capturing SQL plans
Enabling the capture of the automatic plan helps the database to automatically create and maintain the history of the plan for SQL statements by using the information offered by the optimizer. This plan history includes all the necessary information deployed by the optimizer to produce an execution plan like the text of the SQL, its outline, the compilation environment, and bind variables.
The optimizer symbols the initial phase that has been created for the SQL statement that has been accepted for use. It represents the plan history and the baseline for the SQL plan. The history of the plan incorporates all subsequent plans that have been created. During the evolution phase of the SQL plan baseline, the system adds plans to the verified SQL baseline so that regressions in the performance are not caused.
Consultants in SQL server state that businesses should keep the above factors in mind when they are creating SQL base plans. It is prudent to consult experts in the above field, so that database systems perform to their optimal levels without hassles at all!
