OracleSQL execution plan baseline summary (SQLPlanBaseline)

Starting from Oracle11g, it provides a new method of fixed Execution Plan, namely SQLplanbaseline, the Chinese name SQL Execution Plan baseline (baseline for short), which can be considered as OUTL

Starting from Oracle 11g, it provides a new method for fixed execution plans, namely, SQL plan baseline, a Chinese-name SQL Execution plan baseline (baseline for short), which can be considered as OUTL.

I. Basic Concepts

At the beginning of Oracle 11g, a new method for fixed execution plans is provided, namely, SQL plan baseline, the Chinese name SQL Execution plan baseline (baseline for short), which can be considered as OUTLINE (OUTLINE) or an improved version of SQL PROFILE. Basically, its main functions can be summarized as follows:

1. Stabilize the execution plan of the given SQL statement to prevent the impact of changes in the execution environment or object statistics on the Execution Plan of the SQL statement!

2. Reduce the probability of degradation of SQL statement performance in the database. Theoretically, a statement cannot be switched to a new execution plan that is much slower than the execution plan that has been executed!

Note:

1. From the perspective of Oracle development, it is estimated that this method is the direction of Oracle development and improvement. Now outline has been abandoned, and SQL profile is estimated to be difficult to improve in subsequent releases. Therefore, for those who have been familiar with Oracle since 11g, they must have some knowledge about the SQL plan baseline, because this is the mainstream in the future!

2. the SQL Execution Plan baseline is stored in the data dictionary, and the query optimizer automatically determines whether to use them.

Oracle Tutorial: instance fault recovery

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Ii. Working Mechanism

Since Oracle 11g, due to the existence of the baseline, the parsing process of a statement is roughly as follows:

1. After the SQL statement is hard parsed, the CBO (Optimizer) will generate many execution plans, from which the CBO selects a minimum cost execution plan.
2. Generate a hash value (signature) based on the SQL statement text. This hash value is used to check whether the same baseline exists in the data dictionary.
3. If the baseline exists, the optimizer compares the execution plan generated just now with the execution plan saved in the SQL plan baseline.
4. if an SQL Execution Plan that matches the execution plan just generated by CBO exists in the baseline and is marked as acceptable ('accepted'), the execution plan generated by the CBO is enabled.
5. If no matching SQ execution plan exists in the baseline, the CBO evaluates multiple execution plans marked as 'accepted' in the baseline and selects the execution plan with the lowest cost. (Note: The baseline of a statement can have multiple execution plans saved, which is different from other Outline and SQL profiel)
6. if the execution plan selected by CBO in the hard parsing process is lower than the execution plan COST saved in the baseline, the newly generated execution plan is marked as not-accepted and saved in the baseline. This execution plan will not be considered for use until it is evolved and verified, namely marked as accepted (evolution and verification can be simply understood as Oracle's confirmation of this execution plan can bring better performance ).

Oracle ensures that the performance of SQL statements does not degrade through the above method (that is, the second major role I summarized in the first part), which is called "conservative execution plan selection policy"

Iii. Baseline features

The following are some examples:

1. OPTIMIZER_USE_ SQL _PLAN_BASELINE is used to control whether Oracle uses baselines. The default value is TRUE, indicating that baselines are automatically used.
In 2.11g, the baseline is not automatically created by default.
3. Unlike OUTLINE and SQL Profile, the baseline does not have a classification concept.
4. Unlike OUTLINE and SQL Profile, each SQL statement can have multiple baselines. Oracle determines the baseline based on the rules.
5. The baseline takes effect for all instances in RAC.
6. The baseline has two representations: SQL _handle, which can be understood as the unique identifier of the statement text, and SQL _plan_name, which can be understood as the unique identifier of the execution plan.
7. You cannot use the force_matching attribute like the SQL profile to apply multiple statements to different SQL statements with different nominal values to one baseline.

Iii. Methods for creating baselines

1. automatically capture baselines. By setting optimizer_cature_ SQL _plan_baselines to true, the optimizer generates and saves baselines by repeatedly executing more than two SQL statements (which can be modified at the system level or session level)

2. Load the baseline from the SQL tuning set by using the package dbms_spm.load_plans_from_sqlset.

DECLARE

Rochelle plans_loaded PLS_INTEGER;

BEGIN

Rochelle plans_loaded: = DBMS_SPM.load_plans_from_sqlset (

Sqlset_name => 'my _ sqlset ');

END;

/

3. Load the data from the database cache. Use the dbms_spm.load_plans_from_cursor_cache function to create a baseline for a statement already in the cursor cache.

DECLARE

Rochelle plans_loaded PLS_INTEGER;

BEGIN

Rochelle plans_loaded: = DBMS_SPM.load_plans_from_cursor_cache (SQL _id => '1fkh93md0802n ', plan_hash_value => null );

END;

/

Note: There are multiple loading methods, such as fuzzy matching based on SQL text and user name for SQL statement parsing. For details, see the document.

Iv. Baseline Status

Baseline corresponding to an SQL statement. I classify them into three states.

1. accepted (acceptable). The optimizer considers the execution plan in this baseline only for baselines in this status.
2. no-accepted (unacceptable). The optimizer does not consider this baseline during SQL statement parsing. The baseline in this state must be converted to an accepted state after being evolved and verified before being considered by the optimizer.
3. fixed is yes (fixed), which is the highest priority inherent in the baseline! Takes precedence over the other two types of baselines.

5. View baselines

1. Basic View: dba_ SQL _plan_baselines, dba_ SQL _management_config

2. Underlying view: sqlobj $ data and sqlobj $ (Save the specific hint). view the execution plan statement saved in the baseline as follows:

Select

Extractvalue (value (d), '/hint') as outline_hints

From

Xmltable ('/outline_data/hint'

Passing (

Select

Xmltype (comp_data) as xmlval

From

Sqlobj $ data sod, sqlobj $ so

Where so. signature = sod. signature

And so. plan_id = sod. plan_id

And comp_data is not null

And name like '& baseline_plan_name'

)

) D;

3. view the Baseline details through functions:

Select * from table (dbms_xplan.display_ SQL _plan_baseline (SQL _handle => 'sys _ SQL _11bcd50cd51504e9 ', plan_name =>' SQL _ PLAN_13g6p1maja1790cce5f0e '));