oracle的临时表空间无法释放！多半是blob害的。

原创史天赐云和恩墨 2022-06-20

8615

适用范围

oracle清除temp表空间,Temp表空间占用长时间不释放；

问题概述

Oracle临时表空间主要是用于数据库较大的临时排序用，在PGA分配的工作区空间不足以容纳排序数据时使用临时表空间。在用户执行的排序相关的语句执行完毕后临时段回收，相应的临时空间占用也会自动释放。但针对LOB所产生的临时段的释放不会遵从些机制。

问题原因

临时表空间使用率过高带来的影响：由于一般业务中都会存在大量排序的sql(如distinct、order by、group by、union、union all、hash join等)，所以一旦临时表空间长时间使用率过高将导致核心sql因ORA-1652错误而终止，最终导致业务处理失败，如果是核心业务那么这个影响是巨大的。
遇到的问题某核心OLTP生产系统最近发现临时表空间占用高达90%多，且持续监控发现一直不释放，而且还在缓慢增长
select c.tablespace_name,to_char(c.bytes/1024/1024/1024,‘99,999.999’) total_gb,to_char( (c.bytes-d.bytes_used)/1024/1024/1024,‘99,999.999’) free_gb,to_char(d.bytes_used/1024/1024/1024,‘99,999.999’) use_gb,to_char(d.bytes_used*100/c.bytes,‘99.99’) || '%'use from (select tablespace_name,sum(bytes) bytes from dba_temp_files GROUP by tablespace_name) c,(select tablespace_name,sum(bytes_cached) bytes_used from v$temp_extent_pool GROUP by tablespace_name) d where c.tablespace_name = d.tablespace_name;
TABLESPACE_NAME TOTAL_GB FREE_GB USE_GB USE

TEMP 111.999 110.101 1.898 1.70%
TEMP1 80.000 .107 79.893 99.87%
原因查找80G的临时表空间使用了79G，这么大的临时表空间使用率在一个正常的OLTP系统中是很罕见的。
1、首先查看活动会话数，如下显示活动会话数总共27个，是很正常的，可以肯定临时表空间占用较高的原因并非由大量需要排序的异常会话引起，常见的这个原因可以排除。
INST_ID RNAME CURRENU MAXU INALLOCATION LIMIT_VALUE

     1 processes                             135        206       2000            2000
     1 sessions                              152        274       3024            3024
     2 processes                             104        364       2000            2000
     2 sessions                              118        455       3024            3024
复制

2、定位哪些进程对应的哪些语句占用了较多临时表空间，经检查发现如下：
SQL> select a.username,a.sql_id,a.SEGTYPE,b.BYTES_USED/1024/1024/1024||‘G’ ,b.BYTES_FREE/1024/1024/1024 from V$TEMPSEG_USAGE a join V$TEMP_SPACE_HEADER b on a.TABLESPACE=b.tablespace_name;

USERNAME SQL_ID SEGTYPE B.BYTES_USED/1024/1024/1024||‘G’ B.BYTES_FREE/1024/1024/1024

MSS_ODS_M d5dfxy83f07cq LOB_DATA 19.99999237060546875G 0
MSS_ODS_M d5dfxy83f07cq LOB_DATA 19.99999237060546875G 0
MSS_ODS_M d5dfxy83f07cq LOB_DATA 19.99999237060546875G 0
MSS_ODS_M d5dfxy83f07cq LOB_DATA 19.99999237060546875G 0
MSS_ODS_M 9d2nz13x276fg LOB_DATA 19.99999237060546875G 0
MSS_ODS_M 9d2nz13x276fg LOB_DATA 19.99999237060546875G 0
MSS_ODS_M 9d2nz13x276fg LOB_DATA 19.99999237060546875G 0
MSS_ODS_M 9d2nz13x276fg LOB_DATA 19.99999237060546875G 0
MSS_ODS_M d5dfxy83f07cq LOB_DATA 19.99999237060546875G 0
MSS_ODS_M d5dfxy83f07cq LOB_DATA 19.99999237060546875G 0
MSS_ODS_M d5dfxy83f07cq LOB_DATA 19.99999237060546875G 0

从上面可以看出有10多个进程占用了大约80多G的临时表空间，且显示为LOB_DATA，说明正是这10个进程导致的临时表空间被占满且不释放，经连续多次观察这10多个进程执行的为sql_id为d5dfxy83f07cq 的同一个sql。该sql语句如下：
SQL> select sql_text from v$sql where sql_id=‘d5dfxy83f07cq’;

SQL_TEXT

SELECT CASE WHEN rw.TYPE=‘02’ THEN ‘’ WHEN rw.TYPE=‘03’ THEN ‘ʩλ’ WHEN rw.TYPE=‘04’ THEN ‘’ WHEN rw.TYPE=‘05’ THEN ‘λ’ ELSE ‘’ END serviceName, bp.PROJCODE projectId, nvl(rw.SENDFEE,0) serviceSubmitAmount, nvl(rw.AUDITFEE,0) serviceAuditAmount FROM M
SS_DS.BPROJECT bp LEFT JOIN MSS_DS.BPROJAUDITROW rw ON rw.PROJID = bp.PROJID WHERE bp.PROJCODE in ( :1 , :2 , :3
, :4 , :5 , :6 , :7 , :8 , :9 , :10 ,
:11 , :12 )

这个sql查询的是所有字段，且这个表有个字段正是LOB型，从以下图中可以看出该字段存储应该是xml文件解析的内容：

设计表结构不展示；但是有lob字段

3、确定这些语句对应的会话，明确来源
SQL> select * from v$temporary_lobs;

   SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
