Re: Temporary tablespace growth

Charles Hooper wrote:
> hgha wrote:
> > my temporary tablespace has grown to it's maximum (32GB) in two
> > separate incidents. is there
> > any way I can find out what query has been responsible for this growth?
> > V$sort_usage doesn't tell me much.
> >
> > thanks
>
> In addition to what has already been suggested, one possible way to
> track this down, assuming that the user who executed the SQL statement
> has not disconnected from Oracle, and the SQL statement has not aged
> out of the shared pool, is to use something similar to the following to
> track down the user who made extensive use of the temporary tablespace:
> SELECT
> S.SID,
> S.USERNAME,
> S.PROGRAM,
> S.MACHINE,
> S.OSUSER,
> SN.NAME,
> SS.VALUE
> FROM
> V$SESSION S,
> V$SESSTAT SS,
> V$STATNAME SN
> WHERE
> SN.STATISTIC#=SS.STATISTIC#
> AND SN.NAME IN ('physical writes direct temporary
> tablespace','physical writes direct','sorts (disk)','workarea
> executions - multipass')
> AND SS.VALUE>0
> AND SS.SID=S.SID
> AND S.USERNAME IS NOT NULL
> ORDER BY
> S.SID,
> SN.NAME;
>
> You may also be able to use the wait event interface to track down the
> user:
> SELECT
> S.SID,
> S.USERNAME,
> S.PROGRAM,
> S.MACHINE,
> S.OSUSER,
> SE.EVENT,
> SE.TIME_WAITED
> FROM
> V$SESSION S,
> V$SESSION_EVENT SE
> WHERE
> SE.EVENT IN ('direct path write','direct path read','direct path
> write temp','direct path read temp')
> AND SE.SID=S.SID
> AND S.USERNAME IS NOT NULL
> ORDER BY
> SE.TIME_WAITED DESC;
>
> Tracking down the SQL statements:
> SELECT
> SQL_TEXT,
> SHARABLE_MEM,
> PERSISTENT_MEM,
> RUNTIME_MEM,
> SORTS,
> FETCHES,
> EXECUTIONS,
> FIRST_LOAD_TIME,
> DIRECT_WRITES,
> PARSING_USER_ID
> FROM
> V$SQL
> WHERE
> PARSING_USER_ID=offending_sid_here
> AND SORTS>0;
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Thanks you. These queries helped me find out who has the most use of temporary tablespace. Received on Thu Dec 07 2006 - 11:19:07 CST