Re: detect parallel queries that have been serialized

From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>

To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>

Date: Sat, 22 Jun 2013 22:16:57 +0200

Jonathan,
as far as I know the PX_FLAGS column only contains three different infos:
trunc(px_flags / 2097152): The actual DOP (for the current DFO)
trunc(mod(px_flags/65536, 32)): the "PX Step ID" (1 or 2 for PX
operations of the plan, )
mod(px_flags, 65536): The "PX Step ARG" (Allows to identify the (other)
Table Queue involved in the operation)
The latter two are probably used by Real-Time SQL Monitoring to perform
some advanced analysis regarding skew, which is surprisingly part of the
"raw" XML generated by the "Active" version of the report, but not
processed by the "Flash" front-end and therefore only visible when
looking into the XML / HTML code generated.
The information you see in SQL Monitoring reports about downgrades etc.
are actually coming from V$SQL_MONITOR / V$SQL_PLAN_MONITOR (e.g.
V$SQL_MONITOR.PX_MAXDOP, PX_SERVERS_REQUESTED, PX_SERVERS_ALLOCATED
etc.) and are not extracted from ASH I believe.
So there is no easy way to tell "downgrades" purely from ASH I think -
but at least the information about the actual DOP can be very valuable.
Randolf
Am 22.06.2013 13:39, schrieb Jonathan Lewis:
>
> Randolf,
>
> Thanks for that - Josh's post had prompted me to run up an 11.2 instance with
> OEM and see where the SQL monitoring screen was getting its information.
>
> Since the screen (in 11.2, at least) highlights the parallel operations in a
> different colour to show which ones failed to get the expected degree, and
> given your closing comment, would you assume (or have you observed) that one
> of the lower bits on the PX_FLAG is used to show whether the correct degree
> was used or not ?
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
> of Randolf Geist [info@xxxxxxxxxxxxxxxxxxxxxxxxx]
> Sent: 22 June 2013 10:51
> To: Josh Collier; oracle-l@xxxxxxxxxxxxx
> Subject: Re: detect parallel queries that have been serialized
>
> Hi Josh,
>
> I think to remember that you actually have a Diagnostic Pack license -
> if yes you're lucky: From 11.2.0.2 on there is a new, (yet) undocumented
> column PX_FLAGS in the Active Session History (V$ACTIVE_SESSION_HISTORY
> / DBA_HIST_ACTIVE_SESS_HISTORY), and you can determine the *actual*
> Parallel Degree (DOP) used at execution time via this expression:
>
> trunc(px_flags / 20971would be52)
>
> So you can use Active Session History (ASH) to answer this question for
> past executions.
>
> This should be straightforward, except for those complications caused by
> multiple Data Flow Operations (DFOs) in one Parallel execution plan with
> different DOPs, which is not common but possible.
>
> Unfortunately you can't extract the *requested* Parallel Degree from
> ASH, by the way.
>
> Randolf
>
--
http://www.freelists.org/webpage/oracle-l