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

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

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