Re: query to find high temp usage
- From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- To: Niels Jespersen <NJN@xxxxxx>
- Date: Mon, 4 Feb 2013 22:30:06 +0200
The v$sql_workarea_active would show the allocation anyway, even if the
corresponding session(s) happen to be idle during the sampling, so that's
the most reliable option.
From ASH you could perhaps first group the ASH data by 10 seconds or a
minute and sum together the MAX allocation values across that grouping
period ... this may over-report the actual usage though, as perhaps one
slave used 100 MB in the beginning of the 10seconds and released this
memory by the time some other slave allocated it, but the MAX value would
still show 100MB ... but yep that's a limitation of ASH - by default idle
sessions don't show up in there and there may be gaps when summing the new
memory usage columns together.
Writing a little collector of v$sql_workarea_active is just a couple of
lines of PL/SQL code (just insert-select), so I've sometimes scheduled such
scripts for better troubleshooting.... it's basically like ASH for workarea
memory usage :)
--
*Tanel Poder*
*New Advanced Oracle Troubleshooting v2.1 and*
*Advanced SQL Tuning v2.0 classes this spring!*
http://blog.tanelpoder.com/seminar/
On Sun, Feb 3, 2013 at 7:13 PM, Niels Jespersen <NJN@xxxxxx> wrote:
> 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:
