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.
Received on Wed Dec 06 2006 - 16:31:44 CST
