Oracle SPM execution plan management, oraclespm

Oracle SPM execution plan management, oraclespm

**************************************** * ******************** Part 1: concept *************************************** * ******************* SQL scheduler management is a new function introduced with Oracle Database 11g, by maintaining the so-called "SQL plan baseline (11g)", the system can automatically control the evolution of SQL plans. After this feature is enabled, you only need to verify that the newly generated SQL plan is integrated with the SQL plan baseline and does not cause performance regression. Therefore, when executing an SQL statement, you can only use the plan included in the corresponding SQL plan baseline. You can use the SQL optimization set to automatically load or implant the SQL plan baseline. The main advantage of the SQL scheduler management function is that the system performance is stable and there will be no planned regression. In addition, this function can save DBA a lot of time, which usually takes to determine and analyze SQL Performance regression and find available solutions. (1) instant Capture: use automatic scheduled capture by setting the initialization parameter OPTIMIZER_CAPTURE_ SQL _PLAN_BASELINES to TRUE. By default, this parameter is set to FALSE. Setting this parameter to TRUE will enable the automatic identification of repeated SQL statements and the automatic creation of plan history for such statements. (2) Batch loading: Use the DBMS_SPM package. This package supports manual management of SQL scheduler baselines. Using this package, you can directly load an SQL plan from the cursor cache or an existing SQL optimization set (STS) to the SQL plan baseline. For SQL statements that need to be loaded from STS to the SQL plan baseline, they need to be stored in STS. You can use DBMS_SPM to change the baseline plan status from accepted to unacceptable (and never accepted to accepted), and export the baseline plan from the stage table, then, use the exported baseline plan to load the SQL plan baseline to other databases. NOTE: SQL scheduler management uses an SQL scheduler benchmark mechanism. A scheduler baseline is a set of execution plans that the SQL optimizer allows to use and accept. In typical use cases, the database accepts only those execution plans that pass verification and are executed well into the plan baseline. ----- Set parameters: Enable SQL _plan_baselineshow parameter type value -------------------------------------- optimizer_capture_ SQL _plan_baselines boolean FALSEalter system set parameters = true; show parameter parameters --- 1. prepare the test environment create table t2 (sid number not null, sname varchar2 (10 )) Tablespace test; -- cyclically import data declare maxrecords 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 ('Data Input successful! '); Commit; end;/exec dbms_stats.gather_table_stats ('Scott', 't2', cascade => true ); **************************************** ********************* Part 2: create a scheduler baseline ****************************** * how to create a baseline. automatic baseline capture 2. load from the SQL optimization set by using the package dbms_spm.load_plans_from_sqlset3. load from the library cache, and use the dbms_spm.load_plans_from_cursor_cache function to create a baseline for a statement already in the cursor cache ---------------------------------- * Method 1. automatic baseline capture -------------------------------- * -------- case study Step 1: perform a simple query of set autotrace on; var v varchar2 (5); exec: v: = 1000; select * from t2 where sid <=: v; set autotrace off; Execution Plan -------------------------------------------------------- Plan hash value: 1513984157 Bytes | 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 | 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, accepted FROM dba_ SQL _plan_baselinesWHERE SQL _text LIKE '% select * from t2 where sid <=: v %'; SQL _HANDLE PLAN_NAME ena acc has been --- SQL _60fea6835db2e913 guest 60bcf2 YES Step 4: create index index_01 on t2 (sid); exec dbms_stats.gather_table_stats ('Scott ', 't2', cascade => true); Step 5: set autotrace on; var v varchar2 (5); exec: v: = 1000; select * from t2 where sid <=: v; set autotrace off; execution Plan ---------------------------------------------------------- Plan hash value: 1513984157 bytes | Id | O Peration | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 1000 | 12000 | 15 (0) | 00:00:01 | * 1 | table access full | T2 | 1000 | 12000 | 15 (0) | 00:00:01 | identified Predicate Information (identified by operation id ):------------------------- -------------------------- 1-filter ("SID" <= TO_NUMBER (: V) Note ------SQL plan baseline "used" 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 _60 Fea6835db2e913 success yes no -- SQL _60fea6835db2e913 SQL _PLAN_61zp6hdfv5u8mb860bcf2 YES -------------------------------- * method 2 is not enabled. load ------------------------------------------ * from the SQL tuning set by using the package dbms_spm.load_plans_from_sqlset step 1. create STSBEGIN DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'ospyang _ sts'); END;/--- create STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'ospyang _ sts ', => 'Sys ', description => 'oppyangtest'); END;/step 2. fill in mongodbms_sqltune.sqlset_cursor; beginopen baseline_ref_cur forselect VALUE (p) from table (partition (& begin_snap_id, & end_snap_id, NULL, 'all') p; DBMS_SQLTUNE.LOAD_SQLSET ('ospyang _ STS ', baseline_ref_cur); end;/input begin_snap value: 11647 egin Snapshot Id specified: 11647 input en D_snap VALUE: 11859nd Snapshot Id specified: 11859 -- or specify mongodbms_sqltune.sqlset_cursor; beginopen baseline_ref_cur forselect VALUE (p) from table (values (& begin_snap_id, & end_snap_id, 'SQL _ id =' | CHR (39) | '& SQL _id' | CHR (39) | '', NULL, NULL, 'all') p; DBMS_SQLTUNE.LOAD_SQLSET ('ospyang _ STS ', baseline_ref_cur); end;/Step 3: load from SQLSET TO BE DBM S_SPM as input: DECLAREmy_plans pls_integer; BEGINmy_plans: = DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name => 'oss _ sts'); END; // ******* syntax DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2: = NULL, basic_filter IN VARCHAR2: = NULL fix, ed IN VARCHAR2: = 'no ', enabled IN VARCHAR2: = 'yes' commit_rows in number: = 1000) RETURN PLS_INTEGER ;************** /Step 4: view the baseline select SQL _handle, plan_name, SQL _text from dba_ SQL _baselines; ---------------------------------- * method 3. load -------------------------------------- * Create a baseline for a statement already in the cursor cache by using the dbms_spm.load_plans_from_cursor_cache function. ---- Method 1: import a specified sqlid -- view the 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% '; declareu int; beginu: = dbms _ Spm. LOAD_PLANS_FROM_CURSOR_CACHE (SQL _ID => '57pk967xw5jqn', PLAN_HASH_VALUE => '123'); DBMS_OUTPUT.put_line ('import completed! '); End;/declareret varchar2 (100); beginret: = upper (SQL _id => 'fwjgwwp18z7ad', -- plan_hash_value => '2016' plan _ hash_value => NULL ); end;/If the hash value of the execution plan is not specified or NULL, all available execution plans of the given SQL statement will be loaded. --- Method 2: Import multiple declareu int; beginu: = dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (SQL _ID => '57pk967xw5jqn', PLAN_HASH_VALUE => '123'); u: = dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (SQL _ID => '57pk967xw5 Jqk ', PLAN_HASH_VALUE => '000000'); u: = partition (SQL _ID => '57pk967xw5jqm', PLAN_HASH_VALUE => '20170901'); DBMS_OUTPUT.put_line ('import completed! '); End;/---- method 3: Create a baseline declareret varchar2 (100) for a user's cursor; beginret: = dbms_spm.load_plans_from_cursor_cache (attribute_name => 'parsing _ schema_name ', attribute_value => 'Scott '); end;/---- Method 4: Create an SQL plan baseline for each SQL statement containing string t1 in the library cache: declareret varchar2 (100 ); beginret: = dbms_spm.load_plans_from_cursor_cache (attribute_name => 'SQL _ text', attribute_value =>' % t1 % '); end; // ***** syntax limit ACHE (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; struct (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; assign (attribute_name IN VARCHAR2, attribute_value IN VARCHAR2, fixed IN VARCHAR2: = 'no', enabled IN VARCHAR2: = 'yes') RETURN PLS_INTEGER; *****/--- check whether an execution plan exists SELECT SQL _handle, plan_name, enabled, accepted FROM dba_ SQL _plan_baselinesWHERE SQL _t Ext LIKE '% select sid, sname from t5 where sid <=: v % '; SQL _HANDLE PLAN_NAME ena acc installed before --- specified when YES then YES YESSQL_93ffdec9273ee793 then YES ---- check whether SQL plan baselineselect SQL _id, child_number, SQL _plan_baseli is used in a query Ne, SQL _textfrom v $ sqlwhere SQL _plan_baseline is not nulland SQL _text like '% select count (*) from scott. tblorders % '; ------- Case Study: 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% '; declareu int; beginu: = dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (SQL _ID => 'g5f5cz344h5dz', PLAN_HASH_VALUE => '123'); DBMS_OUTPUT.put_line ('import completed! '); End;/--- check whether an execution plan exists: SELECT SQL _handle, plan_name, enabled, accepted FROM dba_ SQL _plan_baselinesWHERE SQL _text LIKE' % select count (1) from scott. tblorders where orderstatus> 0% '; **************************************** ********************* Part 3: SQL plan baseline modification ************************************ * *********************** ---- 1. modify the ACCEPTED of the new plan to YES/*********. Use the DBMS_SPM.EVOLVE_ SQL _PLAN_BASELINE API to control The evolution of execution plans. Syntax: Explain (SQL _handle IN VARCHAR2: = NULL, --> NULL indicates all SQL plan_name IN VARCHAR2: = NULL, time_limit IN INTEGER: = DBMS_SPM.AUTO_LIMIT, verify IN VARCHAR2: = 'yes', commit IN VARCHAR2: = 'yes') return clob; controlled by two tags: o Verify + YES (only a better performance plan will be evolved) + NO (evolution of all plans) o Commit + YES (direct evolution) + NO (only generate reports) Here we can use different permutation and combination to achieve different results: o automatically receives all execution plans with better performance (Verify-> YES, Commit-> YES) o automatically receives all New execution plans (Verify-> NO, Commit-> YES) o compare performance, generate reports, and manually confirm the evolution (Verify-> NO, Commit-> NO) * *******/set serveroutput ondeclare l_plans_altered clob; BEGINl_plans_altered: = reset (SQL _handle => 'SQL _ 60fea6835db2e913', plan_name => 'SQL _ failed ', verify => 'no', commit => 'yes'); DBMS_OUTPUT.put_line ('plans Altered: '| l_plans_altered); END;/---- 2.: Modify the existing Baseline/********* Language 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 ondeclare l_plans_altered PLS_INTEGER; BEGINl_plans_altered: = require (SQL _handle => 'SQL _ 60fea6835db2e913 ', plan_name => 'SQL _ PLAN_61zp6hdfv5u8mb860bcf2', attribute_name => 'Enabled', attribute_value => 'no'); DBMS_OUTPUT.put_line ('plans Altered: '| l_plans_altered); END;/---- 3. delete existing BaselineSET serveroutput ondeclare l_plans_dropped PLS_INTEGER; BEGIN l_plans_dropped: = tables (SQL _handle => 'SQL _ hand', plan_name => NULL); Tables (l_plans_dropped); END; /SELECT SQL _handle, plan_name, enabled, accepted FROM dba_ SQL _plan_ B AselinesWHERE SQL _text LIKE '% select * from t2 where sid <=: v %'; SQL _60fea6835db2e913 then YES YESSQL_60fea6835db2e913 then NO YESset autotrace on; var v varchar2 (5); exec: v: = 1000; select * from t2 where sid <=: v; set autotrace off; Execution Plan -------------------------------------------------------- Plan hash value: 1194324917 ------------------------------ ------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 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 4: SQL plan baseline migration ************************************ * *********************** migration steps: 1. use the dbms_spm package and create_stgtab_baseline to create a process table 2. use dbms_spm.pack_stgtab_baseline to fill the planned baseline with the Process Table created in step 3. use the database link or data pump to copy to the target database. 4. use DBMS_SPM.unpack_stgtab_baseline to import the scheduler baseline to the target database. --- 1. create stage table BEGIN dbms_spm.create_stgtab_baseline (table_name => 'baseline _ STG01 ', table_owner => 'Scott', -- you cannot create 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 baselines from one system to another. syntaxDBMS_SPM.CREATE_STGTAB_BASELINE (table_name IN VARCHAR2, table_owner IN VARCHAR2: = NULL, tablesp Ace_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 has 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 tableExport staging table into a flat file using Oracle EXP utility or Data PumpTrans Fer flat file to the target systemImport staging 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 the SQL plan baseline from the data dictionary to the stage table declarek int; begink: = dbms_spm.pack_stgtab_baseline (TABLE_NAME => 'baseline _ STG01 ', TABLE_OWNER => 'Scott'); end; /declarev_ret number (100); beginv_ret: = dbms_sp M. pack_stgtab_baseline (table_name => 'mystgtab', table_owner => user, SQL _handle => 'SQL _ e436abaac44f99d8', -- plan_name => 'SQL _ upload',); end; // ********* Syntax: DBMS_SPM.PACK_STGTAB_BASELINE (table_name IN VARCHAR2, table_owner IN VARCHAR2: = NULL, SQL _handle IN VARCHAR2: = NULL, plan_name IN VARCHAR2: = NULL, SQL _text IN CLOB: = NULL, creator IN VARCHAR2: = 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. use expdp \ impdp or exp, and the imp tool moves the table from the test database to the target database ---- 4. copy SQL plan baseline from stage table to data dictionary --- 4.1 copy all SQL plan baseline from stage table to 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 => 'Scott'); DBMS_OUTPUT.put_line ('plans Unpacked: '| l_plans_unpacked); END; /--- 4.2 copy table T1 from SQL plan baseline to the data dictionary declarev_ret varchar2 (100); beginv_ret: = dbms_spm.unpack_stgtab_baseline (table_name => 'baseline _ STG01 ', table_owner => 'Scott ', SQL _text =>' % FROM t1 % '); end; // ******** Syntax: DBMS_SPM.UNPACK_STGTAB_BASELINE (table_name IN VARCHAR2, table_owner IN VARCHAR 2: = NULL, SQL _handle IN VARCHAR2: = NULL, plan_name IN VARCHAR2: = NULL, SQL _text IN CLOB: = NULL, creator IN VARCHAR2: = 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; if only table_name and table_owner are specified, all SQL plans are processed. Together with plan_name, SQL _handle can precisely identify an SQL plan baseline. plan_name is optional. Case Sensitive in SQL _text/******** SELECT SQL _handle, plan_name, enabled, accepted FROM dba_ SQL _plan_baselinesWHERE SQL _text LIKE '% select sid, sname from t4 where sid <=: v % '; SQL _HANDLE PLAN_NAME ena acc -------------------------------- ---------------------------- --- SQL _4e6155ac1d5b5962 SQL _PLAN_4wsapphfpqqb214816fa9 YES --- Delete the SQL scheduler baseline declarev_ret varchar2 (100); beginv_ret: = begin (SQL _handle => 'mystgtab', plan_name => 'swew223 '); end;/You must specify at least one of the two parameters.