The Oracle (tm) Users' Co-Operative FAQ

How can I associate the parallel query slaves with the session that's running the query?


Author's name: Andy Brooker

Author's Email: andy@onezero.co.uk

Date written: 27th July 2001

Oracle version(s): 8.0.4+

At any particular point in time it can be useful to see how many PQ slaves are allocated/available and which session each slave is supporting. This snapshot approach to analysing what is going on can help when tuning the PQ parameters. This particular script was actually born of the need to tie PQ slave wait events to actual sessions whilst analysing a non-specific data warehouse performance problem. If you are using Oracle version 7.3 or 8.0.3 then things are rather more complicated and I refer you instead to Metalink note 50739.1

Back to index of questions


One way to associate each (allocated) PQ slave with its parent is via v$lock since the parent session raises a PS type lock on the child PQ slave, the id of which is identified by column id2 of the relevant row in v$lock. We can then use the PQ slave id to link to v$px_process and identify the actual slave session.

The following script will return , for each PQ slave, the parent session (if applicable) and the wait event for both the child and parent sessions.

	column child_wait  format a30
	column parent_wait format a30
	column server_name format a4  heading 'Name'
	column x_status    format a10 heading 'Status'
	column schemaname  format a10 heading 'Schema'
	column x_sid format 9990 heading 'Sid'
	column x_pid format 9990 heading 'Pid'
	column p_sid format 9990 heading 'Parent'

	break on p_sid skip 1

	select x.server_name
	     , x.status as x_status
	     , x.pid as x_pid
	     , x.sid as x_sid
	     , w2.sid as p_sid
	     , v.osuser
	     , v.schemaname
	     , w1.event as child_wait
	     , w2.event as parent_wait
	from  v$px_process x
	    , v$lock l
	    , v$session v
	    , v$session_wait w1
	    , v$session_wait w2
	where x.sid <> l.sid(+)
	and   to_number (substr(x.server_name,2)) = l.id2(+)
	and   x.sid = w1.sid(+)
	and   l.sid = w2.sid(+)
	and   x.sid = v.sid(+)
	and   nvl(l.type,'PS') = 'PS'
	order by 1,2
	/

How can this help setting PQ parameters?
Well, PQ slaves are allocated dynamically up to a maximum of parallel_max_servers concurrent slaves for the instance as a whole. If all of the PQ slaves have been started and are in use, and yet as a group are serving few of the current database querys (ie have few parent sessions), then one may feel that more slaves should be made available or indeed that degrees of parallelism should be reduced. Oracle do offer parallel_automatic_tuning as an alternative option to manual configuration but which ever method you choose, you may still like the comfort of running a few ad hoc on-the-spot checks.

How did it help the non-specific performance problem?
Basically the daily feed was suffering because steps in that batch that ran concurrently had offered erratic timings and were generally much slower than when performed in a serial manner. Analysis of the PQ slaves and associated parents led us to increase (treble) the maximum number of slaves possible. Having eliminated any obvious problems with the PQ configuration we were then able to see many I/O waits which ultimately led us to a hardware bottleneck in the disk system.

The following list is an example of the output from the script.

	SQL> @parallel
	Parallel Processes

	Name Status       Pid   Sid Parent OS USer   Schema   CHILD_WAIT                  PARENT_WAIT
	---- ---------- ----- ----- ------ --------- -------- --------------------------- ---------
	P000 IN USE        18    20     61 asmith    SCOTT    PX Deq: Execution Msg       SQL*Net message from client
	P001 IN USE        19    27     61 asmith    SCOTT    PX Deq: Execution Msg       SQL*Net message from client
	P002 IN USE        20   112     61 asmith    SCOTT    PX Deq: Execution Msg       SQL*Net message from client
	P003 IN USE        21    54     61 asmith    SCOTT    PX Deq: Execution Msg       SQL*Net message from client

	P004 AVAILABLE     22
	P005 AVAILABLE     23
	P006 AVAILABLE     25
	P007 AVAILABLE     26

	P008 IN USE        39    72     78 bjones    SCOTT    PX Deq Credit: send blkd    SQL*Net message from client
	P009 IN USE        45   101     78 bjones    SCOTT    PX Deq Credit: send blkd    SQL*Net message from client
	P010 IN USE        48    82     78 bjones    SCOTT    PX Deq Credit: send blkd    SQL*Net message from client
	P011 IN USE        49    11     78 bjones    SCOTT    PX Deq Credit: send blkd    SQL*Net message from client

	P012 IN USE        53     8     94 cblack    SCOTT    direct path read            PX Deq: Execute Reply
	P013 IN USE        54    75     94 cblack    SCOTT    direct path read            PX Deq: Execute Reply
	P014 IN USE        55    50     94 cblack    SCOTT    direct path read            PX Deq: Execute Reply
	P015 IN USE        56   108     94 cblack    SCOTT    buffer busy waits           PX Deq: Execute Reply
	P016 IN USE        59    90     94 cblack    SCOTT    PX Deq: Execution Msg       PX Deq: Execute Reply
	P017 IN USE        63   116     94 cblack    SCOTT    PX Deq: Execution Msg       PX Deq: Execute Reply
	P018 IN USE        64    12     94 cblack    SCOTT    PX Deq: Execution Msg       PX Deq: Execute Reply
	P019 IN USE        65    68     94 cblack    SCOTT    PX Deq: Execution Msg       PX Deq: Execute Reply

	20 rows selected.

	SQL>

Further reading: any suggestions anyone?


Back to top

Back to index of questions