In this post i will show a quick way to check for the actual DOP for a past query(Only for version higher than 126.96.36.199).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;
hatem@ORCL> select /*+ parallel(4) */ count(*) from t1,t2,t3;
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
hatem@ORCL> select DFO_NUMBER,count(distinct process) from v$pq_tqstat where process like ‘P%’ group by DFO_NUMBER;
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)
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 😀