Oracleseg,V$TEMPSEG_USAGE与Oracle排序的关系及使用方法

V$TEMPSEG_USAGE与Oracle排序

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

查询dba_objects视图&＃xff0c;发现原来这是一个同义词。

SQL> select object_type from dba_objects

2 where object_name&＃61;&＃39;V$TEMPSEG_USAGE&＃39;;

OBJECT_TYPE

------------------

SYNONYM

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

SQL> select * from dba_synonyms

2 where synonym_name&＃61;&＃39;V$TEMPSEG_USAGE&＃39;;

OWNER      SYNONYM_NAME    TABLE_OWNE TABLE_NAME        DB_LINK

---------- --------------- ---------- ----------------- ----------

PUBLIC     V$TEMPSEG_USAGE SYS        V_$SORT_USAGE

如果再进一步&＃xff0c;我们可以看到:

SQL> SELECT view_definition FROM v$fixed_view_definition

2  WHERE view_name&＃61;&＃39;GV$SORT_USAGE&＃39;;

VIEW_DEFINITION

--------------------------------------------------------------------------------

select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, p

rev_hash_value, ktssotsn, decode(ktssocnt, 0, &＃39;PERMANENT&＃39;, 1, &＃39;TEMPORARY&＃39;), deco

de(ktssosegt, 1, &＃39;SORT&＃39;, 2, &＃39;HASH&＃39;, 3, &＃39;DATA&＃39;, 4, &＃39;INDEX&＃39;, 5, &＃39;LOB_DATA&＃39;, 6, &＃39;LO

B_INDEX&＃39; , &＃39;UNDEFINED&＃39;), ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno fro

m x$ktsso, v$session where ktssoses &＃61; v$session.saddr and ktssosno &＃61; v$session.s

erial#

格式化一下&＃xff0c;v$sort_usage的创建语句如下:

SELECT x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr,

prev_hash_value, ktssotsn,

DECODE (ktssocnt, 0, &＃39;PERMANENT&＃39;, 1, &＃39;TEMPORARY&＃39;),

DECODE (ktssosegt,

1, &＃39;SORT&＃39;,

2, &＃39;HASH&＃39;,

3, &＃39;DATA&＃39;,

4, &＃39;INDEX&＃39;,

5, &＃39;LOB_DATA&＃39;,

6, &＃39;LOB_INDEX&＃39;,

&＃39;UNDEFINED&＃39;

),

ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno

FROM x$ktsso, v$session

WHERE ktssoses &＃61; v$session.saddr AND ktssosno &＃61; 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.

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

By eygle on 2006-03-24 15:29 |

Comments (0) |

Internal | 718 |