OracleSQL execution plan baseline summary (SQLPlanBaseline)

I. Basic Concept: Oracle started at 11g and provided a new method for fixed execution plans, namely, SQL plan baseline, Chinese name SQL Execution plan baseline (baseline for short ), it can be considered as an improved version of OUTLINE or SQL profile. Basically, its main functions can be summarized as follows: 1. The execution plan of a given SQL statement is stable, prevent SQL statement execution plans from being affected by changes in execution environment, object statistics, and other factors! 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 deprecated, and SQL profile is estimated to be difficult to improve in subsequent releases, therefore, for friends 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.

Ii. Working Mechanism starting from Oracle 11g. Due to the existence of the baseline, the parsing process of a statement is roughly as follows: after the SQL statement is hard parsed, the CBO (Optimizer) there will be many execution plans, and CBO selects one of them with the lowest cost. The text based on SQL statements forms a hash value (signature). This hash value is used to check whether the same baseline exists in the data dictionary. If the baseline exists, the optimizer compares the generated execution plan with the execution plan saved in the SQL plan baseline. 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. 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) 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. Some features of the baseline are summarized as follows: OPTIMIZER_USE_ SQL _PLAN_BASELINE is used to control whether Oracle uses the baseline. The default value is TRUE, and the baseline is automatically used. By default, the baseline is not automatically created in 11g, which is different from that in OUTLINE and SQL Profile. The concept of classification does not exist in the baseline. different from that in OUTLINE and SQL Profile, each SQL statement can have multiple baselines. Oracle determines whether or not a baseline is effective for all instances in RAC based on the rules. One baseline is SQL _handle, which can be understood as the unique identifier of the statement text, one SQL statement is SQL _plan_name, which can be understood as the unique identifier of the Execution Plan. It cannot use the force_matching attribute like the SQL profile to apply multiple statements to one baseline for SQL statements with different nominal values. Iii. Methods for creating baselines 1. automatically capture baselines by setting optimizer_cature_ SQL _plan_baselines to true, the optimizer generates and saves baselines for SQL statements that have been repeatedly executed for more than two times (can be modified at the system level or session level). 2. It loads the baseline from the SQL tuning set, use the package dbms_spm.load_plans_from_sqlset to load the baseline DECLARE l_plans_loaded PLS_INTEGER from the SQL optimization set; BEGIN l_plans_loaded: = BEGIN (sqlset_name => 'my _ sqlset'); END; /3. load from the library cache. Use the package lifecycle function to create a baseline for a statement already in the cursor cache: DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded: = bytes (SQL _id => '1fkh93md0802n ', plan_hash_value => null); END;/Note: There are multiple loading methods, such as fuzzy matching based on SQL text and username for SQL statement parsing. For details, see the document.

4. The baseline is the baseline corresponding to an SQL statement in several statuses. I will classify them into three statuses (acceptable): Only the baseline in this status, the optimizer considers the execution plan no-accepted (unacceptable) in this baseline, which is not considered during SQL statement parsing. A baseline in this state must be changed to an accepted state after it passes evolution and verification before being considered by the optimizer to use fixed as yes (fixed). The baseline in this state has the highest priority! Takes precedence over the other two types of baselines.

5. View baseline 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 Baseline details through functions: select * from table (dbms_xplan.display_ SQL _plan_baseline (SQL _handle => 'sys _ SQL _11bcd50cd51504e9 ', plan_name =>' SQL _ PLAN_13g6p1maja1790cce5f0e '));

6. Evolution baselines to verify whether an unacceptable execution plan in the baseline is more efficient than an acceptable execution plan, it must be verified through evolution, the optimizer needs to execute this SQL statement with different execution plans, and check whether the unacceptable execution plan baseline brings better performance. If the performance is indeed higher, A baseline in this unacceptable state is converted to an acceptable state. There are two evolution methods: 1. Manually run SELECT DBMS_SPM.evolve_ SQL _plan_baseline (SQL _handle => 'sys _ SQL _xxxxxxxxxxxxx ') From dual; and time_limit/verify/commit parameters, you can refer to document 2. Enable the optimization package to automatically evolve the baseline. It can be understood that when a scheduling task is started, the baseline in the unacceptable status can be periodically checked.

