Temporary tablespace usage

From: Raju Angani <angani_at_gmail.com>
Date: Thu, 15 Dec 2011 18:19:05 -0800
Message-ID: <CAJ-04OpoQxNnpxkY2jf7P3Qqtw7Fk34ODisq3jW3GAiuZ_r8rQ_at_mail.gmail.com>

Hi All,

I have been looking into a temporary tablespace usage issue, and I notice there is a gradual increase of the temporary tablespace for a given session.

Below is the query I have used to find the temporary tablespace usage, but for every execute(say 1sec) of the below statement, the sql_id/sql_text keeps changing. I enabled the trace for the sessionid, but couldn't find any relevant sql statement which is causing the problem.

Could someone help me with some steps or approach on how to troubleshoot this problem.

Server/DB information:
OS: RHEL5
DB: 11.2.0.1.0

--Find temporary tablespace usage

SELECT sysdate "TIME_STAMP", su.username, su.sql_id, su.tablespace, su.usage_mb, vst.sql_text, vp.spid

      FROM
      (
              SELECT username, sqladdr, sqlhash,
                     sql_id, tablespace, session_addr,
                     sum(blocks)*8192/1024/1024 "USAGE_MB"
              FROM v$sort_usage
              HAVING SUM(blocks)> 1000
              GROUP BY username, sqladdr, sqlhash, sql_id, tablespace,
session_addr
      ) su,
      v$sqltext vst,
      v$session vs,
      v$process vp
WHERE su.sql_id = vst.sql_id
  AND su.sqladdr = vst.address
  AND su.sqlhash = vst.hash_value

  AND su.session_addr = vs.saddr
  AND vs.paddr = vp.addr
  AND vst.piece = 0
  AND USAGE_MB > 600
ORDER BY SPID ; Sample data from the above query when run in quick intervals.

---

TIME_STAMP                  USERNAME SQL_ID       TABLESPACE  USAGE_MB
    SQL_TEXT
        SPID


---------- ------------------------------

-------------------------------------------- ----------

---------------------------------------------------------------------------------------------------------------------


12/15/2011 6:05:30 PM	PADMIN	b5n1usr0nzp3p	TEMP	1170	UPDATE PX_TSO_ST
SET LAST_PEF_TIME = :1 WHERE ID = :2	20786
12/15/2011 6:05:30 PM	PADMIN	g08yksa6zh7rh	TEMP	1211	insert /*+ append
*/ into PX_HIT_ATT1_60(TIME_ID, SHAPE_ID,	        20790

12/15/2011 6:10:48 PM	PADMIN	7sy6n2c9w7m32TEMP	1177	UPDATE
PX_MACHINE_PART SET MPART_ID = :1, GRIP_ID = :2 	20786
12/15/2011 6:10:48 PM	PADMIN	2x31xnrqm7n95	TEMP	1218	BEGIN
insert_part_proc(:1, :2, :3, :4); END;	
        20790

12/15/2011 6:11:04 PM	PADMIN	9qx7fk138hjx8	TEMP	1177	select
t1.DPART_KEY from PX_DPORT t1 left outer join PX_DPO  	20786
12/15/2011 6:11:04 PM	PADMIN	2x31xnrqm7n95	TEMP	1218	BEGIN
insert_part_proc(:1, :2, :3, :4); END;	
        20790

Thank you
RA
--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 15 2011 - 20:19:05 CST