Oracle LOB 临时段占用 TEMP 排查实战：从 99% 使用率到应用释放临时 LOB

大家好，我是睿。

最近遇到一个很有代表性的 Oracle TEMP 临时表空间问题：客户反馈数据库 TEMP 使用率持续升高，一开始看上去还不算严重，第二天其中一个实例直接到了 99%。

这类问题很容易被简单理解成 “临时表空间满了，加文件就行”。但生产环境里，TEMP 涨高只是现象，真正要看的不是单纯加多大，而是谁在用、用的是什么类型的临时段、为什么会一直占着不释放。

这次排查到最后，问题和业务 SQL 访问 LOB/XML 字段有关，更准确地说，是临时 LOB 没有被应用侧及时释放，导致 TEMP 中持续存在 LOB 相关临时段。

一、故障现象

某业务系统客户反馈： Oracle TEMP 临时表空间使用率偏高。

先通过下面 SQL 查看各实例 TEMP 使用率：

select inst_id,

        trunc(

          sum(a.bytes_used / 1024 / 1024 / 1024)

          / sum(a.bytes_cached / 1024 / 1024 / 1024) * 100

        ) || '%' as temp_usage

from gv$temp_extent_pool a

group by inst_id;

 

第一次查询结果大概是：

INST_ID  TEMP_USAGE

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

1        56%

2        1%

 

当时看，实例 1 的 TEMP 使用率虽然偏高，但还没有到非常危险的程度。这里先给客户解释了一个容易误解的点：

TEMP 中的临时段不一定会在 SQL 执行完后立刻归还给操作系统。

Oracle 会缓存和复用临时区间，空间可能仍显示为已使用或已缓存。

判断是否真的有会话占用，不能只看 TEMP 文件大小，还要结合 GV$SORT_USAGE / GV$TEMPSEG_USAGE 。

 

也就是说， TEMP 文件层面的“使用率高”，不一定等于当前有 SQL 正在疯狂消耗 TEMP；但如果 GV$SORT_USAGE 里确实有大量记录，那就需要继续定位会话和 SQL。

二、第二天 TEMP 到 99%

第二天客户继续反馈 TEMP 问题，再查结果变成：

INST_ID  TEMP_USAGE

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

1        99%

2        1%

 

这时就不能只解释 “TEMP 可复用”了，必须继续看当前到底有哪些会话在占用 TEMP。

先查 TEMP 使用会话，并生成可执行的断开会话语句：

