楊廷琨,網(wǎng)名 yangtingkun

云和恩墨技術(shù)總監(jiān),Oracle ACE Director,ACOUG 核心專家

最近進(jìn)行了一次更新操作,整個(gè)處置和優(yōu)化的過程很有意思,于是將這個(gè)過程記錄了下來.

首先描述一下更新的要求：根據(jù)遠(yuǎn)端數(shù)據(jù)庫(kù)中幾張表的關(guān)聯(lián)結(jié)果來刷新當(dāng)?shù)乇碇械囊粋€(gè)字段的值.如果當(dāng)?shù)乇碇杏涗浀腎D在遠(yuǎn)端表關(guān)聯(lián)中可以查詢到,則這條記錄的相應(yīng)字段更新為1；如果對(duì)應(yīng)記錄在遠(yuǎn)端無法查詢到記錄,則這個(gè)字段更新為0.

這個(gè)需求比擬簡(jiǎn)單,但是被更新的表是物化視圖復(fù)制的基表,這張表的所有修改都會(huì)同步到多個(gè)遠(yuǎn)端的物化視圖中.為了避免將大量不必要的修改同步到遠(yuǎn)端站點(diǎn),更新應(yīng)該針對(duì)當(dāng)前狀態(tài)不正確的記錄.簡(jiǎn)單地說就是要判斷這條記錄的當(dāng)前值和更新后的值是否一致,只有二者不一樣的記錄才須更新.

此外還有一點(diǎn)要求就是不建立臨時(shí)表,使用SQL或PL/SQL來盡量高效地實(shí)現(xiàn)這個(gè)功能.不使用臨時(shí)表主要出于兩點(diǎn)考慮：一是由于需求本身很簡(jiǎn)單,寫SQL或PL/SQL最多也就十幾行語(yǔ)句罷了,為這么簡(jiǎn)單的需求建立一個(gè)臨時(shí)表沒有太大必要；另外一點(diǎn)是由于當(dāng)前數(shù)據(jù)庫(kù)版本為9204,在這個(gè)版本中,以INSERT INTO SELECT方式插入臨時(shí)表存在Bug.一般來說,臨時(shí)表的優(yōu)點(diǎn)之一就是產(chǎn)生很少的REDO,但是由于這個(gè)Bug的存在會(huì)導(dǎo)致這個(gè)版本的臨時(shí)表在插入時(shí)產(chǎn)生的REDO比普通表還要高.

由于原始的SQL相對(duì)比擬復(fù)雜,因此構(gòu)造了一個(gè)相對(duì)簡(jiǎn)單的例子來模擬問題：

SQL> CONN YANGTK/YANGTK@YTK102

已連接.

SQL> CREATE TABLET1 AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已創(chuàng)立.

SQL> ALTER TABLE T1ADD PRIMARY KEY (ID);

表已變動(dòng).

SQL> CREATE TABLET2 AS SELECT ROWNUM ID, B.* FROM DBA_SYNONYMS B;

表已創(chuàng)立.

SQL> CREATE INDEXIND_T2_ID ON T2(ID);

索引已創(chuàng)立.

SQL> ALTER TABLE T2MODIFY ID NOT NULL;

表已變動(dòng).

SQL> CREATE TABLET3 AS SELECT ROWNUM ID, C.OWNER, C.TABLE_NAME, C.COLUMN_NAME

2 FROM DBA_TAB_COLUMNS C;

表已創(chuàng)立.

SQL> ALTER TABLE T3ADD PRIMARY KEY (ID);

表已變動(dòng).

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T1')

PL/SQL 過程已勝利完成.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T2')

PL/SQL 過程已勝利完成.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T3')

PL/SQL 過程已勝利完成.

SQL> CONN YANGTK/YANGTK@YTK92

已連接.

SQL> CREATE TABLET AS SELECT ROWNUM ID, OBJECT_NAME, MOD(ROWNUM, 2) TYPE FROM DBA_OBJECTS A;

表已創(chuàng)立.

SQL> ALTER TABLE TADD PRIMARY KEY (ID);

表已變動(dòng).

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T')

PL/SQL 過程已勝利完成.

SQL> CREATE DATABASELINK YTK102 CONNECT TO YANGTK IDENTIFIED BY YANGTK USING 'YTK102';

數(shù)據(jù)庫(kù)鏈接已創(chuàng)立.

在這個(gè)例子中,當(dāng)?shù)財(cái)?shù)據(jù)庫(kù)是YTK92,要更新的是T表的TYPE字段.更新的依據(jù)是遠(yuǎn)端數(shù)據(jù)庫(kù)YTK102中的T1、T2和T3表.如果T表中一條記錄的ID可以在遠(yuǎn)端T1、T2、T3表的聯(lián)合查詢中得到,則這條記錄的TYPE應(yīng)該更新為1；如果查詢不到對(duì)應(yīng)的記錄,則要更新TYPE的值為零.此外如果要更新需要更新的記錄,則要判斷當(dāng)前表中的TYPE是否已經(jīng)是正確的結(jié)果,如果TYPE的值本身就是正確的,則這條記錄不需要更新.

最簡(jiǎn)單的辦法莫過于更新兩次,每次只更新一部分?jǐn)?shù)據(jù)：

SQL> SET TIMING ON

SQL> BEGIN

2 UPDATET SET TYPE = 1

3 WHERETYPE = 0

4 ANDID IN

5 (

6 SELECTT1.ID

7 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3

8 WHERET1.ID = T2.ID

9 ANDT2.ID = T3.ID

10 );

11

12 UPDATET SET TYPE = 0

13 WHERETYPE = 1

14 ANDNOT EXISTS

15 (

16 SELECT1

17 FROMT1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3

18 WHERET1.ID = T2.ID

19 ANDT2.ID = T3.ID

20 ANDT.ID = T1.ID

21 );

22 END;

23 /

PL/SQL 過程已勝利完成.

已用時(shí)間: 00: 00: 44.28

SQL> ROLLBACK;

回退已完成.

已用時(shí)間: 00: 00: 01.10

這是最簡(jiǎn)單的思路,但是要通過PL/SQL來實(shí)現(xiàn),并且是兩條UPDATE語(yǔ)句,此外效率還有點(diǎn)低：對(duì)于測(cè)試的例子來說,只有幾萬條記錄,而更新就用了44秒.

上面的語(yǔ)句可以通過一個(gè)UPDATE來實(shí)現(xiàn)更新,只不外邏輯略微復(fù)雜了一些：

SQL> UPDATE T SETTYPE =

2 (

3 SELECTTYPE

4 FROM

5 (

6 SELECTT.ID, DECODE(T1.ID, NULL, 0, 1) TYPE

7 FROMT,

8 (

9 SELECTT1.ID

10 FROM T1@YTK102T1, T2@YTK102 T2, T3@YTK102 T3

11 WHERE T1.ID= T2.ID

12 AND T2.ID= T3.ID

13 ) T1

14 WHERET.ID = T1.ID(+)

15 AND T.TYPE!= DECODE(T1.ID, NULL, 0, 1)

16 ) A

17 WHERE T.ID= A.ID

18 )

19 WHERE EXISTS

20 (

21 SELECT1

22 FROM

23 (

24 SELECTT.ID, DECODE(T1.ID, NULL, 0, 1) TYPE

25 FROM T,

26 (

27 SELECT T1.ID

28 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3

29 WHERE T1.ID = T2.ID

30 ANDT2.ID = T3.ID

31 ) T1

32 WHERET.ID = T1.ID(+)

33 AND T.TYPE!= DECODE(T1.ID, NULL, 0, 1)

34 ) A

35 WHERE T.ID= A.ID

36 )

37 ;

已更新15407行.

已用時(shí)間: 00: 01: 18.03

SQL> ROLLBACK;

回退已完成.

已用時(shí)間: 00: 00: 00.15

有的時(shí)候,一個(gè)復(fù)雜的SQL并不比兩個(gè)簡(jiǎn)單的SQL效率要高,上面就是一個(gè)例子.在這個(gè)例子中造成一個(gè)SQL效率更低的主要原因是：無論是前面的兩次更新,還是一個(gè)UPDATE語(yǔ)句,對(duì)遠(yuǎn)端對(duì)象的兩次拜訪是無法避免的,且后一個(gè)UPDATE的邏輯更加復(fù)雜,選擇執(zhí)行計(jì)劃更加困難.

現(xiàn)在的瓶頸在于拜訪遠(yuǎn)端對(duì)象的代價(jià)相對(duì)較大,因此下面通過PL/SQL的方式來避免對(duì)遠(yuǎn)端對(duì)象的多次拜訪：

SQL> DECLARE

2 V_TYPENUMBER;

3 BEGIN

4 FOR I IN(SELECT ID, TYPE FROM T) LOOP

5 SELECTDECODE(COUNT(T1.ID), 0, 0, 1) INTO V_TYPE

6 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3

7 WHERET1.ID = T2.ID

8 AND T2.ID= T3.ID

9 AND T1.ID= I.ID;

10

11 IF I.TYPE != V_TYPE THEN

12 UPDATET SET TYPE = V_TYPE WHERE ID = I.ID;

13 END IF;

14 END LOOP;

15 END;

16 /

PL/SQL 過程已勝利完成.

已用時(shí)間: 00: 00: 10.67

SQL> ROLLBACK;

回退已完成.

已用時(shí)間: 00: 00: 00.07

到目前為止,UPDATE的執(zhí)行效率已經(jīng)基本可以接受了,但是這只是一個(gè)簡(jiǎn)單的例子,對(duì)于數(shù)據(jù)量比擬大的情況,這種方式效率仍然比擬低.雖然對(duì)遠(yuǎn)端表只讀取一次,但是這個(gè)讀取在循環(huán)中完成,肯定有不少的交互開銷,操作效率肯定要低于通過一個(gè)SQL來完成,而且對(duì)于每個(gè)匹配的記錄都要執(zhí)行一次UPDATE,這也是比擬低效的.修改PL/SQL代碼,通過批量處理的方式來執(zhí)行：

