SV: query to find high temp usage
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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] På vegne af Tanel Poder
Sendt: 2. februar 2013 09:38
Til: Josh Collier
Cc: oracle-l_at_freelists.org
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 <http://blog.tanelpoder.com/seminar/> | Troubleshooting<http://blog.tanelpoder.com/> | Exadata<http://www.amazon.com/Expert-Oracle-Exadata-Apress/dp/1430233923> | Voicee App <http://voic.ee/> On Fri, Feb 1, 2013 at 7:45 PM, Josh Collier <Josh.Collier_at_banfield.net>wrote:Received on Sun Feb 03 2013 - 18:13:46 CET
> 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 11.2.0.2?
>
> Thanks for your time,
>
> Josh C.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l