oracle使用outline固定执行计划事例

来源：这里教程网时间：2026-03-03 11:48:27 作者：

1.查看现在数据库等待事件 SQL> select event,count(*) from v$session_wait group by event; EVENT COUNT(*) ---------------------------------------------------------------- ---------- SQL*Net message from client 85 SQL*Net message to client 1 buffer busy waits 3 db file scattered read 2 enqueue 1 pmon timer 1 rdbms ipc message 7 smon timer 1 8 rows selected. 对比之前的等待事件，enqueue，buffer busy waits 下降了很多 2.查看下现在造成enqueue等待的sql语句 SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request; SESS ID1 ID2 LMODE REQUEST TYPE ------------------------------------------------ ---------- ---------- ---------- ---------- ---- Holder: 28 720940 432 6 0 TX Waiter: 59 720940 432 0 6 TX SQL> SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = 28) ORDER BY piece ASC; SQL_TEXT ---------------------------------------------------------------- update tab_test_sshz set jksj = :1 , clockfacktor = :2 where yhsbh = :3 and skph = :4 and fplx_dm = :5 SQL> / SQL_TEXT ---------------------------------------------------------------- update tab_test_sshz set jksj = :1 , clockfacktor = :2 where yhsbh = :3 and skph = :4 and fplx_dm = :5 过了几分钟再看等待事件 SQL> / EVENT COUNT(*) ---------------------------------------------------------------- ---------- SQL*Net message from client 91 SQL*Net message to client 1 db file scattered read 1 db file sequential read 1 pmon timer 1 rdbms ipc message 7 smon timer 1 7 rows selected. 可以看到enqueue，buffer busy waits 两个等待事件已经消失,前面那条update语句的阻塞已经自动释放掉了在之前为以下delete语句阻塞了update语句 delete from tab_test_hz_zb a where exists(select 'y' from tab_test_sshz b where a.kphzjs_id = b.kphzjs_id and b.skph = :1 and b.fplx_dm = :2 and b.kpqssj = TO_Date( :3, 'yyyy-mm-dd') ) update tab_test_sshz set jksj = :1 , clockfacktor = :2 where yhsbh = :3 and skph = :4 and fplx_dm = :5 3.查看之前经常发生阻塞的sql的执行计划 delete from SKSKJ.tab_test_hz_zb a where exists (select 'y' from SKSKJ.tab_test_hz b where a.kphzjs_id = b.kphzjs_id and b.skph = :1 and b.fplx_dm = :2 and b.kpqssj = TO_Date(:3, 'yyyy-mm-dd')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | | | | | 1 | DELETE | tab_test_hz_ZB | | | | |* 2 | FILTER | | | | | | 3 | TABLE ACCESS FULL | tab_test_hz_ZB | | | | |* 4 | TABLE ACCESS BY INDEX ROWID| tab_test_hz | | | | |* 5 | INDEX UNIQUE SCAN | PKtab_test_hz | | | | ------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( EXISTS (SELECT 0 FROM "SKSKJ"."tab_test_hz" "B" WHERE "B"."KPHZJS_ID"=:B1 AND "B"."KPQSSJ"=TO_DATE(:Z,'yyyy-mm-dd') AND "B"."FPLX_DM"=:Z AND "B"."SKPH"=:Z)) 4 - filter("B"."KPQSSJ"=TO_DATE(:Z,'yyyy-mm-dd') AND "B"."FPLX_DM"=:Z AND "B"."SKPH"=:Z) 5 - access("B"."KPHZJS_ID"=:B1) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Note: rule based optimization 23 rows selected. 这里发现 tab_test_hz_ZB 表为全表扫描,a.kphzjs_id 有索引,并且数据库优化器模式为rule, 查询oracle文档得知,在基于规则的优化器模式下, 如果 a.kphzjs_id = b.kphzjs_id，a.kphzjs_id表达式作用了一个字段上，无论该字段有无索引，RBO都会全表扫描。在session级别设置CHOOSE的优化器后tab_test_hz_ZB 表走了索引 SQL> alter session set optimizer_mode = CHOOSE; Session altered. SQL> explain plan for 2 delete from SKSKJ.tab_test_hz_zb a 3 where exists (select 'y' 4 from SKSKJ.tab_test_hz b 5 where a.kphzjs_id = b.kphzjs_id 6 and b.skph = :1 7 and b.fplx_dm = :2 8 and b.kpqssj = TO_Date(:3, 'yyyy-mm-dd')); Explained. SQL> select plan_table_output from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------ | 0 | DELETE STATEMENT | | 1 | 101 | 11 | | 1 | DELETE | tab_test_hz_ZB | | | | | 2 | NESTED LOOPS | | 1 | 101 | 11 | | 3 | SORT UNIQUE | | | | | | 4 | TABLE ACCESS BY INDEX ROWID| tab_test_hz | 1 | 57 | 4 | |* 5 | INDEX RANGE SCAN | C01$SKPH_FPLXDM_KPQSSJ | 1 | | 3 | |* 6 | INDEX RANGE SCAN | PKtab_test_hz_ZB | 1 | 44 | 2 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("B"."SKPH"=:Z AND "B"."FPLX_DM"=:Z AND "B"."KPQSSJ"=TO_DATE(:Z,'yyyy-mm-dd')) 6 - access("A"."KPHZJS_ID"="B"."KPHZJS_ID") Note: cpu costing is off 21 rows selected. 该数据库由于历史原因,数据库优化器模式不能更改,也不能修改程序使用HINT提示,建议使用outline 更改固定为走索引的执行计划 4.固定执行计划方案如下: 4.1.查看原来语句执行计划： SQL> explain plan for delete from SKSKJ.tab_test_hz_zb a where exists (select 'y' from SKSKJ.tab_test_hz b where a.kphzjs_id = b.kphzjs_id and b.skph = :1 and b.fplx_dm = :2 and b.kpqssj = TO_Date(:3, 'yyyy-mm-dd')) SQL> select plan_table_output from table(dbms_xplan.display());查看原来语句执行计划：查看加了hist提示后走索引的执行计划 SQL> explain plan for 2 delete from SKSKJ.tab_test_hz_zb a 3 where exists (select /*+ index(SKSKJ.tab_test_hz C01$SKPH_FPLXDM_KPQSSJ ) */'y' 4 from SKSKJ.tab_test_hz b 5 where a.kphzjs_id = b.kphzjs_id 6 and b.fplx_dm = :2 7 and b.skph = :1 8 and b.kpqssj = TO_Date(:3, 'yyyy-mm-dd')); SQL> select plan_table_output from table(dbms_xplan.display()); 4.2.创建outlines 创建两个public stroed outline，第一个是目前运行的，第二个是加了hints. create or replace outline tab_test_hz_zb_full on delete from SKSKJ.tab_test_hz_zb a where exists (select 'y' from SKSKJ.tab_test_hz b where a.kphzjs_id = b.kphzjs_id and b.skph = :1 and b.fplx_dm = :2 and b.kpqssj = TO_Date(:3, 'yyyy-mm-dd')) create or replace outline tab_test_hz_zb_index on delete from SKSKJ.tab_test_hz_zb a where exists (select /*+ index(SKSKJ.tab_test_hz C01$SKPH_FPLXDM_KPQSSJ ) */'y' from SKSKJ.tab_test_hz b where a.kphzjs_id = b.kphzjs_id and b.fplx_dm = :2 and b.skph = :1 and b.kpqssj = TO_Date(:3, 'yyyy-mm-dd')); 会话参数环境设置: alter session set query_rewrite_enabled = true alter session set star_transformation_enabled = true 当前模式下创建纲要表,确保OUTLN用户存在: exec dbms_outln_edit.create_edit_tables 为交换两个stored outline的执行计划做准备 create or replace private outline PRIV_tab_test_hz_ZB_F from tab_test_hz_zb_full; create or replace private outline PRIV_tab_test_hz_ZB_I from tab_test_hz_zb_index; --必须和上面的命令使用同一个session UPDATE OL$HINTS SET OL_NAME=DECODE(OL_NAME,'PRIV_tab_test_hz_ZB_F','PRIV_tab_test_hz_ZB_I','PRIV_tab_test_hz_ZB_I','PRIV_tab_test_hz_ZB_F') WHERE OL_NAME IN ('PRIV_tab_test_hz_ZB_F','PRIV_tab_test_hz_ZB_I'); commit; SQL> set line 200 SQL> select OL_name,HINT_TEXT from ol$hints; -- 刷新内存中的outline信息 alter session set use_private_outlines=true;刷新 execute dbms_outln_edit.refresh_private_outline('PRIV_tab_test_hz_ZB_F'); execute dbms_outln_edit.refresh_private_outline('PRIV_tab_test_hz_ZB_I');发布到 public outline --创建或更新public outline create or replace outline tab_test_hz_zb_full from private PRIV_tab_test_hz_ZB_F; create or replace outline tab_test_hz_zb_index from private PRIV_tab_test_hz_ZB_I;设置使用，调整完毕 alter system set use_stored_outlines=true;启用outlines 4.3验证: 查看该语句执行计划是否为之前加了hist提示后走索引的执行计划,如果使,表示固定执行计划成功 SQL> explain plan for delete from SKSKJ.tab_test_hz_zb a where exists (select 'y' from SKSKJ.tab_test_hz b where a.kphzjs_id = b.kphzjs_id and b.skph = :1 and b.fplx_dm = :2 and b.kpqssj = TO_Date(:3, 'yyyy-mm-dd')) SQL> select plan_table_output from table(dbms_xplan.display()); 5总结 5.1可以先使用outline固定delete语句的执行计划走索引 5.2由于 tab_test_sshz表及tab_test_hz_zb表的initrans=1，如果这两个表访问比较频繁，建议调到4或者5 5.3目前数据库 db_cache_size=128M,鉴于之前数据库缓慢时有大量buffer busy waits等待，建议调大