Oracle12C SPM的使用方法

2020-09-13 00:00:00 语句 执行 扫描 计划 基线

SPM 


先将自动捕获计划关闭

alter system set optimizer_capture_sql_plan_baselines=false;


select description from spm_test where id=1000;

分析其执行计划,并查询该语句的SQL_ID通过V$sql视图。

手动加载将该计划加入计划基线

SQL> declare

    l_plans_loaded pls_integer;

    begin

     l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(

    sql_id=>'a2dxh1122gkag');

    dbms_output.put_line('plans loaded: '||l_plans_loaded);

    end;

/


验证手工加载结果,默认手动加载是accepted

select sql_handle,plan_name,sql_text ,enabled,accepted

from dba_sql_plan_baselines

where sql_text like '%spm_test%'

and sql_text not like '%dba_sql_plan_baselines%'

/


此该计划选择走全表扫描


下面创建索引,收集统计信息,然后再次执行该语句看执行计划如何走

create index spm_test_idx on spm_test(id);

exec dbms_stats.gather_table_stats(user,’SPM_TEST’,cascade=>true);


set autotr on;

select description from spm_test where id=1000;

发现此时依然走全表扫描


下面查询该sql_handle的计划基线,发现有两个执行计划

下面我们手工evolve

SQL> select sql_handle,plan_name ,enabled,accepted from dba_sql_plan_baselines

  2* where sql_handle='SQL_1ec40ae2b72304a6'


SQL_HANDLE       PLAN_NAME                                           ENA ACC

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

SQL_1ec40ae2b72304a6       SQL_PLAN_1xj0awavk615691cb0adf   YES YES

SQL_1ec40ae2b72304a6       SQL_PLAN_1xj0awavk6156f2fc655a   YES NO


发现优的执行计划没有被accepted,下面手工解决,调用dbms_spm.evolve_sql_plan_baseline实现执行计划的演进(EVOLVE)

SQL> declare 

    cl clob;

    begin

    cl := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_68be3b6446a96c24');

    end;

    /


再次查询

SQL> select sql_handle,plan_name ,enabled,accepted from dba_sql_plan_baselines

  2* where sql_handle='SQL_1ec40ae2b72304a6'


SQL_HANDLE                         PLAN_NAME                               ENA ACC

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

SQL_1ec40ae2b72304a6       SQL_PLAN_1xj0awavk615691cb0adf   YES YES

SQL_1ec40ae2b72304a6       SQL_PLAN_1xj0awavk6156f2fc655a   YES YES


再次查询,看执行计划


SQL>select description from spm_test

  2* where id=1000


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

| Id  | Operation     | Name   | Rows  | Bytes | Cost (%CPU)| Time   |

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

|   0 | SELECT STATEMENT     |   | 1 | 22 | 2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST   | 1 | 22 | 2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN     | SPM_TEST_IDX | 1 |   | 1   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):

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


   2 - access("ID"=1000)


Note

-----

   - SQL plan baseline "SQL_PLAN_1xj0awavk615691cb0adf" used for this statement


此时,优化器选择了在accepted的执行计划中更好的计划




Fix 计划

将全表扫描的计划的基线属性设置为fixed


declare

l_p pls_integer;

begin

l_p :=dbms_spm.alter_sql_plan_baseline(

sql_handle =>'SQL_1ec40ae2b72304a6',

plan_name =>'SQL_PLAN_1xj0awavk6156f2fc655a',

attribute_name =>'fixed',

attribute_value =>’NO’);

end;

/

验证修改结果


SQL> run

  1  select sql_handle,plan_name ,enabled,accepted,fixed from dba_sql_plan_baselines

  2* where sql_handle='SQL_1ec40ae2b72304a6'


SQL_HANDLE       PLAN_NAME ENA ACC FIX

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

SQL_1ec40ae2b72304a6       SQL_PLAN_1xj0awavk615691cb0adf YES YES NO

