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 144
set trimspool on
set pagesize  24
set arraysize 25

column server_type format a15
column process     format a15
column instance    format 999

column row_share  format 990.00
column data_share format 990.00

break on dfo_number skip 1 on tq_id skip 1 on server_type

set null n/a

spool pq_tqstat

        dfo_number, tq_id, server_type, instance, process,
        num_rows, bytes,
                100 * ratio_to_report(num_rows) over (
                        partition by dfo_number, tq_id, server_type, instance
        )       row_share,
                100 * ratio_to_report(bytes)    over (
                        partition by dfo_number, tq_id, server_type, instance
        )       data_share,
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 BST 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 BST 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 BST 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 BST Feb 12,2015 | Reply

  5. […] to my hint the query has been given a parallel execution plan – and a check of v$pq_tqstat after running the query showed that it had run parallel. Note, however, where the PX SEND QC and PX […]

    Pingback by Parallel Query | Oracle Scratchpad — May 12, 2015 @ 7:23 pm BST May 12,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by