These examples use the dynamic performance views described in "Monitoring Parallel Execution Performance with Dynamic Performance Views".
Use GV$PX_SESSION to determine the configuration of the server group executing in parallel. In this example, session 9 is the query coordinator, while sessions 7 and 21 are in the first group, first set. Sessions 18 and 20 are in the first group, second set. The requested and granted DOP for this query is 2, as shown by the output from the following query:
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set", DEGREE "Degree", REQ_DEGREE "Req Degree" FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;
Your output should resemble the following:
QCSID SID Inst Group Set Degree Req Degree
---------- ---------- ---------- ---------- ---------- ---------- ----------
9 9 1
9 7 1 1 1 2 2
9 21 1 1 1 2 2
9 18 1 1 2 2 2
9 20 1 1 2 2 2
For a single instance, use SELECT FROM V$PX_SESSION and do not include the column name Instance ID.
The processes shown in the output from the previous example using GV$PX_SESSION collaborate to complete the same task. The next example shows the execution of a join query to determine the progress of these processes in terms of physical reads. Use this query to track any specific statistic:
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set", NAME "Stat Name", VALUE FROM GV$PX_SESSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC# AND NAME LIKE 'PHYSICAL READS' AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;
Your output should resemble the following:
QCSID SID Inst Group Set Stat Name VALUE
------ ----- ------ ------ ------ ------------------ ----------
9 9 1 physical reads 3863
9 7 1 1 1 physical reads 2
9 21 1 1 1 physical reads 2
9 18 1 1 2 physical reads 2
9 20 1 1 2 physical reads 2
Use the previous type of query to track statistics in V$STATNAME. Repeat this query as often as required to observe the progress of the query server processes.
The next query uses V$PX_PROCESS to check the status of the query servers.
SELECT * FROM V$PX_PROCESS;
Your output should resemble the following:
SERV STATUS PID SPID SID SERIAL# IS_GV CON_ID ---- --------- ------ --------- ------ ------- ----- ------- P002 IN USE 16 16955 21 7729 FALSE 0 P003 IN USE 17 16957 20 2921 FALSE 0 P004 AVAILABLE 18 16959 FALSE 0 P005 AVAILABLE 19 16962 FALSE 0 P000 IN USE 12 6999 18 4720 FALSE 0 P001 IN USE 13 7004 7 234 FALSE 0