联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
临时表空间被使用现状
接到客户反馈,他们的数据库使用了长连接,临时表空间使用率一直不下降,一个会话占用了几百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
惜分飞,
谢谢