Friday 26 January 2018

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;



2 comments:

  1. This information you provided in the blog that is really unique I love it!! Thanks for sharing such a great blog Keep posting..
    Oracle Training in Gurgaon
    Oracle Institute in Gurgaon
    Oracle Course in Gurgaon

    ReplyDelete
  2. Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital informationOracle DBA Training in Chennai

    ReplyDelete