Find sessions performing sort operations : TEMP Tablespace occupants

Most of the time, user will ask you to check long running sessions in database and you will figure out the query which the session is performing sort/join operation – reading the query you can say the query is related to sorting. But  you will also see the high usage of Temporary segments or will see wait events such as “Direct Path Temp Read” Or “Direct Path Temp Write”.

To rectify this you can use views such as  V$SORT_USAGE and V$SORT_SEGMENT to identify the other sessions performing sort operation

Oracle provides two basic dynamic performance views for online monitoring of temporary tablespace usage.

These are v$sort_usage to query online sessions using temporary segments and v$sort_segment to query the size of the temporary segments in the database.

V$SORT_USAGE has below main columns  which can be useful here:

USERNAME       –              Session User name.

SQL_ID                –              unique identifier of the SQL that requires the sort.

EXTENTS           –              number of extents in the temporary segment being used by particular sessions.

BLOCKS              –              number of blocks in the temporary segment being used by particular sessions.

SESSION_ADDR – address of the session, can be used to identify the session in V$SESSION according to the SADDR column.

--Use below query to find the sessions consuming temp segments

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(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE 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;
 

This will give the sid and serial# which be used to find current query that is taking temp segment using view v$sqltext and passing sql_id of the sid and serial#

using  V$SORT_SEGMENT

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total-SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;