Oracle11g performance tuning-use SQLPlanManagement (1)

This document describes how to use SQL Plan Management to manage SQL Execution plans. SQL Plan management provides components for capturing, selecting, and evolving SQL Plan information to prevent performance degradation caused by sudden changes in the execution Plan of SQL statements.

This article includes the following topics:

1. SQL Plan Baseline overview

2. Manage SQL Plan Baseline

1. SQL Plan Baseline overview

SQL Plan ManagementIs a preventive mechanism. This mechanism can establish an SQL statement by recording and evaluating the execution plan of the SQL statement for a period of time.Accepted planSet, that isSQL Plan Baseline.

1.1 SQL Plan Baseline objective

The goal of SQL Plan Baseline is to maintain the performance of the corresponding SQL statements, regardless of database changes. Examples of changes include:

New optimizer version

Optimizer statistics and optimizer Parameter Changes

Schema and metadata definition changes

Changes in system settings

SQL Profile Creation

SQL Plan Baseline is powerless when an event has caused irreversible changes to the execution Plan. For example, when an index is deleted, the execution Plan of the corresponding SQL Plan baseline is deleted, this will not help avoid performance degradation. I personally think this is a comprehensive choice of Oracle, and it is not a limitation of SQL Plan Management.

The SQL Tuning function of the Oracle database can generate an SQL profile to help the optimizer generate a well-adjusted plan. However, this mechanism is passive and does not guarantee stable performance when dramatic database changes occur. SQL Tuning can only solve the performance problems that have been identified, for example, SQL statements that may become overloaded due to planned changes. SQL optimization can solve this problem only when the execution plan changes.

Common scenarios in which SQL Plan Management can improve or maintain SQL Performance include:

After the database is upgraded, a new optimizer version is installed, which usually leads to a small amount of scheduled changes to SQL statements. Most of these planned changes cause no performance changes or improvements. However, some planned changes may cause performance degradation.Import SQL Tuning Sets of SQL Performance Analyzer,SQL Plan Baseline can help significantly reduce the potential performance degradation caused by the upgrade.

Changes in the running system and data may affect the plan of some SQL statements, which may lead to performance degradation. SQL Plan Baseline helps minimize performance degradation and stabilize SQL Performance.

The deployment of the new application module means introducing new SQL statements to the database. The application software may have used an appropriate SQL Execution Plan for the new SQL statement in the standard test configuration environment. If the system configuration differs greatly from the test configuration, the database first loads the execution plan prepared by the test environment.

