生产环境sql语句调优实战第四篇

2022-04-04 10:03:44

生产中有一条sql语句消耗了大量的cpu资源，执行时间在18秒左右，

Session

:

PRODBUSER (1560:61133)

SQL ID

:

1hg2wcuapy3y3

SQL Execution ID

:

16871963

Execution Started

:

07/21/2014 12:30:20

First Refresh Time

:

07/21/2014 12:30:24

Last Refresh Time

:

07/21/2014 12:30:37

Duration

:

18s

Module/Action

:

JDBC Thin Client/-

Service

:

PRODB

Program

:

JDBC Thin Client

Fetch Calls

:

771

sql语句如下，略微做了删改。

select t_run_request.run_mode,
       t_cycle_groups.flow_id,
       t_cycle_groups.request_id,
       t_cycle_groups.dynamic_attributes,
       t_cycle_groups.sys_creation_date,
       t_cycle_groups.sys_update_date,
       t_cycle_control.cycle_code
  from (select t_cycle_groups.*, a.request_id
          from (select t_cycle_groups.group_id,
                       t_cycle_groups.flow_id,
                       t_cycle_groups.cycle_seq_no,
                       t_cycle_groups.route,
                       t_group_status.request_id
                  from t_cycle_groups, --8000多条数据
                       t_group_status  --100多万条数据
                 where t_cycle_groups.group_id = t_group_status.group_id
                   AND t_cycle_groups.flow_id = t_group_status.flow_id
                   AND t_cycle_groups.cycle_seq_no =
                       t_group_status.cycle_seq_no
                   AND t_cycle_groups.route = t_group_status.route
                 group by t_cycle_groups.group_id,
                          t_cycle_groups.flow_id,
                          t_cycle_groups.cycle_seq_no,
                          t_cycle_groups.route,
                          t_group_status.request_id) a,
               t_cycle_groups
         where t_cycle_groups.group_id = a.group_id
           AND t_cycle_groups.flow_id = a.flow_id
           AND t_cycle_groups.cycle_seq_no = a.cycle_seq_no
           AND t_cycle_groups.route = a.route) t_cycle_groups,
       t_cycle_control, --2121多条数据
       t_run_request   --6000多条数据
 where t_cycle_groups.status = 'FIN'
   AND t_cycle_groups.request_id = t_run_request.request_id
   AND t_cycle_control.cycle_seq_no = t_cycle_groups.cycle_seq_no
   AND (t_run_request.population_type = 'CYC' OR
       t_run_request.population_type = 'CCD')
   AND t_run_request.population_id = t_cycle_control.cycle_seq_no
 order by t_cycle_groups.request_id

执行计划如下：

Plan hash value: 2458454793


