Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 临时表空间一直增长且不释放

低调小马哥

8 积分	1 好友	1 主题

发消息

[SQL调优] 临时表空间一直增长且不释放

1^#

发表于 2013-6-17 15:34:53 | 查看: 15456| 回复: 16

本帖最后由低调小马哥于 2013-6-17 15:34 编辑

os：redhat 5.8 db：10.2.0.5单机
应用服务器的java后台报：Caused by: java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 31 ，我到数据库上查询临时表空间的使用率，发现临时表空间已经扩展到了32G并完全胀满，查询临时表空间如下：

15:27:12 SQL> select * from ( select blocks*8/1024 ,a.session_addr,sql_id,tablespace,segtype,segfile# from v$tempseg_usage a order by blocks desc )
where rownum=1;15:27:13 2
BLOCKS*8/1024 SESSION_ADDR SQL_ID TABLESPACE SEGTYPE SEGFILE#
------------- ---------------- ------------- ------------------------------- --------- ----------
575 00000000C1A4F8F8 624083w8tmfh8 TEMP LOB_DATA 204
15:27:17 SQL> /
BLOCKS*8/1024 SESSION_ADDR SQL_ID TABLESPACE SEGTYPE SEGFILE#
------------- ---------------- ------------- ------------------------------- --------- ----------
594 00000000C1A4F8F8 fj2zjsht7cjny TEMP LOB_DATA 204
15:27:30 SQL> /
BLOCKS*8/1024 SESSION_ADDR SQL_ID TABLESPACE SEGTYPE SEGFILE#
------------- ---------------- ------------- ------------------------------- --------- ----------
614 00000000C1A4F8F8 fj2zjsht7cjny TEMP LOB_DATA 204

复制代码

每隔几秒就会增长10到20M，而且就这一个会话一直增长，其他的都正常，查询这个sql如下：

SELECT SJSZ.XBNDZY AS YXNDZY, SJSZ.XKKSSJ, SJSZ.XKJSSJ, XFMSXZ.ZYXXMS, XFMSXZ.ZYXXXF, XFMSXZ.GGXXMS, XFMSXZ.GGXXXF,XKZD.YXJWYDH,XKZD.YXJWYSJ,XKZD.YXJWYXM FROM T_XKGL_ZYNJXKZD XKZD,T_XKGL_XKSJSZ SJSZ, (SELECT ZY YXZYNDID, MAX (ZYXXMS) ZYXXMS, MAX (ZYXXXF) ZYXXXF, MAX (GGXXMS) GGXXMS, MAX (GGXXXF) GGXXXF FROM (SELECT ZY, DECODE (XXXZDM, 0, XXMSSX, NULL) ZYXXMS, DECODE (XXXZDM, 0, XXXFSX, NULL) ZYXXXF, DECODE (XXXZDM, 1, XXMSSX, NULL) GGXXMS, DECODE (XXXZDM, 1, XXXFSX, NULL) GGXXXF FROM (SELECT XFMSKZ.ZY, XFMSKZ.XXXZDM, XFMSKZ.XXMSSX, XFMSKZ.XXXFSX FROM T_XKGL_XKXFMSKZ XFMSKZ, T_XKGL_XKPZB PZ WHERE XFMSKZ.XNDM = PZ.XN AND XFMSKZ.XQDM = PZ.XQ AND XFMSKZ.XKCL = PZ.XKCL)) GROUP BY ZY) XFMSXZ WHERE SJSZ.XBNDZY = XKZD.YXNDZY(+) and SJSZ.XBNDZY = XFMSXZ.YXZYNDID(+)

复制代码

杀掉这个会话后，临时表空间立即清空了，但过一会又开始增长了。
请教各位有没有什么办法查出根本问题？

收藏0 回复只看该作者道具举报

2^#

发表于 2013-6-17 15:37:56

GROUP BY ZY 有排序。pga_aggregate_target 是多大？是不是设置的太小了。导致频繁写数据。

回复只看该作者道具举报

低调小马哥

3^#

发表于 2013-6-17 16:10:53

总的物理内存是4G，我设置的sga是1.5G，pga是0.5G，现在调整pga_aggregate_target =1G了，不过现在临时表空间还是一直在增长，无法阻止，真郁闷了

回复只看该作者道具举报

4^#

发表于 2013-6-17 16:15:21

查询下这张视图？
V$PGA_TARGET_ADVICE

回复只看该作者道具举报

低调小马哥

5^#

发表于 2013-6-17 16:40:50

SQL> set linesize 199 pagesize 999
SQL> select * from V$PGA_TARGET_ADVICE;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ------------------- ----------------------------- --------------------
134217728 .125 ON 1875335168 1445490688 56 92
268435456 .25 ON 1875335168 240320512 89 17
536870912 .5 ON 1875335168 0 100 0
805306368 .75 ON 1875335168 0 100 0
1073741824 1 ON 1875335168 0 100 0
1288489984 1.2 ON 1875335168 0 100 0
1503238144 1.4 ON 1875335168 0 100 0
1717986304 1.6 ON 1875335168 0 100 0
1932734464 1.8 ON 1875335168 0 100 0
2147483648 2 ON 1875335168 0 100 0
3221225472 3 ON 1875335168 0 100 0
4294967296 4 ON 1875335168 0 100 0
6442450944 6 ON 1875335168 0 100 0
8589934592 8 ON 1875335168 0 100 0
14 rows selected.

复制代码

好像目前的pga设置也没问题啊

回复只看该作者道具举报

低调小马哥

6^#

发表于 2013-6-17 17:45:57

目前发现个新情况，那个session其实还有好几个sql呢，其中一个sql返回wm_concat函数，这个函数的返回值为clob，我怀疑是大量大查询导致了创建很多clob，sql如下：

SELECT LOWER(JBXX.XH) XH, WM_CONCAT(JXB.KCDM) KCDMS
FROM T_XSJBXX_JBXX JBXX, T_XKGL_XKJGB JG, T_PKGL_JXB JXB, T_XKGL_XKPZB PZ
WHERE JBXX.SFZJ = 1
AND JG.XSBH = JBXX.XSBH
AND JG.JXBBH = JXB.JXBBH
AND (JXB.XNDM != PZ.XN OR JXB.XQDM != PZ.XQ)
GROUP BY JBXX.XH;

复制代码

但clob类型的为什么不能释放，还要进一步查！

回复只看该作者道具举报

7^#

发表于 2013-6-17 19:31:44

clob 可能是bug，特别是存储过程调用没释放
在一个aix的10205中遇到过

回复只看该作者道具举报

低调小马哥

8^#

发表于 2013-6-17 19:48:05

恩我也觉得可能是bug，在mos查了篇文章，How to Release Temporary LOB Segments without Closing the JDBC Connection [ID 1384829.1]，用alter system set events '60025 trace name context forever ';来避免clob自动不释放临时表空间，但还是不行，看来要让开发把wm_concat函数加个to_char转换试试了

回复只看该作者道具举报

低调小马哥

9^#

发表于 2013-6-18 10:42:17

这个问题已经解决了，当初迁移时从10.2.0.1迁移到10.2.0.5，但wm_concat这个函数中10.2.0.4之前返回都是varchar2类型，到10.2.0.5及以上版本返回为clob类型，而返回的临时clob类型会时临时表空间一直增长，怀疑是bug，解决办法：
从低版本（10.2.0.4一下）的oracle中拷贝如下三个文件到目标库：$ORACLE_HOME/rdbms/admin/owmctab.plb、
$ORACLE_HOME/rdbms/admin/owmaggrs.plb、$ORACLE_HOME/rdbms/admin/owmaggrb.plb，然后删除wmsys用户，drop user wmsys cascade，再执行上述三个脚本，目前临时表空间已经不会自动增长而不释放空间了。

回复只看该作者道具举报

10^#

发表于 2013-6-18 10:48:48

强悍～～～～～～～～～～～～～～

回复只看该作者道具举报

11^#

发表于 2013-6-18 10:49:35

本帖最后由 saup007 于 2013-6-18 11:01 编辑

低调小马哥发表于 2013-6-18 10:42
这个问题已经解决了，当初迁移时从10.2.0.1迁移到10.2.0.5，但wm_concat这个函数中10.2.0.4之前返回都是var ...

LZ的解决方法在哪里找到的？在线上这样做没有问题吗？

回复只看该作者道具举报

12^#

发表于 2013-6-18 11:22:52

请问一下楼主，你的数据是用数据泵迁移的吗？

回复只看该作者道具举报

低调小马哥

13^#

发表于 2013-6-18 13:51:54

恩是的，是生产库做的，不过我们的生产库不是很忙，应用程序报点错也没多大问题，我就直接做了，迁移是expdp/impdp做的。

回复只看该作者道具举报

14^#

发表于 2013-6-18 15:40:48

低调小马哥发表于 2013-6-18 13:51
恩是的，是生产库做的，不过我们的生产库不是很忙，应用程序报点错也没多大问题，我就直接做了，迁移是ex ...

这个处理方法在MOS上吗？文章号给下呗，谢谢。

回复只看该作者道具举报

低调小马哥

15^#

发表于 2013-6-18 16:05:07

不好意思，mos上没找到解决办法，我是绕过这个问题解决的

回复只看该作者道具举报

16^#

发表于 2013-6-18 16:47:42

eygle有一篇文章，处理的这种方法是，用一个自定义函数来替换wm_concat

回复只看该作者道具举报

17^#

发表于 2013-6-19 21:34:25

学习了解

回复只看该作者道具举报

|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 15:44 , Processed in 0.059890 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部

TEL/電話+86 13764045638

Email service@parnassusdata.com

QQ 47079569