Re: query to find high temp usage
- From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- To: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
- Date: Sat, 2 Feb 2013 10:37:38 +0200
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
Other related posts:
