? Setting up SQL Plan management? Set up various SQL plan management scenarios
- SQL Plan Management: Overview
? SQL Plan management is an automatic control of the evolution of SQL planning. The optimizer can automatically manage the SQL plan baselines. – Use only known and validated plans.? The scheduled changes are automatically validated. – Continue to use comparable or better plans only. SQL Performance Analyzer can be used in the SQL optimization set (STS) to pre-implantation of Important SQL SQL Plan Management: There may be a performance risk when the SQL execution plan for an overview SQL statement changes. There are many reasons why the SQL plan has changed, such as the optimizer version, optimizer statistics, optimizer parametersdefinition, System design, and SQL profile creation. Various scheduling control techniques, such as stored outlines and SQL profiles, have been introduced in previous versions of Oracle DB, etc.), to resolve performance regressions caused by planned changes. However, these technologies are passiveprocess. SQL Plan management is a new feature introduced with Oracle Database one, by maintaining the so-called "SQL planbaseline to enable the system to automatically control the evolution of SQL plans. When this feature is enabled, only the newly generated SQL meter is provenintegration with the SQL Plan baseline does not lead to performance regression. Therefore, when you perform aSQL statement,only plan that is included in the corresponding SQL plan baseline can be used. You can use the SQL optimization set to automatically addload or implant SQL plan baselines. The main advantage of the SQL Plan management feature is the stable system performance,no planned regression will occur。 In addition, this feature can also beto save a lot of DBA time, these times are typically spent in identifying and analyzing SQL performance regressions and looking for availablesolution.
- SQL Plan Baseline: Architecture
SQL Plan Baseline: ArchitectureSQL Program Management (SPM)Functionality introduces the necessary infrastructure and the ability to support planned maintenance and performance validation of the new planservice. For SQL statements that are executed more than once, the optimizer maintains a plan history for a single SQL statement. Optimization processThe order identifies repeatable SQL statements by maintaining the statement log. If you do this again for one of the logged SQL statementsParsing or executing the statement again, the SQL statement is identified as a repeatable statement. To label an SQL statementThe various plans generated by the optimizer will be included as information such as SQL text, largebound variables and compilation environments, etc.) are maintained; The optimizer uses this information to replicateexecution Plan. As an alternative or supplement to automatically identify repeatable SQL statements and create their plan history, the system also supportsA series of SQL statements are manually implanted into the plan. Plan HistoryContains the different plans that the optimizer generated for the SQL statement during a certain period of time. However, only the plansome of the plans in the history may be accepted and used. For example, you would normally not use the optimizer to generatenew plan, unless the plan is validated and does not result in a performance regression. Run as an automation task in the Maintenance window fromprogram validation is done automatically when you move SQL optimization. The only goal of the automatic SQL optimization task isget high-load SQL statements。 To do this, the task automatically performs someaction, for example, to make a successful validated plan an accepted plan. A series of acceptable plans make up aSQL Plan baseline. The first plan generated for an SQL statement is obviously an acceptable plan, so the meterThe original plan baseline was formed. Any new plans that the optimizer discovers later are included in the plan history, butare not initially included in the plan baseline. statement logs, plan history, and plan baselines are stored in the SQL Management Library (SMB)The library also contains SQL overviewto the file. SMB is part of a database dictionary and is stored in the Sysaux table space. SMB uses automatic space tube(for example, regular cleanup of unused plans). SMB can be configured to change the plan retention policy and the setspace size limit. Note: When using Oracle database one G, if the DB instance is started, but the Sysaux table space isOFFLINE, the optimizer will not be able to access the SQL Management object. This may affect the sex of some SQL workloadscan.
There are two ways to load a SQL plan baseline. Instant capture: Use automatic scheduled capture by: Initialize parametersOptimizer_capture_sql_plan_baselines is set to true. By default, the parameterthe number is set to false. Setting this parameter to True willturn on automatic identification of repeatable SQL statements, as well as fromThe ability to create schedule history for such statements. This is illustrated in the diagram on the left, where you cansee the first generated SQL plan that is automatically integrated into the original SQL plan baseline. ? Batch load: UsingDBMS_SPMpackage, which supports manual management of SQL plan baselines. Use thispackage, you can move the SQL plan fromthe cursor cache or an existing SQL optimization set (STS) is loaded directly into thein the SQL plan baseline. For SQL statements that you want to load from the STS to the SQL plan baseline, you need to add their SQLThe schedule is stored in the STS. Use DBMS_SPM to change the state of the baseline plan from accepted to unacceptable(and never accept changes to accepted), you can also export the baseline plan from the staging table, and then use the exported baseThe line plan loads the SQL plan baselines into other databases.
- Evolutionary SQL plan baselines
Evolutionary SQL plan baselines during the evolution of the SQL plan baseline, Oracle DB evaluates the performance of the new plan in a regular manner and delivers better performanceThe plan is integrated into the SQL plan baseline. When the optimizer finds a new plan for the SQL statement, it adds the plan as an unacceptable plan to the plan historythe record. Then, compare the performance of the SQL plan baseline to verify the performance of the plan. If it is proven that an unacceptableplan does not result in a performance regression (manual or automatic), the plan is changed to an accepted schedule and integrated intothe SQL plan baseline. Successful validation of an unplanned process includes: performance for this plan and baseline from SQL planCompare the performance of one of the selected plans to ensure better performance. There are two ways to evolve a SQL plan baseline:? Use DBMS_SPM. The Evolve_sql_plan_baseline function. The figure shows a callexample. The function returns a report that shows whether some existing history plans have been moved to the plan basethe line. You can also specify specific plans to test in the history. ? Run SQL Tuning Guide: Evolve SQL by using SQL optimization guidance to manually or automatically optimize SQL statementsplan baselines. SQL Optimization guidance discovers optimized plans and confirms that their performance is better than the corresponding SQL planBaseline, a build proposal is generated to accept the SQL profile when the planned performance is selected in the baselines. Accepted theSQL profile, the optimized plan is added to the appropriate SQL plan baseline.
- Important Baseline SQL Plan properties
Important Baseline SQL Plan properties if you add a plan to the plan history, the plan will be associated with some important attributes:? SIGNATURE, sql_handle, Sql_text, and plan_name are important identifiers for search operationscharacters. ? Use origin to determine whether a plan is automatically captured (auto-capture), manually evolved (Manual-load), automatically evolved via SQL Optimization guidance (manual-sqltune), or through automated SQL optimizationAutomatic Evolution (auto-sqltune). ? The enabled and Accepted:enabled properties indicate that the plan is enabled and available for use by the optimizer. IfIf enabled is not set, the system will not consider this schedule. The ACCEPTED property indicates that the user is changing the scheduleThe plan has been validated as an effective plan (automated by the system or manually by the user) for accepted). If you change a schedule to accepted, only if you use theDBMS_SPM. When Alter_sql_plan_baseline () changes its state, the plan is a non-ACCEPTED's. You can temporarily disable the accepted schedule by removing the enabled setting. The plan must beenabled and accepted, the optimizer will only consider using it. ? Fixed indicates that the optimizer considers only plans that are marked as fixed, regardless of other plans. For example, if you have10 baseline plans, of which three are marked as fixed, the optimizer will use only the three plannedthe best plan, ignoring all other plans. If a SQL plan baseline contains at least one enabledFix the plan, the SQL plan baseline is fixed. If you added a fix in the SQL plan baselinenew plan, these new plans cannot be used until you manually declare these new plans as fixed. You can use the Dba_sql_plan_baselines view to view the properties of each plan, as shown in. Then, you can use DBMS_SPM. The Alter_sql_plan_baseline function changes some of these properties. can also beto use DBMS_SPM. The Drop_sql_plan_baseline function deletes the schedule or the entire schedule history. FigureThe example shown in changes the Enabled property of Sys_sql_plan_8dfc352f359901ea toNO. Note: The Dba_sql_plan_baselines view contains some additional properties that you can use to determine the individualthe scheduled last use time, and whether a schedule should be automatically cleared.
SQL Plan Selection if you are using automatic scheduled capture, the first time an SQL statement is identified as repeatable, its best costwill be added to the corresponding SQL plan baseline. The plan will then be used to execute the corresponding statement. If a plan baseline exists for an SQL statement, and the parameters are initializedOptimizer_use_sql_plan_baselines is set to True (the default value), the optimizerUse the comparison plan to select a policy. Each time the SQL statement is compiled, the optimizer first uses the traditional cost-basedThe search method establishes an optimal cost plan, and then attempts to find a matching plan in the SQL plan baseline. AsIf a matching plan is found, the optimizer continues to run as usual. If a matching schedule is not found, the optimizerThe new plan is added to the plan history, and then the various accepted plans in the SQL Plan baseline are calculatedand select the lowest cost plan. Use the outlines that are stored with each accepted schedule to replicate these acceptedplan. Therefore, the advantage of having a SQL plan baseline for SQL statements is that the optimizer alwaysone of the accepted plans in the SQL plan baseline. With SQL plan management, the optimizer can generate the best cost plan, or it can generate a baseline plan. This information willis dumped in the Other_xml column of the plan_table that explains the plan. In addition, you can use the new Dbms_xplain.display_sql_plan_baseline function to display a plan baselineone or more execution plans for a given sql_handle. If Plan_name is also specified, the correspondingThe line plan. Note: In order to preserve backward compatibility, if the stored outline pair of an SQL statement for a user session is active, theCompile the statement using this storage outline. In addition, even if automatic scheduled capture is enabled for the session, the optimizer is notplans that are generated with the storage outline are stored in SMB. Although the storage outline does not have any explicit migration procedures, you can use the DBMS_SPM packageLoad_plan_from_cursor_cache process or load_plan_from_sqlset process to migrate itto the SQL plan baseline. When the migration is complete, you should disable or delete the original storage outline.
- Possible SQL Plan manageability Scenarios
What are the possible SQL plan manageability scenarios? Database Upgrade: When you upgrade a system from an earlier version to Oracle database, the batch load SQL meteris particularly useful. To do this, you can capture a plan for a SQL workload to the SQL tuning set (STS) before you upgradeand then load these plans from the STS into the SQL plan baseline immediately after the upgrade. This policy can be maximizedreduce the planned regression resulting from the use of the new optimizer version. ? New application Deployment: Deploying new application modules means introducing new SQL statements into the system. SoftwareThe vendor can provide the application software along with the corresponding SQL plan baselines for the newly introduced SQL statement. byThe new SQL statement will initially be associated with a known good performance in a standard test configurationrun with the row. However, if the customer system configuration differs greatly from the test configuration, the plan baseline can beevolve to produce better performance. In both cases, you can use automatic SQL plan capture after manual loading to ensure that only the betterfor future applications. Note: In all scenarios in this section, it is assumed that Optimizer_use_sql_plan_baselines is set toTRUE.
- SQL Performance Analyzer and SQL plan baseline scenarios
A variant of the first method described in the SQL Performance Analyzer and SQL Plan baseline scenario is by using SQL Performance Analyzer. Can capturebefore Oracle database one G in the STS, and import these plans into Oracle database one G. Then, set the initialization parameter optimizer_features_enable to 10g so that the optimizer sets this dataThe library operates as a 10g Oracle DB. Next, run the SQL Performance Analyzer for the Sts. After the run is complete,Set initialization parameter optimizer_features_enable back to 11g and rerun SQL for STScan be analyzer. The SQL Performance Analyzer generates a report that lists the SQL statements from 10g to 11g whose plans have occurred. For SQL statements that SQL Performance Analyzer displays for performance regression due to the new optimizer version, you canuse the STS to capture its schedule, and then load the plans into SMB. This approach provides the best form for planning the implant process, as it helps in preserving the performance of the database upgradeIn the same time, prevent performance regression.
- Automatically load SQL plan baselines
Automatically load SQL Plan baselines: Scenario Another upgrade scenario involves using an automatic SQL plan capture mechanism. In this scenario, the initial period of time (such asthe initialization parameter optimizer_features_enable (OFE) is set to Oracle within a quarter)the version value before Database one, and then use automatic SQL Plan capture after the upgrade to do your work. During this initial period, the optimizer can replicate Oracle for most SQL statements due to the Ofe parameter settingDatabase One g before the plan. Because automatic SQL plan captures are also initiated during this period, the optimizerprior to Oracle Database, the plan will be captured as a SQL plan baseline. At the end of the initial period, you can delete the settings for ofe so that when the minimum planned regression occurs due to a plan baseline or nowhen you plan to return, take advantage of the new optimizer version. The regression plan will use the previous optimizer version;statement will benefit from the new optimizer version.
- Clear the SQL Management library policy
Clearing the SQL Management library policy system takes into account the limits defined by the weekly check for the space occupied by the SQL Management Library (SMB). Restrictions are based on SysauxThe percentage size of the tablespace is defined. By default, the space budget limit for SMB is set to Sysaux sizeof 10%. However, you can use DBMS_SPM. The CONFIGURE process configures SMB to change the space budget to a mediuma value between 1% and 50%. If the SMB space exceeds the defined percent limit, a warning is written to the alert log. by clearing someSQL Management objects, such as SQL plan baselines or SQL profiles, to increase the SMB space limit, increasealerts are generated by the week before the Sysaux size or the SMB size is reduced. The spatial management of the SQL plan baseline will be completed ahead of time with weekly cleanup tasks. The task is in the Maintenance window as an automaticrun the task. Any plans that have not been used for more than 53 weeks will be cleared. However, you can configure SMB to not use thethe scheduled retention period is set to a value between 5 weeks and 523 weeks (slightly longer than in 10). To do this, you can useDBMS_SPM. CONFIGURE process. You can view the current configuration settings for SMB by checking the Dba_sql_management_config view. This, you can also use DBMS_SPM. The Drop_sql_plan_baseline function manually clears the SMB (shown in the example below).
- Enterprise Manager and SQL plan baselines
Enterprise Manager and SQL plan baselines use the SQL Plan Management (SQL Program Management) page to manage SQL profiles in one placeSQL patches and SQL plan baselines without having to do so in multiple separate locations in Enterprise ManagerManagement. You can also enable, disable, delete, package, unpack, load, and evolve the selected baselines. From this page, you can also configure various SQL plan baseline settings.
? Set up SQL Plan management? Set up various SQL plan management scenarios http://blog.csdn.net/rlhua/article/details/16369811
