[20120806]11G SPM的學習4.txt

[20120806]11G SPM的學習4.txt

繼續上面的學習:

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create table t as select rownum id , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level <=1e5;

--分析表

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

      OwnName        => user

     ,TabName        => 'T'

    ,Estimate_Percent  => NULL

    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1 '

    ,Degree            => 4

    ,Cascade           => TRUE

    ,No_Invalidate     => TRUE);

END;

/

--建立sql plan baseline,忽略...

create index i_t_id on t(id);

select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;

--為了後面能操作比較快，我定義如下變數：
variable v_sql_handle      varchar2(30);
variable v_plan_name_full  varchar2(30);
variable v_plan_name_index varchar2(30);
exec :v_sql_handle      := 'SYS_SQL_a45a9e109f85e5a4'
exec :v_plan_name_full  := 'SQL_PLAN_a8qny22gsbtd494ecae5c'
exec :v_plan_name_index := 'SQL_PLAN_a8qny22gsbtd40893a4b2'

  為了遷移時保持執行計劃的穩定，可以把原來儲存的基線遷移到新系統上來。
一下僅僅是為了測試：

1.儲存基線到表檔案中。

SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd40893a4b2 YES NO  NO  YES AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO  YES MANUAL-LOAD       11842951964357158308

SQL> exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO  YES AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO  YES MANUAL-LOAD       11842951964357158308

SQL> exec dbms_spm.create_stgtab_baseline('t_base',user);

PL/SQL procedure successfully completed.

--這樣就建立了sql baselines的表檔案。

SQL> select * from t_base ;

no rows selected

2.匯出sql baselines:

variable v_basenum number ;

exec :v_basenum := dbms_spm.pack_stgtab_baseline('t_base',user,:v_sql_handle);

SQL> select count(*) from t_base;

  COUNT(*)

----------

         2

--可以發現已經匯入了2條記錄。

3.匯入sql baselines：
由於我的測試環境僅僅一臺機器，必須刪除前面建立的sql baselines;
SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO  YES AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO  YES MANUAL-LOAD       11842951964357158308

exec :v_basenum := DBMS_SPM.drop_sql_plan_baseline (sql_handle => :v_sql_handle,plan_name  => :v_plan_name_index);

exec :v_basenum := DBMS_SPM.drop_sql_plan_baseline (sql_handle => :v_sql_handle,plan_name  => :v_plan_name_full);

SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;

no rows selected

--匯入sql baselines：

exec :v_basenum := dbms_spm.unpack_stgtab_baseline('t_base',user,:v_sql_handle);

SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT ORIGIN                       SIGNATURE

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

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO  YES AUTO-CAPTURE      11842951964357158308

SYS_SQL_a45a9e109f85e5a4       SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO  YES MANUAL-LOAD       11842951964357158308

--可以透過如下方式成功遷移，保證執行計劃的穩定。