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 |
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?
