Oracle Performance View: V$sort_usage_temp

1. Table structure

Sql> desc v$sort_usage

is the name empty? Type

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

USERNAME VARCHAR2 (30)

USER VARCHAR2 (30)

Session_addr RAW (8)

Session_num number

Sqladdr RAW (8)

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

2. SQL statement

Temp table space usage, when the temp table space becomes huge, according to SESSION_ADDR can get session ID, according to SQLADDR and Sqlhash can get executing sql:

Select Se.username,

Se.sid,

Su.extents,

Su.blocks * To_number (RTrim (P.value)) as space,

Tablespace,

Segtype,

Sql_text

From V$sort_usage Su, v$parameter p, v$session se, v$sql s

where p.name = ' db_block_size '

and su.session_addr = Se.saddr

and S.hash_value = Su.sqlhash

and s.address = Su.sqladdr

Order by Se.username, Se.sid;

The results of the query are as follows:

USERNAME SID extents space tablespace segtype Sql_text

1 SYS 145 1 1048576 TEMP DATA Select value from v$sesstat where SID =: Sid ORDER by statistic#

2 SYS 145 1 1048576 TEMP INDEX Select value from v$sesstat where SID =: Sid ORDER by statistic#

3 SYS 145 1 1048576 TEMP lob_data Select value from v$sesstat where SID =: Sid ORDER by statistic#