![]() A null value removes the filter for parameter_name entirely. Parsing schema to include ( =) or exclude ( ) for SQL plan management auto capture, depending on whether allow is TRUE or FALSE. The database only uses this filter when OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is TRUE. ![]() Module to include ( =) or exclude ( ) for SQL plan management auto capture, depending on whether allow is TRUE or FALSE. Table 163-8 Names and Values for CONFIGURE Procedure Parameters Parameter NameĪction to include ( =) or exclude ( ) for SQL plan management automatic capture, depending on whether allow is TRUE or FALSE. Unpacks (imports) SQL plan baselines from a staging table into SQL management base Resets an evolve task to its initial stateĭisplays the results of an execution of an automatic evolve task. Packs (exports) SQL plan baselines from SQL management base into a staging table Migrates existing stored outlines to SQL plan baselines Loads plans stored in a SQL tuning set (STS) into SQL plan baselines Loads the SQL Management Base (SMB) with SQL plan baselines for a set of SQL statements using the plans from the AWR, and returns the number of plans loaded Loads one or more plans present in the cursor cache for a SQL statement Interrupts a currently executing evolve task Implements a plan based on the recommendation of an evolve task Sets configuration options for SQL management base, in parameter/value formatĬreates an advisor task and sets its parametersĬreates a staging table that used for transporting SQL plan baselines from one system to anotherĭrops a single plan, or all plans associated with a SQL statementĮvolves SQL plan baselines associated with one or more SQL statementsĮxecutes a previously created evolve task Table 163-2 DBMS_SPM Package Subprograms SubprogramĪccepts a plan based on the recommendation of an evolve taskĬhanges an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value formatĬancels a currently executing evolve task ![]() ![]() If the system production configuration differs significantly from the test configuration, SQL plan baselines can be evolved over time to produce better performance. The application software may use appropriate SQL execution plans developed under a standard test configuration for the new SQL statements. The use of SQL plan baselines helps to minimize performance regressions and stabilize SQL performance.ĭeployment of new application modules means introducing new SQL statements into the system. Ongoing system and data changes can impact plans for some SQL statements, potentially causing performance regressions. The use of SQL plan baselines significantly minimizes potential performance regressions resulting from a database upgrade. However, certain plan changes may cause performance regressions. Common usage scenarios where SQL plan management can improve or preserve SQL performance include:Ī database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements, with most of the plan changes resulting in either no performance change or improvement. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system. SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by recording and evaluating the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |