Re: Temporary tablespace usage
- From: Adric Norris <landstander668@xxxxxxxxx>
- To: angani@xxxxxxxxx
- Date: Fri, 16 Dec 2011 14:36:22 -0600
I seem to recall a few cases where an application created temporary LOBS
without closing them, which would then persist until the end of the
session. Superficially, at least, this seemed very similar to the scenario
you're describing.
Try including the SEGTYPE column of v$sort_usage... I'd expect to see a lot
of space used for LOB_DATA/INDEX if this is a similar situation.
On Thu, Dec 15, 2011 at 20:19, Raju Angani <angani@xxxxxxxxx> wrote:
> 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 ;
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
