RE: detect parallel queries that have been serialized

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 22 Jun 2013 11:39:32 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9A705A2_at_exmbx09.thus.corp>

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 Received on Sat Jun 22 2013 - 13:39:32 CEST