GV$PX_PROCESS

This contains generic process-related information about parallel processes, including status, session ID, process ID, and other information. SELECT pxp.inst_id INT, pxp.server_name, pxp.status, pid, spid, pxp.sid, pxp.serial#, username, service_name svcname FROM gv$px_process pxp, gv$session gvs WHERE gvs.inst_id = pxp.inst_id AND gvs.sid = pxp.sid AND gvs.serial# = pxp.serial# AND username NOT IN ('SYS', 'SYSTEM');

SELECT pxp.inst_id INT, pxp.server_name, pxp.status, pid, spid, pxp.sid, pxp.serial#, username, service_name svcname FROM gv$px_process pxp, gv$session gvs WHERE gvs.inst_id = pxp.inst_id AND gvs.sid = pxp.sid AND gvs.serial# = pxp.serial# AND username NOT IN ('SYS', 'SYSTEM');

The output from the query illustrates that there are two parallel jobs currently running with the QC on instance 8 and instance 5. The output has 24 parallel execution servers on instance 5 and 4 parallel execution servers on instance 8. The parallel servers are confined to single instance because in this specific example, the parameter PARALLEL_FORCE_LOCAL has been enabled. 

INT SERV STATUS PID SPID SID SERIAL# USERNAME SVCNAME 

---- ---- --------- ------ -------- ----- ---------- ---------- ----- 

8 P000 IN USE 49 8630 59 13633 MVALLATH SSSVC1 8 P002 IN USE 51 8632160 7979 MVALLATH SSSVC1 8 P003 IN USE 54 8634 315 3890 MVALLATH SSSVC1 8 P001 IN USE 56 30395 413 324 MVALLATH SSSVC1 5 P005 IN USE 64 26015 17 842 DWH SSSVC1 5 P006 IN USE 65 26017 53 4300 DWH SSSVC1 5 P007 IN USE 66 26019 108 711 DWH SSSVC1 5 P012 IN USE 34 24171 111 1139 DWH SSSVC1 5 P008 IN USE 67 26021 163 8261 DWH SSSVC1 5 P009 IN USE 68 26023 209 3222 DWH SSSVC1 5 P014 IN USE 52 24175 219 1002 DWH SSSVC1 5 P010 IN USE 69 26025 257 1442 DWH SSSVC1 5 P015 IN USE 53 24177 263 1118 DWH SSSVC1 5 P000 IN USE 54 26005 306 1045 DWH SSSVC1 5 P011 IN USE 70 26027 319 1063 DWH SSSVC1 5 P001 IN USE 55 26007 350 820 DWH SSSVC1 5 P018 IN USE 71 24183 356 3612 DWH SSSVC1 5 P019 IN USE 72 24185 400 2714 DWH SSSVC1 5 P020 IN USE 73 24187 454 3617 DWH SSSVC1 5 P021 IN USE 74 24189 502 1276 DWH SSSVC1 5 P016 IN USE 58 24179 504 1404 DWH SSSVC1  272 5 P022 IN USE 75 24191 549 739 DWH SSSVC1 5 P017 IN USE 59 24181 562 22027 DWH SSSVC1 5 P023 IN USE 76 24193 592 7108 DWH SSSVC1 5 P013 IN USE 44 24173 605 210 DWH SSSVC1 5 P002 IN USE 61 26009 648 4453 DWH SSSVC1 5 P003 IN USE 62 26011 701 358 DWH SSSVC1 5 P004 IN USE 63 26013 738 7416 DWH SSSVC1