All partitioned objects can be scheduled for parallel operation.
- Index creation
- CTAS
- Move/Split/Coalesce partitions
- Index/Split partition
- Insert/Update/Delete/Select
Parallel DDL for non partitioned tables and indexes
- Index creation
- CTAS
- Move table
- Index rebuild
- LOB columns not supported
- Select
First step to monitor is from OS top command which will show ora_p0* process in COMMAND column which means that parallel slaves are spawned and utilized
top - 15:02:13 up 2:47, 3 users, load average: 0.76, 0.55, 0.50
Tasks: 268 total, 1 running, 264 sleeping, 3 stopped, 0 zombie
%Cpu(s): 0.2 us, 0.1 sy, 0.0 ni, 99.2 id, 0.3 wa, 0.1 hi, 0.0 si, 0.0 st
MiB Mem : 9670.6 total, 96.6 free, 859.0 used, 8715.1 buff/cache
MiB Swap: 4096.0 total, 4035.0 free, 61.0 used. 2850.6 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3728 oracle 20 0 6507020 73256 69588 S 0.3 0.7 0:04.76 ora_p001_hydrup
3824 oracle 20 0 6504384 74240 70456 S 0.3 0.7 0:04.47 ora_p002_hydrup
Check v$pq_tqstat for parallel statistics after a commit/rollback operations specifically for any DML in 11g. But in 12c, statistics are printed without any commit/rollback. This view shows addition information about Query coordinator/Consumer and Producer with bytes processed by each parallel process for chunks of object. This view contains information only during the lifespan of a session and vanish after the session clears.
SQL> update /*+parallel(4)*/ rp set r_name='xyz' where r_id between 10000 and 100000;
0 rows updated.
SQL> col SERVER_TYPE for a15
SQL> col PROCESS for a10
SQL> set lines 200 pages 1000
SQL> select * from v$pq_tqstat;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCESS INSTANCE CON_ID
---------- ---------- --------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
1 0 Producer 0 24 0 0 1 0 P019 1 0
1 0 Producer 0 24 0 0 1 0 P018 1 0
1 0 Producer 0 24 0 0 1 0 P017 1 0
1 0 Producer 0 24 0 0 2 0 P016 1 0
1 0 Consumer 0 96 0 0 11 2 QC 1 0
After using some joins, the degree used and required degree column can give idea on the number of parallel workers used and also ‘physical reads’ stats from stat name.
SQL> set lines 200 pages 1000
col NAME for a20
SELECT pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,pss.degree,pss.req_degree,sn.name
ORDER BY sn.name;
SQL> SQL> 2 3 4 5 6 7
SID SERIAL# QCSID QCSERIAL# DEGREE REQ_DEGREE NAME VALUE
---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
1044 1105 522 2501 16 16 physical reads 10
522 2501 522 physical reads 325
28 14695 522 2501 16 16 physical reads 10
1042 16300 522 2501 16 16 physical reads 11
21 16376 522 2501 16 16 physical reads 6
535 17448 522 2501 16 16 physical reads 21
1046 17667 522 2501 16 16 physical reads 8
22 20258 522 2501 16 16 physical reads 2
538 22774 522 2501 16 16 physical reads 10
31 29211 522 2501 16 16 physical reads 4
540 31302 522 2501 16 16 physical reads 8
30 34806 522 2501 16 16 physical reads 11
29 39740 522 2501 16 16 physical reads 10
1045 44806 522 2501 16 16 physical reads 9
539 45883 522 2501 16 16 physical reads 12
536 48194 522 2501 16 16 physical reads 11
27 48201 522 2501 16 16 physical reads 10
537 61286 522 2501 16 16 physical reads 10
1035 63251 522 2501 16 16 physical reads 10
1044 1105 522 2501 16 16 physical reads cache 10
522 2501 522 physical reads cache 325
28 14695 522 2501 16 16 physical reads cache 10
1042 16300 522 2501 16 16 physical reads cache 11
21 16376 522 2501 16 16 physical reads cache 6
535 17448 522 2501 16 16 physical reads cache 21
1046 17667 522 2501 16 16 physical reads cache 8
22 20258 522 2501 16 16 physical reads cache 2
538 22774 522 2501 16 16 physical reads cache 10
31 29211 522 2501 16 16 physical reads cache 4
540 31302 522 2501 16 16 physical reads cache 8
30 34806 522 2501 16 16 physical reads cache 11
29 39740 522 2501 16 16 physical reads cache 10
1045 44806 522 2501 16 16 physical reads cache 9
539 45883 522 2501 16 16 physical reads cache 12
536 48194 522 2501 16 16 physical reads cache 11
27 48201 522 2501 16 16 physical reads cache 10
537 61286 522 2501 16 16 physical reads cache 10
1035 63251 522 2501 16 16 physical reads cache 10
522 2501 522 physical reads cache 12
prefetch
39 rows selected.
Check the parallel server which is used currently and which is idle
SQL> set lines 200 pages 1000
col NAME for a20
SELECT pxp.server_name,pxp.status,pxp.spid,
pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
INNER JOIN v$px_process pxp on ( pxp.sid = pss.sid )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,
pxp.server_name,pxp.status,pxp.spid
ORDER BY sn.name;
SERV STATUS SPID SID SERIAL# QCSID QCSERIAL# DEGREE REQ_DEGREE NAME VALUE
---- --------- ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
P005 IN USE 3830 27 5577 534 61515 16 16 physical reads 207
P002 IN USE 3824 22 10359 534 61515 16 16 physical reads 161
P00C IN USE 31616 531 11558 534 61515 16 16 physical reads 191
To be more query specific which uses parallel.
SQL> set lines 200 pages 1000
col sql_id for a10
col sql_text for a20
col spid for a10
col NAME for a15
col sid for 999999
col degree for 999
col serial# for 999999
col REQ_DEGREE for 999
SELECT ses.sql_id,sql.sql_text,pxp.server_name,pxp.status,pxp.spid,
pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
INNER JOIN v$px_process pxp on ( pxp.sid = pss.sid )
INNER JOIN v$session ses on ( ses.sid = pss.sid )
INNER JOIN v$sqlarea sql on ( sql.sql_id = ses.sql_id )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,
pxp.server_name,pxp.status,pxp.spid,
ses.sql_id,sql.sql_text
ORDER BY sn.name;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SQL_ID SQL_TEXT SERV STATUS SPID SID SERIAL# QCSID QCSERIAL# DEGREE REQ_DEGREE NAME VALUE
---------- -------------------- ---- --------- ---------- ------- ------- ---------- ---------- ------ ---------- --------------- ----------
25zjubx56k SELECT /*+parallel(1 P003 IN USE 2996 526 3682 516 47395 16 16 physical reads 139
pm7 6)*/ * from c##hydra
.addresses
25zjubx56k SELECT /*+parallel(1 P005 IN USE 3000 15 10456 516 47395 16 16 physical reads 175
pm7 6)*/ * from c##hydra
.addresses
To check the user who executes parallel
set lines 200 pages 1000
col sql_id for a10
col sql_text for a20
col spid for a10
col NAME for a15
col sid for 999999
col degree for 999
col serial# for 999999
col RDEG for 999
col username for a10
col QCSID for 99999
SELECT ses.username,ses.sql_id,sql.sql_text,pxp.server_name,
pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,pxp.status,pxp.spid,
pss.degree,pss.req_degree as "RDEG",sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
INNER JOIN v$px_process pxp on ( pxp.sid = pss.sid )
INNER JOIN v$session ses on ( ses.sid = pss.sid )
INNER JOIN v$sqlarea sql on ( sql.sql_id = ses.sql_id )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,
pxp.server_name,pxp.status,pxp.spid,
ses.sql_id,sql.sql_text,ses.username
ORDER BY sn.name;
Similar to normal sysstat, there is a separate view for px as well. This view retrieves the parallel servers capacity and utilization for system statistics
SQL> SELECT * FROM V$PX_PROCESS_SYSSTAT;
STATISTIC VALUE CON_ID
------------------------------------------------------------------------------------------------------------------------ ---------- ----------
Servers In Use 32 0
Servers Available 12 0
Servers Started 84 0
Servers Shutdown 40 0
Servers Highwater 40 0
Servers Cleaned Up 0 0
Server Sessions 4128 0
Memory Chunks Allocated 16 0
Memory Chunks Freed 6 0
Memory Chunks Current 10 0
Memory Chunks HWM 10 0
Buffers Allocated 27686 0
Buffers Freed 26822 0
Buffers Current 864 0
Buffers HWM 946 0
15 rows selected.
To check for individual session statistics related to px, use the below query.
SQL> SELECT * FROM V$PQ_SESSTAT;
STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 0 0
DML Parallelized 0 0 0
DDL Parallelized 0 0 0
DFO Trees 0 0 0
Server Threads 0 0 0
Allocation Height 0 0 0
Allocation Width 0 0 0
Local Msgs Sent 0 0 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 0 0 0
Distr Msgs Recv'd 0 0 0
DOP 0 0 0
Slave Sets 0 0 0
13 rows selected.
Below parameters are from both CDB and non-CDB database. These parameters should tweaked at times during any parallel issues. So monitoring these parameters in every database is essential.
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_execution_message_size integer 32768
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 100
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent integer 0
parallel_min_servers integer 12
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 48
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
awr_pdb_max_parallel_slaves integer 10
containers_parallel_degree integer 65535
fast_start_parallel_rollback string LOW
max_datapump_parallel_per_job string 50
optimizer_ignore_parallel_hints boolean FALSE
parallel_adaptive_multi_user boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 20
parallel_min_degree string 1
parallel_min_percent integer 0
parallel_min_servers integer 2
parallel_min_time_threshold string AUTO
parallel_servers_target integer 20
parallel_threads_per_cpu integer 1
recovery_parallelism integer 0
Check diag folders for parallel traces for any further monitoring after a parallel execution.
/u01/app/oracle/diag/rdbms/hydrupgd/hydrupgd/trace
[oracle@hydrupgrd trace]$ ls -lrt |grep p0|tail
-rw-r----- 1 oracle oinstall 4015 Mar 20 17:04 hydrupgd_psp0_3670.trm
-rw-r----- 1 oracle oinstall 43367 Mar 20 17:04 hydrupgd_psp0_3670.trc
-rw-r----- 1 oracle oinstall 84 Mar 21 18:38 hydrupgd_p001_40548.trm
-rw-r----- 1 oracle oinstall 1492 Mar 21 18:38 hydrupgd_p001_40548.trc
-rw-r----- 1 oracle oinstall 85 Mar 21 18:38 hydrupgd_p000_40546.trm
-rw-r----- 1 oracle oinstall 1496 Mar 21 18:38 hydrupgd_p000_40546.trc
-rw-r----- 1 oracle oinstall 84 Mar 22 07:30 hydrupgd_p001_3836.trm
-rw-r----- 1 oracle oinstall 1492 Mar 22 07:30 hydrupgd_p001_3836.trc
-rw-r----- 1 oracle oinstall 84 Mar 22 07:30 hydrupgd_p000_3834.trm
-rw-r----- 1 oracle oinstall 1493 Mar 22 07:30 hydrupgd_p000_3834.trc
