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	Run your parallel query, then run this query immediately afterwards.
rem

set linesize 156
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 “Producers” before “Consumers” in a table queue, and that’s exactly the order in which the activity has to go. (Some versions of Oracle will get a little messed up and assign all the Rangers to table queue zero; and there are other defects in the way Oracle populate the structure in older versions.)

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