Grokbase › Groups › Oracle › oracle-l › December 2011
FAQ
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

Search Discussions

  • Adric Norris at Dec 16, 2011 at 8:37 pm ⇧
    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 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
  • Adric Norris at Dec 16, 2011 at 10:50 pm ⇧
    Unfortunately, this is purely an application issue... the LOB segments
    won't be released until the session terminates, *unless* they're explicitly
    closed. In our case, the only immediately viable solution was to recycle
    the application (or kill the relevant SID) since so many sessions were
    affected. The long-term solution was an application-side code change, to
    call DBMS_LOB.CLOSE at the appropriate times.
    On Fri, Dec 16, 2011 at 16:07, Raju Angani wrote:

    Adric,

    you were spoton, when I added the segtype as you recommended, all
    those rows showed as LOB_DATA.
    Now, is there a way I can make a fix to shrink the temporary tablespace.

    Thank you
    RA

    --
    http://www.freelists.org/webpage/oracle-l
  • Raju Angani at Dec 16, 2011 at 11:24 pm ⇧
    I see selects/insert/update/delete statements which have blob datatype
    on those tables.
    e.g
    update table_name set blob_col1 =:2 where id = :16
    insert into table_name(blob_col1) values(:1);
    delete from table_name where blob_id = :1;

    So it ok to select/DML(select/update/insert/delete) operations on blob
    columned tables without using the dbms_lob package?

    Thank you
    RA

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 16, '11 at 2:20a
activeDec 16, '11 at 11:24p
posts4
users2
websiteoracle.com

2 users in discussion

Raju Angani: 2 posts Adric Norris: 2 posts

Content

People

Support

Translate

site design / logo © 2022 Grokbase