select 'alter system disconnect session '''

        || se.sid || ',' || se.serial# || ',@' || se.inst_id

        || ''' immediate;' as kill_sql,

        se.username,

        se.sid,

        se.serial#,

        se.sql_address,

        se.inst_id,

        se.sql_id,

        su.blocks * 8 / 1024 / 1024 as temp_gb,

        se.status,

        se.machine,

        se.program,

        su.tablespace,

        su.segtype,

        su.contents

from gv$session se

join gv$sort_usage su

   on su.session_addr = se.saddr

  and su.inst_id = se.inst_id

where se.status = 'INACTIVE'

order by temp_gb desc;

 

这里有几个字段要重点看：

1. TEMP_GB ：当前会话占用的 TEMP 大小。

2. STATUS ：会话是 ACTIVE 还是 INACTIVE 。

3. SQL_ID ：对应 SQL 的 ID，用于后续查 SQL 文本和历史。

4. SEGTYPE ：临时段类型，比如 SORT 、 HASH 、 LOB_DATA 等。

5. CONTENTS ：临时段内容类型。

如果只是普通排序或 hash join 消耗 TEMP，优化方向可能是执行计划、SQL 改写、PGA、统计信息等。

但如果看到 SEGTYPE 里出现 LOB_DATA ，方向就不一样了：这往往意味着 SQL 或应用正在产生临时 LOB。

三、定位占用 TEMP 的 SQL

为了看全量情况，可以不限定 INACTIVE ，直接按 TEMP 占用倒序查：

select se.username,

        se.sid,

        se.serial#,

        se.sql_address,

        se.inst_id,

        se.sql_id,

        su.blocks * 8 / 1024 / 1024 as temp_gb,

        se.status,

        se.machine,

        se.program,

        su.tablespace,

        su.segtype,

        su.contents

from gv$session se

join gv$sort_usage su

   on su.session_addr = se.saddr

  and su.inst_id = se.inst_id

order by temp_gb desc;

 

现场最终定位到一个业务 SQL，占用 TEMP 较高。原始 SQL_ID 已脱敏，下面用 <SQL_ID> 代替：

SQL_ID = <SQL_ID>

 

再查 SQL 文本：

select sql_id,

        sql_text

from gv$sql

where sql_id = '<SQL_ID>';

 

原始业务 SQL 中涉及 XML 字段，字段类型属于 LOB。脱敏后的结构大概如下：

select id,

        code,

        name,

        branch_code,

        seq,

        state,

        modify_user,

        modify_time,

        xml_payload

from app_user.app_xml_config

where id = :1;

 

这里的 XML_PAYLOAD 是一个 XML/LOB 类型字段。后续分析也证明，问题和业务 SQL 对 LOB 字段的访问方式有关。

四、为什么 LOB 会占用 TEMP？

有两类关键信息：

第一类来自 MOS 关于 XMLType / Temporary LOB 的说明。核心意思可以概括为：

当服务端生成 Temporary LOB 并返回给客户端后，

服务端无法准确判断客户端什么时候真正用完这个 LOB 。

因此， Oracle 不会总是主动替客户端关闭或清理临时 LOB 。

如果客户端不主动释放，临时 LOB 可能一直保留到会话结束或生命周期清理发生。

 

简单说， LOB 和普通标量字段不太一样。

普通字段查完结果，客户端取走值，服务端基本可以结束对应资源生命周期。但 LOB 可能是 locator，也可能涉及流式读取。服务端把临时 LOB 交给客户端后，并不知道客户端是马上读完，还是后面还要继续读写。

所以，对于返回到 JDBC、OCI 或其他客户端程序的临时 LOB，应用侧要承担释放责任。

第二类信息来自 JDBC 临时 LOB 释放相关说明。常见做法包括：

1. 使用 Java 标准 LOB 接口，例如 java.sql.NClob.free()

2. 或者显式调用 DBMS_LOB.FREETEMPORARY

 

示例思路如下：

NClob clob = resultSet.getNClob("XML_PAYLOAD");

try {

     // 读取和处理 CLOB 内容

} finally {

     if (clob != null) {

         clob.free();

     }

}

 

如果是服务端临时 LOB，也可以看到类似思路：

begin

   dbms_lob.freetemporary(:lob_value);

end;

/

 

当然，具体怎么释放要看应用语言、驱动版本、框架封装和连接池行为。 DBA 能定位问题，但根因修复通常要应用开发配合。

五、最后的重点： wm_concat 和 LOB_DATA

核心：

使用 wm_concat(LOB 字段 ) 时，会根据 LOB 字段大小占用不同大小的 LOB TEMP 。

 

只要使用了能够返回 LOB 结果的函数，即使传入列本身不是 LOB 字段，也可能在 TEMP 中产生 LOB_DATA 。

 

例如 wm_concat(id) ，不管 id 列是否为 LOB 字段，只要函数返回结果走了 LOB 临时对象，就可能占用 TEMP 中的 LOB_DATA 。

 

针对 11g 上 LOB 类型 TEMP 不释放的情况，可以考虑设置事件 60025 ，使后续临时 LOB 在合适时机及时释放。

 

5.1 不是只有 LOB 列才会产生临时 LOB

很多人会误以为：只有查询 CLOB/BLOB/XMLType 字段才会产生临时 LOB。

实际不是这样。

有些函数本身会返回 LOB 或内部使用临时 LOB 承载结果。例如聚合拼接、XML 序列化、XMLType 处理、某些大字符串拼接，都可能在 TEMP 中产生 LOB_DATA 。

所以排查时不要只看表字段类型，还要看 SQL 里是否有这些函数或表达式：

select wm_concat(xml_payload)

from app_user.app_xml_config;

 

或者类似：

select xmlserialize(content xml_payload as clob)

from app_user.app_xml_config;

 

更隐蔽的是，有时候业务写的是普通字段：

select wm_concat(id)

from app_user.app_xml_config;

 

如果函数返回或中间处理使用了临时 LOB，也可能在 TEMP 中看到 LOB_DATA 。

5.2 60025 事件解决什么问题？

事件 设置 ：

alter session set events '60025 trace name context forever';

 

通过登录触发器为新会话设置：

create or replace trigger sys.login_set_60025

after logon on database

begin

   execute immediate

     'alter session set events ''60025 trace name context forever''';