• 1.2 architecture of SQL Plan Baseline
  

  An SQL Plan baseline contains one or more accepted plans. Each accepted plan contains the following information:

  Hints

  Planned Hash Value

  Plan-related information

  • Plan History is a group of plans generated by the optimizer within a period of time that contain the accepted and not accepted plans. Because only the accepted plan is in the SQL Plan Baseline, the Plan in the SQL plan Baseline is a subset of the plan history. For example, after the optimizer generates the first acceptable plan, the subsequent plan is part of the plan history, but not part of the plan baseline.

    The SQL Plan baseline process is called plan evolution. To be able to be evolved, the plan must be enabled for the optimizer to use.

    SQL Management Base (SMB) is part of the data dictionary. It stores SQL plan baseline and plan history in the SYSAUX tablespace. SMB also includes SQL profile. SMB uses automatic space management.

    2. Manage SQL Plan Baseline

    The management of SQL Plan Baseline includes the following phases:

    Capture SQL Plan Baseline

    Select SQL Plan Baseline

    Evolve SQL Plan Baseline

    2.1 capturing SQL plan baseline

    In the SQL Plan Baseline capture phase, the database detects changes to the Plan and records new plans so that they can be managed by the database administrator evlove (verified ). Therefore, the database maintains a plan history for each SQL statement. Because temporary SQL statements are not repeated, there will be no performance degradation. The database only maintains the plan history of SQL statements that can be repeated.

    To identify duplicate SQL statements, the database maintains a statment log that contains the SQL IDs of various SQL statements evaluated by the optimizer. After an SQL statement is recorded by the database and parsed or executed again, the database recognizes the SQL statement as repeated.

    For each SQL statement that can be repeated, the database maintains a plan history that contains all the plans generated by the optimizer. All acceptable plans in plan history are SQL Plan Baseline.

    You can configure the SQL Plan Baseline capture phase to automatically obtain the plan history and SQL Plan baseline, or configure the manual load Plan to the SQL Plan baseline.

    2.1.1 automatic plan acquisition

    When automatic plan capture is enabled, the database uses the information provided by the optimizer to automatically create and maintain the plan history for SQL statements. The plan history includes the information used by the optimizer to generate execution plans, such as SQL text, outline, bind variables, and compiling environment.

    The optimizer uses the initial plan generated by an SQL statement as the accepted plan. At this time, this initial plan is both SQL plan baseline and plan history (that is, the current plan history = SQL plan baseline ). Plan history includes all subsequent plans. In the SQL Plan Baseline evolution phase, the database adds a Plan that is confirmed to not cause performance degradation to the Baseline.

    To enable automatic plan retrieval, set the OPTIMIZER_CAPTURE_ SQL _PLAN_BASELINES initialization parameter to TRUE. By default, this parameter is FALSE.

    2.1.2 create a baseline from an existing plan

    You can manually attach an existing plan of a group of SQL statements to Plan baseline to create an SQL plan baseline. The database does not verify the performance of the manual loading plan and adds them as an accepted Plan to an existing or new SQL plan baseline. You can use the manual loading plan together with the automatic plan capture, or use the manual loading plan as an alternative to the automatic plan capture.

    You can execute a manual loading plan:

    A. Load the plan from SQL Tuning Sets and AWR snapshots

    Load the plan from the SQL Tuning Set and use the LOAD_PLANS_FROM_SQLSET FUNCTION OF THE DBMS_SPM package. The following example loads the scheduler TSET1 stored in the SQL tuning set:

    DECLARE   my_plans PLS_INTEGER; BEGIN   my_plans：= DBMS_SPM.LOAD_PLANS_FROM_SQLSET（sqlset_name =>'TSET1'）; END; /

    To load a scheduler from AWR, You need to load the scheduler stored in AWR snapshot to the SQL Tuning Set before using the above features.

    B. Load the plan from the Shared SQL Area

    Use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package for the plan of the Shared SQL area to be loaded. In the following example, the Oracle database loads the SQL plan determined by SQL _ID in the Shared SQL zone:

    DECLARE   my_plans PLS_INTEGER; BEGIN   my_plans：= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE（SQL_ID => '99twu5t2dn5xd'）; END; /

    2.2 select SQL Plan Baselines

    In the SQL Plan Baseline selection stage, the Oracle database detects changes based on the storage plan history and selects a Plan to avoid potential performance degradation.

    Each time the database compiles an SQL statement, the optimizer performs the following operations:

    Use the Cost-based search method to build an optimal Cost solution

    Try to find a matching Plan in SQL Plan Baseline

    If yes, the optimizer uses the matching Plan. If no match Plan is found, the optimizer evaluates all accepted SQL plans in the SQL Plan baseline and selects the lowest cost SQL Plan.

    The optimizer finds the best-cost plan. If it does not match the plan in the plan history of the SQL statement, the best-cost plan is a new plan, the database will add this plan as a nonaccepted plan to plan history. The database does not use this new plan until it is confirmed that it will not cause performance degradation. However, if a system change (such as dropping an index) causes all accepted plans to be unavailable, the optimizer selects the best-cost plan. therefore, the SQL Plan baseline allows the optimizer to use a conservative Plan Selection policy.

    To use SQL Plan Baseline, set OPTIMIZER_USE_ SQL _PLAN_BASELINES to TRUE (default ).

    2.3 evloving SQL PLAN baselines

    During the evolution of SQL Plan Baseline, the database evaluates the performance of the new Plan and integrates the Plan with better performance into the SQL Plan baseline.
    When the optimizer discovers a new plan for an SQL statement, the database adds the plan to plan history as a nonaccepted plan. The database can verify the performance of the Plan relative to the SQL Plan Baseline. A successful verification process includes comparing it with SQL Plan baseline, and ensuring that it can deliver better performance. When the database verifies a nonaccepted plan, the database changes it to an accepted plan and integrates it into the baseline.

    A. Use manual load Plan to evolve the plan

    As shown in 2.1.2, If you manually load the plan from the Shared SQL Area or from the SQL tuning set to the SQL Plan baseline, the database adds these plans to the SQLP plan baseline as the accepted Plan.

    B. Use the DBMS_SPM.EVOLVE_ SQL _PLAN_BASELINE evolutionary plan

    The PL/SQL function DBMS_SPM.EVOLVE_ SQL _PLAN_BASELINE will try to get the optimizer into the new plan of plan history. If the function can verify that the new Plan works better than the Plan in the corresponding SQL Plan baseline, the database adds the new Plan as an accepted Plan.

    In the following example, the DBMS_SPM.EVOLVE_ SQL _PLAN_BASELINE function evolves a new plan for an SQL statement marked by an SQL handle (the SQL handle is a unique identifier of an SQL statement, marked as a string ). You can query DBA_ SQL _PLAN_BASELINES. SQL _HANDLE to find the SQL handle.
    SET SERVEROUTPUT ON
    Sets LONG 10000

    SET SERVEROUTPUT ONSET LONG 10000DECLARE    report clob;BEGIN    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(                  sql_handle => 'SYS_SQL_593bc74fca8e6738');    DBMS_OUTPUT.PUT_LINE(report);END;/

    Note: You can use this function to specify the name of a special plan, a group of plans, or no value. If it is no value, the database will evolve all nonaccepted plans.