oracle SPM 執行計畫管理，oraclespm

oracle SPM 執行計畫管理，oraclespm

************************************************************第一部分：概念************************************************************SQL 計劃管理是一種隨Oracle Database 11g 引入的新功能，通過維護所謂的“SQL計劃基準(SQL plan baseline(11g))”來使系統能夠自動控制SQL 計劃演變。啟用此功能後，只要證明新產生的SQL 計劃與SQL 計劃基準相整合不會導致效能迴歸，就可以進行此項整合。因此，在執行某個SQL 陳述式時，只能使用對應的SQL 計劃基準中包括的計劃。可以使用SQL最佳化集自動載入或植入SQL 計劃基準。SQL 計劃管理功能的主要優點是系統效能穩定，不會出現計劃迴歸。此外，該功能還可以節省DBA 的許多時間，這些時間通常花費在確定和分析SQL 效能迴歸以及尋找可用的解決方案上.（1）  即時捕獲：使用自動計劃捕獲，方法是：將初始化參數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 設定為TRUE。預設情況下，該參數設定為FALSE。將該參數設定為TRUE將開啟自動標識可重複SQL 陳述式，以及自動為此類語句建立計劃記錄的功能。（2）  成批載入：使用DBMS_SPM 程式包；該程式包支援手動管理SQL計劃基準。使用此程式包，可以將SQL 計劃從遊標快取或現有的SQL最佳化集(STS) 直接載入到SQL計劃基準中。對於要從STS 載入到SQL 計劃基準的SQL語句，需要將其SQL計劃儲存在STS中。使用DBMS_SPM可以將比較基準計劃的狀態從已接受更改為未接受（以及從未接受更改為已接受），還可以從登台表匯出比較基準計劃，然後使用匯出的比較基準計劃將SQL計劃基準載入到其它資料庫中。NOTE:SQL計劃管理使用一種叫做SQL計劃基準機制。計劃基準是針對sql最佳化器允許使用並接受的執行計畫的一個集合。在典型使用方式下，資料庫只接受那些通過驗證並執行良好的執行計畫到計劃基準中。-----設定參數：啟用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.準備測試環境create table t2(sid number not null ,sname varchar2(10))tablespace test;--迴圈匯入資料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(' 成功錄入資料！ ');    commit;    end; /exec dbms_stats.gather_table_stats('SCOTT','T2',cascade => true);************************************************************第二部分：為sql 調優集中的sql語句建立計劃基準************************************************************建立基準的幾種方式1.自動捕獲基準2.從SQL調優集合中載入，通過使用包dbms_spm.load_plans_from_sqlset3.從庫緩衝中載入，通過包dbms_spm.load_plans_from_cursor_cache函數為一條已經在遊標緩衝中的語句建立基準----------------------------------*方式1.自動捕獲基準----------------------------------*--------案例示範步驟1：簡單查詢set autotrace on;var v varchar2(5);  exec :v :=1000;  select   * from t2 where sid<=:v;set autotrace off;執行計畫----------------------------------------------------------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))步驟2：簡單查詢set autotrace on;var v varchar2(5);  exec :v :=1000;  select   * from t2 where sid<=:v;set autotrace off;步驟3：查看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_60fea6835db2e913       SQL_PLAN_61zp6hdfv5u8mb860bcf2 YES YES步驟4：建立索引create index index_01 on t2(sid);exec dbms_stats.gather_table_stats('SCOTT','T2',cascade => true);步驟5：簡單查詢set autotrace on;var v varchar2(5);  exec :v :=1000;  select   * from t2 where sid<=:v;set autotrace off;執行計畫----------------------------------------------------------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 baseline "SQL_PLAN_61zp6hdfv5u8mb860bcf2" used for this statement步驟6：查看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_60fea6835db2e913       SQL_PLAN_61zp6hdfv5u8m8d82fa42 YES NO   --未啟用SQL_60fea6835db2e913       SQL_PLAN_61zp6hdfv5u8mb860bcf2 YES YES----------------------------------*方式2.從SQL調優集合中載入----------------------------------*通過使用包dbms_spm.load_plans_from_sqlset步驟1.建立STSBEGIN  DBMS_SQLTUNE.DROP_SQLSET(    sqlset_name => 'OCPYANG_STS'    );END;/---建立STSBEGIN  DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'OCPYANG_STS',     sqlset_owner => 'SYS',    description  => 'ocpyangtest');END;/步驟2.填充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;DBMS_SQLTUNE.LOAD_SQLSET('OCPYANG_STS', baseline_ref_cur);end;/輸入 begin_snap 的值:  11647egin Snapshot Id specified: 11647輸入 end_snap 的值:  11859nd   Snapshot Id specified: 11859--或指明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(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;DBMS_SQLTUNE.LOAD_SQLSET('OCPYANG_STS', baseline_ref_cur);end;/步驟3：從SQLSET中載入即將DBMS_SPM作為輸入為sql調優集中包含的每一個查詢建立計劃基準DECLAREmy_plans pls_integer;BEGINmy_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'OCPYANG_STS');END;//********文法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   := 1000)RETURN PLS_INTEGER;**************/步驟4：查看相關計劃基準select sql_handle,plan_name,sql_text from dba_sql_baselines;----------------------------------*方式3.從庫緩衝中載入----------------------------------*通過包dbms_spm.load_plans_from_cursor_cache函數為一條已經在遊標緩衝中的語句建立基準.----方法1：匯入一個指定的sqlid--查看sql_id和hash_value值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=>'2002323537');DBMS_OUTPUT.put_line('匯入完成!' );end;/ declareret  varchar2(100);beginret := dbms_spm.load_plans_from_cursor_cache(sql_id=>'fwjgwwp18z7ad',--plan_hash_value=>'1601196873'plan_hash_value=>NULL);end;/如果執行計畫的雜湊值沒有指定或指定為NULL,則給定SQL語句的所有可用執行計畫都會被載入.---方法2：同時匯入多條declareu int;beginu:=dbms_spm.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');DBMS_OUTPUT.put_line('匯入完成!' );end;/ ----方法3：為某個使用者的遊標建立基準declareret  varchar2(100);beginret := dbms_spm.load_plans_from_cursor_cache(attribute_name=>'parsing_schema_name',attribute_value=>'SCOTT');end;/----方法4：為library cache中每一條文本中包含字串t1的SQL語句建立一個SQL計劃基準：declareret  varchar2(100);beginret := dbms_spm.load_plans_from_cursor_cache(attribute_name=>'sql_text',attribute_value=>'%t1%');end;//*****文法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;DBMS_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 VARCHAR2 := 'NO',   enabled          IN VARCHAR2 := 'YES')  RETURN PLS_INTEGER;******/---查看是否存在執行計畫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_e0c42f010eb9d50f       SQL_PLAN_f1j1g047bmp8gb73cade2 YES YESSQL_a9e4491f6b5d9737       SQL_PLAN_amt293xppv5tr14816fa9 YES YESSQL_93ffdec9273ee793       SQL_PLAN_97zyyt4mmxtwm95fcfc25 YES YES----查看某個查詢是否使用了sql plan baselineselect sql_id,child_number,sql_plan_baseline,sql_textfrom v$sqlwhere sql_plan_baseline is not nulland sql_text like '%select count(*) from scott.tblorders%';-------案例示範: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=>'3360167359');DBMS_OUTPUT.put_line('匯入完成!' );end;/ ---查看是否存在執行計畫SELECT sql_handle, plan_name,enabled, accepted FROM   dba_sql_plan_baselinesWHERE  sql_text LIKE '%select count(1) from scott.tblorders where orderstatus>0%';************************************************************第三部分：sql plan baseline修改************************************************************----1.修改新計劃的ACCEPTED為YES/*********文法使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE這個API來控制執行計畫的演化。文法：DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (  sql_handle IN VARCHAR2 := NULL, --> NULL 表示針對所有SQL  plan_name  IN VARCHAR2 := NULL,  time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,  verify     IN VARCHAR2 := 'YES',  commit     IN VARCHAR2 := 'YES' )RETURN CLOB;這裡由兩個標記控制：o Verify   + YES (只有效能更好的計劃才會被演化)  + NO (演化所有的計劃)o Commit  + YES (直接演化)  + NO (只產生報告)這裡可以通過不同的排列組合，達到不同的效果：o 自動接收所有效能更好的執行計畫 (Verify->YES, Commit->YES)o 自動接收所有新的執行計畫 (Verify->NO, Commit->YES)o 比較效能，產生報告，人工確認是否演化 (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       => 'SQL_PLAN_61zp6hdfv5u8m8d82fa42',verify           =>'NO',commit=>'YES');DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);END;/----2.:修改已有的Baseline/*********文法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 := DBMS_SPM.alter_sql_plan_baseline(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.刪除已有的BaselineSET SERVEROUTPUT ONDECLARE l_plans_dropped  PLS_INTEGER;BEGIN l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (   sql_handle => 'SQL_3a8461388a9bfa52',   plan_name  => NULL);     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;執行計畫----------------------------------------------------------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 |----------------------------------------------------------------------------------------************************************************************第四部分：sql plan baseline遷移************************************************************遷移步驟:1.使用dbms_spm包和create_stgtab_baseline建立一個過程表2.使用dbms_spm.pack_stgtab_baseline將計劃基準填充到第一步中建立的過程表3.使用資料庫連結或資料泵複製到目標資料庫中4.使用DBMS_SPM.unpack_stgtab_baseline匯入計劃基準到目標資料庫中---1.建立舞台表BEGIN  dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_STG01',table_owner => 'SCOTT', --不能建立在SYS賬戶下tablespace_name=>'USERS'); END;//****** dbms_spm.create_stgtab_baseline文法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,   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 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 PumpTransfer 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.將sql plan baseline從資料字典複製到舞台表declarek int;begink:=dbms_spm.pack_stgtab_baseline(TABLE_NAME=>'BASELINE_STG01',TABLE_OWNER=>'SCOTT');end;/ declarev_ret  number(100);beginv_ret := dbms_spm.pack_stgtab_baseline(table_name =>'mystgtab',table_owner=>user,sql_handle=>'SQL_e436abaac44f99d8',--plan_name=>'SQL_PLAN_f8dpbpb24z6fs94ecae5c',);end;//**********文法: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.用expdp\impdp或exp,imp工具從測試庫將表移到目標庫----4.將sql plan baseline從舞台表複製到資料字典---4.1  將所有sql plan baseline從舞台表複製到資料字典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 將sql plan baseline中有關T1表的從舞台表複製到資料字典declarev_ret  varchar2(100);beginv_ret := dbms_spm.unpack_stgtab_baseline(table_name =>'BASELINE_STG01',table_owner=>'SCOTT',sql_text=>'%FROM t1%');end;//*********文法:DBMS_SPM.UNPACK_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;如果只指定table_name與table_owner,就是處理所有sql plan baseline。sql_handle與plan_name一起能精確識別一個sql plan baseline,plan_name為可選項。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 YES---刪除sql計劃基準declarev_ret  varchar2(100);beginv_ret := dbms_spm.unpack_stgtab_baseline(sql_handle=>'mystgtab',plan_name=>'swew223');end;/這兩個參數至少要指定一個。