|Author's name: Andy Brooker
Author's Email: email@example.com
|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
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