How-to find the SQL that using lots of temp tablespace in Oracle

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@ALEXZENG: 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@ALEXZENG: 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 ALEXZENG1 TEMP01     0/2004054495             LOB_DATA  TEMPORARY        128
      2750 ALEXZENG1 TEMP01     0/1950821498             LOB_DATA  TEMPORARY        128
      6481 ALEXZENG1 TEMP01     0/104254066              LOB_DATA  TEMPORARY        128
      9842 ALEXZENG1 TEMP01     0/1950821498             LOB_DATA  TEMPORARY        128
      8470 ALEXZENG1 TEMP01     0/1655124149             LOB_DATA  TEMPORARY        128
      8176 ALEXZENG1 TEMP01     0/487817532              LOB_DATA  TEMPORARY        128
      6449 SYS       TEMP01     1523493484/1950821498    HASH      TEMPORARY        512
      9926 ALEXZENG3 TEMP01     3849710509/887856235     HASH      TEMPORARY       2560
      9926 ALEXZENG3 TEMP01     3849710509/887856235     SORT      TEMPORARY      56192
      6639 ALEXZENG3 TEMP01     2351869958/4158575278    SORT      TEMPORARY     337792
      9730 ALEXZENG3 TEMP01     0/543173518              SORT      TEMPORARY     337792
      8959 ALEXZENG3 TEMP01     0/4158575278             SORT      TEMPORARY     337792
      1320 ALEXZENG3 TEMP01     0/2542463110             SORT      TEMPORARY     337920
      7905 ALEXZENG3 TEMP01     0/543173518              SORT      TEMPORARY     337920
      6852 ALEXZENG3 TEMP01     0/2631006892             SORT      TEMPORARY     409472
      6761 ALEXZENG3 TEMP01     0/231059081              SORT      TEMPORARY     409472
      7971 ALEXZENG3 TEMP01     0/4158575278             SORT      TEMPORARY     409472
      9060 ALEXZENG3 TEMP01     0/4158575278             SORT      TEMPORARY     409472
      7873 ALEXZENG3 TEMP01     0/4158575278             SORT      TEMPORARY     409472
      7448 ALEXZENG3 TEMP01     0/887856235              SORT      TEMPORARY     409472

20 rows selected.

BTW, v$sort_usage is same as v$tempseg_usage.

As you can see, there are different segment types. Most of time, SORT is the one we need to check. If you are lucky enough, or the client only have 1 SQL cursor, you will get the SQL hash value in the above result.

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:

SYS@ALEXZENG: SQL> col hash_value for 999999999999
SYS@ALEXZENG: 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='ALEXZENG3'
 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 3490377209 sorts lots of rows every time. It used most of the tempspace in this session.