4 Replies Latest reply: Sep 28, 2012 3:04 AM by 927467 RSS

    HIGH Temp tablespace usage

      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

          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

          • 2. Re: HIGH Temp tablespace usage
            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,
                       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,
                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
              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
                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