SQL Base Line working mechanism

<Personal configuration>normal configuration, OK, no need to, Oracle 11g default startup development:1, from the perspective of Oracle's development, it is estimated that this approach is the direction of Oracle's development and improvement, and now that outline has been deprecated, SQL profile estimates are also difficult to improve in subsequent releases, so for a friend who begins to contact Oracle from 11g,    Be sure to understand the SQL plan baseline, as this is the future mainstream! 2, the SQL execution plan baselines are saved in the data dictionary, and the query optimizer automatically determines that they are used. Working mechanism starting with Oracle 11g, the parsing of a statement is probably as follows because of the presence of a baseline:1.    After the SQL statement is hard-parsed, the CBO (optimizer) generates a number of execution plans from which the CBO chooses a minimum cost execution plan. 2The SQL statement-based text forms a hash value (signature) that checks the data dictionary for the presence of the same baseline. 3If the baseline is present, the optimizer will make the execution plan that was just generated and save the SQLPlanthe execution plan in the baseline is compared. 4If there is a SQL execution plan in the baseline that matches the execution plan generated by the CBO, and is marked as acceptable (' accepted '), the CBO-generated execution plan is enabled. 5If no matching sq execution plan exists in the baseline, the CBO evaluates multiple execution plans that are marked as ' accepted ' in the baseline and selects the execution plan with the lowest cost. (Note that a baseline of a statement can have multiple execution plans saved, which is different from other outline and SQL Profiel)6This newly generated execution plan is marked ' If the execution plan selected by the CBO during the hard parse process is lower than the cost of execution plan saved in the baseline.not-accepted ' and stored in the baseline.    The execution plan is not considered until it is evolved and validated, which is marked as accepted (evolution and validation, which can be understood simply as Oracle confirms that the execution plan can deliver better performance). This is how Oracle ensures that the performance of the SQL statement is not degraded (that is, the second major role I summed up in Part I), which is known as some of the characteristics of the baseline of the conservative selection strategy for the execution plan1Using Optimizer_use_sql_plan_baseline to control whether Oracle uses baselines, the default value is true, and the baseline is automatically used. 2The baseline is not automatically created by default in. 11g3Unlike outline and SQL profiles, there is no concept of classification in baselines4Unlike outline and SQL profile, each SQL statement can have multiple baselines. Oracle determines which baseline is specific based on the rules established5the baseline is in effect for all instances in the RAC6The baseline has two representations, one for sql_handle, which can be understood as a unique identifier that represents the statement text, and a unique identity that Sql_plan_name can understand as the execution plan7. You cannot apply multiple statements using a baseline with a SQL statement that does not have the same literal value as SQL profile through the Force_matching property. Several ways to create baselines1, automatic capture of baselines, by setting Optimizer_cature_sql_plan_baselines to True, the optimizer generates and saves baselines for repeated executions of more than two SQL statements (can be modified at system or session level)2, load from SQL tuning collection, load baselines from SQL Tuning collection by using package Dbms_spm.load_plans_from_sqlsetDECLAREl_plans_loaded Pls_integer; BEGINl_plans_loaded:=Dbms_spm.load_plans_from_sqlset (Sqlset_name= 'My_sqlset'); END; /    3, load from the library cache, and create a baseline from the package Dbms_spm.load_plans_from_cursor_cache function for a statement already in the cursor cacheDECLAREl_plans_loaded Pls_integer; BEGINl_plans_loaded:=Dbms_spm.load_plans_from_cursor_cache (sql_id= '1fkh93md0802n', Plan_hash_value=NULL); END; /Note: Can be loaded in a variety of ways, such as can be based on the SQL text fuzzy matching, SQL statement resolution of the user name, and so on, see the status of the document baseline a SQL statement corresponding to the baseline, I summarized them into three states1. Accepted (acceptable), only the baseline of this state, the optimizer will consider the execution plan in this baseline2, N--accepted (unacceptable), the baseline of this state, the optimizer does not consider during SQL statement parsing. The baseline of this state must be transformed into a accepted state after evolution and validation, before it is considered by the optimizer3. Fixed to Yes, the baseline of this state is inherently the highest priority! Priority is given to viewing baselines over the other two types of baselines1, basic view: Dba_sql_plan_baselines, Dba_sql_management_config2, bottom view: Sqlobj$data, sqlobj$ (save specific hint), view the execution plan statements saved in the baseline as follows:SelectExtractvalue (Value (d),'/hint') asoutline_hints fromXMLTable ('/outline_data/hint'Passing (SelectXmlType (comp_data) asXmlval fromsqlobj$data sod, sqlobj$ sowhereSo.signature=sod.signature andso.plan_id=sod.plan_id andComp_data is  not NULL     andName like '&baseline_plan_name')) D; 3to view the details of the baseline through a function:Select *  from Table(Dbms_xplan.display_sql_plan_baseline (sql_handle='Sys_sql_11bcd50cd51504e9', Plan_name='sql_plan_13g6p1maja1790cce5f0e')); Evolutionary baselines in order to verify that an execution plan in an unacceptable state in the baseline is more efficient than an execution plan that is in an acceptable state, it must be validated by evolution and requires the optimizer to execute the SQL statement with a different execution plan. Observing whether the execution plan baselines of an unacceptable state will result in better performance, and if the performance is indeed higher, the base of this unacceptable state will be converted to an acceptable state. There are two ways to evolve:1, manually execute the runSELECTDbms_spm.evolve_sql_plan_baseline (sql_handle= 'sys_sql_xxxxxxxxxxxxx') fromdual; and a time_limit ./Verify/commit a few parameters, you can refer to the document2, tuning package to achieve the automatic evolution of the baseline, it can be understood to start a scheduling task, periodic check whether there is an unacceptable state of the baseline can be modified by the evolution of the baseline can be modified by the Dbms_spm.alter_sql_plan_baseline package to modify some of the baseline properties, There are several main properties1. ENABLED: Set the value of this property to no tells Oracle 11g to temporarily disable a schedule, and one SQL plan must both be marked as enabled and accepted, otherwise the CBO will ignore it2. FIXED: Set to Yes, that plan will be the only option for the optimizer[Highest priority level], even if a plan might have a lower cost. This allows the DBA to revoke the default behavior of SMB, which is particularly useful for converting a storage profile into a stable SQL plan baseline, noting that when a new plan is added to a SQL plan baseline that is marked as fixed, the new plan cannot be exploited unless it is declared as a fixed state3. Autopurg: Setting the value of this property to no tells Oracle 11g to keep it indefinitely, without worrying about the automatic scavenging mechanism of SMB4. plan_name: Changing SQLPlanname5. Description: Changing the SQL plan description syntax:SETServeroutput on    DECLAREV_text Pls_integer; BEGINV_text:=Dbms_spm.alter_sql_plan_baseline (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; /

SQL Base Line working mechanism