复制

    20     935733            0             0
   519     930359            0             0
   527     927781            0             0
   764     928182            0             0
  1028     166704            0             0
  1522     166496            0             0
  1530          0            0             0
  1781     928185            0             0
  1785     928667            0             0
  2525    1962295            0             0
  2794    1951581            0             0
复制

USERNAME SID SERIAL# SQL_ADDRESS MACHINE PROGRAM TABLESPACE SEGTYPE CONTENTS GB
MSS_ODS_M 2794 20501 07000001894197E0 CentOS74 JDBC Thin Client TEMP1 LOB_DATA TEMPORARY 14.9814453
MSS_ODS_M 2525 21841 07000001894197E0 CentOS74 JDBC Thin Client TEMP1 LOB_DATA TEMPORARY 15.0703125

从临时LOB段的使用情况来看，主要由这几个会话占用了临时表空间，且这些会话均是LOB缓存占用了较大的空间。通过持续查看这个会话的session信息，可以看出这个会话执行的正是第一步定位的语句，且这个会话均是由IP地址为xxxx的应用主机的JDBC Thin Client这个程序发起，会话sid一直不变，

通过以上的分析不难看出引起临时表空间使用率超高且不释放的来源是应用的程序使用长连接模式连接到数据库对数据库一张含有clob字段表进行按条件排序查询导致。

解决方案

4、为何由应用调用的使用的临时LOB缓存未释放

Oracle在对包含lob字段进行读取、修改或写入时均使用临时表空间TEMPORARY LOB_DATA段以缓存形式来存储这些临时数据。从以下Oracle对LOB临时段的解释中可以看出如果是通过过应用程序来访问LOB字段时Oracle并不会显式地去关闭或清理临时LOB，这是由于Oracle作为服务端并不知道客户端应用程序什么时候结束对LOB临时段的使用，何时应该清理是由应用程序确定的。
The client could be finished with the Temporary LOB as soon as it reads the content, or may choose to read / write to it for anextended period of time. The server doesn’t explicitly close or clean up a Temporary LOBs as the server does not know when theclient has finished with it. The client could be finished with the Temporary LOB as soon as it reads the content on one or manyoccasions or even write to it.Ilts the responsiblity of the LOB user to free the LOB to prevent leaks and accumulations.If anexpliclt free ls not done, the LOB will persist until the end of duration cleanup happens.
· Once the sever generates the Temporary LOB and hands it off to the client then the server assumes that its the client that hasful responsibility for cleaning up the lob after its been used.The Temporary LOB will till stick around even with if all duraticreferencing it on the server ends.
When aLOB is returned to the cllent side application, the LOB layer doesn’t know the intended duration of the LOB.Once theclient has been invoked the server side object will continue to exist until the session closes or the client code explicitly freesCLOB.Even Client Garbage Collection will not cause it to be released. So it is the responsiblity of the client application to frecthe LOB.
lt does not matter how the Temporary LOB lis created (explicity using DBMS_LOB or implicity using XMLSERIALZE), if theresulting CLOB is returned to a program like JDBC, the program MUSTtake responsibility for freeing it.

那么客户端应用应该如何去清理这些临时LOB以释放其占用的临时表空间呢？

既然清理由应用程序确定，那么必然是程序开发语言是有这样释放lob缓存的代码的，比如对于Java程序有java.sql.NClob.free()这个方法来释放lob，对于其它通过调用 Oracle的API接口来实现的应用，oracle给出了相应如下的方法来释放Lob：

解决措施
1.应用程序更改相应模块的连接方式为短连接(例如对于由应用程序在调用完毕后执行关闭jdbc连接)
2.应用程序在调用相应的程序方法在对LOB引用完毕后主动关闭(此方法为最佳方法，但往往涉及到一些核心代码，开发商通常不愿意去更改这些代码，更改实施难度较大)
3.相应的应用模块定期重启(此方法操作简单，但如果相应的应用模块为核心业务时，往往需要有效的停机窗口)
4.数据库级别设置事件打开清理开关：
alter session set events ‘60025 trace name context forever’;
此方法限制条件为：
10g版本及以上且只有当数据库中所有缓存的临时段和非缓存的临时段均为0时才执行清理，如果数据库中事务较繁忙且始终存在cache temp lob and no-cache temp lobs均不为0时此方法将失效。就目前而言生产库上的这个lob缓存由于是长连接，所以cache tmp lob始终不为0，所以此方法不可行。
如果生产中遇到类似问题，可以考虑以下方法，寻找最适合自己的方法，本次由于这个业务模块是非核心模块，在晚上业务空闲时应用厂商对相应模块的应用进行了重启，重启完成后Temp表空间随之也释放了。
引申问题既然之前说到通过应用程序来引用lob数据时oracle不会也不应该去负责清理这些不再使用的lob段，那么还有通过pl/sql或sql直接对lob字段的访问这种情况的存在，那么对于这些访问方式oracle又是如何清理释放临时lob段的呢？

通过oracle声明我们可以看出，这些方式oracle会在访问下一行数据时显示释放这些lob缓存。

SQL*Plus or PL/SQL explicitly frees the LOB before accessing the next row.
Please note however,when a XMLType instance is passed across language boundary ,
e.g. from Java implementatlon to SqLprocedure via Java store procedure, a copy is made.Freeing XMLType in PL/SQL won’t free the object in Java.lt’s the
responsibility of the Java program to free any XMLType instances created from it.

参考文档

Best Practises for XMLType Temporary LOB Usage (文档 ID 1955135.1)

How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (文档 ID 802897.1)

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者