Wednesday, November 16, 2011

Temp Tablespace Usage Monitoring

Temp space usage per session

SET pagesize 10000
SET linesize 133
COL tablespace format a15 heading 'Tablespace Name'
COL segfile# format 9,999 heading 'File|ID'
COL segblk# format 999,999,999 heading 'Block|ID'
COL blocks format 999,999,999 heading 'Blocks'
COL username format a15
SELECT   b.TABLESPACE, b.segfile#, b.segblk#, b.blocks, a.SID, a.serial#,
         a.username, a.osuser, a.status
    FROM v$session a, v$sort_usage b
   WHERE a.saddr = b.session_addr
ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;

Temp segment usage per 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(*) 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 mb_used;

Identify temp segment usages per statement:

SELECT S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;

No comments: