commit后lob字段使用临时表空间未释放

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：commit后lob字段使用临时表空间未释放

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

临时表空间被使用现状
接到客户反馈,他们的数据库使用了长连接,临时表空间使用率一直不下降,一个会话占用了几百M甚至几个G的临时表空间不释放,随着时间的积累,会话占用的临时表空间还在继续增加,最终的现象是100G的数据文件,160G的临时表空间还在继续报临时表空间不足.查询v$sort_usage发现其SEGTYPE全部为LOB_DATA而CONTENTS为TEMPORARY,而且BLOCKS都很大,通过上面的信息大概分析,怀疑是因为数据库查询或者操作LOB类型时候使用了TEMPORARY,但是没有释放导致

相关版本信息

OS:AIX 6.1(64)
DB:10.2.0.5

测试案例证明

--执行查询脚本
$ more check.sql
connect / as sysdba
select * from v$tempseg_usage where username not in ('HDDS_CLPS_DTA','FOGLIGHT');
--测试脚本1
$ more test1.sh
sqlplus /nolog <<EOF
connect / as sysdba
drop user xifenfei cascade;
create user xifenfei identified by tc
default tablespace users temporary tablespace temp quota unlimited on users;
grant connect,resource,alter session to xifenfei;
revoke unlimited tablespace from xifenfei;
connect xifenfei/tc
select to_nclob('a') from dual;
!sqlplus /nolog @check
commit;
!sqlplus /nolog @check
EOF
--测试脚本2
$ more test2.sh
sqlplus /nolog << EOF2
connect xifenfei/tc
alter session set events '60025 trace name context forever';
select to_nclob('a') from dual;
!sqlplus /nolog @check
commit;
!sqlplus /nolog @check
EOF2

测试结果

$ ./test1.sh
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> Connected.
SQL> drop user xifenfei cascade
          *
ERROR at line 1:
ORA-01918: user 'XIFENFEI' does not exist
Grant succeeded.
SQL>
Revoke succeeded.
SQL> SQL> SQL> Connected.
SQL>
TO_NCLOB('A')
--------------------------------------------------------------------------------
a
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
      10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
         1        128          1
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
Commit complete.
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
      10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
         1        128          1
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--测试脚本2
$ ./test2.sh
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> Connected.
SQL>
Session altered.
SQL>
TO_NCLOB('A')
--------------------------------------------------------------------------------
a
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
no rows selected
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
Commit complete.
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
no rows selected
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.Without event 60025 set (before and after commit):都出现v$tempseg_usage中存在对应记录,而且提交后不能释放Temp LOB space
2.With event 60025 set (before and after commit):都未现v$tempseg_usage中存在对应记录,证明提交后释放Temp LOB space

解决方案
通过上面的试验证明我们可以通过设置event 60025来解决该版本的会话提交后Temp LOB space不能被回收的问题.
我们可以通过在session级别使用”alter session set events ‘60025 trace name context forever’;”来实现。如果想实现全库级别的，但是因为event 60025不能通过system设置生效,所以我们可以通过logon触发器来实现该功能

create or replace trigger sys.login_db after logon on database
begin
execute immediate 'alter session set events ''60025 trace name context forever''';
end;
/

注意这个是ORCLE bug(Bug 5723140 – Temp LOB space not released after commit [ID 5723140.8]),从10.2.0.4开始虽然已经修复了该bug,但是默认情况下:为了更加高效的利用temp,在session未断开前,不自动释放temp 空间,可以通过设置event 60025来强制会话在commit之后就立即释放temp space

One thought on “commit后lob字段使用临时表空间未释放”

  1. Bug 5723140 Temp LOB space not released after commit

    While using temporary LOBs the temp segment used for this
purpose remains active until the session exits.
When there is a request to cleanup those temp lobs we do free space from
temp tablespace and the same session can reuse it, but the temp segment is not
released which means it is not available for use to other sessions.
The actual cleanup happens only on session exit. This is intentional.
This fix introduces a new event 60025 such that when there are
no active temp lobs in the session (ie: both cache temp lob and
no-cache temp lobs used are zero) then the temp segment itself
will also be freed releasing the space for other sessions to use.
Note that this change is disabled by default.
    回复

  2. 你好,
    我想知道,这个所谓的FIXED是指什么意思.
    根据文章的脚本,我在10.2.0.5.0和11.2.0.4.0上测试,得到的结果,都是:
    SQL> connect xifenfei/tc@A_SYJ
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
    Connected as xifenfei@A_SYJ
    SQL> –alter session set events ‘60025 trace name context forever’;
    SQL> select to_nclob(‘a’) from dual;
    TO_NCLOB(‘A’)
    ——————————————————————————–
    a
    SQL> select t.blocks from v$tempseg_usage t where t.username like ‘%XIFENFEI%’;
    BLOCKS
    ———-
    128
    SQL> commit;
    Commit complete
    SQL> select t.blocks from v$tempseg_usage t where t.username like ‘%XIFENFEI%’;
    BLOCKS
    ———-
    128
    SQL>
    是我这安装有问题?
    是更高版本BUG又回来了?

    回复

  3. 你好,
    我想知道,这个所谓的FIXED是指什么意思.
    根据文章的脚本,我在10.2.0.5.0和11.2.0.4.0上测试,得到的结果,都是:
    SQL> connect xifenfei/tc@A_SYJ
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
    Connected as xifenfei@A_SYJ
    SQL> –alter session set events ’60025 trace name context forever’;
    SQL> select to_nclob(‘a’) from dual;
    TO_NCLOB(‘A’)
    ——————————————————————————–
    a
    SQL> select t.blocks from v$tempseg_usage t where t.username like ‘%XIFENFEI%’; BLOCKS
    ———-128
    SQL> commit;
    Commit complete
    SQL> select t.blocks from v$tempseg_usage t where t.username like ‘%XIFENFEI%’; BLOCKS
    ———-128
    SQL>
    是我这安装有问题?
    是更高版本BUG又回来了?

    我看你的 –alter session set events ’60025 trace name context forever’; 是注释掉的，如果注释了，就有该问题

    回复

  4. 惜分飞,
    谢谢回应。
    示例里是注释的，注释了才是原始环境。
    我想表达的意思是，10.2.0.5.0和11.2.0.4.0
    两版本，不是应该默认释放掉的么？

    回复

  5. 惜分飞,
    谢谢回应。
    示例里是注释的，注释了才是原始环境。
    我想表达的意思是，10.2.0.5.0和11.2.0.4.0
    两版本，不是应该默认释放掉的么？

    我们已经测试，在10205中依旧有该问题，对于11204，我尚未测试，不清楚

    回复

发表评论

邮箱地址不会被公开。 必填项已用*标注

1 × 5 =