> Oracle SPM(Sql Plan Management) --by Firsouler 2021/03/25 #### 概述 Oracle11g之后推出的，SPM是一种主动稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。 相关参数 ``` sql -- SPM默认开启,自动捕获默认关闭 SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ -------------------------------- ----------- optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE --查看基线视图 set lines 200 select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like '%'; --根据sql_handle查看执行计划 select * from table(dbms_xplan.display_sql_plan_baseline('SQL_4e255b34b4cf2fd9')); ``` 如果一个SQL有多个执行计划，该SQL会有多个SPB，可以从DBA_SQL_PLAN_BASELINES中查看所有的SPB。其所对应的执行计划是否被启用，只有ENABLED和ACCEPTED的值均为"YES"才行。如果有多个，选择成本较少的。 Oracle有两种方式可以产生SQL Plan Baseline - 自动捕获 - 手工生成/批量导入 #### 自动捕获 参数 optimizer_capture_sql_plan_baselines用于控制是否开启自动捕获SQL Plan Baselines。 自动捕获，相同sql且执行计划相同执行两次才会放入SPB中，且ENABLED/ACCEPTED均为"YES",如果再次执行该sql，执行计划变了，新执行计划会增加到dba_sql_plan_baselines中，但ACCEPTED的值是"NO"，因此该执行计划不会被启用。 修改SPB执行计划属性，来帮助sql指定执行计划 ``` sql --查看是否加入spb select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%'; --修改，先将no置为yes exec :temp :=dbms_spm.evlove_sql_plan_baseline(sql_handle=>'SQL_SQL_ajlx809xxxx',plan_name=>'',verify=>'NO',commit=>'YES'); --将原有YES改为no exec :temp :=dbms_spm.evlove_sql_plan_baseline(sql_handle=>'SQL_SQL_ajlx809xxxx',plan_name=>'',attribute_name='ENABLED',attribute_value=>'NO'); ``` #### 手动生成SPB > 具体参考最后例子 ``` sql --手动生成初始执行计划所对应的SPB exec :temp := dbms_spm.load_plans_from_cursor_cache ( sql_id=>'原sql', plan_hash_value=>'' ); --通过hint，多次执行，找出理想执行计划，加入到spb exec :temp :=dbms_spm.load_plans_from_cursor_cache ( sql_id=>'新sql_id', plan_hash_value=>'新hash', sql_handle=>'原sql锁产生的spb中的sql_handle' ); --删除初始的原SQL的spb exec :temp :=dbms_spm.drop_sql_plan_baseline ( sql_handle=>'', plan_name=>'' ); ``` #### 批量导入 **Oralce提供六种方式把计划导入到sql plan baseline中:** - 1.从 SQL Tuning Set STS 导入：DBMS_SPM.LOAD_PLANS_FROM_SQLSET - 2.从Cursor Cache中装载：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE - 3.从Stored Outlines中导入： DBMS_SPM.MIGRATE_STORED_OUTLINE - 4.从内存中存在的计划中导入：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE - 5.从staging table表中导入：dbms_spm.create_stgtab_baseline - 6.迁移，从其他数据库迁入，具体参考<迁移基线> eg: ``` sql --从SQL Tuning Set中装载: DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1'); END; / --从Cursor Cache中装载 DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7qqnad1j615m7'); END; / ``` #### 迁移基线 ``` sql --1.创建保存表 exec :temp := dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'MYTEST',tablespace_name =>''); --2、将基线加载到表 "BASELINE_TABLE" exec :temp := dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TABLE', table_owner => 'MYTEST'); --3、导出导入 expdp/impdp --4、新库插入基线 exec :temp := dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'MYTEST'); ``` #### 修改原始基线 例子 ``` sql --1.创建环境 SQL> create table c2 as select * from dba_objects; Table created. SQL> create index idx_c2 on c2(object_id); Index created. --收集统计信息 exec dbms_stats.gather_table_stats(ownname=>'MYTEST',tabname=>'C2', estimate_percent=>100,cascade=>TRUE); --2.运行sql 走全表扫描 select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where object_id=4; --查看基线 因为没开启自动捕获，所以没有加入到spb set lines 200 select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(%'; --3.手动加入 set serverouput on var temp number exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'02dp0ka5ysk0h', plan_hash_value=>3416629809); --查看 SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT ------------------------------ ------------------------------ -------------- --- --- -------------------------------------------------------------------------------- SQL_4e255b34b4cf2fd9 SQL_PLAN_4w9av6kucybyt2c7305c9 MANUAL-LOAD YES YES select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where obj --4.走索引方式，查看执行计划 alter session set statistics_level=all; select /*+ index(c2 idx_c2) */ object_name,object_id from mytest.c2 where object_id=4; select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline')); --5.更改 var temp number exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'65khvmhg2wb6n',plan_hash_value=>1328050837,sql_handle=>'SQL_4e255b34b4cf2fd9'); --再次查询 SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT ------------------------------ ------------------------------ -------------- --- --- -------------------------------------------------------------------------------- SQL_4e255b34b4cf2fd9 SQL_PLAN_4w9av6kucybyt2c7305c9 MANUAL-LOAD YES YES select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where obj SQL_4e255b34b4cf2fd9 SQL_PLAN_4w9av6kucybytbdbf52e0 MANUAL-LOAD YES YES select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where obj --6.删除旧的 var temp number exec :temp :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_4e255b34b4cf2fd9',plan_name=>'SQL_PLAN_4w9av6kucybyt2c7305c9'); --7.再次执行步骤2，虽然加入了hint，但会选择走索引，如下所示 SQL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_4e255b34b4cf2fd9')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- SQL handle: SQL_4e255b34b4cf2fd9 SQL text: select /*+ no_index(c2 idx_c2) */ object_name,object_id from mytest.c2 where object_id=:"SYS_B_0" -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_4w9av6kucybytbdbf52e0 Plan id: 3183432416 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 1328050837 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| C2 | 1 | 30 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_C2 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=TO_NUMBER(:SYS_B_0)) 26 rows selected. ``` > 注意 PLAN_HASH_VALUE/HASH_VALUE区别，前者为执行计划HASH值，后者是SQL游标HASH值 #### 参考 - https://blog.csdn.net/oradh/article/details/30456915 - http://blog.itpub.net/16648/viewspace-967230/ - 《基于Oracle的SQL优化》