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
rem	Script:		pq_tqstat.sql
rem	Dated:		March 2001
rem	Author:		Jonathan Lewis
rem	Purpose:	Report PX message passing after the event
rem
rem	Last tested 
rem		12.1.0.2
rem		11.2.0.4
rem		10.2.0.5
rem		 9.2.0.8
rem		 8.1.7.4
rem
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
rem	The DFOs are 'pushed down', so number 1 will be the most recent
rem	if you have several outstanding sets of results
rem
rem	TQ	Table Queue
rem	DFO	Data Flow Operation
rem
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).
rem

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

select
        dfo_number, tq_id, server_type, instance, process,
        num_rows, bytes,
        round(
                100 * ratio_to_report(num_rows) over (
                        partition by dfo_number, tq_id, server_type, instance
                ),2
        )       row_share,
        round(
                100 * ratio_to_report(bytes)    over (
                        partition by dfo_number, tq_id, server_type, instance
                ),2
        )       data_share,
        waits,
        timeouts,
        avg_latency
from
        v$pq_tqstat
order by
        dfo_number,
        tq_id,
        server_type desc,
        instance,
        process
;

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.