www.eygle.com

« Google中文桌面第3版Beta发布 | Blog首页 | LOB对象与临时段 »

V$TEMPSEG_USAGE与Oracle排序

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

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

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

再追本溯源原来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#

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#的定义为:

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

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

这个绝对文件号可以和<View:V$DATAFILE> . 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.

收录于此,不再多做说明.

By eygle on 2006-03-24 15:29 | Comments (0) | Posted to Internal | Edit |Pageviews:

Listed below are links to weblogs that reference V$TEMPSEG_USAGE与Oracle排序:

» v$tempfile与v$sort_usage之关系解析 from Friends Life and Oracle
在前面的V$TEMPSEG_USAGE与Oracle排序中我谈到V$TEMPSEG_USAGE和V$SORT_USAGE同源,其中的SEGFILE#代表的是绝对文件号(AFN). [Read More]

Tracked on September 6, 2006 6:50 PM

留言 (0)

发表留言:

Name(*):
Email(*):

You Link: Remember Me? YesNo
验证码(*): (输入验证码后方可评论,谢谢支持)
Comments: