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.1
rem		11.2.0.3
rem		10.2.0.3
rem		 9.2.0.8
rem		 8.1.7.4
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.

3 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers