SV: query to find high temp usage
- From: Niels Jespersen <NJN@xxxxxx>
- To: "tanel@xxxxxxxxxxxxxx" <tanel@xxxxxxxxxxxxxx>
- Date: Sun, 3 Feb 2013 17:13:46 +0000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] På
vegne af Tanel Poder
Sendt: 2. februar 2013 09:38
Til: Josh Collier
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx>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 11.2.0.2?
>
> Thanks for your time,
>
> Josh C.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » SV: query to find high temp usage - Niels Jespersen
