Re: detect parallel queries that have been serialized
Date: Fri, 28 Jun 2013 22:13:02 -0700 (PDT)
Message-ID: <1372482782.35671.YahooMailNeo_at_web121606.mail.ne1.yahoo.com>
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> To: "info_at_www.sqltools-plusplus.org" <info_at_www.sqltools-plusplus.org>; Josh Collier <Josh.Collier_at_banfield.net>; "oracle-l_at_freelists.org" <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 [oracle-l-bounce_at_freelists.org] on behalf of Randolf Geist [info_at_www.sqltools-plusplus.org] Sent: 22 June 2013 10:51
To: Josh Collier; 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-lReceived on Sat Jun 29 2013 - 07:13:02 CEST