导航：首页 > 数据库 >

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

发表于：2025-01-22 作者：千家信息网编辑

千家信息网最后更新 2025年01月22日，这篇文章主要介绍了oracle如何使用outline固定执行计划事例，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。1.查看现在数据库等

千家信息网最后更新 2025年01月22日oracle如何使用outline固定执行计划事例

这篇文章主要介绍了oracle如何使用outline固定执行计划事例，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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)

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());

------------------------------------------------------------------------------------------
| 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 |

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等待，建议调大

感谢你能够认真阅读完这篇文章，希望小编分享的"oracle如何使用outline固定执行计划事例"这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!

很赞哦！