Temporary tablespace usage in Oracle

Posted by David Yahalom under Oracle

Temp is used for sorting - which, by itself, is performed automatically “the in background” for various operations such as creating an index, using order by or group by. In addition, using hash joins uses temp space for the hash tables created by the process.

 The session will begin by sorting data in memory and if exceeds a certain threshold (note – not necessarily exceeding the amount of memory available), Oracle will break the operation into smaller “chunks” and will write partial results to the temporary tablespace. This will result in temp space usage.

The behavior described above is controlled by the workarea_size_policy parameter. When set to AUTO, the pga_aggregate_target parameter will instruct Oracle how much memory can be used by all sessions for sort activities. Oracle will automatically determine how much of this memory each individual session can use.

 When the workarea_size_policy parameter is set to manual on the other hand, parameters such as sort_area_size, hash_area_size, and bitmap_merge_area_size define the amount of memory a session can use for various sort related operations.

 It’s also important to note that a single SQL statement can perform multiple sorts.

 When temp blocks which belong to a sort are no longer required, these blocks are marked as such and will be re-allocated when a new sort is started.

When there’s not enough free space in the temporary tabelsapce to meet the sort requirements, the sort operation will fail. This can occur when there are no unused blocks for the sort segments (too many parallel sorts running) or no space available in the temporary tablespace as a whole.

 When this happens Oracle will raise the “ORA-1652: unable to extend temp segment” error. Please note that not a lot of information is provided in addition to the ORA-1652 error. So further diagnostic of such errors requires some additional work.

 

Diagnosing temp space usage in Oracle can be done utilizing the internal Oracle diagnostic mechanism to give us information about ORA-1652 errors by generating trace files. These traces will contain all the required information for diagnostic including the SQL statement text. There will be overhead to the system, but should be minimal under most conditions as the trace file will only be written when an error occurs.

When an ORA-1652 error occurs, a trace file will be created in the udump directory.

 Tracing ORA-1652 errors can either be done at the session or instance level:

 ALTER SESSION SET EVENTS ‘1652 trace name errorstack’;

 OR

 ALTER SYSTEM SET EVENTS ‘1652 trace name errorstack’;

 Also, monitoring the temporary tablespace in realtime can be done using the following query:

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;

 

Sort segment usage divided by session:

 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 sid_serial;

Sort segment usage divided by statement:

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 S.sid;