spm执行计划的绑定

来源：这里教程网时间：2026-03-03 16:16:58 作者：

spm执行计划的绑定主要步骤： 1)查看sql的sql_id,plan_hash_value值 2)将原始sql语句加入基线 3)查看基线（如果有多个需要删除只剩一个） 4）验证sql执行计划两种方式：直接使用dbms_spm.load_plans_from_cursor_cache和coe_xfr_sql_profile.sql 实验一、直接使用dbms_spm.load_plans_from_cursor_cache绑定 1.创建用户 SQL> create user xmc identified by xmc; User created. SQL> grant dba to xmc; Grant succeeded. SQL> conn xmc/xmc Connected. 2.创建表和索引并收集统计信息 SQL> create table test2 as select * from dba_objects; Table created. SQL> create index idx_test2 on test2(object_id)online; 这里注意online加了可以在线dml，搞大表索引的时候尤其要注意 Index created. SQL> begin 2 dbms_stats.gather_table_stats(ownname=>'XMC',tabname=>'TEST2',cascade=>true,no_invalidate=>false); 3 end; 4 / PL/SQL procedure successfully completed. 3.执行原始的sql SQL> set autot trace SQL> select * from test2 where object_id=20; Execution Plan ---------------------------------------------------------- Plan hash value: 4047680367 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TEST2 | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- 4.新开一个窗口，查原始sql的sql_id和plan_hash_value SQL> set linesize 2000 SQL> col sql_id for a20 SQL> col sql_test for a40 SQL> col sql_text for a60 SQL> select sql_id,plan_hash_value,sql_text from v$sqlarea where sql_text like 'select * from test2 where object_id=20%'; SQL_ID PLAN_HASH_VALUE SQL_TEXT -------------------- --------------- ------------------------------------------------------------ 4tm6j886yvzj3 4047680367 select * from test2 where object_id=20 5.将原始sql语句加入基线，查看原始sql的基线（如果没有加入基线，可能查出来的时候no rows） SQL> var temp number; SQL> begin 2 :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'4tm6j886yvzj3',plan_hash_value=>4047680367); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%'; SQL_HANDLE SQL_TEXT PLAN_NAME ------------------------------ -------------------------------------------------------------------------------- ------------------------------ SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k60b1ef84 6.对之前的sql加hint，执行新的sql SQL> select /*+full(test2)*/* from test2 where object_id=20; 7.获得新sql语句的sql_id和plan_hash_value SQL> select sql_id,plan_hash_value,sql_text from v$sqlarea where sql_text like 'select /*+full(test2)*/%'; SQL_ID PLAN_HASH_VALUE SQL_TEXT -------------------- --------------- ------------------------------------------------------------ bqyuwxskwqdun 300966803 select /*+full(test2)*/* from test2 where object_id=20 8.将新的sql_id和plan_hash_value加入到原始sql的基线中 SQL> var temp number; SQL> begin 2 :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'bqyuwxskwqdun',plan_hash_value=>300966803,sql_handle=>'SQL_20df29fdb3e8ac52'); 3 end; 4 / PL/SQL procedure successfully completed. 9.查看原始sql的基线 SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%'; SQL_HANDLE SQL_TEXT PLAN_NAME ------------------------------ ------------------------------------------------------------ ------------------------------ SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k60b1ef84 SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k99963deb 10.此时执行原始sql，他是走的之前的那个SQL_PLAN_21rt9zqtyjb2k60b1ef84 SQL> select * from test2 where object_id=20; Execution Plan ---------------------------------------------------------- Plan hash value: 4047680367 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TEST2 | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=20) Note ----- - SQL plan baseline "SQL_PLAN_21rt9zqtyjb2k60b1ef84" used for this statement 11.删除SQL_PLAN_21rt9zqtyjb2k60b1ef84，留下后面hint的那个 SQL> var temp number; SQL> begin 2 :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_20df29fdb3e8ac52',plan_name=>'SQL_PLAN_21rt9zqtyjb2k60b1ef84'); 3 end; 4 / PL/SQL procedure successfully completed. 12.查看原始sql的基线 SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%'; SQL_HANDLE SQL_TEXT PLAN_NAME ------------------------------ -------------------------------------------------------------------------------- ------------------------------ SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k99963deb 如上只有一个了，再次运行原始sql，查看执行计划 SQL> select * from test2 where object_id=20; Execution Plan ---------------------------------------------------------- Plan hash value: 300966803 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 344 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST2 | 1 | 98 | 344 (1)| 00:00:05 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=20) Note ----- - SQL plan baseline "SQL_PLAN_21rt9zqtyjb2k99963deb" used for this statement 此时走的执行计划是SQL_PLAN_21rt9zqtyjb2k99963deb，即hint的那个实验二：使用oracle官方提供的coe_xfr_sql_profile.sql进行绑定实验一已将执行计划绑定为不走索引，这里将执行计划绑定回走索引的 1.使用之前的走索引的sql_id和hash值直接进行绑定，运行脚本（sysdba权限） SQL> @coe_xfr_sql_profile.sql sql_id:4tm6j886yvzj3 hash_plan_value:4047680367 Execute coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql on TARGET system in order to create a custom SQL Profile with plan 4047680367 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. SQL> @coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql SIGNATURE --------------------- 2368658098642005074 SIGNATUREF --------------------- 10962808917454791067 ... manual custom SQL Profile has been created 2.查看基线 select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%'; SQL_HANDLE SQL_TEXT PLAN_NAME ------------------------------ ------------------------------------------------------------ ------------------------------ SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k60b1ef84 SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k99963deb 3.此时执行原始sql，并查看执行计划走的是哪一个 SQL> explain plan for select * from test2 where object_id=20; Explained. SQL> select * from table(dbms_xplan.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 4047680367 -------------------------------------------------------------------------------- --------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TEST2 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=20) Note ----- - SQL profile "coe_4tm6j886yvzj3_4047680367" used for this statement 这里看出是走的"coe_4tm6j886yvzj3_4047680367" 定义的执行计划。 4.删除"coe_4tm6j886yvzj3_4047680367" $more coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql 找到如下语句 To drop this custom SQL Profile after it has been created: EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_4tm6j886yvzj3_4047680367'); PL/SQL procedure successfully completed. 5.查看基线并再次验证原始sql，并查看执行计划走的是哪一个 SQL>select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%'; SQL_HANDLE SQL_TEXT PLAN_NAME ------------------------------ ------------------------------------------------------------ ------------------------------ SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k60b1ef84 SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k99963deb SQL> select * from table(dbms_xplan.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 300966803 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 344 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST2 | 1 | 98 | 344 (1)| 00:00:05 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------- 1 - filter("OBJECT_ID"=20) Note ----- - SQL plan baseline "SQL_PLAN_21rt9zqtyjb2k99963deb" used for this statement 可以看到，虽然在基线里面还可以看到coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql指定的执行计划，但是在执行sql的时候并没有再使用这个执行计划 6.删除无用的SQL_PLAN_21rt9zqtyjb2k60b1ef84，留下之前hint的那个 SQL> var temp number; SQL> begin 2 :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_20df29fdb3e8ac52',plan_name=>'SQL_PLAN_21rt9zqtyjb2k60b1ef84'); 3 end; 4 / PL/SQL procedure successfully completed. 7.再次查看基线 SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%'; SQL_HANDLE SQL_TEXT PLAN_NAME ------------------------------ ------------------------------------------------------------ ------------------------------ SQL_20df29fdb3e8ac52 select * from test2 where object_id=20 SQL_PLAN_21rt9zqtyjb2k99963deb

编辑推荐：

返回列表

spm执行计划的绑定

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推