Re: Temporary tablespace usage

From: Adric Norris <landstander668_at_gmail.com>
Date: Fri, 16 Dec 2011 14:36:22 -0600
Message-ID: <CAJueESqaYBQ0Yu4fQk947FQ4ZPL6pbRUERvHjp-yD3neWd6VUQ_at_mail.gmail.com>

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_at_gmail.com> 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 ;
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 16 2011 - 14:36:22 CST