SQL> DECLARE

2 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

3 TYPE T_TYPEIS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

4 V_IDT_ID;

5 V_TYPET_TYPE;

6 BEGIN

7

8 SELECTT.ID, DECODE(T1.ID, NULL, 0, 1) TYPE

9 BULK COLLECTINTO V_ID, V_TYPE

10 FROM T,

11 (

12 SELECTT1.ID

13 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3

14 WHERE T1.ID = T2.ID

15 AND T2.ID= T3.ID

16 ) T1

17 WHERE T.ID= T1.ID(+)

18 AND T.TYPE!= DECODE(T1.ID, NULL, 0, 1)

19 ;

20

21 FORALL I IN 1..V_ID.COUNT

22 UPDATET SET TYPE = V_TYPE(I) WHERE ID = V_ID(I);

23

24 END;

25 /

PL/SQL 過程已勝利完成.

已用時(shí)間: 00: 00: 00.35

SQL> ROLLBACK;

回退已完成.

已用時(shí)間: 00: 00: 00.12

通過運(yùn)用PL/SQL減少遠(yuǎn)端對(duì)象的拜訪次數(shù)并利用FORALL進(jìn)行批量更新.UPDATE語(yǔ)句的執(zhí)行時(shí)間已經(jīng)從原來的50多秒優(yōu)化到了0.35秒.

這個(gè)執(zhí)行效率沒有任何的問題,但這并不意味著上面的辦法就是最優(yōu)的.如果這時(shí)檢查執(zhí)行計(jì)劃就可以發(fā)現(xiàn)：由于是對(duì)本地表進(jìn)行更新,Oracle選擇當(dāng)前站點(diǎn)作為驅(qū)動(dòng)站點(diǎn),而對(duì)遠(yuǎn)端三個(gè)表的查詢采用了NESTEDLOOP.如果使用HINT來指定驅(qū)動(dòng)站點(diǎn)并使用HASH JOIN連接方式,還能獲得一定的性能提升：

SQL> DECLARE

2 TYPE T_IDIS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

3 TYPE T_TYPEIS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

4 V_ID T_ID;

5 V_TYPE T_TYPE;

6 BEGIN

7

8 SELECTT.ID, DECODE(T1.ID, NULL, 0, 1) TYPE

9 BULK COLLECTINTO V_ID, V_TYPE

10 FROM T,

11 (

12 SELECT/*+ DRIVING_SITE(T1) USE_HASH(T1 T2) USE_HASH(T3) */ T1.ID

13 FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3

14 WHERE T1.ID = T2.ID

15 AND T2.ID= T3.ID

16 ) T1

17 WHERE T.ID= T1.ID(+)

18 AND T.TYPE!= DECODE(T1.ID, NULL, 0, 1)

19 ;

20

21 FORALLI IN 1..V_ID.COUNT

22 UPDATET SET TYPE = V_TYPE(I) WHERE ID = V_ID(I);

23

24 END;

25 /

PL/SQL 過程已勝利完成.

已用時(shí)間: 00: 00: 00.31

SQL> ROLLBACK;

回退已完成.

已用時(shí)間: 00: 00: 01.12

從0.35秒提高到0.31秒,僅優(yōu)化了0.04秒,效果似乎并不明顯.不外這0.04秒的執(zhí)行時(shí)間已經(jīng)超過了總執(zhí)行時(shí)間的10%,對(duì)于大數(shù)據(jù)量的情況,10%的性能提升也是十分可觀的.

通過這個(gè)例子可以闡明幾個(gè)問題：

第一,Tom所說的能使用一條SQL就用一條SQL完成,不能使用SQL的話,可以使用PL/SQL完成.這句話在大部分的情況下是正確的,但是并不意味著SQL必定比PL/SQL快,單條SQL必定比兩條SQL快,上面的例子就是很好的說明.

第二,批量操作一般情況下要比PL/SQL循環(huán)效率高.上面的例子中通過循環(huán)和批量?jī)煞N方法對(duì)比很好地說明了這一點(diǎn).但是不要認(rèn)為批量操作就一定比循環(huán)操作快.對(duì)于例子中的一個(gè)UPDATE語(yǔ)句的實(shí)現(xiàn),它本身就是一個(gè)批量操作,但是由于對(duì)遠(yuǎn)端表拜訪了兩次,效率卻遠(yuǎn)遠(yuǎn)低于只拜訪遠(yuǎn)端對(duì)象一次的循環(huán)操作.

第三,優(yōu)化的方法是多種多樣的,但是優(yōu)化思路是固定的.這個(gè)例子中優(yōu)化的原則無非是盡量減少對(duì)遠(yuǎn)端對(duì)象的拜訪、將單條操作轉(zhuǎn)化為批量操作、盡量減少交互次數(shù)等幾種.

如何參加"云和恩墨大講堂"微信群

搜索 蓋國(guó)強(qiáng)(Eygle)微信號(hào)：eeygle,備注：云和恩墨年夜講堂,即可入群.每周與千人共享免費(fèi)技術(shù)分享,與講師在線討論.