end;

/

 

这类做法的目标是：让某些版本或场景下提交后没有及时释放的临时 LOB，在后续生命周期中更及时地释放。

但这里一定要注意：

60025 不是 SQL 优化器参数，也不是通用性能开关。

它属于事件类设置，是否适合使用，要看数据库版本、补丁、 MOS 说明和测试验证。

 

更关键的是，事件通常只影响设置之后的新会话或后续执行。已经在设置之前占用了临时 LOB 的会话，不会因为你现在设置事件就立刻释放。

这里强调的点：

如果某个会话在设置事件之前已经占用了 LOB TEMP ，

那么该会话已经占用的 LOB TEMP 不会立刻释放。

后续再次执行使用临时 LOB 的语句时，才可能按新的事件行为及时释放。

 

所以， 60025 只能算一种经过验证后的辅助方案，不是根因修复。

六、如何确认 SQL 是什么时候上线的？

定位到 <SQL_ID> 后，现场还通过 AWR/ASH 查询该 SQL 最早出现的采样时间，用于判断问题从什么时候开始出现。

SQL 如下：

select min(sample_time)

from (

   select sample_time,

          session_id,

          session_serial#,

          sql_id,

          temp_space_allocated / 1024 / 1024 as temp_mb,

          temp_space_allocated / 1024 / 1024

            - lag(temp_space_allocated / 1024 / 1024, 1, 0)

              over (order by sample_time) as temp_diff

   from dba_hist_active_sess_history

   where sql_id = '<SQL_ID>'

   order by sample_time desc

) a;

 

示例结果：

MIN(SAMPLE_TIME)

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

202 6 -0 8 - 08 08:41:55.250

 

这个时间点很有价值。它可以帮助我们和业务侧确认：

6. 这个 SQL 是不是近期上线或改版后出现的。

7. TEMP 问题是否和某次版本发布、任务调度、接口调用有关。

8. 是否可以从应用日志或发布记录里找到对应变更。

排查 TEMP 问题时，不要只盯着当前会话。能把 SQL 上线时间、调用频率、TEMP 增长趋势串起来，才更容易让业务方接受根因判断。

七、临时缓解：清理高 TEMP 占用会话

根因处理需要应用释放临时 LOB，但现场往往不能等代码马上上线。为了避免 TEMP 持续打满，可以先做临时缓解：定时清理 TEMP 占用较高的 INACTIVE 会话。

注意，这是缓解，不是根治。

生成断开会话语句的 SQL 可以写成：

