v$sort_usage tips

Question:  I am seeing LOB segments insides v$sort_usage for a query against a table that does not have any LOB segments.

SESSION_ADDR     TABLESPACE CONTENTS SEGTYPE  SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
C000000415114208 TEMP1     TEMPORARY LOB_DATA 5059      72965       1    1280 2
C000000415114208 TEMP_ETL1 TEMPORARY SORT     5067     532485      75   96000 5

Answer:  The Oracle script collection has a script that will display all TEMP segments in the TEMP tablespace, but Oracle does not have any way to join v$session information into the actual objects created within the TEMP tablespace.

For sorting in the TEMP tablespace, the v$sort_usage view can be queried to see what session is storing temporary segments in the TEMP tablespace:

select 
   (select
    username
  from
    v$session
  where
   saddr = session_addr) uname,
  v.*
from
   v$sort_usage v;

The v$sort_usage will show more than just disk sorts. The sort segment in v$sort_usage can be temporary tables, open cursors or some temporary LOBs.

select
   a.name,
   b.value
from
   v$statname a,
   v$sesstat b,
   v$session s,
   v$sort_usage su
where
   See code depot for complete script
   a.statistic#=b.statistic#
and
   b.sid=s.sid
and
   s.saddr=su.session_addr;

The v$sort_usage view can be queried to identify internal components for all sorting and some internal operations:

select
   a.name,
   b.value
from
   v$statname    a,
   v$sesstat     b,
   v$session     s,
   v$sort_usage su
where
   a.statistic#=b.statistic#
and
   b.sid=s.sid
and
   s.saddr=su.session_addr;
 

According to Brian Peasland, v$sort_usage will show more than just disk sorts. The sort segment in v$sort_usage can be temporary tables, open cursors or some temporary LOBs. 

This script will use v$sort_usage and v$session to show the TEMP tablespace usage for a specific session:

select
   s.sid || ?,? || s.serial# sid_serial,
   s.username,
   s.osuser,
   p.spid,
   s.module,
   p.program,
   sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used,
   t.tablespace,
   count(*) nbr_statements
from
   v$sort_usage t,
   v$session s,
   dba_tablespaces tbs,
   v$process p
where
   See code depot for complete script
   t.session_addr = s.saddr
and
   s.paddr = p.addr
and
   t.tablespace = tbs.tablespace_name
group by
   s.sid,
   s.serial#,
   s.username,
   s.osuser,
   p.spid,
   s.module,
   p.program,
   tbs.block_size,
   t.tablespace
order by
   sid_serial;

The temporary LOB's seen in v$sort_usage indicate that the session is doing a disk sort and the session statistic for 'sorts (disk)' would not be increased.

---