SQL_1ec40ae2b72304a6       SQL_PLAN_1xj0awavk6156f2fc655a YES YES YES


执行SQL语句,此时走全表扫描

SQL> set autot on;

SQL> select description from spm_test where id=1000;

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

| Id  | Operation   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |     |   1 |   22 |   13   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| SPM_TEST |   1 |   22 |   13   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=1000)

Note

-----

   - SQL plan baseline "SQL_PLAN_1xj0awavk6156f2fc655a" used for this statement



查询计划基线中某个plan_name的执行计划信息,可以清楚看到对应的SQL语句,计划基线属性,执行计划等。

SQL> set long 10000;

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_1xj0awavk615691cb0adf'));


PLAN_TABLE_OUTPUT

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


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

SQL handle: SQL_1ec40ae2b72304a6

SQL text: select description from spm_test where id=1000

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


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

Plan name: SQL_PLAN_1xj0awavk615691cb0adf   Plan id: 2446002911

Enabled: YES Fixed: NO Accepted: YES   Origin: AUTO-CAPTURE

Plan rows: From dictionary

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


PLAN_TABLE_OUTPUT

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


Plan hash value: 1155944573


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

| Id  | Operation                                     | Name                 | Rows  | Bytes | Cost (%CPU) | Time   |

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

|   0 | SELECT STATEMENT                             |                 | 1  | 22     | 2   (0)       | 00:00:01 |

|   1  |  TABLE ACCESS BY INDEX ROWID BATCHED   | SPM_TEST         | 1  | 22     | 2   (0)       | 00:00:01 |

|*  2 |   INDEX RANGE SCAN                             | SPM_TEST_IDX  | 1  |         | 1   (0)        | 00:00:01 |

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



PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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


   2 - access("ID"=1000)


26 rows selected.


设置SQL Management Base


SMP的数据包括SQL Plan baselines,sql 语句,plan history以及SQL profile,这些信息存储sysaux表空间,它能分配的存储空间由

两个属性控制。通过dbms_spm.configure存储过程来修改。


SQL> SELECT parameter_name, parameter_value FROM   dba_sql_management_config


PARAMETER_NAME PARAMETER_VALUE

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

SPACE_BUDGET_PERCENT 10

PLAN_RETENTION_WEEKS 53


begin

dbms_spm.configure('space_budget_percent',11);

dbms_spm.configure('plan_retention_weeks',54);

end;

/


再次验证

SQL> SELECT parameter_name, parameter_value FROM   dba_sql_management_config

  2  ;


PARAMETER_NAME PARAMETER_VALUE

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

SPACE_BUDGET_PERCENT 11

PLAN_RETENTION_WEEKS 54



在不同库之间迁移SQL plan baseline

 1 创建staging

begin

DBMS_SPM.CREATE_STGTAB_BASELINE(

table_name      =>'spm_stageing_tab', 

table_owner     => 'DAVE',

tablespace_name => 'USERS');

end;

/


2 SQL plan baselines 导入staging table 

使用pack_stgtab_baseline函数

declare

l_plans_packed  PLS_INTEGER;

BEGIN

l_plans_packed := DBMS_SPM.pack_stgtab_baseline(

table_name      =>'spm_stageing_tab',

table_owner     => 'DAVE');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);

end;

/


3 staging表传输到目标库

使用expdp/impdp 实现,此时略去n个字,哈哈!!!


4 staging表导入目标库


UNPACK_STGTAB_BASELINE函数实现这个功能

declare

l_plans_unpacked  PLS_INTEGER;

begin

l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(

table_name      =>'spm_stageing_tab',

table_owner     => 'DAVE',

creator         => 'DAVE');

DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);

end;

/



删除plansbaselines 


declare

l_plans_dropped pls_integer;

begin

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

sql_handle =>'SQL_49e47af7a3b2aa5b',

plan_name=>null);

DBMS_OUTPUT.put_line(l_plans_dropped);

end;

/

相关文章