#Baseline data creator upgrade
#Baseline data creator software

For example, changes in initialization parameters and optimizer statistics enable the optimizer to choose a better plan. For example, a hint may correct the cardinality estimate of a table.īecause a profile does not constrain the optimizer to any one plan, a SQL profile is more flexible than a SQL plan baseline. Rather, the hints correct miscalculations in the optimizer estimates that lead to suboptimal plans. SQL profiles are also implemented using hints, but these hints do not specify any specific plan. Each plan is implemented using a set of outline hints that fully specify a particular plan. SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.Ī SQL plan baseline is a set of accepted plans. If the system configuration is significantly different from the test configuration, then the database can evolve SQL plan baselines over time to produce better performance.įigure 23-1 SQL Plan Baselines and SQL Profilesĭescription of 'Figure 23-1 SQL Plan Baselines and SQL Profiles'

#Baseline data creator software

The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. SQL plan baselines help minimize performance regressions and stabilize SQL performance.ĭeployment of new application modules introduces new SQL statements into the database. Ongoing system and data changes can affect plans for some SQL statements, potentially causing performance regressions. If the plans perform better, then the database promotes them into the baseline otherwise, the database does not promote them. The database puts new plans that are not in the current baseline into a holding area, and later evaluates them to determine whether they use fewer resources than the current plan in the baseline. When you upgrade, the database only uses plans from the plan baseline. SQL plan baselines significantly minimize potential regressions resulting from an upgrade. However, some plan changes may cause performance regressions. Most plan changes result in either improvement or no performance change.

#Baseline data creator upgrade

SQL plan management can improve or preserve SQL performance in database upgrades and system and data changes.Ī database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements. Storage Architecture for SQL Plan Management This section contains the following topics: This component is the process of adding new plans to existing SQL plan baselines, either manually or automatically. This component is the detection by the optimizer of plan changes based on stored plan history, and the use of SQL plan baselines to select appropriate plans to avoid potential performance regressions. This component stores relevant information about plans for a set of SQL statements. The main components of SQL plan management are as follows: In this context, a plan includes all plan-related information (for example, SQL plan identifier, set of hints, bind values, and optimizer environment) that the optimizer needs to reproduce an execution plan.

In the typical use case, the database accepts a plan into the plan baseline only after verifying that the plan performs well. A plan baseline is a set of accepted plans that the optimizer is allowed to use for a SQL statement. SQL plan management uses a mechanism called a SQL plan baseline.

SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.