临时表空间被使用现状
接到客户反馈,他们的数据库使用了长连接,临时表空间使用率一直不下降,一个会话占用了几百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
文章来自:http://www.xifenfei.com/3915.html