Re: query to find high temp usage

From: Tanel Poder <>
Date: Sat, 2 Feb 2013 10:37:38 +0200
Message-ID: <>

Look into TEMP_SPACE_ALLOCATED (and PGA_ALLOCATED) fields of DBA_HIST_ACTIVE_SESS_HISTORY. Some more performance counter fields (like read & write IO requests & bytes) have been introduced into ASH in Oracle 11.2.
These counters are not necessarily tied to the SQL_ID ASH record shows (as the allocation may have been made just a few milliseconds before the ASH sample was taken), but in practice, if a SQL uses a lot of TEMP, its SQL_ID will show up in ASH around the memory allocations too... And starting from 11g, you can use the SQL_PLAN_LINE* fields in ASH to tie the allocation back to the rowsource doing the allocation (but the same sampling-related limitations apply in ASH).

If that's not enough, then write a little PL/SQL loop which saves the contents of V$SQL_WORKAREA_ACTIVE every minute, it will give you everything you need ...

*Tanel Poder*
Enkitec (The Exadata Experts)
Training <> |
 | Exadata<>
 | Voicee App <>

On Fri, Feb 1, 2013 at 7:45 PM, Josh Collier <>wrote:

> I am having a hard time finding queries that consistently use a lot of
> temp i/o for sorts and joins. In the AWR.
> Anyone have any quick pointers for
> Thanks for your time,
> Josh C.
> --
Received on Sat Feb 02 2013 - 09:37:38 CET

Original text of this message