Oracle MERGE INTO 误用导致TEMP表空间爆满

原创 bicewow All China Database Union 2025-01-20

676

业务背景

业务部门反应，9点30左右，业务跑批量报表逻辑时，报错表空间不足，需要添加表空间，如下是报错截图。

问题验证

在业务部门反应后，立刻查看了数据库日志，确实有相关表空间不足的报错，不过是TEMP表空间不足。

问题分析

针对业务部门的反馈，如果是普通表空间不足，给他立刻加上，那是毫无疑问的。

但根据业务发的报错截图和数据库日志可以发现，这是TEMP表空间不足，虽然说现在给添加上，可能临时解决表空间不足的问题，也可能解决不了，取决于SQL逻辑，以及操作的数据量大小（这可以说是个无底洞）。

简单梳理了处理思路：

1、和业务沟通说明报错原因，以及TEMP表空间的作用

2、和业务沟通后，得知此报表不是立刻需要，晚点没关系，这就给排查问题预留了时间

3、定位问题SQL，具体分析，如无调优空间，在扩展临时表空间不迟

在有了以上沟通的前提下，开始定位问题。

问题排查

定位问题SQL

问题出现时间为9点30分左右，通过查询历史会话视图，很容易就定位到相关SQL，在对应时间段内，只有此SQL使用TEMP较多，将SQL拿给业务人员，确认后，为其业务逻辑一部分。

历史会话视图定位SQL如下

SQL脱敏如下

MERGE /*+ parallel(10) */
INTO TAR_A T01
USING (SELECT khh from SRC_TMP ) T02
ON (T01.khh = T02.khh)
WHERE MATCHED THEN
  UPDATE SET T01.YYB='008' WHERE RQ=20250116;
复制

可以看出，使用MERGE INTO语法，而且加了10个并行，目测TAR_A和SRC_TMP数据量不会小。

SQL相关表分析

根据抓取的SQL，首先分析SQL中，相关表的基础信息，包含数据量，行数，索引之类。

表 TAR_A

数据量 66G，行数10亿索引2个，且为分区表

数据量

行数

索引

分区表

表 SRC_TMP

数据量 4M，行数16.5万无索引

数据量

行数

从搜集的表信息上看，表 TAR_A较大，表 SRC_TMP数据也不少，SQL属于等值连接，走hashjoin的可能比较大，接下来看看执行计划。

SQL执行计划

这里使用explain plan for 查询（虽说不准，但可以参考），看到执行计划后，也是大吃一惊，竟然走出了Nested loop的计划，这么大的数据量，明显计划是不对的，而且分区表显示扫描了所有分区，然而SQL中是看到有RQ限制的，应该直接可以定位指定分区，有点疑惑。

思考与沟通

根据以上排查的信息，得出下面几个思考点：

1、SQL执行计划走Nested loop 可能与统计信息不准有关
2、根据执行计划显示扫描了所有分区，观察SQL MERGE INTO有RQ限制，但RQ限制的位置不对
3、SRC_TMP 无索引，可在KHH上创建索引

下面针对各个思考点进行逐个解决（数据都有备份，且不影响现有环境），可直接实施。

思考点 1

搜集表 TAR_A较大，表 SRC_TMP的统计信息。

思考点 2

与业务部门沟通，他们对于MERGE INTO使用存在误解，他们需要实现的效果也只是更新《20250116》一天的数据即可，却把RQ写在了UPDATE处，虽说数据逻辑没有问题，但数据库扫描的数据量天差地别。所以调整SQL如下。

MERGE 
INTO TAR_A T01
USING (SELECT khh from SRC_TMP ) T02
ON (T01.khh = T02.khh and T01.RQ=20250116)
WHERE MATCHED THEN
  UPDATE SET T01.YYB='008';
复制

注：去掉了并行。