Top 10 Parallel Processing Views and Monitoring Queries in Oracle Database

Last updated on September 1st, 2019 at 06:01 pm

Parallel Processing Data Dictionary views

In this section we are trying to give a glimpse of Parallel Processing Data Dictionary views
Oracle keep changing the view name,but below query should be able to give all the views

SELECT table_name
FROM dict
WHERE table_name LIKE 'V%PQ%'
OR table_name like 'V%PX%‘;
Examples
V$PX_SESSION
V$PX_SESSTAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT

Some important views
a)V$PQ_SESSTAT
V$PQ_SESSTAT shows you PX statistics for your current session.
Following query could be used

SQL> SELECT * FROM v$pq_sesstat;
Queries Parallelized                         1               2
DML Parallelized                             0               0
DDL Parallelized                             0               0
DFO Trees                                    1               2
Server Threads                               2               0
Allocation Height                            1               0
Allocation Width                             2               0
Local Msgs Sent                              3               6
Distr Msgs Sent                              3               6
Local Msgs Recv'd                            3               6
Distr Msgs Recv'd                            3               6

This gives us a nice ways to track the parallelism in current session

b)V$PQ_SYSSTAT
This view is useful for getting an instance-wide overview of how PX slaves are being used and is particularly helpful in determining possible changes to parallel_max_servers and parallel_min_server

SQL> column STATISTIC format a40
SQL> SELECT * FROM v$pq_sysstat;
STATISTIC                                     VALUE
---------------------------------------- ----------
Servers Busy                                      0
Servers Idle                                      3
Servers Highwater                                17
Server Sessions                              672683
Servers Started                               11740
Servers Shutdown                              11737
Servers Cleaned Up                                0
Queries Queued                                    0
Queries Initiated                            191358
Queries Initiated (IPQ)                           0
DML Initiated                                   100
DML Initiated (IPQ)                               0
DDL Initiated                                     7
DDL Initiated (IPQ)                               0
DFO Trees                                    193693
Sessions Active                                   0
Local Msgs Sent                            94708255
Distr Msgs Sent                              323561
Local Msgs Recv'd                          95268766
Distr Msgs Recv'd                            360143

c. 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

d. 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 example a

And here is the output the quert 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

e. 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 definition 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';

Parallel Execution Monitoring Queries

Query to check Parallel execution in the Oracle database

SELECT
   PS.SID, 
   DECODE(SERVER_SET, NULL, 'COORDINATOR', 1, '  CONSUMER', 
     '    PRODUCER') ROLE, 
   DECODE(SW.WAIT_TIME, 0, SW.EVENT, 'CPU') ACTION, 
   SQ.SQL_TEXT 
FROM
   V$PX_SESSION PS, 
   V$SESSION_WAIT SW, 
   V$SQL SQ, 
   V$SESSION S, 
   AUDIT_ACTIONS AA 
WHERE
   PS.SID = SW.SID AND 
   S.SID = PS.SID AND 
   S.SQL_ADDRESS = SQ.ADDRESS (+) AND 
   AA.ACTION = S.COMMAND 
ORDER BY PS.QCSID, NVL(PS.SERVER#, 0), PS.SERVER_SET;

Query to show the actual progress in terms of work allocated to the parallel slave and the amount of work still to be completed by those parallel slaves.

Select
decode(px.qcinst_id,NULL,username, 
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30)  operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s 
where px.sid=s.sid 
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by
  decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
  px.QCSID,
  decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
  px.SERVER_SET, 
  px.INST_ID
/

Query to check Parallel execution parameters

SELECT inst_id, name, display_value
FROM gv$parameter
WHERE name IN ('cpu_count','parallel_threads_per_cpu')
ORDER BY name;
 INST_ID NAME                           VALUE
-------- ------------------------------ ---------------
       1 cpu_count                      4
       1 parallel_threads_per_cpu       2
Thus, the default degree of parallelism is 8.  


SELECT inst_id, name, value
FROM gv$parameter
WHERE name IN ( 
  'parallel_max_servers',
  'parallel_adaptive_multi_user',
  'parallel_automatic_tuning'
)
ORDER BY name;
 INST_ID NAME                           VALUE
-------- ------------------------------ ---------------
       1 parallel_adaptive_multi_user   FALSE
       1 parallel_automatic_tuning      FALSE
       1 parallel_max_servers           180

select name, value from v$parameter where name like '%parallel%' ;

How to check the parallel server health

SELECT * FROM gv$pq_sysstat
WHERE TRIM(statistic) = 'Servers Busy';
 INST_ID STATISTIC                 VALUE
-------- -------------------- ----------
       1 Servers Busy                  6

Query to find the parallel operation which were downgraded

column stat_name format A40

column instance format 99999999

-- parallel operations not downgraded hour by hour for yesterday
SELECT instance_number AS instance,
       stat_name,
       TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24') interval_hour,
       CASE WHEN not_dg < 0 THEN 0 ELSE not_dg END AS not_dg FROM ( SELECT sysstat_end.instance_number, sysstat_end.stat_name stat_name, TRUNC(begin_interval_time,'HH') begin_interval_time, SUM(sysstat_end.value - sysstat_begin.value) not_dg FROM sys.dba_hist_snapshot snapshot, sys.dba_hist_sysstat sysstat_end, sys.dba_hist_sysstat sysstat_begin WHERE sysstat_end.snap_id = snapshot.snap_id AND sysstat_end.dbid = snapshot.dbid AND sysstat_end.instance_number = snapshot.instance_number AND sysstat_begin.snap_id = sysstat_end.snap_id - 1 AND sysstat_begin.dbid = sysstat_end.dbid AND sysstat_begin.stat_id = sysstat_end.stat_id AND sysstat_begin.instance_number = sysstat_end.instance_number AND snapshot.begin_interval_time > TRUNC(systimestamp,'DD') - 1
  AND snapshot.end_interval_time <= TRUNC(systimestamp,'DD') + 1/(24*10)
  AND snapshot.dbid = ( SELECT dbid FROM v$database )
  AND TRIM(sysstat_end.stat_name) = 'Parallel operations not downgraded'
  GROUP BY TRUNC(begin_interval_time,'HH'),
           sysstat_end.instance_number, sysstat_end.stat_name
  ORDER BY sysstat_end.instance_number, TRUNC(begin_interval_time,'HH')
);

SELECT name FROM V$sysstat
WHERE name like 'Parallel%'
ORDER BY name;
NAME
--------------------------------------------
Parallel operations downgraded 1 to 25 pct
Parallel operations downgraded 25 to 50 pct
Parallel operations downgraded 50 to 75 pct
Parallel operations downgraded 75 to 99 pct
Parallel operations downgraded to serial
Parallel operations not downgraded 

Leave a Reply