刚才Kamus说起V$TEMPSEG_USAGE这个视图,看着很眼生,我说没注意过,然后动手查一下这个东西究竟来自何方.

查询dba_objects视图，发现原来这是一个同义词。

SQL> select object_type from dba_objects 
   2 where object_name='V$TEMPSEG_USAGE';

OBJECT_TYPE
------------------
SYNONYM

再追本溯源原来V$TEMPSEG_USAGE是V_$SORT_USAGE的同义词，也就是和V$SORT_USAGE同源。

SQL> select * from dba_synonyms 
   2 where synonym_name='V$TEMPSEG_USAGE';

OWNER      SYNONYM_NAME    TABLE_OWNE TABLE_NAME        DB_LINK
---------- --------------- ---------- ----------------- ----------
PUBLIC     V$TEMPSEG_USAGE SYS        V_$SORT_USAGE

如果再进一步，我们可以看到:

SQL> SELECT view_definition FROM v$fixed_view_definition
  2  WHERE view_name='GV$SORT_USAGE';


VIEW_DEFINITION
--------------------------------------------------------------------------------
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, p
rev_hash_value, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'), deco
de(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LO
B_INDEX' , 'UNDEFINED'), ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno fro
m x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.s
erial#

格式化一下，v$sort_usage的创建语句如下:

SELECT x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr,
       prev_hash_value, ktssotsn,
       DECODE (ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'),
       DECODE (ktssosegt,
               1, 'SORT',
               2, 'HASH',
               3, 'DATA',
               4, 'INDEX',
               5, 'LOB_DATA',
               6, 'LOB_INDEX',
               'UNDEFINED'
              ),
       ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno
  FROM x$ktsso, v$session
 WHERE ktssoses = v$session.saddr AND ktssosno = v$session.serial#
/

我们注意到在Oracle文档中SEGFILE#的定义为:

SEGFILE#

NUMBER

File number of initial extent

在视图中,这个字段来自:x$ktsso.ktssofno .

也就是说这个字段实际上代表的是绝对文件号.

这个绝对文件号可以和 . FILE# 进行联合查询.或者和TEMPFILE的绝对文件号进行联合查询.

临时文件的绝对文件号并不能从V$TEMPFILE中得到,需要从v$tempfile的底层表x$kcctf 中获得. x$kcctf.TFAFN 可以和v$sort_usage.SEGFILE#进行关联.

Kamus提醒我,在Oracle Concept手册中有这样一段话值得注意并自行阅读.

Sort Segments
One or more temporary tablespaces can be used only for sort segments. A temporary
tablespace is not the same as a tablespace that a user designates for temporary
segments, which can be any tablespace available to the user. No permanent schema
objects can reside in a temporary tablespace.
Sort segments are used when a segment is shared by multiple sort operations. One sort
segment exists for every instance that performs a sort operation in a given tablespace.
Temporary tablespaces provide performance improvements when you have multiple
sorts that are too large to fit into memory. The sort segment of a given temporary
tablespace is created at the time of the first sort operation. The sort segment expands
by allocating extents until the segment size is equal to or greater than the total storage
demands of all of the active sorts running on that instance.
另外还有一篇文章
v$tempfile与v$sort_usage之关系解析
http://www.eygle.com/archives/2006/03/tempfile_and_sort_usage.html
转载自：
作者：eygle |English Version
链接：http://www.eygle.com/archives/2006/03/oracle_sort_usage.html

ITPub博客

V$TEMPSEG_USAGE与Oracle排序

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

最新文章