SV: query to find high temp usage

From: Niels Jespersen <>
Date: Sun, 3 Feb 2013 17:13:46 +0000
Message-ID: <3967181ED595B445B1E39718204FD4BD13C894B4_at_SRVEXC5.dst.local>

I am also interested in tracking temp usage over time by selected long-running sql's. Using ash is easy enough tracking a single session, but I have problems aggregating a parallel execution. Not all slaves are present in every sample which makes simple aggregation unreliable. Any ideas?

Regards Niels Jespersen

-----Oprindelig meddelelse-----
Fra: [] På vegne af Tanel Poder Sendt: 2. februar 2013 09:38
Til: Josh Collier
Emne: Re: query to find high temp usage

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 <> | Troubleshooting<>
 | 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 Sun Feb 03 2013 - 18:13:46 CET

Original text of this message