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             19.11.0.0
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
rem     Update (19.11)
rem     When a Ranger is involved the Producers in a table Queue (tq_id) pass data 
rem     to the Ranger (which will be the QC) to sample before distributing the full
rem     data set to the Consumers, hence a recent update to order the server_types
rem     through a decode() in the order producer, range, consumer. If you sum the
rem     bytes column for a tq_id you should find:
rem             producer bytes = consumer bytes + ranger bytes.
rem
rem     NB: The server_type is (in at least some versions) padded with spaces to 
rem     10 characters, hence the trim() in the decode(). You may find in some old
rem     versions of Oracle that it is padded with chr(0).
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,
        decode(
                trim(server_type),
                        'Producer', 1,
                        'Ranger',   2,
                        'Consumer', 3
        ),
        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 (now archived) 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.