联系生活来简化sql(r3笔记第43天)

目前生产环境中有一条sql语句的CPU消耗很高。执行时间比较长。从awr中抓到的sql语句如下:

SELECT   run_request.run_mode, cycle_groups.flow_id,
         cycle_groups.request_id, cycle_groups.dynamic_attributes,
         cycle_groups.sys_creation_date, cycle_groups.sys_update_date,
         cycle_control.cycle_code
    FROM (SELECT cycle_groups.*, a.request_id
            FROM (SELECT   cycle_groups.GROUP_ID,
                           cycle_groups.flow_id,
                           cycle_groups.cycle_seq_no,
                           cycle_groups.route,
                           group_status.request_id
                      FROM cycle_groups, --数据量在1万左右
 group_status   --数据量在百万
                     WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID  --这些都是索引列
                       AND cycle_groups.flow_id = group_status.flow_id   --这些都是索引列
                       AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
                       AND cycle_ghttp://blog.itpub.net/23718752/viewspace-1317283/roups.route = group_status.route
                  GROUP BY cycle_groups.GROUP_ID,
                           cycle_groups.flow_id,
                           cycle_groups.cycle_seq_no,
                           cycle_groups.route,
                           group_status.request_id) a,
                 cycle_groups
           WHERE cycle_groups.GROUP_ID = a.GROUP_ID  --这个地方又关联了一次
             AND cycle_groups.flow_id = a.flow_id
             AND cycle_groups.cycle_seq_no = a.cycle_seq_no
             AND cycle_groups.route = a.route) cycle_groups,
         cycle_control,
         run_request
   WHERE cycle_groups.status = 'FIN'
     AND cycle_groups.request_id = run_request.request_id
     AND cycle_control.cycle_seq_no = cycle_groups.cycle_seq_no
     AND    cycle_control.cycle_code
         || '_'
         || cycle_groups.flow_id
         || '_'
         || run_request.run_mode IN (
            SELECT    cycle_control.cycle_code
                   || '_'
                   || run_request.flow_id
                   || '_'
                   || run_request.run_mode
              FROM run_request, cycle_control
             WHERE (   run_request.population_type = 'CYC'
                    OR run_request.population_type = 'CCD'
                   )
               AND run_request.population_id =cycle_control.cycle_seq_no)
ORDER BY cycle_groups.request_id

从第一印象来看,sql语句有些臃肿,过滤条件也比较奇怪。 首先是标黄的部分,关联的连接条件都是索引列. 执行计划如下,可以看到还是消耗很大的。

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |       |       |       |   273K(100)|          |       |       |
|   1 |  SORT ORDER BY               |                       |  5714K|  1111M|  1174M|   273K  (1)| 00:54:46 |       |       |
|*  2 |   HASH JOIN                  |                       |  5714K|  1111M|       | 19993   (1)| 00:04:00 |       |       |
|   3 |    VIEW                      | VW_NSO_1              |   423 | 13959 |       |    28  (11)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE              |                       |   423 | 12690 |       |    28  (11)| 00:00:01 |       |       |
|*  5 |      HASH JOIN               |                       |  6992 |   204K|       |    26   (4)| 00:00:01 |       |       |
|   6 |       VIEW                   | index$_join$_009      |  2157 | 17256 |       |     3  (34)| 00:00:01 |       |       |
|*  7 |        HASH JOIN             |                       |       |       |       |            |          |       |       |
|   8 |         INDEX FAST FULL SCAN |     CYCLE_CONTROL_PK  |  2157 | 17256 |       |     1   (0)| 00:00:01 |       |       |
|   9 |         INDEX FAST FULL SCAN |     CYCLE_CONTROL_1IX |  2157 | 17256 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |       TABLE ACCESS FULL      |     RUN_REQUEST       |  7641 |   164K|       |    23   (0)| 00:00:01 |       |       |
|* 11 |    HASH JOIN                 |                       |  1350K|   220M|       | 19944   (1)| 00:04:00 |       |       |
|  12 |     TABLE ACCESS FULL        |     RUN_REQUEST       |  7735 | 92820 |       |    23   (0)| 00:00:01 |       |       |
|* 13 |     HASH JOIN                |                       |  1350K|   204M|       | 19916   (1)| 00:03:59 |       |       |
|  14 |      JOIN FILTER CREATE      | :BF0000               | 12436 |  1590K|       |    70   (2)| 00:00:01 |       |       |
|* 15 |       HASH JOIN              |                       | 12436 |  1590K|       |    70   (2)| 00:00:01 |       |       |
|  16 |        VIEW                  | index$_join$_006      |  2157 | 17256 |       |     3  (34)| 00:00:01 |       |       |
|* 17 |         HASH JOIN            |                       |       |       |       |            |          |       |       |
|  18 |          INDEX FAST FULL SCAN|     CYCLE_CONTROL_PK  |  2157 | 17256 |       |     1   (0)| 00:00:01 |       |       |
|  19 |          INDEX FAST FULL SCAN|     CYCLE_CONTROL_1IX |  2157 | 17256 |       |     1   (0)| 00:00:01 |       |       |
|* 20 |        TABLE ACCESS FULL     |     CYCLE_GROUPS      | 13072 |  1570K|       |    67   (0)| 00:00:01 |       |       |
|  21 |      VIEW                    |                       |  1426K|    38M|       | 19840   (1)| 00:03:59 |       |       |
|  22 |       HASH GROUP BY          |                       |  1426K|    69M|    87M| 19840   (1)| 00:03:59 |       |       |
|  23 |        JOIN FILTER USE       | :BF0000               |  1426K|    69M|       |  1746   (1)| 00:00:21 |       |       |
|  24 |         NESTED LOOPS         |                       |  1426K|    69M|       |  1746   (1)| 00:00:21 |       |       |
|  25 |          PARTITION RANGE ALL |                       |  1426K|    38M|       |  1740   (1)| 00:00:21 |     1 |    19 |
|  26 |           INDEX FULL SCAN    |     GROUP_STATUS_PK   |  1426K|    38M|       |  1740   (1)| 00:00:21 |     1 |    19 |
|* 27 |          INDEX UNIQUE SCAN   |     CYCLE_GROUPS_PK   |     1 |    23 |       |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

这个调优可以从一个生活的例子来简单说明。 我平时早餐吃面包,就举吃面包的例子吧。 比如我在所住的校区门口有家面包店,每次我买面包都会得到一个收据,上面有所买的面包明细和卡号,还有一个流水号。 在公司楼下也有一个面包店,都可以用同一张卡来消费,也会得到一个流水号。 可以关联起来,表cycle_groups就类似一个客户清单,清单中的每一项就代表一个客户卡号。而表group_status就代表消费的明细,比如流水号,所卖的面包和所消费的金额时间等。 cycle_group里只有卡号等信息,在cycle_status中是消费的数据。 这个时候我们想得到某张卡号 消费的情况,比如卡号,年月日,消费的金额,如果流水号相同只输出一个流水号。 这个操作就类似下面的形式。

SELECT   cycle_groups.GROUP_ID,
                           cycle_groups.flow_id,
                           cycle_groups.cycle_seq_no,
                           cycle_groups.route,
                           group_status.request_id
                      FROM cycle_groups, --数据量在1万左右
 group_status   --数据量在百万
                     WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID  --这些都是索引列
                       AND cycle_groups.flow_id = group_status.flow_id   --这些都是索引列
                       AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
                       AND cycle_groups.route = group_status.route
                  GROUP BY cycle_groups.GROUP_ID,
                           cycle_groups.flow_id,
                           cycle_groups.cycle_seq_no,
                           cycle_groups.route,
                           group_status.request_id

而这个时候我们得到了一个卡号,消费年月日,消费流水号的清单,流水号相同的情况下,可以只得到50万条记录。 这个时候我们想给予目前的统计结果,得到卡号,消费的门店,消费金额,消费流水号时,这个时候就相当于根据流水号把表cycle_status里面的数据又重新查取了一遍。 这个时候你想想,还不如直接关联卡号和消费记录来查取一次呢。因为,在子查询里过滤了流水号,只显示一条不重复的流水号,但是外部查询中又需要得到更为详细的信息,导致过滤了一次数据,然后又重新还原了一遍。

SELECT cycle_groups.*, a.request_id
            FROM (SELECT   cycle_groups.GROUP_ID,
                           cycle_groups.flow_id,
                           cycle_groups.cycle_seq_no,
                           cycle_groups.route,
                           group_status.request_id
                      FROM cycle_groups, --数据量在1万左右
 group_status   --数据量在百万
                     WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID  --这些都是索引列
                       AND cycle_groups.flow_id = group_status.flow_id   --这些都是索引列
                       AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
                       AND cycle_groups.route = group_status.route
                  GROUP BY cycle_groups.GROUP_ID,
                           cycle_groups.flow_id,
                           cycle_groups.cycle_seq_no,
                           cycle_groups.route,
                           group_status.request_id) a,
                 cycle_groups
           WHERE cycle_groups.GROUP_ID = a.GROUP_ID  --这个地方又关联了一次
             AND cycle_groups.flow_id = a.flow_id
             AND cycle_groups.cycle_seq_no = a.cycle_seq_no
             AND cycle_groups.route = a.route


所以可能自己想了很多的思路,但是最后还是又把问题回归到原点。

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

原文发表时间:2014-11-01

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏landv

一、K3 WISE 开发插件《K3 WISE常用数据表整理》

4157
来自专栏Grace development

电商系统设计之商品 (下)

完成上述流程则是完成了一笔交易,经常网上购物的童鞋都懂这个。今天我们讲下从商品系统到交易系统和订单系统的存储过程及其设计上的应该注意的“坑”。

9932
来自专栏数据和云

元宵佳节:看Oracle技术粉们用SQL画团圆

话团圆,画团圆,元宵佳节倍思亲,可是大家知道吗,万能的SQL可以帮助大家绘制团圆。 在ITPUB论坛里,一群SQL爱好者们会用SQL来描摹一切可能。请看如下这段...

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

oracle中关于小数中0的格式化(55天)

今天碰到一个小问题,分享一下。 oracle中输入0.1查出的时候是.1,现在想把结果格式化成varchar2,格式化成0.10,保留两位精度。 先拿大于1的数...

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

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

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

2474
来自专栏JetpropelledSnake

SQL学习笔记之MySQL查询练习2

github地址: https://github.com/nql1314/sql-practises

872
来自专栏Grace development

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下

1882
来自专栏程序员宝库

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下:

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

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

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

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

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

4094

扫码关注云+社区

领取腾讯云代金券