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

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

时间： 2024-09-07 09:48:52

生产环境sql语句调优实战第四篇的相关文章

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

陆陆续续写了九篇关于生产环境sql语句的调优案例,发现了不少问题,可能有些问题回头来看是比较低级的错误,稍加改动就能够运行在秒级,有些可能是在秒级到毫秒级的小步提升等等,不管调优的改进多大,从dba的角度来看,好多问题都是基于资源来调优的,比如添加索引,降低IO,降低CPU消耗,提高CPU利用率等等.如果有时候从业务角度来下下功夫,可能某种程度上效果要更好于基于资源/代价的调优. 最近客户反馈有几条sql语句IO消耗很高,希望我们能够给提点建议. sql语句很短,但是运行时间在9秒左右.运行频率

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

生产环境中的sql语句执行时间是很关键的性能指标,如果某个sql语句执行几个小时,优化以后几分钟,几十秒的话.会有很大的成就感,同时如果某个sql语句执行10秒,能够优化到1秒,感觉提升的幅度不是很大,但是如果这条语句执行极为频繁的话,那这种调优还是更有成就感的. 执行时间是sql调优的一把标尺,但是同时也需要考虑到系统资源的平衡. 今天在系统中发现一条sql语句执行时间很长.平均一个查询要执行一个半小时左右,而且系统的资源消耗极大. 需要说明的service_details 是数据量过亿的表.

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

生产环境中有大量的sql语句在运行,尽管有awr,ash做数据的收集统计,但是dba的调优工作大多数情况都是在问题已经发生后做排查的,有些sql语句可能执行的时间有1,2分钟左右,但是sql语句本身有潜在的性能问题,通过awr是定位不到的,ash尽管能够查到,但是我们在未知的情况下怎么知道问题发生的精确时间点,通过sql monitor能够查到一些实时的性能问题,但是还是需要按照自己的情况和要求来不间断地进行性能的监控.通过一个工具一劳永逸是不现实的. 今天想做数据迁移也有些日子了,看看生产环境

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

在数据迁移完成之后,开始了例行的后期数据库维护,早上一来就发现了一个sql执行时间很长了.达到了37279秒.最后在改进调优之后执行速度在1分钟以内. 这个速度是毫无疑问的性能问题,但是是否是因为数据迁移直接导致的呢,通过简单的脚本分析,得出了如下的图表. 显示了同样的sql语句在7月份至今的执行情况,还真是奇怪,昨天以前一直正常,期间也进行过两次数据迁移,昨天的数据迁移完成以后,性能就出奇的差. 通过sql_monitor得到了执行计划和对应的sql语句. 可以看到执行时间是昨天的下午,一直执

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

生产环境有一条sql语句执行比较频繁,占用了大量的cpu资源.原本执行需要花费11秒.在一次排查中引起了我的注意,决定看看cpu消耗到底在哪儿? sql语句是比较简单的,通过查询SUBSCRIBER_FA_V是一个视图.在视图中关联了几个和业务核心表. SELECT TO_CHAR(SUBSCRIBER_NO) SUBSCRIBER_ID,        SUB_STATUS,        SUB_STS_RSN_CD,        TO_CHAR(SUB_STATUS_DATE, 'yyy

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

生产环境中有一些sql语句是不定时炸弹,不声不响的运行着,可能相关的表很大,运行时间达数小时甚至数天. 上周在生产环境中发现一条sql语句,运行时间几乎是按照天来计算的.看到这种sql语句,就希望能够马上能够找到立竿见影的效果,不过欲速则不达,需要做不少工作.一定要反复验证可行. sql语句的运行情况如下: SQL Execution ID : 16777217  Execution Started : 10/18/2014 11:46:30  First Refresh Time : 10/1

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

在生产环境通过sql monitor监控到有一条sql执行效率很差.执行了大约5个小时,得到的sql monitor报告如下: Global Information: EXECUTING Instance ID : 1 Buffer Gets IO Requests Database Time Wait Activity . 40M . 17M . . 8450s . . . 100% Session : PRODUSER(14:22343) SQL ID : fkzafpjs28d6d SQL

生产环境大型sql语句调优实战第一篇（一）

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时.sql语句比较长,需要点耐心往下看.我对表的数据量都做了简单的说明. 首先拿到sql语句,一看到关联的表有一长串,而且都是很大的表.如果性能很差,可能和走全表扫描有关,还有可能和多表关联时,表的查取顺序也有一定的关系. SELECT   DISTINCT CA.L9_CONVERGENCE_CODE AS ATB2,                 CU.CUST_SUB_TYP

生产环境大型sql语句调优实战第一篇（二）

继续昨天的部分,上一篇的链接为: http://blog.itpub.net/23718752/viewspace-1217012/ 对这条大sql的性能瓶颈进行了分析.主要瓶颈在于一个很大的业务表,数据量在亿级.如果通过时间条件来过滤,会有5%以内的数据被过滤出来. 但是没有时间相关的索引字段,所以会走全表扫描,在目前的产品线中,这个大分区表的索引时严格控制的,所以最后经过测试和比对,还是考虑加并行来提高数据的查取速度. --查找性能瓶颈,根据反馈,查取的数据其实并不错,可能在几千条以内的样子