楊廷琨,網名 yangtingkun

云和恩墨技術總監,Oracle ACE Director,ACOUG 核心專家

最近進行了一次更新操作,整個處置和優化的過程很有意思,于是將這個過程記錄了下來.

首先描述一下更新的要求：根據遠端數據庫中幾張表的關聯結果來刷新當地表中的一個字段的值.如果當地表中記錄的ID在遠端表關聯中可以查詢到,則這條記錄的相應字段更新為1；如果對應記錄在遠端無法查詢到記錄,則這個字段更新為0.

這個需求比擬簡單,但是被更新的表是物化視圖復制的基表,這張表的所有修改都會同步到多個遠端的物化視圖中.為了避免將大量不必要的修改同步到遠端站點,更新應該針對當前狀態不正確的記錄.簡單地說就是要判斷這條記錄的當前值和更新后的值是否一致,只有二者不一樣的記錄才須更新.

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

由于原始的SQL相對比擬復雜,因此構造了一個相對簡單的例子來模擬問題：

SQL> CONN YANGTK/YANGTK@YTK102

已連接.

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

表已創立.

SQL> ALTER TABLE T1ADD PRIMARY KEY (ID);

表已變動.

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

表已創立.

SQL> CREATE INDEXIND_T2_ID ON T2(ID);

索引已創立.

SQL> ALTER TABLE T2MODIFY ID NOT NULL;

表已變動.

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

2 FROM DBA_TAB_COLUMNS C;

表已創立.

SQL> ALTER TABLE T3ADD PRIMARY KEY (ID);

表已變動.

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;

表已創立.

SQL> ALTER TABLE TADD PRIMARY KEY (ID);

表已變動.

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

PL/SQL 過程已勝利完成.

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

數據庫鏈接已創立.

在這個例子中,當地數據庫是YTK92,要更新的是T表的TYPE字段.更新的依據是遠端數據庫YTK102中的T1、T2和T3表.如果T表中一條記錄的ID可以在遠端T1、T2、T3表的聯合查詢中得到,則這條記錄的TYPE應該更新為1；如果查詢不到對應的記錄,則要更新TYPE的值為零.此外如果要更新需要更新的記錄,則要判斷當前表中的TYPE是否已經是正確的結果,如果TYPE的值本身就是正確的,則這條記錄不需要更新.

最簡單的辦法莫過于更新兩次,每次只更新一部分數據：

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 過程已勝利完成.

已用時間: 00: 00: 44.28

SQL> ROLLBACK;

回退已完成.

已用時間: 00: 00: 01.10

這是最簡單的思路,但是要通過PL/SQL來實現,并且是兩條UPDATE語句,此外效率還有點低：對于測試的例子來說,只有幾萬條記錄,而更新就用了44秒.

上面的語句可以通過一個UPDATE來實現更新,只不外邏輯略微復雜了一些：

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

已用時間: 00: 01: 18.03

SQL> ROLLBACK;

回退已完成.

已用時間: 00: 00: 00.15

有的時候,一個復雜的SQL并不比兩個簡單的SQL效率要高,上面就是一個例子.在這個例子中造成一個SQL效率更低的主要原因是：無論是前面的兩次更新,還是一個UPDATE語句,對遠端對象的兩次拜訪是無法避免的,且后一個UPDATE的邏輯更加復雜,選擇執行計劃更加困難.

現在的瓶頸在于拜訪遠端對象的代價相對較大,因此下面通過PL/SQL的方式來避免對遠端對象的多次拜訪：

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 過程已勝利完成.

已用時間: 00: 00: 10.67

SQL> ROLLBACK;

回退已完成.

已用時間: 00: 00: 00.07

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

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 過程已勝利完成.

已用時間: 00: 00: 00.35

SQL> ROLLBACK;

回退已完成.

已用時間: 00: 00: 00.12

通過運用PL/SQL減少遠端對象的拜訪次數并利用FORALL進行批量更新.UPDATE語句的執行時間已經從原來的50多秒優化到了0.35秒.

這個執行效率沒有任何的問題,但這并不意味著上面的辦法就是最優的.如果這時檢查執行計劃就可以發現：由于是對本地表進行更新,Oracle選擇當前站點作為驅動站點,而對遠端三個表的查詢采用了NESTEDLOOP.如果使用HINT來指定驅動站點并使用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 過程已勝利完成.

已用時間: 00: 00: 00.31

SQL> ROLLBACK;

回退已完成.

已用時間: 00: 00: 01.12

從0.35秒提高到0.31秒,僅優化了0.04秒,效果似乎并不明顯.不外這0.04秒的執行時間已經超過了總執行時間的10%,對于大數據量的情況,10%的性能提升也是十分可觀的.

通過這個例子可以闡明幾個問題：

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

第二,批量操作一般情況下要比PL/SQL循環效率高.上面的例子中通過循環和批量兩種方法對比很好地說明了這一點.但是不要認為批量操作就一定比循環操作快.對于例子中的一個UPDATE語句的實現,它本身就是一個批量操作,但是由于對遠端表拜訪了兩次,效率卻遠遠低于只拜訪遠端對象一次的循環操作.

第三,優化的方法是多種多樣的,但是優化思路是固定的.這個例子中優化的原則無非是盡量減少對遠端對象的拜訪、將單條操作轉化為批量操作、盡量減少交互次數等幾種.

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

搜索 蓋國強(Eygle)微信號：eeygle,備注：云和恩墨年夜講堂,即可入群.每周與千人共享免費技術分享,與講師在線討論.