Re: detect parallel queries that have been serialized

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 2 Jul 2013 11:03:57 -0700 (PDT)
Message-ID: <1372788237.21823.YahooMailNeo_at_web121604.mail.ne1.yahoo.com>



PX_MAXDOP should give you the actual DoP set for the query if it is not NULL; NULL values for this column equate to a DoP of 1.  I get a number of values for this column from several of the databases I administer and, looking at the plans generated, they agree with the DoP set for the query (this on Exadata, where Auto DoP is configured).

David Fitzjarrell  



 From: Josh Collier <Josh.Collier_at_banfield.net> To: David Fitzjarrell <oratune_at_yahoo.com>; "jonathan_at_jlcomp.demon.co.uk" <jonathan_at_jlcomp.demon.co.uk>; "info_at_www.sqltools-plusplus.org" <info_at_www.sqltools-plusplus.org>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, July 2, 2013 10:57 AM
Subject: RE: detect parallel queries that have been serialized   

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_at_yahoo.com] Sent: Friday, June 28, 2013 10:13 PM
To: jonathan_at_jlcomp.demon.co.uk; info_at_www.sqltools-plusplus.org; Josh Collier; oracle-l_at_freelists.org 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_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> To: "info_at_www.sqltools-plusplus.org<mailto:info_at_www.sqltools-plusplus.org>" <info_at_www.sqltools-plusplus.org<mailto:info_at_www.sqltools-plusplus.org>>; Josh Collier <Josh.Collier_at_banfield.net<mailto:Josh.Collier_at_banfield.net>>; "oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> 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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Randolf Geist [info_at_www.sqltools-plusplus.org<mailto:info_at_www.sqltools-plusplus.org>] Sent: 22 June 2013 10:51
To: Josh Collier; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jul 02 2013 - 20:03:57 CEST

Original text of this message