------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |  4271K|   822M|       |   201K  (1)| 00:40:23 |       |       |
|   1 |  SORT ORDER BY               |                       |  4271K|   822M|   855M|   201K  (1)| 00:40:23 |       |       |
|*  2 |   HASH JOIN                  |                       |  4271K|   822M|       | 13779   (1)| 00:02:46 |       |       |
|   3 |    VIEW                      | VW_NSO_1              |   423 | 13959 |       |    27   (8)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE              |                       |   423 | 12690 |       |    27   (8)| 00:00:01 |       |       |
|*  5 |      HASH JOIN               |                       |  5876 |   172K|       |    26   (4)| 00:00:01 |       |       |
|   6 |       VIEW                   | index$_join$_009      |  2119 | 16952 |       |     3  (34)| 00:00:01 |       |       |
|*  7 |        HASH JOIN             |                       |       |       |       |            |          |       |       |
|   8 |         INDEX FAST FULL SCAN | T_CYCLE_CONTROL_PK    |  2119 | 16952 |       |     1   (0)| 00:00:01 |       |       |
|   9 |         INDEX FAST FULL SCAN | T_CYCLE_CONTROL_1IX   |  2119 | 16952 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |       TABLE ACCESS FULL      | T_RUN_REQUEST         |  6038 |   129K|       |    23   (0)| 00:00:01 |       |       |
|* 11 |    HASH JOIN                 |                       |  1009K|   162M|       | 13735   (1)| 00:02:45 |       |       |
|  12 |     TABLE ACCESS FULL        | T_RUN_REQUEST         |  6110 | 67210 |       |    23   (0)| 00:00:01 |       |       |
|* 13 |     HASH JOIN                |                       |  1009K|   152M|       | 13708   (1)| 00:02:45 |       |       |
|  14 |      JOIN FILTER CREATE      | :BF0000               |  8824 |  1128K|       |    48   (3)| 00:00:01 |       |       |
|* 15 |       HASH JOIN              |                       |  8824 |  1128K|       |    48   (3)| 00:00:01 |       |       |
|  16 |        VIEW                  | index$_join$_006      |  2119 | 16952 |       |     3  (34)| 00:00:01 |       |       |
|* 17 |         HASH JOIN            |                       |       |       |       |            |          |       |       |
|  18 |          INDEX FAST FULL SCAN| T_CYCLE_CONTROL_PK    |  2119 | 16952 |       |     1   (0)| 00:00:01 |       |       |
|  19 |          INDEX FAST FULL SCAN| T_CYCLE_CONTROL_1IX   |  2119 | 16952 |       |     1   (0)| 00:00:01 |       |       |
|* 20 |        TABLE ACCESS FULL     | T_CYCLE_GROUPS        |  8862 |  1064K|       |    45   (0)| 00:00:01 |       |       |
|  21 |      VIEW                    |                       |  1014K|    26M|       | 13656   (1)| 00:02:44 |       |       |
|  22 |       HASH GROUP BY          |                       |  1014K|    48M|    62M| 13656   (1)| 00:02:44 |       |       |
|  23 |        JOIN FILTER USE       | :BF0000               |  1014K|    48M|       |  1170   (1)| 00:00:15 |       |       |
|  24 |         NESTED LOOPS         |                       |  1014K|    48M|       |  1170   (1)| 00:00:15 |       |       |
|  25 |          PARTITION RANGE ALL |                       |  1014K|    26M|       |  1165   (0)| 00:00:14 |     1 |    19 |
|  26 |           INDEX FULL SCAN    | T_GROUP_STATUS_PK     |  1014K|    26M|       |  1165   (0)| 00:00:14 |     1 |    19 |
|* 27 |          INDEX UNIQUE SCAN   | T_CYCLE_GROUPS_PK     |     1 |    23 |       |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

可以看到，执行计划里面的预估，资源消耗是很严重的的，幸好涉及的几个表数据量都不算大。
根据数据量和执行计划可以得到。执行计划和实际的执行情况有很大的差别。实际执行时，返回的最终数据量只有9000多条，但是根据执行计划却有400多万。

其实问题可以归结为下面的一个子查询。
子查询中，因为t_cycle_groups含有clob字段且要作为最终的输出，clob字段不能做order by/group by的操作。所以开发最开始就先根据主键的情况和t_group_status来做关联(t_group_status中有100多万条数据),同时匹配t_group_status中的一个字段，过滤掉多余的数据记录，然后在子查询外再和表t_group_status做关联输出clob字段。
下面的例子中，子查询的别名为a,在子查询中根据主键和大表做关联，输出了小表的所有主键列和大表的一个列。大表的这个列和小表的主键列匹配会有很多冗余数据，需要做group by，在外层又重新和t_cycle_groups做关联。
这个操作可以打个比方，比如我有一张信用卡，在这个月的10,11,12,14号每天都刷了3次卡，现在就是想输出我在哪些天刷了卡，只需要输出10,11,12,14就可以，不需要输出每次刷卡的具体时间。

select t_cycle_groups.*, a.request_id
          from (select t_cycle_groups.group_id,
                       t_cycle_groups.flow_id,
                       t_cycle_groups.cycle_seq_no,
                       t_cycle_groups.route,
                       t_group_status.request_id
                  from t_cycle_groups, --8000多条数据
                       t_group_status  --100多万条数据
                 where t_cycle_groups.group_id = t_group_status.group_id
                   AND t_cycle_groups.flow_id = t_group_status.flow_id
                   AND t_cycle_groups.cycle_seq_no =
                       t_group_status.cycle_seq_no
                   AND t_cycle_groups.route = t_group_status.route
                 group by t_cycle_groups.group_id,
                          t_cycle_groups.flow_id,
                          t_cycle_groups.cycle_seq_no,
                          t_cycle_groups.route,
                          t_group_status.request_id) a,
               t_cycle_groups
         where t_cycle_groups.group_id = a.group_id
           AND t_cycle_groups.flow_id = a.flow_id
           AND t_cycle_groups.cycle_seq_no = a.cycle_seq_no
           AND t_cycle_groups.route = a.route

