Temporary tablespace usage
- From: Raju Angani <angani@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 15 Dec 2011 18:19:05 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts: