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;
/
删除plans和baselines
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;
/
