eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« V$TEMPSEG_USAGE与Oracle排序 | Blog首页 | v$tempfile与v$sort_usage之关系解析 »

LOB对象与临时段

刚刚和DCBA聊到他们刚刚遇到的一个问题:LOB对象与临时表空间的关系.

如果程序中使用了临时的LOB类型变量,Oracle会分配临时空间,如果并发很高,初始区很大,那么数据库可能产生严重的TEMP表空间的不足问题.

可以通过测试轻易再现这种状况,在多个Session中执行如下代码:

declare                
  A CLOB;             
BEGIN                 
  A := 'ABC';         
  DBMS_LOCK.SLEEP(120);
END;                  
/                     

查询v$sort_usage视图,我们可以获得如下信息:

SQL> select s.username, s.sid, u.tablespace, u.contents, u.segtype,     
  2  round(u.blocks*8192/1024/1024,2) MB                               
  3  from v$session s, v$sort_usage u                                  
  4  where s.saddr = u.session_addr                                    
  5  and u.contents = 'TEMPORARY'                                      
  6  order by MB DESC ;                                                
 
USERNAME                    SID TABLESPACE      CONTENTS  SEGTYPE           MB
-------------------- ---------- --------------- --------- --------- ----------
SYS                           9 TEMP            TEMPORARY LOB_DATA           1
SYS                          10 TEMP            TEMPORARY LOB_DATA           1
SYS                          13 TEMP            TEMPORARY LOB_DATA           1
SYS                          15 TEMP            TEMPORARY LOB_DATA           1
SQL> 

Metalink Note:228479.1 称这并非一个Bug:

To workaround the problem, disconnect the database user session.

This behaviour does not reproduce in Oracle10, but is not a bug.

9i中临时表空间的定义为:

SQL> select BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE
  2  from dba_tablespaces where tablespace_name='TEMP';
BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO
---------- -------------- ----------- ---------- ---------
      8192        1048576     1048576 LOCAL      UNIFORM

在Oracle8i中情况有所不同.

Oracle 会分配多个段,在Oracle8i中执行如下代码:

SQL> declare
  2    a clob;
  3  begin
  4  dbms_lob.createtemporary(a,true);
  5  dbms_lob.write(a,3,1,'abc');
  6  dbms_lock.sleep(120);
  7  end;
  8  /

会看到如下结果:

SQL> select user,SEGTYPE,CONTENTS,blocks from v$sort_usage;
USER                           SEGTYPE   CONTENTS      BLOCKS
------------------------------ --------- --------- ----------
SYS                            LOB_DATA  TEMPORARY          8
SYS                            LOB_DATA  TEMPORARY          8
SYS                            LOB_INDEX TEMPORARY          8

本测试中Oracle8i中临时表空间定义为:

SQL> select INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE
  2  from dba_tablespaces where tablespace_name='TEMP';
INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO
-------------- ----------- ---------- ---------
         65536       65536 DICTIONARY USER

 


历史上的今天...
    >> 2013-03-25文章:
    >> 2008-03-25文章:
           SOX(萨班斯)法案知多少
    >> 2007-03-25文章:
           eygle的菜谱:金钩玉条
    >> 2005-03-25文章:
           那些花儿

无觅

By eygle on 2006-03-25 15:34 | Comments (0) | Internal | 719 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com