首先构建一个简单的测试用例来实际演示： create table emp as select * from scott.emp; create table dept as select * from scott.dept; create index idx_emp_empno on emp(empno); create index idx_dept_deptno on dept(deptno); 测试过程中查看真实执行计划的方法： set lines 1000 pages 1000 alter session set statistics_level = ALL; Execute SQL; select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); 正常的SQL执行，执行计划会走相应的索引： --good SQL: 39dv3d8jkzyuw select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; --good xplan: 1725450077 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 39dv3d8jkzyuw, child number 0 ------------------------------------- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788 Plan hash value: 1725450077 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 6 | 2 | | 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 6 | 2 | | 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 | 2 | | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 3 | 1 | |* 4 | INDEX RANGE SCAN | IDX_EMP_EMPNO | 1 | 1 | 1 |00:00:00.01 | 2 | 1 | |* 5 | INDEX RANGE SCAN | IDX_DEPT_DEPTNO | 1 | 1 | 1 |00:00:00.01 | 2 | 1 | | 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("EMPNO"=7788) 5 - access("A"."DEPTNO"="B"."DEPTNO") 糟糕的SQL执行，执行计划走全表扫描（这里实验直接利用使用hint强制不走索引来模拟这种情况）: --bad SQL: dqd10y7wqrg7f select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; --bad xplan: 1123238657 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID dqd10y7wqrg7f, child number 1 ------------------------------------- select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788 Plan hash value: 1123238657 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5 | | | | |* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 5 | 1214K| 1214K| 377K (0)| |* 2 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 2 | | | | | 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 3 | | | | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO") 2 - filter("EMPNO"=7788) 假设此时这些糟糕的SQL就是业务实际的SQL，且对应开发人员无法更改SQL文本（这里就是指无法去掉不走索引的hint），那么现在如何能将这些糟糕的SQL绑定成走索引的执行计划呢？ 糟糕的SQL清单： select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7900; 如何让其走索引？目前Oracle常见的2种稳固执行计划的方式： 1.SQL Profile稳固执行计划 2.SPM稳固执行计划 1.SQL Profile稳固执行计划 适用于Oracle 10g及以上版本。 利用MOS文档215187.1提供的系列脚本中的coe_xfr_sql_profile.sql来稳固执行计划，只需要输入要调整SQL的SQL_ID和好的执行计划的plan_hash_value即可，脚本内容可参考： 使用COE脚本绑定SQL Profile 在本次演示实验中，就是将sql_id='dqd10y7wqrg7f'的SQL绑定好的plan_hash_value=1725450077，具体使用过程如下： SQL> @coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) Enter value for 1: dqd10y7wqrg7f PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 1123238657 .095 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 1725450077 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "dqd10y7wqrg7f" PLAN_HASH_VALUE: "1725450077" ... Execute coe_xfr_sql_profile_dqd10y7wqrg7f_1725450077.sql on TARGET system in order to create a custom SQL Profile with plan 1725450077 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. 然后按照提示执行生成的coe_xfr_sql_profile_dqd10y7wqrg7f_1725450077.sql脚本即可； 需要特别注意的是：可以根据实际情况是否需要修改这个脚本中的force_match的值为true。 本次的例子，就是没有使用到绑定变量，而需求是不仅让empno = 7788的条件走索引，还要让其他输入值，比如empno = 7900也同样走索引，那就需要修改这个force_match的值为true。稳固执行计划的效果如下： SQL> select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; EMPNO ENAME DNAME JOB SAL ---------- ---------- -------------- --------- ---------- 7788 SCOTT RESEARCH ANALYST 3000 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID dqd10y7wqrg7f, child number 0 ------------------------------------- select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788 Plan hash value: 1725450077 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 6 | | 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 6 | | 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 | | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 3 | |* 4 | INDEX RANGE SCAN | IDX_EMP_EMPNO | 1 | 1 | 1 |00:00:00.01 | 2 | |* 5 | INDEX RANGE SCAN | IDX_DEPT_DEPTNO | 1 | 1 | 1 |00:00:00.01 | 2 | | 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 1 | 1 |00:00:00.01 | 1 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("EMPNO"=7788) 5 - access("A"."DEPTNO"="B"."DEPTNO") Note ----- - SQL profile coe_dqd10y7wqrg7f_1725450077 used for this statement 常用操作： 1)查询sql_profile 可以通过查询dba_sql_profiles来确认数据库中的sql_profile： select * from dba_sql_profiles; 2)删除sql_profile 如果有一天不再需要这个sql_profile来稳固执行计划，可以这样删除sql_profile： exec dbms_sqltune.drop_sql_profile('name'); exec dbms_sqltune.drop_sql_profile('coe_dqd10y7wqrg7f_1725450077'); 3)清除SQL执行计划 还可以清除共享池中指定SQL的执行计划： exec sys.dbms_shared_pool.purge('address,hash_value','c'); SQL> select sql_id, address, hash_value, plan_hash_value, sql_profile from v$sql where sql_id = 'dqd10y7wqrg7f'; SQL_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE SQL_PROFILE ------------- ---------------- ---------- --------------- ---------------------------------------------------------------- dqd10y7wqrg7f 0000000076B909F8 4184587502 1123238657 dqd10y7wqrg7f 0000000076B909F8 4184587502 1123238657 dqd10y7wqrg7f 0000000076B909F8 4184587502 1725450077 coe_dqd10y7wqrg7f_1725450077 SQL> exec sys.dbms_shared_pool.purge('0000000076B909F8,4184587502','c'); PL/SQL procedure successfully completed. SQL> select sql_id, address, hash_value, plan_hash_value, sql_profile from v$sql where sql_id = 'dqd10y7wqrg7f'; no rows selected 2.SPM稳固执行计划 适用于Oracle 11g及以上版本。 删除掉之前的sql_profile，尝试使用SPM来稳固执行计划，实际上，手工生成sql_plan_baseline的方式要更加灵活，但我实际用的比较少。 查看sql_plan_baselines： select * from dba_sql_plan_baselines; select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines; SPM稳固执行计划方法： var temp number --1.bad: sql_id & plan_hash_value exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '', plan_hash_value => ); --2.good: sql_id & plan_hash_value & sql_handle exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '', plan_hash_value => , sql_handle => ); --3.drop bad plan_name exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle => '', plan_name => ''); 用上面的例子具体说明： --1.bad: sql_id & plan_hash_value SQL> var temp number SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => 'dqd10y7wqrg7f', plan_hash_value => 1123238657); SQL> select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ------------------------------ ------------------------------ -------------- --- --- --- SQL_9c3626a309e5e8bd SQL_PLAN_9sdj6nc4ybu5x96fd8705 MANUAL-LOAD YES YES NO --2.good: sql_id & plan_hash_value & sql_handle（上面查到的） SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '39dv3d8jkzyuw', plan_hash_value =>1725450077, sql_handle => 'SQL_9c3626a309e5e8bd'); PL/SQL procedure successfully completed. SQL> select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ------------------------------ ------------------------------ -------------- --- --- --- SQL_9c3626a309e5e8bd SQL_PLAN_9sdj6nc4ybu5x2b78d17a MANUAL-LOAD YES YES NO SQL_9c3626a309e5e8bd SQL_PLAN_9sdj6nc4ybu5x96fd8705 MANUAL-LOAD YES YES NO --3.drop bad plan_name SQL> exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_9c3626a309e5e8bd', plan_name => 'SQL_PLAN_9sdj6nc4ybu5x96fd8705'); PL/SQL procedure successfully completed. SQL> select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ------------------------------ ------------------------------ -------------- --- --- --- SQL_9c3626a309e5e8bd SQL_PLAN_9sdj6nc4ybu5x2b78d17a MANUAL-LOAD YES YES NO 验证稳固执行计划的效果： SQL> select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; EMPNO ENAME DNAME JOB SAL ---------- ---------- -------------- --------- ---------- 7788 SCOTT RESEARCH ANALYST 3000 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID dqd10y7wqrg7f, child number 1 ------------------------------------- select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788 Plan hash value: 1725450077 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------