您好,登錄后才能下訂單哦!
這篇文章主要介紹了oracle如何使用outline固定執(zhí)行計(jì)劃事例,具有一定借鑒價(jià)值,感興趣的朋友可以參考下,希望大家閱讀完這篇文章之后大有收獲,下面讓小編帶著大家一起了解一下。
1.查看現(xiàn)在數(shù)據(jù)庫(kù)等待事件
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.
對(duì)比之前的等待事件,enqueue,buffer busy waits 下降了很多
2.查看下現(xiàn)在造成enqueue等待的sql語(yǔ)句
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
過(guò)了幾分鐘再看等待事件
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 兩個(gè)等待事件已經(jīng)消失,前面那條update語(yǔ)句
的阻塞已經(jīng)自動(dòng)釋放掉了
在之前為以下delete語(yǔ)句阻塞了update語(yǔ)句
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.查看之前經(jīng)常發(fā)生阻塞的sql的執(zhí)行計(jì)劃
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.
這里發(fā)現(xiàn) tab_test_hz_ZB 表為全表掃描,a.kphzjs_id 有索引,并且數(shù)據(jù)庫(kù)優(yōu)化
器模式為rule,
查詢(xún)oracle文檔得知,在基于規(guī)則的優(yōu)化器模式下,
如果 a.kphzjs_id = b.kphzjs_id,a.kphzjs_id表達(dá)式作用了一個(gè)字段上,無(wú)論該字
段有無(wú)索引,RBO都會(huì)全表掃描。
在session級(jí)別設(shè)置CHOOSE的優(yōu)化器后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.
該數(shù)據(jù)庫(kù)由于歷史原因,數(shù)據(jù)庫(kù)優(yōu)化器模式不能更改,也不能修改程序使用HINT提示,建議使用outline 更改固定為走索引的執(zhí)行計(jì)劃
4.固定執(zhí)行計(jì)劃方案如下:
4.1.查看原來(lái)語(yǔ)句執(zhí)行計(jì)劃:
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());查看原來(lái)語(yǔ)句
執(zhí)行計(jì)劃:
查看加了hist提示后走索引的執(zhí)行計(jì)劃
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.創(chuàng)建outlines
創(chuàng)建兩個(gè)public stroed outline,第一個(gè)是目前運(yùn)行的,第二個(gè)是加了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'));
會(huì)話(huà)參數(shù)環(huán)境設(shè)置:
alter session set query_rewrite_enabled = true
alter session set star_transformation_enabled = true
當(dāng)前模式下創(chuàng)建綱要表,確保OUTLN用戶(hù)存在:
exec dbms_outln_edit.create_edit_tables
為交換兩個(gè)stored outline的執(zhí)行計(jì)劃做準(zhǔn)備
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;
--必須和上面的命令使用同一個(gè)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;
-- 刷新內(nèi)存中的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');發(fā)布到
public outline
--創(chuàng)建或更新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;設(shè)置使用,調(diào)整完畢
alter system set use_stored_outlines=true;啟用outlines
4.3驗(yàn)證:
查看該語(yǔ)句執(zhí)行計(jì)劃是否為之前加了hist提示后走索引的執(zhí)行計(jì)劃,如果使,表示固定
執(zhí)行計(jì)劃成功
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總結(jié)
5.1可以先使用outline固定delete語(yǔ)句的執(zhí)行計(jì)劃走索引
5.2由于 tab_test_sshz表及tab_test_hz_zb表的initrans=1,如果這兩個(gè)表
訪問(wèn)比較頻繁,建議調(diào)到4或者5
5.3目前數(shù)據(jù)庫(kù) db_cache_size=128M,鑒于之前數(shù)據(jù)庫(kù)緩慢時(shí)有大量buffer busy
waits等待,建議調(diào)大
感謝你能夠認(rèn)真閱讀完這篇文章,希望小編分享的“oracle如何使用outline固定執(zhí)行計(jì)劃事例”這篇文章對(duì)大家有幫助,同時(shí)也希望大家多多支持億速云,關(guān)注億速云行業(yè)資訊頻道,更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。