資料庫遷移，spm baseline 保持執行計劃的穩定性

目前負責的資料庫壓力過大，要把其中一個比較核心的SCHEMA遷移到另一套環境裡來降低壓力。遷移前後的版本是一樣的，都是11.1.0.7.統計資訊也是透過DBMS_STATS包匯出匯入的，按理說，執行計劃變化的可能性不大。但是為了確保執行計劃不出錯，打算還是折騰一把，透過11G的 SPM BASELINE在老庫生成一個調優集，然後把這個調優集載入到新的資料庫上的，然後透過SPM BASELINE提供的過程來為這個調優集生成BASEINE。這樣就能保證這些調優集裡的SQL 執行計劃不發生變化，而且還可以透過SPM提供的檢視來檢視那些SQL在新的環境下自動的產生了一些可以改進的執行計劃，如果這種改變是好的，我們可以透過BASELINE重演來接受這種改變。最後可以把沒有產生執行計劃改變的BAELINE刪除掉。具體方案如下：

1）新建立一個SQL 調優集

exec dbms_sqltune.create_sqlset(sqlset_name=>'vodka_set');

2）為新建立的SQL 調優集載入SQL，這裡可以選擇載入那些SQL，本例里載入了VODKA 使用者產生的，簽名不是0的，BUFFER_GETS(總）大於1000的，執行次數（總）大於100的，命令型別不為INSERT的SQL

DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('PARSING_SCHEMA_NAME=''VODKA'' and force_matching_signature<>0 and BUFFER_GETS>1000 and EXECUTIONS>100 and command_type<>2',
                                                  NULL,
                                                  NULL,
                                                  NULL,
                                                  NULL,
                                                  1,
                                                  NULL,
                                                  'ALL')) P;

  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name     => 'vodka_set',
                           populate_cursor => cur);

END;
/

3）把調優集的內容載入到一張舞臺表裡
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('WXH_TBD0931');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET('vodka_set','SYSTEM','WXH_TBD0931','SYSTEM');

4）透過匯入匯出工具來把舞臺表傳輸到新的資料庫上

exp system/0o98udrft00oop file=/tmp/weixh.dmp tables=WXH_TBD0931
scp /tmp/weixh.dmp
imp system/0o98udrft00oop file=/tmp/weixh.dmp fromuser=system touser=SYSTEM

5）在新的資料庫上把SQL調優集載入進來
begin
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name          => 'vodka_set',
                                    sqlset_owner         => 'SYSTEM',
                                    replace              => true,
                                    staging_table_name   => 'WXH_TBD0931',
                                    staging_schema_owner => 'SYSTEM');
END;
/

6）透過SPM BASELINE的包來把SQL調優集裡的SQL都批次的生成BASELINE
declare
ret number;
begin
ret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'vodka_set',sqlset_owner => 'SYSTEM');
end;
/

這樣做了後，就能確保資料庫遷移後，兩邊的執行計劃是一致的，不會出現效能問題。如果在新庫上，ORACLE認為有更好的執行計劃，會在dba_sql_plan_baselines裡產生出一個origin為AUTO-CAPTURE，ACCEPTED為NO的baseline。你可以透過如下兩種方法來驗證到底是新產生出來的執行計劃好，還是舊的好。我的例子裡SQL HANDLE為'SYS_SQL_b13d37f143c8d367'的產生一個新的不可接受的執行計劃。

select sql_handle,plan_name,origin,accepted from dba_sql_plan_baselines where sql_handle='SYS_SQL_b13d37f143c8d367';

SQL_HANDLE                     PLAN_NAME                      ORIGIN                       ACCEPT
------------------------------ ------------------------------ ---------------------------- ------
SYS_SQL_b13d37f143c8d367       SYS_SQL_PLAN_43c8d3671a342c8e  AUTO-CAPTURE                 NO
SYS_SQL_b13d37f143c8d367       SYS_SQL_PLAN_43c8d367affaa175  MANUAL-LOAD                  YES

1）檢視兩種執行計劃的差異來判斷

SELECT *
  FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SYS_SQL_b13d37f143c8d367',
                                                  plan_name  => 'SYS_SQL_PLAN_43c8d3671a342c8e'));
SQL handle: SYS_SQL_b13d37f143c8d367
SQL text: UPDATE APP_USER_LOGIN SET IS_ONLINE = 'y', LAST_REGISTER = :B6 , IP =
          :B5 , ROLE_NAME = :B4 , ORG_ID = :B3 , FALSE_COUNT = 0, CHANNEL = :B2
          WHERE LOGIN_ID = :B1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_43c8d3671a342c8e
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2701429484

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                   |     1 |    72 |     2   (0)| 00:00:01 |
|   1 |  UPDATE            | APP_USER_LOGIN    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| APP_USER_LOGIN_PK |     1 |    72 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LOGIN_ID"=:B1)

 

SELECT *
  FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SYS_SQL_b13d37f143c8d367',
                                                  plan_name  => 'SYS_SQL_PLAN_43c8d367affaa175'));

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_43c8d367affaa175
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2066069757

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                |     1 |    63 |    15   (0)| 00:00:01 |
|   1 |  UPDATE            | APP_USER_LOGIN |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| APP_USER_LOGIN |     1 |    63 |    15   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LOGIN_ID"=:B1)

可以看到自動捕獲出來的不可接受的BASELINE的執行計劃比較優秀，走了索引，因此我們可以透過BASELINE重演接受它，後面會介紹怎麼用BASELINE重演

 

2）重演BASELINE來檢視兩種執行計劃到底哪種更好

SELECT dbms_spm.evolve_sql_plan_baseline(
sql_handle => 'SYS_SQL_b13d37f143c8d367',
plan_name => NULL,
time_limit => 10,
verify => 'yes',
commit => 'no'
)
FROM dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_B13D37F143C8D367',PLAN_NA
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_b13d37f143c8d367
  PLAN_NAME  =
  TIME_LIMIT = 10
  VERIFY     = yes
  COMMIT     = no

Plan: SYS_SQL_PLAN_43c8d3671a342c8e
-----------------------------------
  Plan was verified: Time used .13 seconds.
  Passed performance criterion: Compound improvement ratio >= 115.54

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):              74             22              3.36
  CPU Time(ms):                  54              6                 9
  Buffer Gets:                  295              2             147.5
  Disk Reads:                   292              2               146
  Direct Writes:                  0              0
  Fetches:                       19              2               9.5
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.

可以看到如果接受這種改變的話，效能能提升很多。

接受重演

SELECT dbms_spm.evolve_sql_plan_baseline(
sql_handle => 'SYS_SQL_b13d37f143c8d367',
plan_name => NULL,
time_limit => 10,
verify => 'yes',
commit => 'yes'
)
FROM dual;

至此，這個SQL就接受了這個ORACLE自動產生的BASELINE.

最後，如果覺得必要，可以把執行計劃沒有發生變化的BASELINE都刪除掉。