生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条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秒左右。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-07-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

SQL之美 - 分页查询的排序问题

编辑手记:前面我们分享过分页查询的基础知识,其目的就是控制输出结果集大小,将结果尽快的返回。主要有两种方式,一种是嵌套的查询方式,一种是通过范围控制分页的最大值...

4246
来自专栏杨建荣的学习笔记

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

2799
来自专栏乐沙弥的世界

INDEX FULL SCAN vs INDEX FAST FULL SCAN

     INDEX FULL SCAN 与 INDEX FAST FULL SCAN两个长相差不多,乃是一母同胞,因此既有其共性,也有其个性。两者来说其共性是...

1162
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

3188
来自专栏杨建荣的学习笔记

关于oracle中的反连接(r3笔记第95天)

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对...

2237
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

28211
来自专栏乐沙弥的世界

SQL 基础--> 视图(CREATE VIEW)

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

723
来自专栏杨建荣的学习笔记

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

3104
来自专栏数据和云

突破常识:SQL增加DISTINCT后查询效率反而提高

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 只要增加了DISTINCT关键字,Orac...

3506
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第二篇)(r3笔记第82天)

在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况,已经初步根据awr能...

3547

扫码关注云+社区

领取腾讯云代金券