Oracle Scratchpad

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.

7 Comments »

  1. […] 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 | Reply

  2. […] 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 | Reply

  3. […] 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 | Reply

  4. […] 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 | Reply

  5. […] 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 | Reply

  6. […] 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 | Reply

  7. […] – 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 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.