select 'alter system disconnect session '''

        || sid || ',' || serial# || ',@' || inst_id

        || ''' immediate;' as kill_sql

from (

   select se.username,

          se.sid,

          se.serial#,

          se.inst_id,

          se.sql_id,

          su.blocks * 8 / 1024 / 1024 as temp_gb,

          se.status,

          se.machine,

          se.program,

          su.tablespace,

          su.segtype,

          su.contents

   from gv$session se

   join gv$sort_usage su

     on se.saddr = su.session_addr

    and se.inst_id = su.inst_id

   where se.status = 'INACTIVE'

     and su.blocks * 8 / 1024 / 1024 >= 1

   order by temp_gb desc

)

where rownum <= 10;

 

如果确实要做定时任务，脚本里不要写真实业务账号密码。可以优先考虑：

9. 操作系统认证。

10. Oracle Wallet。

11. 受控的只读 /运维账号。

12. 权限最小化。

13. 日志记录清楚每次断开的会话。

下面是脱敏后的脚本模板：

#!/bin/bash

 

export ORACLE_HOME=/path/to/oracle/home

export ORACLE_SID=<INSTANCE_NAME>

export PATH=$ORACLE_HOME/bin:$PATH

 

LOG_FILE=/path/to/log/kill_temp_sessions.log

 

KILL_SESSIONS=$(sqlplus -s / as sysdba <<'SQL'

set pagesize 0

set linesize 200

set feedback off

set heading off

 

select 'alter system disconnect session '''

        || sid || ',' || serial# || ',@' || inst_id

        || ''' immediate;'

from (

   select se.sid,

          se.serial#,

          se.inst_id,

          su.blocks * 8 / 1024 / 1024 as temp_gb

   from gv$session se

   join gv$sort_usage su

     on se.saddr = su.session_addr

    and se.inst_id = su.inst_id

   where se.status = 'INACTIVE'

     and su.blocks * 8 / 1024 / 1024 >= <TEMP_GB_THRESHOLD>

   order by temp_gb desc

)

where rownum <= <MAX_SESSION_COUNT>;

SQL

)

 

echo "$(date '+%F %T') generated commands:" >> "$LOG_FILE"

echo "$KILL_SESSIONS" >> "$LOG_FILE"

 

echo "$KILL_SESSIONS" | while IFS= read -r line; do

   if [ -n "$line" ]; then

     echo "$(date '+%F %T') execute: $line" >> "$LOG_FILE"

     sqlplus -s / as sysdba <<SQL >> "$LOG_FILE" 2>&1

$line

SQL

   fi

done

 

Crontab 示例：

00 01,09,17 * * * sh /path/to/kill_temp_sessions.sh >> /path/to/log/kill_temp_sessions.cron.log 2>&1

 

这里我建议至少加三个保护：

14. 阈值不要太低，比如先从几 个 GB 级别开始。

15. 限制每次最多处理的会话数。

16. 每次执行都记录 SID 、 SERIAL# 、 INST_ID 、 SQL_ID 、 TEMP_GB 。

直接定时杀会话是有风险的。 ALTER SYSTEM DISCONNECT SESSION ... IMMEDIATE 会中断目标会话，可能触发事务回滚、应用报错或连接池重连。执行前必须和业务确认窗口和影响范围。

八、最终建议

这次问题的处理思路可以概括为：

TEMP 使用率升高

   -> 查看 GV$TEMP_EXTENT_POOL 判断整体趋势

   -> 查看 GV$SORT_USAGE / GV$TEMPSEG_USAGE 定位会话

   -> 关注 SEGTYPE 是否为 LOB_DATA

   -> 通过 SQL_ID 查 SQL 文本

   -> 发现 SQL 涉及 XML/LOB 字段或返回 LOB 的函数

   -> 结合 ASH 判断上线时间和调用时间

   -> 应用侧释放临时 LOB

   -> 必要时临时清理高占用 INACTIVE 会话

   -> 评估 60025 事件是否适合当前版本

 

这次案例最有价值的点，不是某条 SQL 怎么写，而是排查方向的变化：

从 “ TEMP 使用率高”

转到 “哪个会话占用 TEMP ”

再转到 “占用的临时段类型是 LOB_DATA ”

最后落到 “应用访问 LOB 后没有及时释放临时 LOB ”

 

DBA 做这种问题排查，最怕停留在表面现象。TEMP 满了可以扩，session 可以杀，但如果应用一直产生临时 LOB 又不释放，问题迟早还会回来。

好了，今天这篇就到这里。你在生产环境里遇到过 LOB 或 XMLType 导致 TEMP 暴涨吗？最后是 SQL 改写、应用释放 LOB，还是通过事件和会话清理缓解的？欢迎在评论区聊聊你的处理经验。

—— 睿 | Oracle性能优化老司机

专注硬核干货，欢迎一起卷技术！