查询(使用聚合函数)调优

Question

我有一个问题(如下所示)。explain计划显示CPU利用率很高，这也导致了我们实验室的停机时间。那么有没有可能进一步提出这个问题呢？我应该做些什么来调整它？

仅供参考，mtr_main_a，mtr_main_b，mtr_hist包含的记录数量可能是1000万甚至更多。

SELECT to_char(MAX(mdt), 'MM-DD-RRRR HH24:MI:SS')
FROM   (
           SELECT MAX(mod_date - 2 / 86400) mdt
           FROM   mtr_main_a
           UNION
           SELECT MAX(mod_date - 2 / 86400) mdt
           FROM   mtr_main_b
           UNION
           SELECT MAX(mod_date - 2 / 86400) mdt
           FROM   mtr_hist@batch_hist
       )
/

解释计划如下

Execution Plan
----------------------------------------------------------
Plan hash value: 1573811822

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |     9 |       | 79803   (1)| 00:18:38 |        |      |
|   1 |  SORT AGGREGATE        |            |     1 |     9 |       |            |          |        |      |
|   2 |   VIEW                 |            |     2 |    18 |       | 79803   (1)| 00:18:38 |        |      |
|   3 |    SORT UNIQUE         |            |     2 |    17 |    77M| 79803   (2)| 00:18:38 |        |      |
|   4 |     UNION-ALL          |            |       |       |       |            |          |        |      |
|   5 |      SORT AGGREGATE    |            |     1 |     8 |       | 79459   (1)| 00:18:33 |        |      |
|   6 |       TABLE ACCESS FULL| MTR_MAIN_A |  5058K|    38M|       | 67735   (1)| 00:15:49 |        |      |
|   7 |      SORT AGGREGATE    |            |     1 |     9 |       |   344   (1)| 00:00:05 |        |      |
|   8 |       TABLE ACCESS FULL| MTR_MAIN_B |     1 |     9 |       |   343   (1)| 00:00:05 |        |      |
|   9 |      REMOTE            |            |       |       |       |            |          |  HISTB | R->S |
-------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   9 - EXPLAIN PLAN SET STATEMENT_ID='PLUS10294704' INTO PLAN_TABLE@! FOR SELECT
       MAX("A1"."MOD_DATE"-.00002314814814814814814814814814814814814815) FROM "MTR_HIST" "A1" (accessing
       'HISTB' )

谢谢和问候，Chandra

Answer 1

在确定最大日期之后，您应该能够通过在mod_date列上建立索引并以在最后执行减法的方式更改查询来极大地提高性能：

SELECT to_char(MAX(mdt) - 2 / 86400, 'MM-DD-RRRR HH24:MI:SS')
FROM   (
           SELECT MAX(mod_date) mdt
           FROM   mtr_main_a
           UNION
           SELECT MAX(mod_date) mdt
           FROM   mtr_main_b
           UNION
           SELECT MAX(mod_date) mdt
           FROM   mtr_hist@batch_hist
       )

这应该会消除全表扫描。

Answer 2

如果你在列上有索引，这个版本是如何工作的：

SELECT to_char((case when a.mdt > b.mdt and a.mdt > c.mdt then a.mdt
                     when b.mdt > c.mdt then b.mdt
                     else c.mdt
                end) - 2 / 86400, 'MM-DD-RRRR HH24:MI:SS')
FROM (SELECT MAX(mod_date) mdt
      FROM   mtr_main_a
     ) a cross join
     (SELECT MAX(mod_date) mdt
      FROM   mtr_main_b
     ) b cross join
     (SELECT MAX(mod_date) mdt
      FROM   mtr_hist@batch_hist
     ) c

如果union版本不能更快地工作，这只是一个建议。