v$sort_usage
Name Type
-------------- -------------
USERNAME VARCHAR2(30)
USER VARCHAR2(30)
SESSION_ADDR RAW(4)
SESSION_NUM NUMBER
SQLADDR RAW(4)
SQLHASH NUMBER
SQL_ID VARCHAR2(13)
TABLESPACE VARCHAR2(31)
CONTENTS VARCHAR2(9)
SEGTYPE VARCHAR2(9)
SEGFILE# NUMBER
SEGBLK# NUMBER
EXTENTS NUMBER
BLOCKS NUMBER
SEGRFNO# NUMBER
A database will often have a single temporary tablespace containing a single temporary segment. This segment is usable by all users. v$sort_usage contains one row for each session which has data held within the temporary segment. So, it's useful for looking at who's using the temporary tablespace.
For example, if I perform a sort on quite a large table, one where the size of the sort operation will exceed sort_area_size, I should see a row appear in v$sort_usage:
Session 1
SQL> conn andy
Enter password:
Connected.
SQL> select * from lots_of_rows order by text
2 /
ID TEXT
---------- -----
681511 AAWE
89133 AAWE
261276 AAWE
.
.
644834 AAWE
928473 AAWG
131349 AAWH
1000000 rows selected.
SQL>
Use this query to see what's in the temporary tablespace:
set pages 999 lines 100
col username format a15
col mb format 999,999
select su.username
, ses.sid
, ses.serial#
, su.tablespace
, ceil((su.blocks * dt.block_size) / 1048576) MB
from v$sort_usage su
, dba_tablespaces dt
, v$session ses
where su.tablespace = dt.tablespace_name
and su.session_addr = ses.saddr
/
Session 2:
SQL> conn / as sysdba
Connected.
SQL> l
1 select su.username
2 , ses.sid
3 , ses.serial#
4 , su.tablespace
5 , ceil((su.blocks * dt.block_size) / 1048576) MB
6 from v$sort_usage su
7 , dba_tablespaces dt
8 , v$session ses
9 where su.tablespace = dt.tablespace_name
10* and su.session_addr = ses.saddr
SQL> /
USERNAME SID SERIAL# TABLESPACE MB
--------------- ---------- ---------- ------------------------------- --------
ANDY 152 19 TEMP 16
SQL>
The row will disappear when I either commit, rollback or exit session 1.
For more useful DBA queries click here.
|
