Temporary Segments Are Not Being De-Allocated After a Sort (Doc ID 1039341.6)

Temporary Segments Are Not Being De-Allocated After a Sort To Bottom Description =========== When monitoring the size of the available free space in the TEMPORARY tablespace it does not increased once sorts are finished even with no client connections to the database. They may also be a large number of EXTENTS of type TEMPORARY in the temporary tablespace. Users may also experience ORA-1652 when running transactions. This error implies that there is no free space left. SMON is not cleaning up after waiting for hours and queries are failing. Scope & Application =================== If the TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released until the instance is shutdown. If the TEMPORARY TABLESPACE is of type PERMANENT, then cleanup is performed by SMON after completion of the process using it. A sort segment is created by the first statement that uses a TEMPORARY tablespace for sorting, after startup. These are not released until the database is restarted. The view V$SORT_SEGMENT can be used to see how many of the currently allocated sort segments are being used. If a PERMANENT tablespace is used for sorting, then any temporary segments created are dropped by SMON on completion of the statement, and the space is released for use by other objects. Running the query: select OWNER, SEGMENT_NAME, SEGMENT_TYPE , TABLESPACE_NAME from DBA_SEGMENTS where SEGMENT_TYPE = 'TEMPORARY'; to give results similar to: OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ----- ------------ ------------ --------------- SYS 4.2 TEMPORARY TEMP Note: segment_name and tablepace_name are likely to be different. Then determine the tablespace type by running: select TABLESPACE_NAME, CONTENTS from DBA_TABLESPACES where TABLESPACE_NAME in (<list of tablespaces from above>); to give results similar to: TABLESPACE_NAME CONTENTS ------------------------------ --------- TEMP TEMPORARY TEMP1 PERMANENT The above query will indicate what type of tablespace the database has defined. Explanation =========== If the tablespace in which the TEMPORARY segment resides is of type TEMPORARY then sort segments not being released is normal behavior in 7.3+. ORA-1652 implies that the tablespace is too small. A sort segment is created by the first statement that used a TEMPORARY tablespace for sorting, after startup. A sort segment created in a TEMPOARY tablespace is only released at shutdown. The large number of EXTENTS is caused when the STORAGE clause has been incorrectly calculated. If the tablespace in which the TEMPORARY segment resides is of type PERMANENT, then check that following events are not set in the initalisation parameter file: event="10061 trace name context forever, level 10" event="10269 trace name context forever, level 10" If they are set, comment out the lines and restart database. These two events prevent SMON from cleaning up TEMP segments and coalescing. In Oracle Version 7.0, only event 10061 existed and if set prevented SMON from both cleaning up TEMP segments and coalescing. From oraus.msg: 10061, 00000, "disable SMON from cleaning temp segments 10269, 00000, "Don't do coalesces of free space in SMON" // *Cause: setting this event prevents SMON from doing free space coalesces As user sessions require space for sorting, they will be allocated temporary segment(s) from the user's temporary tablespace. If the user's temporary tablespace is of type PERMANENT and these two events are set, then the session may eventually run out of space and any new sessions requiring space in the same tablespace may fail with "ORA-1652: unable to extend temp segment by %s in tablespace %s". For performance issues, tablespaces marked as temporary allocate extents as needed, but the extents are not released or deallocated after the operation is done. Instead, the extent is simply marked as FREE or available for the next sort operation. This eliminates the need to continuously allocate and deallocate extents after every sort operation, hence gaining performance benefits. To find out if a TEMP segment is FREE or being USED and also in which tablespace the segment resides, query the new dynamic performance view V$SORT_SEGMENT. For example: select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment; >>> DURING the SORT you will see something like this: TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ---------------- ------------ ----------- ----------- TEMP 590 590 0 >>> AFTER the SORT you will see something like this: TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS --------------- ------------ ------------ ----------- TEMP 590 0 590 This query shows us that during the sort the USED_BLOCK count increased since we are using the sort extents and after the sort is done the USED_BLOCK decreased and FREE_BLOCK count increased because the extents got released back to the sort extent pool. Use the following guidelines to specify DEFAULT STORAGE: Set INITIAL=NEXT.Since a process always writes data equal to SORT_AREA_SIZE to a temporary segment, a good value for the extent size is (n*s + b) where: n is a positive integer s is the value of SORT_AREA_SIZE initialization parameter b is the value of DB_BLOCK_SIZE initialization parameter Using this value optimizes temporary segment usage by allowing sufficient space for a header block and multiple sort run data to be stored in each extent. Specify a PCTINCREASE of zero to ensure that all extents are of the same size. The MAXEXTENTS parameter only affects a temporary segment if the tablespace is a PERMANENT tablespace. Proper calculation of the Default Storage clause parameters will increase performance and maximize use of storage. NOTE: By default when a tablespace is created its contents are set to hold "PERMANENT" objects, (i.e this includes all segments including sort segment). Sort segments residing in PERMANENT tablespace are still cleaned up by SMON as before, after the sort is completed. If a large number of sort segments have been created, SMON may take some time to drop them. To make a tablespace hold only temporary objects we need to set the contents to TEMPORARY, by issuing the following command: ALTER TABLESPACE <TABLESPACE_NAME> TEMPORARY; and verify this with the following query: SVRMGR> select TABLESPACE_NAME,CONTENTS from dba_tablespaces; TABLESPACE_NAME CONTENTS ---------------- --------- SYSTEM PERMANENT RBS PERMANENT TEMP TEMPORARY TOOLS PERMANENT CYN PERMANENT RBSTMP PERMANENT References ========== Note:65973.1 Temporary Tablespace and the Sort Extent Pool Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing Note:35513.1 Removing 'stray' temporary segments Note:50592.1 Extent Sizes for Sort, Direct Load and Parallel Operations Note:102339.1 Temporary Segments: What Happens When a Sort Occurs Note:1069041.6 How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts for Oracle8 and 9 Search Words ============ TEMP TEMPORARY SEGMENTS RELEASED CLEANED PERMANENT SMON ORA-1652 DEALLOCATED ORA-01652 TABLESPACE SORT

