Monitoring TEMP tablespace activity
Question: How do I see the temp space used by a specific Oracle session? Is there a way to see the TEMP space used by a SQL statement, or is it only temporary space used by the session?How-to find the SQL that using lots of temp tablespace in Oracle ?
Answer: There are scripts that reveal the temporary space used by a session and by a SQL statement.
When an Oracle db is running out of temporary tablespace, we need to find out which SQL is using the temp tablespace.
First,Check the space usage:
SYS@AHMEDENG: SQL> select b.Total_MB,
b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,
round(used_blocks*8/1024) Current_Used_MB,
round(max_used_blocks*8/1024) Max_used_MB
from v$sort_segment a,
(select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;
TOTAL_MB CURRENT_FREE_MB CURRENT_USED_MB MAX_USED_MB
---------- --------------- --------------- -----------
36011 1097 34914 35075
Then,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
SYS@AHMEDENG: SQL> 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;
SID USERNAME TABLESPACE HASH_VALUE SEGTYPE CONTENTS BLOCKS
---------- --------- ---------- -------------------- --------- --------- ----------
2749 AHMEDENGB TEMP01 0/2004054495 LOB_DATA TEMPORARY 128
2750 AHMEDENGB TEMP01 0/1950821498 LOB_DATA TEMPORARY 128
6481 AHMEDENGB TEMP01 0/104254066 LOB_DATA TEMPORARY 128
9842 AHMEDENGB TEMP01 0/1950821498 LOB_DATA TEMPORARY 128
8470 AHMEDENGB TEMP01 0/1655124149 LOB_DATA TEMPORARY 128
8176 AHMEDENGB TEMP01 0/487817532 LOB_DATA TEMPORARY 128
6449 SYS TEMP01 1523493484/1950821498 HASH TEMPORARY 512
9926 AHMEDENGA TEMP01 3849710509/887856235 HASH TEMPORARY 2560
9926 AHMEDENGA TEMP01 3849710509/887856235 SORT TEMPORARY 56192
6639 AHMEDENGA TEMP01 2351869958/4158575278 SORT TEMPORARY 337792
9730 AHMEDENGA TEMP01 0/543173518 SORT TEMPORARY 337792
8959 AHMEDENGA TEMP01 0/4158575278 SORT TEMPORARY 337792
1320 AHMEDENGA TEMP01 0/2542463110 SORT TEMPORARY 337920
7905 AHMEDENGA TEMP01 0/543173518 SORT TEMPORARY 337920
6852 AHMEDENGA TEMP01 0/2631006892 SORT TEMPORARY 409472
6761 AHMEDENGA TEMP01 0/231059081 SORT TEMPORARY 409472
7971 AHMEDENGA TEMP01 0/4158575278 SORT TEMPORARY 409472
9060 AHMEDENGA TEMP01 0/4158575278 SORT TEMPORARY 409472
7873 AHMEDENGA TEMP01 0/4158575278 SORT TEMPORARY 409472
7448 AHMEDENGA TEMP01 0/887856235 SORT TEMPORARY 409472
20 rows selected.
As you can see, there are different segment types. Most of time, SORT is the one we need to check.
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:
Check the SQL who consomme huge space in your temporary tablespace :
SYS@AHMEDENG: SQL> col hash_value for 999999999999
SYS@AHMEDENG: SQL> 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='AHMEDENGA'
order by rows_processed/executions;
HASH_VALUE SORTS ROWS_PROCESSED/EXECUTIONS
------------- ---------- -------------------------
887856235 30506 .000196676
2631006892 30227 .001323276
3490377209 632 46993.6709
Now it’s very obviously, the SQL identified by the HASH VALUE "3490377209" sorts lots of rows every time. It used most of the tempspace in this session.