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.
[…] 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 |
[…] 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 |
[…] 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 |
[…] 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 GMT Feb 12,2015 |
[…] 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 |
[…] my test system it took 103 second to complete. And If we look at the V$PQ_TQSTAT […]
Pingback by Automatic Parallel Outer Join Null Handling | Chinar Aliyev`s blog — April 10, 2019 @ 6:29 am BST Apr 10,2019 |
[…] – it’s just a little more convenient to be able to embed that in the SQL. The pq_tqstat script is one I published some time ago to report the contents of the session-specific dunamic […]
Pingback by Interval Partition(s) | Oracle Scratchpad — February 18, 2020 @ 1:45 pm GMT Feb 18,2020 |
[…] one way of checking the actual degree used is to query v$pq_tqstat immediately afterwards (see https://jonathanlewis.wordpress.com/parallel-work/ […]
Pingback by SQL Query Tuning | Cloud On Hand — May 16, 2021 @ 9:37 am BST May 16,2021 |