剛才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之關係解析
轉載自：
作者： |
連結：

V$TEMPSEG_USAGE與Oracle排序

v$tempfile與v$sort_usage之關係解析

相關文章