This discussion is archived
4 Replies Latest reply: Sep 28, 2012 1:04 AM by 927467 RSS

HIGH Temp tablespace usage

927467 Newbie
Currently Being Moderated
In our DB Oracle 11gR1 , We are facing a problem of high temporary tablespace usage..
Currently i made the temp tablespace size as 100GB, but im afraid, That is going to be used up too.

My concern is
1.When i checked in gv$sessions, gv$sql table, no SQL is running, which is using up temp spaces, But the current usage of temp space staands around 75%, i.e 75GB!.
I read on other forums that temp space may not be immediately freed. But 75GB is too much in my opinion

2. Is there any way to know which query is using up so high temp space? In 11gr2, There is a column DBA_HIST_ACTIVE_SESS_HISTORY.TEMP_SPACE_ALLOCATED which makes life easy. But anything of that sort, or any join in 11gr1 which can tell us which query used how much temp space?

3. Also what is the pattern of temp space usage in global temporary tables?. Some of our code contains usage of temporary tables, will the used tablespace re-allocated once the transaction is complete?


Thanks in anticipation of help.
  • 1. Re: HIGH Temp tablespace usage
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    Please review:
    Oracle Recommended Patches For Tablespace Monitoring using the Tablespace Space Used % metric [ID 849498.1]
    How Can Temporary Segment Usage Be Monitored Over Time? [ID 364417.1]
    Also see:
    Temporary Tablespace

    Regard
    Helios
  • 2. Re: HIGH Temp tablespace usage
    952768 Journeyer
    Currently Being Moderated
    Is there information in the view: select * from v$sort_usage; ?

    Or check on regular base:
    SELECT   s.sid "SID",
             s.username "User",
             s.program "Program",
             u.tablespace "Tablespace",
             u.contents "Contents",
             u.extents "Extents",
             u.blocks * 8 / 1024 "Used Space in MB",
             q.sql_text "SQL TEXT",
             a.object "Object",
             k.bytes / 1024 / 1024 "Temp File Size"
      FROM   v$session s,
             v$sort_usage u,
             v$access a,
             dba_temp_files k,
             v$sql q
     WHERE       s.saddr = u.session_addr
             AND s.sql_address = q.address
             AND s.sid = a.sid
             AND u.tablespace = k.tablespace_name;
    You could also check the long operations
    SELECT   sl.sid,
               sl.serial#,
               SYSDATE,
               TO_CHAR (sl.start_time, 'DD-MON-YYYY:HH24:MI:SS') start_time,
               ROUND ( (sl.elapsed_seconds / 60), 2) min_elapsed,
               ROUND ( (sl.time_remaining / 60), 2) min_remaining,
               sl.opname,
               sl.MESSAGE
        FROM   v$session_longops sl, v$session s
       WHERE   s.sid = sl.sid AND s.serial# = sl.serial# AND sl.opname like 'Sort%' 
    ORDER BY   sl.start_time DESC, sl.time_remaining ASC;
  • 3. Re: HIGH Temp tablespace usage
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Use V$TEMPSEG_USAGE to monitor usage of the temp tablespace.

    Note that as the usage grows, extents are allocated to the temporary segment. They do NOT shrink (or deallocate) when usage falls. The Temporary segment always remains at the highest achieved size. So the 75GB "usage" for the tablespace that you see would have been the peak usage. Usage would only shrink when you shutdown and restart the instance (or if you create and designate a new temporary tablespace and drop the existing one).


    Hemant K Chitale
  • 4. Re: HIGH Temp tablespace usage
    927467 Newbie
    Currently Being Moderated
    Hi , I havent restarted or deleted the temp tablespace, but at the moment the usage is very minimal almost 0.01% . from dba_tablespace_usage_metrics.
    Im following the ID 364417 metalink , which is given by above user, will let you guys know if i find something interesting

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points