Temp表空间占用长时间不释放的幕后黑手

最新推荐文章于 2024-07-03 23:04:08 发布

congxin0545

最新推荐文章于 2024-07-03 23:04:08 发布

阅读量591

点赞数

文章标签：数据库

Temp表空间占用长时间不释放-是谁惹的祸

临时表空间简介：

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

遇到的问题：

某核心OLTP生产系统最近发现临时表空间占用高达90%多，且持续监控发现一直不释放，而且还在缓慢增长

原因查找：

定位会话和语句：

106G的临时表空间使用了102G，这么大的临时表空间使用率在一个正常的OLTP系统中是很罕见的。

第一步：首先查看活动会话数，如下显示活动会话数总共27个，是很正常的，可以肯定临时表空间占用较高的原因并非由大量需要排序的异常会话引起，常见的这个原因可以排除。

第二步：定位哪些进程对应的哪些语句占用了较多临时表空间，经检查发现如下

从上面可以看出有10个进程占用了大约100多G的临时表空间，且显示为TEMPORARY LOB_DATA，说明正常是这10个进程导致的临时表空间被占满且不释放，经连续多次观察这10个进程执行的为sql_id为d12s8kpkp85zq的同一个sql。该sql语句如下

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

第三步：确定这些语句对应的会话，明确来源

从临时LOB段的使用情况来看，主要由10个会话占用了临时表空间，且这10个会话均是LOB缓存占用了较大的空间。连续查看这10个会话的session信息，可以看出这10个会话执行的正是第一步定位的语句，且这10个会话均是由IP地址为xxxx的应用主机的ocs_rentfee_imp这个程序发起，与应用确认这个程序是C程序写的。

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

第四步：为何由应用调用的使用的临时LOB缓存未释放

Oracle在对包含lob字段进行读取、修改或写入时均使用临时表空间TEMPORARY LOB_DATA段以缓存形式来存储这些临时数据。从以下Oracle对LOB临时段的解释中可以看出如果是通过过应用程序来访问LOB字段时Oracle并不会显式地去关闭或清理临时LOB，这是由于Oracle作为服务端并不知道客户端应用程序什么时候结束对LOB临时段的使用，何时应该清理是由应用程序确定的。

那么客户端应用应该如何去清理这些临时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';

此方法限制条件为：10.2.0.4版本及以上且只有当数据库中所有缓存的临时段和非缓存的临时段均为0时才执行清理，如果数据库中事务较繁忙且始终存在cache temp lob and no-cache temp lobs均不为0时此方法将失效。就目前而言生产库上的这个lob缓存由于是长连接，所以cache tmp lob始终不为0，所以此方法不可行。