Oracle Scratchpad

Parallel Work

If you can execute a query you can find out the distribution of work across parallel execution slaves by querying the dynamic performance view v$pq_tqstat which is populated by your session after you’ve executed a statement that did some parallel work. Here’s a simple script dumping the contents:

rem	Script:		pq_tqstat.sql
rem	Dated:		March 2001
rem	Author:		Jonathan Lewis
rem	Purpose:	Report PX message passing after the event
rem	Last tested 
rem	Notes:
rem	Ideally, run your parallel query, then run this query immediately
rem	afterwards.  In most cases there will then be only one DFO available.
rem	The DFOs are 'pushed down', so number 1 will be the most recent
rem	if you have several outstanding sets of results
rem	TQ	Table Queue
rem	DFO	Data Flow Operation
rem	Under Oracle 7 and (possibly) 8.0, you could get only one 
rem	DFO for any SQL statement, and a maximum of 2 sets of parallel
rem	query slaves.  From 8.1 onwards, it is possible to get multiple
rem	DFOs for a single SQL statement - each DFO can then operate two
rem	layers of PX slaves. If this occurs, I have found that sometimes
rem	only one or two of the DFOs for the statement survive in v$pq_tqstat.
rem	(v$px_sesstat does not have this problem (see px_stats_92.txt) but
rem	v$px_sesstat data does disappear as soon as the query ends).

set linesize 120
set trimspool on
set pagesize 24
set arraysize 25

column server_type format a11
column process     format a7

break on dfo_number skip 1 on tq_id skip 1 on server_type

spool pq_tqstat

order by
	server_type desc,

spool off

The ordering on server_type is quite important – it gets “rangers” before “producers” and “consumers” in a table queue, and that’s exactly the order in which the activity has to go.

There a similar note on my old website with a few more comments and a sample of output, and a recent example on the blog of using the query to investigate a feature of “Top N” running in parallel on 12c.


  1. […] queries (2001) to report all parameters, including the hidden ones – session and system level Parallel Workloaad: a query to run immediately after a parallel operation in the same session to report the […]

    Pingback by Simple scripts | Oracle Scratchpad — July 12, 2013 @ 8:06 am GMT Jul 12,2013 | Reply

  2. […] I have just published an example of the code I use to produce the output from v$pq_tqstat in my script catalogue. (There are also a few notes the view it on my old […]

    Pingback by 12c Top N (px) | Oracle Scratchpad — July 12, 2013 @ 8:25 am GMT Jul 12,2013 | Reply

  3. […] The 2,635 rows reported as A-rows in line 7 is consistent with the id1 values that would have been extracted in line 3, so it does look as if the Bloom filter has been created at line 2 and used as the predicate in line 7, even though this plan has run as a serial plan. (No slaves started, and no rows returned by querying v$pq_tqstat.) […]

    Pingback by Bloom Filter | Oracle Scratchpad — August 5, 2013 @ 9:22 pm GMT Aug 5,2013 | Reply

  4. […] you look at v$pq_tqstat after running this statement the results are a little disappointing – there are a few […]

    Pingback by Parallel rownum | Oracle Scratchpad — February 12, 2015 @ 7:28 am GMT Feb 12,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 4,750 other followers