In our DB Oracle 11gR1 , We are facing a problem of high temporary tablespace usage..
Currently i made the temp tablespace size as 200GB, 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 stands around 75%, i.e 175GB!.
I read on other forums that temp space may not be immediately freed. But 175GB is too much in my opinion
I tried the below queries which will tell the which session is using how much temporary space. But normally the same query will run and 97Gb of temp tablespace is enough to run the query.
col SID_SERIAL for a10
col OSUSER for a10
col MODULE for a10
col PROGRAM for a10
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
or
col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000
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
---------- --------------- ---------- ---------------------------------------- --------- --------- ----------
819 RDW12SYS TEMP 1144004357/3631869000 HASH TEMPORARY 2921088
819 RDW12SYS TEMP 1144004357/3631869000 HASH TEMPORARY 11505792
Query:
INSERT INTO MG_RDW_INTRANSIT_DIFF
(SELECT LC.LOC_IDNT, PRD.ITEM_IDNT, INV.F_I_IN_TRNST_QTY
FROM INV_ITEM_LD_DM_V INV,
ORG_LOC_DM LC,
TIME_DAY_DM DAT,
PROD_ITEM_DM PRD
WHERE INV.ITEM_KEY = PRD.ITEM_KEY
AND INV.LOC_KEY = LC.LOC_KEY
AND INV.DAY_IDNT = DAT.DAY_IDNT
AND INV.F_I_IN_TRNST_QTY <> 0
AND DAT.DAY_DT = (SELECT CURR_LOAD_DT FROM MAINT_LOAD_DT_DM)
MINUS
SELECT TO_CHAR (SOH.LOC), SOH.ITEM, SOH.IN_TRANSIT_QTY
FROM MG_ITEM_LOC_SOH_RDW_DAILY SOH
WHERE SOH.IN_TRANSIT_QTY <> 0)
SQL> @temp
TABLESPACE_NAME FREESPACEGB USEDSPACEGB TOTALSPACEGB INSTANCE_NAME HOST_NAME
------------------------------ ----------- ----------- ------------ ---------------- ------------------------------
TEMP 66.730 111.318 178.049 rdw rdwdb1
But why from last 2 days utilizing 190 GB of temp tablespace.
Can we find it why suddenly starts using more temp tablespace.
There are no changes made in the query.