Discover what process/query is using oracle temp tablespace

Question

Oracle FAQ defines temp table space as follows:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation.

That's great, but I need more detail about what exactly is using the space. Due to quirks of the application design most queries do some kind of sorting, so I need to narrow it down to client executable, target table, or SQL statement.

Essentially, I'm looking for clues to tell me more precisely what might be wrong with this (rather large application). Any sort of clue might be useful, so long as it is more precise than "sorting".

Solution 1

I'm not sure exactly what information you have to hand already, but using the following query will point out which program/user/sessions etc are currently using your temp space.

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

Once you find out which session is doing the damage, then have a look at the SQL being executed, and you should be on the right path.

Solution 2

Thanks goes for Michael OShea for his answer ,

but in case you have Oracle RAC multiple instances , then you will need this ...

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.inst_ID
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP2'
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

and this the script to generate the kill statements: Please review which sessions you will be killing ...

SELECT  b.TABLESPACE, a.username , a.osuser , a.program , a.status ,
       'ALTER SYSTEM KILL SESSION '''||a.SID||','||a.SERIAL#||',@'||a.inst_ID||''' IMMEDIATE;'
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP'
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

Solution 3

One rule of thumb is that almost any query that takes more than a second probably uses some TEMP space, and these are not the just ones involving ORDER BYs but also:

1. GROUP BYs (SORT GROUPBY before 10.2 and HASH GROUPBY from 10.2 onwards)
2. HASH JOINs or MERGE JOINs
3. Global Temp Tables (obviously)
4. Index rebuilds

Occasionally, used space in temp tablespaces doesn't get released by Oracle (bug/quirk) so you need to manually drop a file from the tablespace, drop it from the file system and create another one.

Comments

[Simon Gibbs]

Will all of that be recorded v$sort_usage?

[Andrew not the Saint]

According to the Oracle docs, V$TEMPSEG_USAGE contains all different operations which require TEMP space.

[Colin Nicholls]

This is gold. Thanks!

[Mikhail Aksenov]

there is no join by instance id and that's cause multiplication of records