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 126.96.36.199 rem 188.8.131.52 rem 10.2.0.3 rem 184.108.40.206 rem 220.127.116.11 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 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 select dfo_number, tq_id, server_type, process, num_rows, bytes, waits, timeouts, avg_latency, instance from v$pq_tqstat order by dfo_number, tq_id, server_type desc, 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.