优化后的子查询如下：
因为主键列是固定的，我直接使用rowid来替代。这样过group by的时候会减少很多的数据过滤，在子查询过滤了最多的数据之后才和表重新关联，最后输出clob字段。
这样就避免了反复比较主键列，过多复杂的数据group by。

select T_cycle_groups.flow_id,
               temp.request_id,
               T_cycle_groups.dynamic_attributes,
               T_cycle_groups.sys_creation_date,
               T_cycle_groups.sys_update_date,
               T_cycle_groups.cycle_seq_no
          from (select T_cycle_groups.rowid temp_id,
                       T_group_status.request_id
                  from T_cycle_groups, T_group_status
                 where T_cycle_groups.group_id = T_group_status.group_id
                   AND T_cycle_groups.flow_id = T_group_status.flow_id
                   AND T_cycle_groups.cycle_seq_no =
                       T_group_status.cycle_seq_no
                   AND T_cycle_groups.route = T_group_status.route
                 group by T_cycle_groups.rowid, T_group_status.request_id) temp,
               T_cycle_groups
         where temp.temp_id = T_cycle_groups.rowid
           and T_cycle_groups.status = 'FIN'

改进后再次查询，整个查询的性能就好多了，从执行计划来说，资源的消耗就比较合理了。

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                       |     2 |   332 |  1277   (4)| 00:00:16 |       |       |
|   1 |  SORT ORDER BY             |                       |     2 |   332 |  1277   (4)| 00:00:16 |       |       |
|*  2 |   HASH JOIN                |                       |     2 |   332 |  1276   (4)| 00:00:16 |       |       |
|*  3 |    TABLE ACCESS FULL       | T_RUN_REQUEST         |  6038 |   112K|    23   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN               |                       |  3224 |   462K|  1253   (4)| 00:00:16 |       |       |
|   5 |     VIEW                   | index$_join$_006      |  2119 | 16952 |     3  (34)| 00:00:01 |       |       |
|*  6 |      HASH JOIN             |                       |       |       |            |          |       |       |
|   7 |       INDEX FAST FULL SCAN | T_CYCLE_CONTROL_PK    |  2119 | 16952 |     1   (0)| 00:00:01 |       |       |
|   8 |       INDEX FAST FULL SCAN | T_CYCLE_CONTROL_1IX   |  2119 | 16952 |     1   (0)| 00:00:01 |       |       |
|*  9 |     HASH JOIN              |                       |  3238 |   439K|  1249   (4)| 00:00:15 |       |       |
|  10 |      VIEW                  |                       |  3252 | 52032 |  1204   (4)| 00:00:15 |       |       |
|  11 |       HASH GROUP BY        |                       |  3252 |   196K|  1204   (4)| 00:00:15 |       |       |
|  12 |        NESTED LOOPS        |                       |  1014K|    59M|  1170   (1)| 00:00:15 |       |       |
|  13 |         PARTITION RANGE ALL|                       |  1014K|    26M|  1165   (0)| 00:00:14 |     1 |    19 |
|  14 |          INDEX FULL SCAN   | T_GROUP_STATUS_PK     |  1014K|    26M|  1165   (0)| 00:00:14 |     1 |    19 |
|* 15 |         INDEX UNIQUE SCAN  | T_CYCLE_GROUPS_PK     |     1 |    35 |     1   (0)| 00:00:01 |       |       |
|* 16 |      TABLE ACCESS FULL     | T_CYCLE_GROUPS        |  8862 |  1064K|    45   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

查询的执行时间也保持在5秒左右。

本文转自ICT时空dbasdk的博客，原文链接：生产环境sql语句调优实战第四篇，如需转载请自行联系原博主。

码农公寓

相关文章