Things to check when TEMP TABLESPACE USAGE IS HIGH.

           

Things to check when TEMP TABLESPACE USAGE IS HIGH.

You need to identify the sqls in the temp tablespace over time executing queries over and over again at some interval to determine this.

Please run the following at intervals - say every 10 minutes.


1>) Temporary Tablespace size:

The following query displays information about usage of temporary tablespace
===
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) >) Temporary Segments

The following query displays information about all sort segments in the database. Typically, Oracle will create a new sort segment the very first time a sort to disk occurs in a new temporary tablespace. The sort segment will grow as needed, but it will not shrink and will not go away after all sorts to disk are completed. A database with one temporary tablespace will typically have just one sort segment.

===
spool /PRD02/prdora1/temporary_segments.html
set markup html on
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;

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;
set markup html off
spool off

===

3>) Sort Space Usage by Session

The following query displays information about each database session that is using space in a sort segment. Although one session may have many sort operations active at once, this query summarizes the information by session.

===
spool /PRD02/prdora1/sort_space_usage_session.html
set markup html on
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
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, S.program, TBS.block_size, T.tablespace
ORDER BY 8 desc;
set markup html off
spool off

===

4>) Sort Space Usage by Statement

The following query displays information about each statement that is using space in a sort
segment. The query displays information about each statement using space in a sort segment,
including information about the database session that issued the statement and the temporary
tablespace and amount of sort space being used.

===
spool /PRD02/prdora1/sort_space_usage_statement.html
set markup html on
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
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 4 desc;
set markup html off
spool off
===

Please note, that on executing the above sqls at regular intervals (during high temp space usage),will help you to locate any particular sql which chews up the temp space, then its an ideal candidate for sql tuning.