一次 SQL 优化

现场人员报告某业务执行缓慢，至少50分钟才能执行完该业务。在测试库中再现现场问题，抓取到的一个大SQL如下：

 

SQL> SELECT w.event, SQL_TEXT
  2    FROM V$SESSION S, V$SQLAREA A, V$SESSION_WAIT W
  3   WHERE S.SID = 144 AND S.SID = W.SID AND S.SQL_ADDRESS = A.ADDRESS;

 

EVENT                     SQL_TEXT
-------------------------------------------------------------------------
db file sequential read   SELECT SUM(A) FROM ( SELECT COUNT(1) A FROM KC03 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1' UNION ALL SELECT COUNT(1) A FROM JC01 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1' UNION ALL SELECT COUNT(1) A FROM LC09 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1' UNION ALL SELECT COUNT(1) A FROM MC03 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1' UNION ALL SELECT COUNT(1) A FROM IC01 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1')

 

 

格式化一下这个SQL:

 

SELECT SUM(A)

  FROM (SELECT COUNT(1) A

          FROM KC03

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1'

        UNION ALL

        SELECT COUNT(1) A

          FROM JC01

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1'

        UNION ALL

        SELECT COUNT(1) A

          FROM LC09

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1'

        UNION ALL

        SELECT COUNT(1) A

          FROM MC03

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1'

        UNION ALL

        SELECT COUNT(1) A

          FROM IC01

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1')

 

该表已经使用到索引：

 

SQL> explain plan for

  2  SELECT SUM(A) FROM ( SELECT COUNT(1) A FROM KC03 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1' UNION ALL SELECT COUNT(1) A FROM JC01 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1' UNION ALL SELECT COUNT(1) A FROM LC09 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1' UNION ALL SELECT COUNT(1) A FROM MC03 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1' UNION ALL SELECT COUNT(1) A FROM IC01 WHERE AAC001 = :B2 AND AAE003 = :B1 AND AAE143 = '1')

  3  ;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT                                                

------------------------------------------------------------------------

                                                                  

------------------------------------------------------------------------

| Id  | Operation                           | Name |Rows|Bytes|Cost|

------------------------------------------------------------------------

|  0 |SELECT STATEMENT               |          | 1 | 13 | 26 | 

|  1 | SORT AGGREGATE                 |          | 1 | 13 |    | 

|  2 |  VIEW                                 |          | 5 | 65 | 26 | 

|  3 |   UNION-ALL                         |          |   |    |    | 

|  4 |    SORT AGGREGATE              |          | 1 | 23 |    | 

|  5 |     INDEX RANGE SCAN           | IND_KC03 | 1 | 23 |  3 | 

|  6 |    SORT AGGREGATE              |          | 1 | 23 |    | 

|  7 |     INDEX RANGE SCAN           | PK_JC01  | 1 | 23 |  3 | 

|  8 |    SORT AGGREGATE              |          | 1 | 23 |    | 

|  9 |     TABLE ACCESS BY INDEX ROWID| LC09     | 1 | 23 | 16 | 

| 10 |      INDEX RANGE SCAN          | INX_LC09 |13 |    |  3 | 

| 11 |    SORT AGGREGATE              |          | 1 | 20 |    | 

| 12 |     INDEX SKIP SCAN             | PK_MC03  | 1 | 20 |  0 | 

| 13 |    SORT AGGREGATE              |          | 1 | 23 |    | 

| 14 |     INDEX RANGE SCAN           | PK_IC01  | 1 | 23 |  4 | 

------------------------------------------------------------------------ 

Note

 

PLAN_TABLE_OUTPUT

----------------------------------

   - 'PLAN_TABLE' is old version

 

已选择24行。

 

SQL>

 

这里要统计五张大表中人员记录的总数，而且每个人员统计一次。对于人数众多的大单位来说，执行起来就很慢了。

 

查看其上下文的代码，发现其实并不需要统计这个总数。下文中是这样使用该统计数据的：

 

SELECT SUM(A) INTO n_count FROM ...

IF n_count > 0 then

...

END IF;

 

 

开发人员的本意是查看该人员在这几张大表中是否存在数据。如果一张表中有数据，变量 n_count 就是大于 0 的了。那么，就可以想办法跳过一些大表的查询了，将最可能有数据的大表排在前面：

 

SELECT 1

  FROM DUAL

 WHERE EXISTS (SELECT 1

          FROM IC01

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1')

    OR EXISTS (SELECT 1

          FROM KC03

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1')

    OR EXISTS (SELECT 1

          FROM LC09

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1')

    OR EXISTS (SELECT 1

          FROM JC01

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1')

    OR EXISTS (SELECT 1

          FROM MC03

         WHERE AAC001 = :B2

           AND AAE003 = :B1

           AND AAE143 = '1')

 

这样，若 IC01 中存在该人员的数据，则只会搜索 IC01，不会再查询其他4张表，速度会提高很多。但对于该业务来说，大部分还是都不存在数据的情况。那么，还是得依次查询每个大表，所以大部分业务还是进行缓慢的。

 

检查该代码的上文，发现可以按照单位来检查，而不是按照个人。这样，只需要执行一次就可以了。将查询结果放在一个联合数组中，然后在循环中进行比较。这样消耗的就是 CPU 的处理时间了，而不会再顺序读取数据文件：

 

1. 定义变量

 

n_index_aac001_exists number(10);

type type_aac001_exists is table of ac01.aac001%type index by pls_integer;

t_aac001_exists type_aac001_exists;

 

2. 将该单位所有在大表中存在的人员记录查询出来，放在联合数组 t_aac001_exists 中：

 

FOR rec_ic01 IN( SELECT aac001 FROM ic01 WHERE aab001 = prm_aab001 AND aae003 = prm_aae002 and aae143 = '1' ) LOOP

  n_index_aac001_exists := n_index_aac001_exists + 1;

  t_aac001_exists(n_index_aac001_exists) := rec_ic01.aac001;

END LOOP;

FOR rec_KC03 IN( SELECT aac001 FROM KC03 WHERE aab001 = prm_aab001 AND aae003 = prm_aae002 and aae143 = '1' ) LOOP

  n_index_aac001_exists := n_index_aac001_exists + 1;

  t_aac001_exists(n_index_aac001_exists) := rec_KC03.aac001;

END LOOP;

FOR rec_JC01 IN( SELECT aac001 FROM JC01 WHERE aab001 = prm_aab001 AND aae003 = prm_aae002 and aae143 = '1' ) LOOP

  n_index_aac001_exists := n_index_aac001_exists + 1;

  t_aac001_exists(n_index_aac001_exists) := rec_JC01.aac001;

END LOOP;

FOR rec_LC09 IN( SELECT aac001 FROM LC09 WHERE aab001 = prm_aab001 AND aae003 = prm_aae002 and aae143 = '1' ) LOOP

  n_index_aac001_exists := n_index_aac001_exists + 1;

  t_aac001_exists(n_index_aac001_exists) := rec_LC09.aac001;

END LOOP;

FOR rec_MC03 IN( SELECT aac001 FROM MC03 WHERE aab001 = prm_aab001 AND aae003 = prm_aae002 and aae143 = '1' ) LOOP

  n_index_aac001_exists := n_index_aac001_exists + 1;

  t_aac001_exists(n_index_aac001_exists) := rec_MC03.aac001;

END LOOP;

...

 

3. 在循环体内，从联合数组中来比较数据：

 

IF t_aac001_exists.EXISTS(1) THEN

  FOR n_pointer IN t_aac001_exists.FIRST..t_aac001_exists.LAST LOOP

    IF v_aac001 = t_aac001_exists(n_pointer) THEN

      n_co := 1;

      EXIT;

    END IF;

  END LOOP; 

ELSE

  n_co := 0;

END IF;

 

这样，执行起来就快了很多，在该步骤几乎察觉不到等待。

 

有时候，当 SQL 没有办法再进一步优化了，那可以从其他地方进行平衡，比如将 I/O 的负载一些分给 CPU。当然，首先还是得对业务熟悉。