7. You can use the dbms_spm.alter_ SQL _plan_baseline package to modify attributes of a baseline. The following attributes ENABLED: setting the value of this attribute to NO indicates that Oracle 11g temporarily disables a plan, an SQL plan must both be marked as ENABLED and ACCEPTED; otherwise, CBO will ignore it FIXED: Set to YES, which plan will be the only choice for the optimizer [highest priority], even if a plan may have lower costs. This allows DBA to revoke the default behavior of SMB, which is particularly useful for converting a storage summary to a stable SQL plan baseline, note: When a new plan is added to an SQL plan baseline marked as FIXED, the new plan cannot be used unless it declares as FIXED status AUTOPURG: setting the value of this attribute to "NO" tells Oracle to retain it for 11g indefinitely, so there is NO need to worry about the automatic clearing Mechanism of SMB plan_name: Changing the SQL plan Name description: Changing the SQL plan description Syntax: set serveroutput on declare v_text PLS_INTEGER; BEGIN v_text: = convert (SQL _handle => 'sys _ SQL _xxxxxx ', plan_name => 'sys _ SQL _PLAN_xxxxxxxxx', attribute_name => 'fixed ', attribute_value => 'yes'); DBMS_OUTPUT.put_line ('plans Altered: '| v_text); END ;/

8. Migration baseline dbms_spm provides multiple procedures for migrating SQL scheduler baselines between databases create_stgtab_baseline create a scheduler baseline save table pack_stgtab_baseline copy the baseline from the data dictionary to the unpack_stgtab_baseline table in step 1 the process of saving the table to the data dictionary of the database to be migrated is as follows: 1. Create a user table exec dbms_spm.create_stgtab_baseline (table_name => 'baseline _ test', table_owner => 'Scott ', tablespace_name => '') that stores the contents of the basic table in the data dictionary ''); 2. Insert the contents of the base table in the data dictionary to the user table created in step 1. exec: I: = dbms_spm.pack_stgtab_baseline (table_name => 'baseline _ test ', table_owner => 'Scott '); Note: Multiple insert methods are supported, such as SQL-related baselines that contain specific characters. SQL _handle is used to precisely identify a baseline, for details, see document 3. migrate the user table exp/imp or expdp/impdp through the migration tool 4. Insert the baseline content stored in the migrated User table into the data dictionary of the current database, to migrate exec: I: = dbms_spm.unpack_stgtab_baseline (table_name => 'baseline _ test', table_owner => 'Scott '); Note: multiple methods are supported, just like step 2, for details, see the document

9. You can use the dbms_SPM.drop_ SQL _plan_baseline package to manually delete the baseline used by the data dictionary. If fixed is no, the baseline is automatically deleted after a certain retention period (you can view the dba_ SQL _management_config view) the manual deletion method is as follows: set serveroutput on declare v_text PLS_INTEGER; BEGIN v_text: = cursor (SQL _handle => 'sys _ hand', plan_name => NULL); DBMS_OUTPUT.put_line (v_text); END ;/

10. Fix an SQL statement as the expected execution plan. I generally perform the following steps (for reference only) 1. Create a baseline for this SQL statement. 2. Add the hint Lai Yuxing to this SQL statement, make sure that the execution plan after the hint is added to the SQL statement is the same as we expected. 3. Add the execution plan generated in step 3 to the baseline created in step 1. (Note: As mentioned above, one SQL statement can have multiple baselines !) 4. Delete the execution plan created in step 1 of the baseline (in this way, we can ensure that there is only the expected execution plan in the baseline, that is, the execution plan of step 2 SQL statement is saved) 5. Check whether the verification takes effect. For more information, see!

