eygle.com   eygle.com
eygle.com  
 

« March 24, 2006 | Blog首页 | March 26, 2006 »


March 25, 2006

v$tempfile与v$sort_usage之关系解析

作者:eygle

出处:http://blog.eygle.com

在前面的V$TEMPSEG_USAGE与Oracle排序中我谈到V$TEMPSEG_USAGE和V$SORT_USAGE同源,其中的SEGFILE#代表的是绝对文件号(AFN).

那么对于临时表空间的临时文件来说,这个字段可以和什么字段进行关联呢?

我们再来看一下V$TEMPFILE的来源,V$TEMPFILE由如下语句创建:

SELECT tf.inst_id, tf.tfnum, TO_NUMBER (tf.tfcrc_scn),
       TO_DATE (tf.tfcrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
       tf.tftsn, tf.tfrfn,
       DECODE (BITAND (tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
       DECODE (BITAND (tf.tfsta, 12),
               0, 'DISABLED',
               4, 'READ ONLY',
               12, 'READ WRITE',
               'UNKNOWN'
              ),
       fh.fhtmpfsz * tf.tfbsz, fh.fhtmpfsz, tf.tfcsz * tf.tfbsz, tf.tfbsz,
       fn.fnnam
  FROM x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh
 WHERE fn.fnfno = tf.tfnum
   AND fn.fnfno = fh.htmpxfil
   AND tf.tffnh = fn.fnnum
   AND tf.tfdup != 0
   AND fn.fntyp = 7
   AND fn.fnnam IS NOT NULL

考察x$kcctf底层表,我们注意到TFAFN(temp file absolute file number)在这里存在:

SQL> desc x$kcctf
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ADDR                                   RAW(4)
 INDX                                   NUMBER
 INST_ID                                NUMBER
 TFNUM                                  NUMBER
 TFAFN                                  NUMBER
 TFCSZ                                  NUMBER
 TFBSZ                                  NUMBER
 TFSTA                                  NUMBER
 TFCRC_SCN                              VARCHAR2(16)
 TFCRC_TIM                              VARCHAR2(20)
 TFFNH                                  NUMBER
 TFFNT                                  NUMBER
 TFDUP                                  NUMBER
 TFTSN                                  NUMBER
 TFTSI                                  NUMBER
 TFRFN                                  NUMBER
 TFPFT                                  NUMBER

而这个字段在构建v$tempfile时并未出现,所以我们不能通过v$sort_usage和v$tempfile直接关联绝对文件号.

通过LOB对象与临时段一文中方法我们可以简单构建一个排序段使用，然后来研究一下: 

SQL> select username,segtype,segfile#,segblk#,extents,segrfno#
  2  from v$sort_usage;
USERNAME SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
-------- --------- ---------- ---------- ---------- ----------
SYS      LOB_DATA           9      18953          1          1

我们看到这里的SEGFILE#=9，而在v$tempfile是找不到这个信息的:

SQL> select file#,rfile#,ts#,status,blocks
  2  from v$tempfile;
     FILE#     RFILE#        TS# STATUS      BLOCKS
---------- ---------- ---------- ------- ----------
         1          1          2 ONLINE       38400

我们可以从x$kcctf中获得这些信息,我们可以看到v$tempfile.file#实际上来自x$kcctf.tfnum，实际上是临时文件的顺序号，而绝对文件号是x$kcctf.tfafn，这个才可以和v$sort_usage.segfile#关联： 

SQL> select indx,tfnum,tfafn,tfcsz       
  2  from x$kcctf;
      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1          9      38400
         1          2         10      12800

临时表空间的绝对文件号可以通过如下查询获得:

  

SQL> select tm.file# Fnum ,tf.tfafn AFN,tm.name FName
  2  from v$tempfile tm,x$kcctf tf
  3  where tm.file# = tf.tfnum;
      FNUM        AFN FNAME
---------- ---------- --------------------------------------------
         1          9 /opt/oracle/oradata/conner/temp1.dbf
         4         12 /opt/oracle/oradata/conner/temp2.dbf
 

至于其他就不再赘述。

 

Posted by eygle at 4:36 PM | Comments (0)

LOB对象与临时段

作者:eygle

出处:http://blog.eygle.com

刚刚和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

 

Posted by eygle at 3:34 PM | Comments (0)

CopyRight © 2004-2008 eygle.com, All rights reserved.