Scripts related to TEMP Tablespace

Scripts related to TEMP Tablespace

— Listing of temp segments.—
===========================================================

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;


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

— Check the sessions that use temp tablespace.-
===========================================================

col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000

SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;


--BTW, v$sort_usage is same as v$tempseg_usage.
--However, the tempspace can be used by any open cursor in that session. The current SQL is not necessary the culprit. In that case, we can check it from v$sql:
===========================================================

col hash_value for 999999999999
select hash_value, sorts, rows_processed/executions
 from v$sql
 where hash_value in (select hash_value from v$open_cursor where sid=7448)
 and sorts > 0
 and PARSING_SCHEMA_NAME='ALEXZENG3'
 order by rows_processed/executions;


To check instance-wise total allocated, total used TEMP for both rac and non-rac
===========================================================

set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;

Total Used and Total Free Blocks
===========================================================

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;

Another Query to check TEMP USAGE
===========================================================

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

Temporary Tablespace groups
===========================================================

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%';

select * from dba_tablespace_groups;

Block wise Check
===========================================================

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';

To Check Percentage Usage of Temp Tablespace
===========================================================

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

To check Used Extents ,Free Extents available in Temp Tablespace
===========================================================

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;

To list all tempfiles of Temp Tablespace
===========================================================

col file_name for a45
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files  order by file_name;

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024
size_m
, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v
WHERE (t.file_id (+)= d.file_id)
AND (d.file_id = v.file#); 

To see Temp Space:
===========================================================

SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",            
ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;


To see top 10 consuming process :
===========================================================

select * from
(SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB Username",a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND d.tablespace_name=b.tablespace ORDER BY b.tablespace, b.blocks DESC)
where rownum <=10


Query to check TEMP USAGE :
===========================================================

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';


To Check Percentage Usage of Temp Tablespace:
===========================================================

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

To find Sort Segment Usage by a particular User:
===========================================================

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;

To find Total Free space in Temp Tablespace :
===========================================================

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,
(used_blocks*8)/1024/1024  UsedSpaceInGB,
(total_blocks*8)/1024/1024  TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'


Get 10 sessions with largest temp usage :
===========================================================

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

Identifying WHO is currently using TEMP Segments :
===========================================================

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;


===========================================================

--Refer below link too
--http://allappsdba.blogspot.in/2012/04/scripts-related-to-temp-tablespace.html

Monitor Temp Usage

REFER below MOS Document..

How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

CREATE OR REPLACE PROCEDURE TEMP_TEMP_SEG_USAGE_INSERT IS
BEGIN
insert into TEMP_TEMP_SEG_USAGE
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024;
COMMIT;
END;
/

SELECT JOB FROM DBA_JOBS;

JOB
----------
19

BEGIN
DBMS_JOB.ISUBMIT(JOB => 20,
WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/

-- Periodically query your monitoring table <temporary tablespace name to monitor>_TEMP_SEG_USAGE. 
--    Also monitor space usage of the table as it could  grow very fast depending on job interval; delete rows or truncate table as appropriate.

select * from <temporary tablespace name to monitor>_TEMP_SEG_USAGE;

select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb
from dba_segments
where segment_name='<temporary tablespace name to monitor>_TEMP_SEG_USAGE;';

truncate table <temporary tablespace name to monitor>_TEMP_SEG_USAGE;

select * from temp_temp_seg_usage;

DATE_TIME USERNAME             SID    SERIAL
--------- --------------------- ----- ----
OS_USER                        SPACE_USED
------------------------------ ----------
SQL_TEXT
-----------------------------------------
29-JUN-07 SYS                158    13
sygaw-ca\sygaw                 768
select * from dba_objects order by object_id, object_name


select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb
from dba_segments
where segment_name='TEMP_TEMP_SEG_USAGE';

SEGMENT_NAME
------------------------------------------
TABLESPACE_NAME USEDMB
------------------------------ ----------
TEMP_TEMP_SEG_USAGE
SYSTEM        .0625

truncate table temp_temp_seg_usage;

Table truncated.