层层升入：SQL极限调优之一次更新操作的N种优化可能

杨廷琨,网名 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,备注：云和恩墨年夜讲堂,即可入群.每周与千人共享免费技术分享,与讲师在线讨论.