生产环境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

------------------------------------------------------------------------------------------------------------------------------

| 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

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'

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

--------------------------------------------------------------------------------------------------------------------

| 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秒左右。

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

继续阅读

Java小案例——随机数猜测随机数猜测

nginx location中斜线的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

sqlServer根据经纬查距离

Effective Java 8:通用程序设计

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method