生产中有一条sql语句消耗了大量的cpu资源，执行时间在18秒左右，
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
| 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秒左右。
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秒左右。
