What was the actual DOP for my query ? (ASH and the PX_FLAGS)

In this post i will show a quick way to check for the actual DOP for a past query(Only for version higher than 11.2.0.2).We will be using for that ASH and the undocumented column PX_FLAGS (Randolf Geist already talked about that and used it in his awesome utility XPLAN_ASH http://oracle-randolf.blogspot.com/2014/03/new-version-of-xplanash-utility.html)

Let’s begin with a simple query using one DFO (Like you may know it’s not the query that run in parallel. It’s the Data flow operations and a query may contain many of them)

Limit the max number of parallel process and then run a parallel query with a requested degree of 4.

hatem@ORCL> alter system set parallel_max_servers=2;
SystÞme modifiÚ.

hatem@ORCL> select /*+ parallel(4) */ count(*) from t1,t2,t3;

COUNT(*)
———-
100000000

1 ligne sÚlectionnÚe.

Check the view (V$PQ_TQSTAT The statistics are compiled after the query completes and only remain for the duration of the session) :

hatem@ORCL> SELECT dfo_number, tq_id, server_type, process, num_rows
2 FROM V$PQ_TQSTAT ORDER BY dfo_number DESC, tq_id, server_type, process;

DFO_NUMBER TQ_ID SERVER_TYP PROCES NUM_ROWS
———-          ———-   ———-            ——       ———-
1             0       Consumer         QC 2
1                           0       Producer           P000        1
1                           0       Producer           P001    1

hatem@ORCL> select DFO_NUMBER,count(distinct process) from v$pq_tqstat where process like ‘P%’ group by DFO_NUMBER;

DFO_NUMBER COUNT(DISTINCTPROCESS)
———- ———————-
1 2

So it’s clear that our query was downgraded, we use only 2 parallel process (we requested 4)

But what to do if we want to check the result later for a past query ? Let’s now check the same result from ASH.

SQL> select session_id,QC_SESSION_ID,trunc(px_flags / 2097152),mod(px_flags, 65536) from v$active_session_history where sql_id = ‘1wkw4pamcra3p’;

SESSION_ID QC_SESSION_ID TRUNC(PX_FLAGS/2097152) MOD(PX_FLAGS,65536)
———- ————-                             ———————–         ——————-
26                  40                             2                                                 3
26                  40                             2                                                 3

Like explained by Randolf Geist : (http://www.freelists.org/post/oracle-l/detect-parallel-queries-that-have-been-serialized,3)
“

trunc(px_flags / 2097152): The actual DOP (for the current DFO)
trunc(mod(px_flags/65536, 32)): the “PX Step ID” (1 or 2 for PX operations of the plan, )
mod(px_flags, 65536): The “PX Step ARG” (Allows to identify the (other) Table Queue involved in the operation)

”

So we can now get the actual dop form ASH and that’s cool !

What about the case of multiple DFO ?

You can try an example using multiple DFO using Jonathan Lewis Sample code you can find it in the comment section (http://jonathanlewis.wordpress.com/2007/03/14/how-parallel/) and check the ASH data.

Hope this help 😀