[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 

--分析表

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

--可以通过如下方式成功迁移，保证执行计划的稳定。