Re: detect parallel queries that have been serialized

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
  • To: Josh Collier <Josh.Collier@xxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 22 Jun 2013 11:51:18 +0200

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 / 2097152)

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

> 11.2.0.2 linux 64 bit.
> I have a problem with some parallel queries being serialized. I am trying to 
> determine which queries where serialized after the fact. Short of catching 
> the query in the act via v$px_session, is there a way to identify queries 
> that have been serialized over a longer period of time? Perhaps a query 
> against the AWR. I have been wracking my brain on this one.

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


Other related posts: