Oracle SPM Execution Plan Management

The first part: Concept ******************************************** SQL Plan management is a new feature introduced with Oracle Database 11g that enables the system to automate the evolution of SQL plans by maintaining so-called "SQL Plan Baseline (11g)". When this feature is enabled, this integration can be done as long as it proves that the newly generated SQL plan integrates with the SQL plan baseline without causing performance regressions. Therefore, when you execute an SQL statement, you can only use the plan that is included in the corresponding SQL plan baseline. SQL plan baselines can be automatically loaded or implanted using SQL optimization sets. The main advantage of the SQL Plan management feature is that system performance is stable and no planned regression occurs. In addition, this feature can save a lot of DBA time, which is typically spent on identifying and analyzing SQL performance regressions and finding available solutions. (1) Instant capture: Use automatic plan capture by Optimizer_capture_sql_plan_ initialization parameters BASELINES is set to true. By default, this parameter is set to False. Setting this parameter to true turns on the ability to automatically identify repeatable SQL statements and automatically create schedule history for such statements. (2) Bulk load: Use the DBMS_SPM package, which supports manual management of SQL plan baselines. With this package, you can load SQL plans from the cursor cache or an existing SQL optimization set (STS) directly into the SQL plan baseline. For SQL statements to be loaded from the STS to the SQL plan baseline, the SQL plan needs to be stored in the STS. You can use DBMS_SPM to change the state of the baseline plan from accepted to unacceptable (and never accept changes to accepted), you can also export the baseline plan from the staging table, and then use the exported baseline plan to load the SQL plan baseline into another database. Note:sql Plan management uses a baseline mechanism called SQL planning. A plan baseline is a collection of execution plans that are allowed and accepted for use by the SQL optimizer. In a typical usage scenario, the database only accepts those that are validated and executed in a good execution plan into the plan baseline. -----Setting Parameters: Enable sql_plan_baselineshow parameter Optimizer_capture_sql_plan_baselinesname TYPE VALUE-----------------------------------------------------------------------------Optimizer_capture_sql_plan_baselines Boolean falsealter system set optimizer_capture_sql_plan_baselines=true;show parameter Optimizer_capture_sql_plan_ Baselines---1. Preparing the test Environment create TABLE T2 (SID number not NULL, sname varchar2) tablespace test;--Loop import data Declare Maxreco        RDS Constant int:=20000;    I int: = 1;        Begin for I in 1..maxrecords loop insert into T2 values (i, ' Ocpyang ');    End Loop; Dbms_output.put_line (' Success in data entry!    ');    Commit End /exec dbms_stats.gather_table_stats (' SCOTT ', ' T2 ', cascade = true); ******************************************** Part II: Create a plan baseline for SQL statements in SQL tuning set ************************************************************ Several ways to create baselines 1. Automatically capture baselines 2. Load from the SQL tuning collection by using the package Dbms_spm.load_plans_from_sqlset3. Loaded from the library cache, via package dbms_spm.load_plans_from_ The Cursor_cache function creates a baseline for a statement that is already in the cursor cache----------------------------------* Way 1. Automatic capture of Baseline----------------------------------*--------Case Demo Step 1: JaneSingle query set Autotrace On;var v varchar2 (5);  Exec:v: = 1000; SELECT * from T2 where Sid<=:v;set autotrace off; Execution plans----------------------------------------------------------Plan hash value:1513984157------------------------ --------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------| 0 |  SELECT STATEMENT | | 1000 |    12000 | 15 (0) | 00:00:01 | |  * 1 | TABLE ACCESS full|  T2 | 1000 |    12000 | 15 (0) | 00:00:01 |--------------------------------------------------------------------------predicate information ( identified by Operation ID):---------------------------------------------------1-filter ("SID" <=to_number (: V))  Step 2: Simple query set Autotrace On;var v varchar2 (5);  Exec:v: = 1000; SELECT * from T2 where sid<=:v;set autotrace off; Step 3: View SQL PLAN baselineselect sql_handle, plan_name,enabled, accepte D from Dba_sql_plan_baselineswhere Sql_text LIKE '%select * from T2 where sid<=:v% '; sql_handle plan_name ENA ACC------------------------------------------------------------------sql_60fea6835d b2e913 sql_plan_61zp6hdfv5u8mb860bcf2 Yes Yes Step 4: Create new index CREATE index index_01 on T2 (SID); EXEC dbms_stats.gather_table_s  Tats (' SCOTT ', ' T2 ', cascade = TRUE); Step 5: Simple query set Autotrace On;var v varchar2 (5);  Exec:v: = 1000; SELECT * from T2 where Sid<=:v;set autotrace off; Execution plans----------------------------------------------------------Plan hash value:1513984157------------------------ --------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------| 0 |  SELECT STATEMENT | | 1000 |    12000 | 15 (0) | 00:00:01 | |  * 1 | TABLE ACCESS full|  T2 | 1000 |    12000 | 15 (0) | 00:00:01 |--------------------------------------------------------------------------predicate information ( Identified by OperatioN ID):---------------------------------------------------1-filter ("SID" <=to_number (: V)) Note------SQL plan base Line "SQL_PLAN_61ZP6HDFV5U8MB860BCF2" used for this statement step 6: View SQL PLAN baselineselect sql_handle, Plan_name, Enabled, accepted from Dba_sql_plan_baselineswhere sql_text like '%select * from T2 where sid<=:v% '; sql_handle plan_name ENA ACC------------------------------------------------------------------sql_60fea6835d b2e913 sql_plan_61zp6hdfv5u8m8d82fa42 Yes no--sql_60fea6835db2e913 not enabled SQL_PLAN_61ZP6HDFV5U8MB860BCF2 Yes YE S----------------------------------* Way 2. Load----------------------------------from the SQL tuning collection * by using the package Dbms_spm.load_plans _from_sqlset Step 1. Create a new Stsbegin dbms_sqltune. Drop_sqlset (sqlset_name = ' ocpyang_sts '); end;/---new Stsbegin dbms_sqltune. Create_sqlset (sqlset_name = ' ocpyang_sts ', Sqlset_owner = ' SYS ', description = ' ocpyangtest '); end;/Step 2. Fill the stsdeclarebaseline_ref_cur dbms_sqltune. Sqlset_Cursor;beginopen baseline_ref_cur forselect VALUE (P) from table (Dbms_sqltune. Select_workload_repository (&begin_snap_id, &end_snap_id,null,null,null,null,null,null,null, ' all ') P;D Bms_sqltune. Load_sqlset (' ocpyang_sts ', baseline_ref_cur); end;/input Begin_snap value: 11647egin Snapshot Id specified:11647 input End_snap value : 11859nd Snapshot Id specified:11859--or indicates sql_iddeclarebaseline_ref_cur dbms_sqltune. Sqlset_cursor;beginopen baseline_ref_cur forselect VALUE (P) from table (Dbms_sqltune. Select_workload_repository (&begin_snap_id, &end_snap_id, ' sql_id= ' | | CHR (39) | | &sql_id ' | | CHR | | ', null,null,null,null,null,null, ' all ') p;dbms_sqltune. Load_sqlset (' ocpyang_sts ', baseline_ref_cur); end;/Step 3: Load from Sqlset will DBMS_SPM as input create a plan baseline for each query included in the SQL tuning set Declaremy_ Plans Pls_integer; Beginmy_plans: = dbms_spm. Load_plans_from_sqlset (sqlset_name = ' ocpyang_sts '); end;//******** syntax dbms_spm. Load_plans_from_sqlset (Sqlset_name in VARCHAR2, sqlset_owner in VARCHAR2: = NULL, Basic_fIlter in VARCHAR2: = NULL, fixed in VARCHAR2: = ' NO ', enabled in VARCHAR2: = ' YES ' Commit _rows in Number: = +) RETURN pls_integer;**************/Step 4: View related plan baselines select Sql_handle,plan_name,sql_text from DB A_sql_baselines;----------------------------------* Way 3. Load----------------------------------from the library cache * via package Dbms_ The Spm.load_plans_from_cursor_cache function creates a baseline for a statement that is already in the cursor cache.----Method 1: Import a specified sqlid--view sql_id and Hash_value values select sql_id, Hash_value from V$sql where Sql_textlike '%select count (1) from scott.tblorders where orderstatus>0% ';d eclareu Int;beg INU:=DBMS_SPM. Load_plans_from_cursor_cache (sql_id=> ' 57pk967xw5jqn ',plan_hash_value=> ' 2002323537 ');D bms_output.put_line (' Import done! '); end;/Declareret varchar2 (n); Beginret: = Dbms_spm.load_plans_from_cursor_cache (sql_id=> ' fwjgwwp18z7ad ',--plan _hash_value=> ' 1601196873 ' plan_hash_value=>null); end;/if the execution plan hash value is not specified or is specified as NULL, All available execution plans for a given SQL statement are loaded.---Method 2: Import multiple Declareu int;beginu:=dbms_spm at the same time. Load_plans_from_cursor_cache (sql_id=> ' 57pk967xw5jqn ',plan_hash_value=> ' 2002323537 '); u:=dbms_spm. Load_plans_from_cursor_cache (sql_id=> ' 57pk967xw5jqk ',plan_hash_value=> ' 2002323538 '); u:=dbms_spm. Load_plans_from_cursor_cache (sql_id=> ' 57pk967xw5jqm ',plan_hash_value=> ' 2002323539 ');D bms_output.put_line (' Import done! '); end;/----Method 3: Create a baseline for a user's cursor Declareret varchar2; Beginret: = Dbms_spm.load_plans_from_cursor_cache (Attribute_ Name=> ' parsing_schema_name ',attribute_value=> ' SCOTT ') end;/----Method 4: For the Library The SQL statement that contains the string T1 in each text in the cache creates a SQL plan baseline: Declareret varchar2; Beginret: = Dbms_spm.load_plans_from_cursor_cache ( Attribute_name=> ' sql_text ',attribute_value=> '%t1% '); end;//***** syntax dbms_spm.          Load_plans_from_cursor_cache (sql_id in VARCHAR2, plan_hash_value in number: = NULL, Sql_text In CLOB, fixed in VARCHAR2: = ' NO ', enabled in VARCHAR2: = ' YES ') RETURN pls_integer;dbm S_SPM. Load_plans_from_cursor_cache (sql_id in VarchAR2, plan_hash_value in number: = NULL, sql_handle in VARCHAR2, fixed in VARCHAR2: = ' NO ', Enabled in VARCHAR2: = ' YES ') RETURN pls_integer;dbms_spm.             Load_plans_from_cursor_cache (sql_id in VARCHAR2, plan_hash_value in number: = NULL, fixed In VARCHAR2: = ' NO ', enabled in VARCHAR2: = ' YES ') RETURN pls_integer;dbms_spm. Load_plans_from_cursor_cache (Attribute_name in VARCHAR2, attribute_value in VARCHAR2, fixed in Varch AR2: = ' NO ', enabled in VARCHAR2: = ' YES ') RETURN pls_integer;******/---See if there is an execution plan select sql_handle, plan_name , enabled, accepted from Dba_sql_plan_baselineswhere sql_text like '%select sid,sname from T5 where sid<=:v% '; sql_handle plan_name ENA ACC------------------------------------------------------------------sql_e0c42f010e b9d50f sql_plan_f1j1g047bmp8gb73cade2 YES yessql_a9e4491f6b5d9737 sql_plan_amt293xppv5tr14816FA9 Yes yessql_93ffdec9273ee793 sql_plan_97zyyt4mmxtwm95fcfc25 Yes Yes----See if a query uses SQL PLAN baselineselect sql_i D,child_number,sql_plan_baseline,sql_textfrom V$sqlwhere Sql_plan_baseline is not nulland sql_text like '%select count ( *) from scott.tblorders% ';-------Case Demo: Select COUNT (1) from Scott.tblorders where Orderstatus>0;select sql_id,hash_ Value from V$sql where Sql_textlike '%select count (1) from scott.tblorders where orderstatus>0% ';d eclareu int;beginu:= DBMS_SPM. Load_plans_from_cursor_cache (sql_id=> ' g5f5cz344h5dz ',plan_hash_value=> ' 3360167359 ');D bms_output.put_line (' Import done! '); end;/---See if there is an execution plan select sql_handle, plan_name,enabled, accepted from Dba_sql_plan_baselineswhere sql_text like '%sele CT count (1) from scott.tblorders where orderstatus>0% '; ********************************************************* Part III: SQL Plan baseline Modify ************************************************************----1. Accepted to modify the new plan is yes/ Syntax uses DBMS_SPM. Evolve_sql_plan_basEline This API to control the evolution of the execution plan. Syntax: DBMS_SPM. Evolve_sql_plan_baseline (sql_handle in VARCHAR2: = NULL, and null means for all SQL plan_name in VARCHAR2: = null, time_l Imit in INTEGER: = DBMS_SPM. Auto_limit, verify in VARCHAR2: = ' yes ', commit in VARCHAR2: = ' yes ') RETURN CLOB; This is controlled by two tags: o verify + YES (only Better-performing plans will evolve) + No (evolution of all Plans) O Commit + YES (direct Evolution) + No (Generate reports only) Here you can get different results with different permutations: O automatically receive all performance-better execution plans (Verify->yes, Commit->yes) o automatically receive all new execution plans (Verify->no, Commit->yes) o compare performance, generate reports, manually confirm Evolution (Verify->no, commit->no) * * * * /set serveroutput ondeclare l_plans_altered clob; Beginl_plans_altered: = dbms_spm.evolve_sql_plan_baseline (sql_handle = ' sql_60fea6835db2e913 ', Plan_name =&G T ' Sql_plan_61zp6hdfv5u8m8d82fa42 ', verify = ' NO ',commit=> ' YES ');D bms_output.put_line (' plans altered: ' | | l_ plans_altered); end;/----2.: Modifies an existing baseline/********* syntax dbms_spm. Alter_sql_plan_baseline (sql_handle in VARCHAR2: = NULL, plan_name in VARCHAR2: = NULL, attribute_name in VARCHAR2, Attribute_value in VARCHAR2) RETURN pls_integer;************/set serveroutput Onde CLARE l_plans_altered Pls_integer; Beginl_plans_altered: = dbms_spm.alter_sql_plan_baseline (sql_handle = ' sql_60fea6835db2e913 ', Plan_name =&gt ; ' Sql_plan_61zp6hdfv5u8mb860bcf2 ', attribute_name = ' ENABLED ', attribute_value = ' NO ');D bms_output.put_line (' Plans altered: ' | | l_plans_altered); end;/----3. Delete the existing baselineset serveroutput ondeclare l_plans_dropped Pls_integer; BEGIN l_plans_dropped: = dbms_spm.drop_sql_plan_baseline (sql_handle = ' sql_3a8461388a9bfa52 ', plan_name = N     ULL); Dbms_output.put_line (l_plans_dropped);  End;/select sql_handle, plan_name,enabled, accepted from Dba_sql_plan_baselineswhere sql_text like '%select * from T2 where sid<=:v% ';  sql_60fea6835db2e913 sql_plan_61zp6hdfv5u8m8d82fa42 YES yessql_60fea6835db2e913 sql_plan_61zp6hdfv5u8mb860bcf2  NO yesset autotrace On;var v varchar2 (5); Exec:v: = 1000; SELECT * from T2 where Sid<=:v;set autotrace off; Execution plans----------------------------------------------------------Plan hash value:1194324917------------------------ ----------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |----------------------------------------------------------------------------------------| 0 |       SELECT STATEMENT |  | 1000 |     12000 | 3 (0) |   00:00:01 | |  1 | TABLE ACCESS by INDEX rowid|  T2 | 1000 |     12000 | 3 (0) | 00:00:01 | |   * 2 | INDEX RANGE SCAN |   index_01 |       180 |     | 2 (0) | 00:00:01 |----------------------------------------------------------------------------------------************** Part IV: SQL Plan baseline Migration ****************************************** Migration steps: 1. Use the DBMS_SPM package and create_stgtab_baseline to create a procedure table 2. Using Dbms_spm.pack_stgtab_ Baseline to populate the plan baseline with the new procedure in the first Step table 3. Use a database link or a data pump to replicate to the target database 4. Use DBms_spm.unpack_stgtab_baseline Import the plan baseline into the target database---1. Create stage table begin Dbms_spm.create_stgtab_baseline (table_name = " Baseline_stg01 ', table_owner = ' SCOTT ',--Cannot create a new tablespace_name=> ' USERS ' under the SYS account; end;//****** Dbms_spm.create_stgtab_baseline Syntax This procedure creates a staging table used for transporting SQL plan Baseli NES from one system to another. SYNTAXDBMS_SPM. Create_stgtab_baseline (table_name in VARCHAR2, table_owner in VARCHAR2: = NULL, tablespace_name in VARCHAR2: = NULL); The creation of staging table is the first step. To migrate SQL plan baselines from one system to another, the USER/DBA have to perform a series of steps as Follows:create A staging table in the source Systemselect SQL plan baselines in the source system and pack them into the staging tableexp ORT staging table into a flat file using Oracle EXP utility or Data Pumptransfer flat file to the target Systemimport stag ing table from the flat file using Oracle IMP utility or Data pumpselect SQL plan Baselines from the staging table and unpack them into the target system************/----2. Copy SQL plan baseline from the data dictionary to the stage table de Clarek int;begink:=dbms_spm.pack_stgtab_baseline (table_name=> ' baseline_stg01 ',TABLE_OWNER=> ' SCOTT '); end;/ Declarev_ret number (+); Beginv_ret: = dbms_spm.pack_stgtab_baseline (table_name = ' mystgtab ',table_owner=> User,sql_handle=> ' Sql_e436abaac44f99d8 ',--plan_name=> ' sql_plan_f8dpbpb24z6fs94ecae5c ',); end;//********** Syntax: DBMS_SPM. Pack_stgtab_baseline (table_name in VARCHAR2, table_owner in VARCHAR2: = NULL, sql_handle in Varch AR2: = null, plan_name in VARCHAR2: = null, sql_text in CLOB: = null, creator in VARCHAR 2: = NULL, origin in VARCHAR2: = NULL, Enabled in VARCHAR2: = NULL, accepted in VARCHAR2  : = null, fixed in VARCHAR2: = null, module in VARCHAR2: = null, action in VARCHAR2: = NULL) RETURN number;*****/----3. With EXPDP\IMPDThe P or Exp,imp tool moves the table from the test library to the target library----4. Copy the SQL plan baseline from the stage table to the data dictionary---4.1 Copy all SQL plan baseline from the stage table to the data dictionary set serveroutput Ondeclare l_plans_unpacked Pls_integer; BEGIN l_plans_unpacked: = dbms_spm.unpack_stgtab_baseline (table_name = ' baseline_stg01 ', Table_owner =&gt ; ' SCOTT '); Dbms_output.put_line (' plans unpacked: ' | | l_plans_unpacked); end;/---4.2 Copy from the stage table in SQL Plan baseline to the data dictionary Declarev_ret varchar2 for the T1 table; Beginv_ret: = Dbms_spm.unpack_stgtab_ Baseline (table_name = ' baseline_stg01 ',table_owner=> ' SCOTT ',sql_text=> '%from t1% '); end;//********* syntax: D BMS_SPM. Unpack_stgtab_baseline (table_name in VARCHAR2, table_owner in VARCHAR2: = NULL, sql_handle in VAR CHAR2: = null, plan_name in VARCHAR2: = null, sql_text in CLOB: = null, creator in Varch AR2: = NULL, origin in VARCHAR2: = NULL, Enabled in VARCHAR2: = NULL, accepted in VARCHAR 2: = null, fixed in VARCHAR2: = null, MOdule in VARCHAR2: = null, action in VARCHAR2: = null) RETURN number, if you specify only table_name and Table_owner, you are handling All SQL Plan baseline. Sql_handle and plan_name can accurately identify a SQL plan baseline,plan_name as an option. Sql_text case/********select sql_handle, plan_name,enabled, accepted from Dba_sql_plan_baselineswhere Sql_text '%select sid,sname from T4 where sid<=:v% '; sql_handle plan_name ENA ACC------------------------------------------------------------------sql_4e6155ac1d 5b5962 sql_plan_4wsapphfpqqb214816fa9 Yes---Delete the SQL plan baseline declarev_ret varchar2; Beginv_ret: = dbms_spm.unpack_s Tgtab_baseline (sql_handle=> ' mystgtab ',plan_name=> ' swew223 '); end;/These two parameters specify at least one.

Oracle SPM Execution Plan Management