RE: detect parallel queries that have been serialized

  • From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
  • To: David Fitzjarrell <oratune@xxxxxxxxx>, "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "info@xxxxxxxxxxxxxxxxxxxxxxxxx" <info@xxxxxxxxxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Jul 2013 16:57:09 +0000

Hello my friends,
Thank you so much for this information. How does one go about detecting all the 
other ways to split up the value in px_flags?

I am trying to figure a way out to parse this value to get the requested DoP.

Josh C.

From: David Fitzjarrell [mailto:oratune@xxxxxxxxx]
Sent: Friday, June 28, 2013 10:13 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; info@xxxxxxxxxxxxxxxxxxxxxxxxx; Josh Collier; 
oracle-l@xxxxxxxxxxxxx
Subject: Re: detect parallel queries that have been serialized

I'm running 11.2.0.3 and the V$SQL_MONITOR view provides the PX_MAXDOP column; 
if you have that view you could run this query:

select sql_id, sql_text, nvl(px_maxdop, 1) px_maxdop
from v$sql_monitor
/

and look for PX_MAXDOP values of 1.

David Fitzjarrell


From: Jonathan Lewis 
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>
To: "info@xxxxxxxxxxxxxxxxxxxxxxxxx<mailto:info@xxxxxxxxxxxxxxxxxxxxxxxxx>" 
<info@xxxxxxxxxxxxxxxxxxxxxxxxx<mailto:info@xxxxxxxxxxxxxxxxxxxxxxxxx>>; Josh 
Collier <Josh.Collier@xxxxxxxxxxxx<mailto:Josh.Collier@xxxxxxxxxxxx>>; 
"oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>" 
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Sent: Saturday, June 22, 2013 5:39 AM
Subject: RE: detect parallel queries that have been serialized


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<mailto:oracle-l-bounce@xxxxxxxxxxxxx> 
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf 
of Randolf Geist 
[info@xxxxxxxxxxxxxxxxxxxxxxxxx<mailto:info@xxxxxxxxxxxxxxxxxxxxxxxxx>]
Sent: 22 June 2013 10:51
To: Josh Collier; oracle-l@xxxxxxxxxxxxx<mailto: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--
//www.freelists.org/webpage/oracle-l




--
//www.freelists.org/webpage/oracle-l


Other related posts: