oracle如何使用outline固定執行計劃事例

151次閱讀

共計 8219 個字符，預計需要花費 21 分鐘才能閱讀完成。

這篇文章主要介紹了 oracle 如何使用 outline 固定執行計劃事例，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

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

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 等待，建議調大

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“oracle 如何使用 outline 固定執行計劃事例”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!

正文完