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;
