sql profile固定执行计划(manual)
--在我们无法修改源代码的时候,我们可以采用sqlprofile来改变执行计划 --新增一张测试表 create table test_emp as select * from scott.emp; --新建索引 create index idx_001 on test_emp(empno); --下面的语句正常是要走全表扫描的 select /*+ no_index(test_emp idx_001) */ empno from test_emp where empno=7656; select /*+ index(test_emp idx_001) */ empno from test_emp where empno=7656; select * from v$sql t where t.SQL_TEXT like '%select /*+ no_index(test_emp idx_001) */ empno from test_emp where empno=7656%'---7mr5qb3b2m0gp,242355602 select * from v$sql t where t.SQL_TEXT like '%select /*+ index(test_emp idx_001) */ empno from test_emp where empno=7656%'---bj0tkbsvv0jgf,1278441224 @coe_xfr_sql_profile.sql --输入源语句的SQL_ID,PLAN_HASH_VALUES(7mr5qb3b2m0gp,242355602) --产生类似coe_xfr_sql_profile_7mr5qb3b2m0gp_242355602.sql @coe_xfr_sql_profile.sql --输入目标语句的SQL_ID,PLAN_HASH_VALUES(bj0tkbsvv0jgf,1278441224) --产生类似coe_xfr_sql_profile_bj0tkbsvv0jgf_1278441224.sql --把目标SQL中的 h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]', q'[DB_VERSION('11.2.0.1')]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[INDEX(@"SEL$1" "TEST_EMP"@"SEL$1" ("TEST_EMP"."EMPNO"))]', q'[END_OUTLINE_DATA]'); --替换源SQL中相同位置的文本,并将源SQL中force_match => FALSE修改为TRUE --最后执行coe_xfr_sql_profile_7mr5qb3b2m0gp_242355602.sql select * from table(dbms_xplan.display_cursor('7mr5qb3b2m0gp',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
![](https://images2017.cnblogs.com/blog/1028519/201712/1028519-20171205095125175-1959780179.png)