v$sort_usage (a.k.a v$tempseg_usage)

* Name

Beginning with 9i, Oracle silently calls v$sort_usage v$tempseg_usage probably because TEMP 
segments are not just for sorting, but also for hashing, storing LOB data or index, storing index 
data before they're completely created. You can view these types in v$sort_usage.segtype. 
Nevertheless, so many DBAs' scripts are written with the name v$sort_usage hardcoded. Oracle won't 
drop the name completely any time soon. (Even in 11gR2, v$tempseg_usage is still just a synonym for
v_$sort_usage.) The only time you need to remember the new name is when you look up the view in 
documentation.


* Error in doc

The column session_addr is not "Address of shared SQL cursor" as Reference claims. It's simply 
session address, or v$session.saddr.


* SQL related to the temp seg usage
[Update 2014-03] In Oracle 11.2.0.2 and up, x$ktsso has a new column ktssosqlid, pointing to the SQL 
that is actually associated with this temp seg. See Bug 17834663 and its description DocID:17834663.8. 
So the following is of historical interest only. Since ktssosqlid is not exposed in v$sort_usage (or 
v$tempseg_usage), you have to query x$ktsso directly.

/*

It's a well-known query that joins v$sort_usage with v$session on session address 
(v$sort_usage.session_addr=v$session.saddr). But what are the relationships between the seemingly 
redundant SQL columns?:
sqladdr and sqlhash of v$sort_usage
sql_address, sql_hash_value, prev_sql_addr, and sql_hash_value of v$session

Here's a test in 10gR2.

First the SQLs (SQL address,hash in that order):

(1) 711181BC,3589703690: select * from t where n = 100. It does a range scan, no sort.
(2) 6D291604,3373689603: select * from t order by n desc. It sorts.

First I run SQL (1) in a session just to make those column values recognizable. The values for that 
session as checked by another session are as follows.

v$sort_usage: 711181BC,3589703690 <-- SQL (1) in sqladdr and sqlhash
v$session:    00,      0          <-- None shown in sql_address or sql_hash_value
       (prev) 711181BC,3589703690 <-- SQL (1) in prev_sql_addr and in prev_hash_value

During running SQL (2):
v$sort_usage: 711181BC,3589703690 <-- Still SQL (1)
v$session:    6D291604,3373689603 <-- Correctly showing SQL (2) being run
       (prev) 711181BC,3589703690 <-- Still SQL (1)

After finished running SQL (2):
v$sort_usage: 6D291604,3373689603 <-- Updated to SQL (2)
v$session:    00,      0          <-- None
       (prev) 6D291604,3373689603 <-- Updated to SQL (2)

So we may conclude that the two SQL columns (sqladdr and sqlhash) in v$sort_usage record the SQL 
that *last* ran using temporary segment. If the SQL is currently running consuming temp space, you 
have to find it in v$session sql_address and sql_hash_value. V$session.prev_sql_addr and 
prev_hash_value are exactly the same as v$sort_usage.sqladdr and sqlhash, respectively.

[2009-07 note] Actually, v$fixed_view_definition for GV$SORT_USAGE clearly shows that:
select ...  prev_sql_addr, prev_hash_value, prev_sql_id,...
from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#

*/