11. Example (fix an SQL statement as our expected Execution Plan) first run two statements with the same structure. The following experiment uses the SQL plan baseline, use the execution plan of one statement to fix SQL> select SQL _handle, plan_name, dbms_lob.substr (SQL _text, 60, 1) SQL _text, ACCEPTED from dba_ SQL _plan_baselines; no SQL> alter system flush shared_pool is selected; the system has changed. SQL> select/* outlinetest2 * // * + full (dh_stat) */* from dh_stat where id = 711; id name type ---------- ---------------------------- --------------- 711 I _STREAMS_PROCESS_PARAMS1 index SQL> select/* outlinetest3 * // * + index (dh_stat) */* from dh_stat where id = 711; id name type ---------- ------------------------------ --------------- 711 I _STREAMS_PROCESS_PARAMS1 INDEX
SQL> select SQL _text, SQL _id, hash_value, child_number, plan_hash_value, to_char (LAST_ACTIVE_TIME, 'hh24: mi: ss ') time 2 from v $ SQL a where SQL _text like '% outlinetest %' and SQL _text not like '% v $ SQL % '; SQL _TEXT SQL _ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME Hour --------------- ---------- --------------------- -------- select/* outlinetest2 * // * + full (dh_stat) */* from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:27:31 h_stat where id = 711 select/* outlinetest3 * // * + index (dh_stat) */* from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id = 711
SQL> select * from table (dbms_xplan.display_cursor ('4vaj9fgjysy9c', '',''); PLAN_TABLE_OUTPUT tables ---------------- SQL _ID 4vaj9fgjysy9c, child number 0 ------------------------------------- select/* outlinetest2 * // * + full (dh_stat) */* from dh_stat where id = 711 Plan hash val Ue: 1845196118 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 124 (100) | * 1 | table access full | DH_STAT | 1 | 38 | 124 (1) | 00:00:02 | ----------------------------------------------------------- ---------------- Predicate Information (identified by operation id): ----------------------------------------------------- 1-filter ("ID" = 711) has 19 rows selected.
SQL> select * from table (dbms_xplan.display_cursor ('fm35jcmypb3qu ', '',''); PLAN_TABLE_OUTPUT tables ---------------- SQL _ID fm35jcmypb3qu, child number 0 ------------------------------------- select/* outlinetest3 * // * + index (dh_stat) */* from dh_stat where id = 711 Plan hash va Lue: 2780970545 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 2 (100) | 1 | table access by index rowid | DH_STAT | 1 | 38 | 2 (0) | 00:00:01 | * 2 | index range scan | IND_1 | 1 | 1 (0) | 00:00:01 | your Predicate Information (identified by operation id): ------------------------------------------------- 2-access ("ID" = 711) has 20 rows selected.

SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1: = DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (5 SQL _id => '4vaj9fgjysy9c ', 6 plan_hash_value => 1845196118 7); 8 end; 9. the PL/SQL process is successfully completed.
SQL> select SQL _handle, plan_name, dbms_lob.substr (SQL _text, 60, 1) SQL _text, ACCEPTED from dba_ SQL _plan_baselines;
SQL _HANDLE PLAN_NAME SQL _TEXT ACC -------------------------------- ---------------------------- success --- explain select/* outlinetest2 * // * + full (dh_stat) */* from d YES h_sta
When the SQL plan baseline is produced, the execution plan cannot be found during the first query until the second execution, the following SQL> select/* outlinetest2 * // * + full (dh_stat) */* from dh_stat where id = 711; id name type ---------- explain --------------- 711 I _STREAMS_PROCESS_PARAMS1 index SQL> select * from table (values ('4vaj9fgjysy9c', '',''); PLAN_TABLE_OUTPUT limit SQL _ID: 4vaj9fgjysy9c cannot be found SQL> select SQL _text, SQL _id, hash_value, child_number, plan_hash_value, to_char (LAST_ACTIVE_TIME, 'hh24: mi: ss ') time 2 from v $ SQL a where SQL _text like '% outlinetest %' and SQL _text not like '% v $ SQL % '; SQL _TEXT SQL _ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME Hour -------------------- ------------ hour -------- select/* outlinetest3 * // * + index (dh_stat) */* from hour 4250242778 0 2780970545 12:27:41 dh_stat where id = 711

SQL> select/* outlinetest2 * // * + full (dh_stat) */* from dh_stat where id = 711; id name type ---------- certificate --------------- 711 I _STREAMS_PROCESS_PARAMS1 index SQL> select SQL _text, SQL _id, hash_value, child_number, plan_hash_value, to_char (LAST_ACTIVE_TIME, 'hh24: mi: ss ') time 2 from v $ SQL a where SQL _text like '% outlinetest %' and SQL _text not like '% v $ SQL % ';
SQL _TEXT SQL _ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME Hour --------------- ---------- --------------------- -------- select/* outlinetest2 * // * + full (dh_stat) */* from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:30:54 h_stat where id = 711 select/* outlinetest3 * // * + index (dh_stat) */* from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id = 711

SQL> select * from table (dbms_xplan.display_cursor ('4vaj9fgjysy9c', '',''); PLAN_TABLE_OUTPUT tables ---------------- SQL _ID 4vaj9fgjysy9c, child number 0 ------------------------------------- select/* outlinetest2 * // * + full (dh_stat) */* from dh_stat where id = 711
Plan hash value: 1845196118 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 124 (100) | * 1 | table access full | DH_STAT | 1 | 38 | 124 (1) | 00:00:02 | identified Predicate Information (identified by operation id ):---------------------------------------------------
1-filter ("ID" = 711) Note ------SQL plan baseline SQL _PLAN_13g6p1maja17934f41c8d used for this statement 23 rows have been selected.
Load the execution plan that meets our expectation to the SQL baseline generated for the first time! SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1: = explain (5 SQL _id => 'fm35jcmypb3qu', 6 plan_hash_value => 2780970545, SQL _handle => 'sys _ hand' 7 ); 8 end; 9/
The PL/SQL process is successfully completed.
We can see that SYS_ SQL _11bcd50cd51504e9 currently has two plan_name SQL> select SQL _handle, plan_name, dbms_lob.substr (SQL _text, 60, 1) SQL _text, ACCEPTED from dba_ SQL _plan_baselines;
SQL _HANDLE PLAN_NAME SQL _TEXT ACC -------------------------------- ---------------------------- success --- explain select/* outlinetest2 * // * + full (dh_stat) */* from d YES h_sta
SYS_ SQL _11bcd50cd51504e9 explain select/* outlinetest2 * // * + full (dh_stat) */* from d YES h_sta Delete the first plan_name, removing unnecessary execution plan versions! SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1: = partition (SQL _handle => 'sys _ SQL _11bcd50cd51504e9 ', plan_name =>' SQL _ upload'); 5 end; 6/
The PL/SQL process is successfully completed.

Through some tests below, we can see that the new SQL plan baseline has taken effect normally, and the prompt statement contains the full prompt, the execution plan also uses the index to locate the data SQL> select/* outlinetest2 * // * + full (dh_stat) */* from dh_stat where id = 711; id name type ---------- ------------------------------ --------------- 711 I _STREAMS_PROCESS_PARAMS1 INDEX
SQL> select * from table (dbms_xplan.display_cursor ('4vaj9fgjysy9c', '',''); PLAN_TABLE_OUTPUT tables ---------------- SQL _ID 4vaj9fgjysy9c, child number 1 ------------------------------------- select/* outlinetest2 * // * + full (dh_stat) */* from dh_stat where id = 711 Plan hash val Ue: 2780970545 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement ||| 2 (100) | 1 | table access by index rowid | DH_STAT | 1 | 38 | 2 (0) | 00:00:01 | * 2 | index range scan | IND_1 | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id): ------------------------------------------------- 2-access ("ID" = 711) note ------SQL plan baseline SQL _PLAN_13g6p1maja1790cce5f0e used for this statement 24 rows have been selected.

You can use dba_ SQL _plan_baselines to display general information about available SQL plan baselines, or you can use the following method to display detailed information about the execution SQL plan baselines! Select * from table (dbms_xplan.display_ SQL _plan_baseline (SQL _handle => 'sys _ SQL _11bcd50cd51504e9 ', plan_name =>' SQL _ PLAN_13g6p1maja1790cce5f0e '));
PLAN_TABLE_OUTPUT tables -------------- SQL handle: SYS_ SQL _11bcd50cd51504e9 SQL text: select/* outlinetest2 * // * + full (dh_stat) */* from dh_stat where id = 711 bytes --------------------------------------------------------------------------------
Your Plan name: SQL _PLAN_13g6p1maja1790cce5f0e Plan id: 214851342 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD Balance --------------------------------------------------------------------------------
Plan hash value: 2780970545
Role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 38 | 2 (0) | 00:00:01 | 1 | table access by index rowid | DH_STAT | 1 | 38 | 2 (0) | 00:00:01 | * 2 | index range scan | IND_1 | 1 | 1 (0) | 00:00:01 | average ---------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):---------------------------------------------------
2-access ("ID" = 711) has 26 rows selected.
View the hint saved in the SQL scheduler baseline. The prompt set SQL> conn/as sysdba is connected. SQL> select 2 extractvalue (value (d), '/hint') as outline_hints 3 from 4 xmltable ('/outline_data/hint' 5 passing (6 select 7 xmltype (comp_data) as xmlval 8 from 9 sqlobj $ data sod, sqlobj $ so 10 where so. signature = sod. signature 11 and so. plan_id = sod. plan_id 12 and comp_data is not null 13 and name like '& baseline_plan_name' 14) 15) d; input baseline_plan_name value: SQL _PLAN_13g6p1maja1790cce5f0e Original Value 13: and name like '& baseline_plan_name' New Value 13: and name like 'SQL _ PLAN_13g6p1maja1790cce5f0e'
OUTLINE_HINTS restart -------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE ('11. 2.0.1 ') DB_VERSION ('11. 2.0.1 ') ALL_ROWS OUTLINE_LEAF (@ "SEL $1") INDEX_RS_ASC (@ "SEL $1" "DH_STAT" @ "SEL $1" ("DH_STAT ". "ID "))
You have selected 6 rows.