Parallel execution is a important aspect of increasing throughput in oracle database.So often use Parallel execution in Oracle database. In this post, i will touch upon Important Parallel processing data dictionary views and How to monitor parallel queries in oracle database
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 oracle 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';
How to monitor parallel queries in oracle database
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
I hope you like the content on Parallel processing dictionary view and How to monitor parallel queries in oracle database. Hope it provide value to your knowlegde
Also Read
Parallel execution in Oracle
https://docs.oracle.com/cd/E18283_01/server.112/e16541/parallel006.htm