Parallel Processing Data Dictionary views Lesson 2



Last updated on July 17th, 2015 at 06:05 pm

V$PQ_SLAVE and V$PX_PROCESS
These two views allow us to track whether individual slaves are in use or not and track down their associated session
details.
SQL> SELECT * FROM v$px_process;
SERV STATUS           PID SPID                            SID    SERIAL#
—- ——— ———- ———————— ———- ———-
PZ98 AVAILABLE        112 19875
PZ99 AVAILABLE        260 53611
PZ97 AVAILABLE        387 20261

V$PQ_TQSTAT
This view contains statistics on parallel execution operations. The statistics are compiled after the query completes and only remain for the duration of the session. It displays the number of rows processed through each parallel execution server at each stage of the execution tree. This view can help determine skew problems in a query’s execution. This view can be used to analyze the performance issues with parallel execution

Following query could be use to get the distribution
break on dfo_number on tq_id
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC, process;

I ran the following query
SELECT /*+ PARALLEL (a) */ *
FROM costs a
And here is the output the queet from v$pq_tqstat

DFO_NUMBER      TQ_ID SERVER_TYP PROCESS              NUM_ROWS      BYTES
———- ———- ———- —————— ———- ———-
         1          0 Producer   P000                     1441     515525
                      Producer   P001                     1386     483336
                      Producer   P002                     1232     435001
                      Producer   P003                     1539     547780
                      Producer   P004                     1508     515556
                      Producer   P005                     1353     483324
                      Producer   P006                     1338     467220
                      Producer   P007                     1504     531645
                      Producer   P008                     1154     402766
                      Producer   P009                     1561     547764
                      Producer   P010                     1524     515556
                      Producer   P011                     1617     579969
                      Producer   P012                     1494     531667
                      Producer   P013                     1591     563866
                      Producer   P014                     1588     563853
                      Producer   P015                     1658     579984
                      Producer   P016                     1539     531648
                      Producer   P017                     1487     531651
                      Producer   P018                     1489     531684
                      Producer   P019                     1585     563889
                      Producer   P020                     1442     515529
                      Producer   P021                     1628     579986
                      Producer   P022                     1577     563863
                      Producer   P023                     1494     515533
                      Producer   P024                     1547     547786
                      Producer   P025                     1391     483318
                      Producer   P026                     1476     531649
                      Producer   P027                     1547     547732
                      Producer   P028                     1480     515520
                      Producer   P029                     1541     547746
                      Producer   P030                     1461     515510
                      Producer   P031                     1406     499456
                      Producer   P032                     1447     499440
                      Producer   P033                     1612     579980
                      Producer   P034                     1560     547732
                      Producer   P035                     1455     499424
                      Producer   P036                     1404     499434
                      Producer   P037                     1471     515558
                      Producer   P038                     1610     579986
                      Producer   P039                     1454     515500
                      Producer   P040                     1274     451105
                      Producer   P041                     1487     531653
                      Producer   P042                     1391     483352
                      Producer   P043                     1541     547772
                      Producer   P044                     1498     531648
                      Producer   P045                     1327     467217
                      Producer   P046                     1540     531665
                      Producer   P047                     1492     531654
                      Producer   P048                     1528     547757
                      Producer   P049                     1436     499448
                      Producer   P050                     1526     531644
                      Producer   P051                     1410     499447
                      Producer   P052                     1541     547774
                      Producer   P053                     1583     547788
                      Producer   P054                     1577     563905
                      Producer   P055                     1348     467176
                      Producer   P056                     1394     499404
                      Producer   P057                     1401     499416
         1          0 Producer   P058                     1446     515562
                      Producer   P059                     1569     563871
                      Producer   P060                     1501     531662
                      Producer   P061                     1403     483319
                      Producer   P062                     1508     515543
                      Producer   P063                     1360     483300
                      Consumer   QC                      88036   31286942

V$PX_SESSTAT
This view is similar to v$sessstat. This view contains information about the sessions running parallel execution.
SELECT stat.qcsid, stat.server_set, stat.server#, nam.name, stat.value
FROM v$px_sesstat stat, v$statname nam
WHERE stat.statistic# = nam.statistic#
AND nam.name LIKE ‘physical reads%’
ORDER BY 1,2,3

Important note:
The defination of all these views can be obtained through
SELECT view_definition FROM v$fixed_view_definition
       WHERE view_name=’&1′;
SELECT view_definition FROM v$fixed_view_definition
       WHERE view_name=’GV$PX_SESSTAT’;

 


Leave a Reply