oracle标量子查询的优势,oracle 标量子查询惹得祸

最新推荐文章于 2024-03-28 01:53:57 发布

weixin_39883670

最新推荐文章于 2024-03-28 01:53:57 发布

阅读量149

点赞数

文章标签： oracle标量子查询的优势

本文通过一个具体的案例展示了标量子查询导致数据库负载激增的问题，并提供了优化后的SQL语句及执行计划，对比了优化前后的差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

标量子查询惹的祸

今天刚连上数据库，习惯性的查看了top ,发现数据库的负载比平时大了近两倍多，排前的几个sql几乎都是消耗了

100% 的cpu.

查看到底在执行什么sql

select a.sql_id,a.SQL_FULLTEXT

from v$sql ahttps://www.cndba.cn/yezhizi_weixb/article/278

join v$session s

on a.SQL_ID = s.PREV_SQL_ID

join v$process p

on s.PADDR = p.ADDR

where p.SPID='pid';

看到sql ,大概知道是什么原因造成的了。

select c.id,https://www.cndba.cn/yezhizi_weixb/article/278

c.vin,

c.collect_time,

c.original_message_id,

c.power_bat_packno,

c.total_cell_bat,

(select sum(total_cell_bat)

from TB_MNT_E_SINGLE_BAT_VOLTAGE v

where v.original_message_id = c.original_message_id) totalCellBatSum,

(select sum(cell_bat_voltage)

from TB_MNT_E_BAT_VOLTAGE_CELL

where single_bat_info_id = c.id) voltage

from TB_MNT_E_SINGLE_BAT_VOLTAGE c

where c.is_valid = 1

and c.collect_time >=

to_date('2014/4/23 14:42:47', 'yyyy-mm-dd hh24:mi:ss') https://www.cndba.cn/yezhizi_weixb/article/278

and c.collect_time <=

to_date('2014/5/30 11:58:42', 'yyyy-mm-dd hh24:mi:ss')

order by c.COLLECT_TIME desc

sql 里面的标量子查询，赶紧通知相关的业务人员，跟他们确认这是什么业务。业务反馈是刚上的新业务。需要重新的修改sql，重新的上线。

对sql 做了一下的改写：

select c.id,

c.vin,

c.collect_time,

c.original_message_id,

c.power_bat_packno,

c.total_cell_bat,

v.total_cell_bat,

d.cell_bat_voltage

from TB_MNT_E_SINGLE_BAT_VOLTAGE c

left join (select v.original_message_id,

sum(total_cell_bat) total_cell_bat

https://www.cndba.cn/yezhizi_weixb/article/278https://www.cndba.cn/yezhizi_weixb/article/278

from TB_MNT_E_SINGLE_BAT_VOLTAGE v

group by v.original_message_id) v

on v.original_message_id = c.original_message_id

left join (select single_bat_info_id,

sum(cell_bat_voltage) cell_bat_voltage

from TB_MNT_E_BAT_VOLTAGE_CELL

group by single_bat_info_id) d

on d.single_bat_info_id = c.id

where c.is_valid = 1

and c.collect_time >=

to_date('2014/4/23 14:42:47', 'yyyy-mm-dd hh24:mi:ss')

and c.collect_time <=

to_date('2014/5/30 11:58:42', 'yyyy-mm-dd hh24:mi:ss')

order by c.COLLECT_TIME desc

Plan Hash Value : 824043275

https://www.cndba.cn/yezhizi_weixb/article/278

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

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

| 0 | SELECT STATEMENT | | 6357 | 508560 | 26144 | 00:05:14 |

| 1 | SORT ORDER BY | | 6357 | 508560 | 26144 | 00:05:14 |

| * 2 | HASH JOIN OUTER | | 6357 | 508560 | 26143 | 00:05:14 |

| * 3 | HASH JOIN OUTER | | 6357 | 394134 | 742 | 00:00:09 |

| * 4 | TABLE ACCESS FULL | TB_MNT_E_SINGLE_BAT_VOLTAGE | 6357 | 279708 | 232 | 00:00:03 |

| 5 | VIEW | | 75948 | 1367064 | 509 | 00:00:07 |

| 6 | HASH GROUP BY | | 75948 | 607584 | 509 | 00:00:07 |

| 7 | TABLE ACCESS FULL | TB_MNT_E_SINGLE_BAT_VOLTAGE | 75948 | 607584 | 232 | 00:00:03 |

https://www.cndba.cn/yezhizi_weixb/article/278

| 8 | VIEW | | 76232 | 1372176 | 25401 | 00:05:05 |

| 9 | HASH GROUP BY | | 76232 | 762320 | 25401 | 00:05:05 |

| 10 | TABLE ACCESS FULL | TB_MNT_E_BAT_VOLTAGE_CELL | 7063164 | 70631640 | 15164 | 00:03:02 |

https://www.cndba.cn/yezhizi_weixb/article/278

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

Predicate Information (identified by operation id):

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

* 2 - access("D"."SINGLE_BAT_INFO_ID"(+)="C"."ID")

* 3 - access("V"."ORIGINAL_MESSAGE_ID"(+)="C"."ORIGINAL_MESSAGE_ID")

* 4 - filter("C"."COLLECT_TIME"<=TO_DATE(' 2014-05-30 11:58:42', 'syyyy-mm-dd hh24:mi:ss') AND "C"."IS_VALID"=1 AND "C"."COLLECT_TIME">=TO_DATE(' 2014-04-23 14:42:47', 'syyyy-mm-dd hh24:mi:ss'))