Round Robin and Parallel Forced to Serial

So we have a parallel process, coordinator 739 running, just not very well… I have an ugly pink section in my OEM grid and as we know, pink is not a DBA Girl’s favorite color… 🙂

 Not so fast… the process, due to the wait on temp and then to buffer, has decided to “force” it to serial?

Description	OBJECT	Obj Node	Order	Rows	Bytes	Cost	In/Out	PQ Dist.
CREATE TABLE STATEMENT			12			3,200
PX COORDINATOR FORCED SERIAL			11
PX SEND QC (RANDOM)	SYS.:TQ20001	:Q2001	10	13,717,999	3.577G	1,382	P->S	QC (RAND)
LOAD AS SELECT		:Q2001	9				PCWP
BUFFER SORT		:Q2001	8				PCWC
PX RECEIVE		:Q2001	7	13,717,999	3.577G	1,382	PCWP
PX SEND ROUND-ROBIN	SYS.:TQ20000		6	13,717,999	3.577G	1,382	S->P	RND-ROBIN
COUNT			5
PX COORDINATOR			4
PX SEND QC (RANDOM)	SYS.:TQ10000	:Q1000	3	13,717,999	3.577G	1,382	P->S	QC (RAND)
PX BLOCK ITERATOR		:Q1000	2	13,717,999	3.577G	1,382	PCWC
TABLE ACCESS FULL	SOURCE_STG_TBL	:Q1000	1	13,717,999	3.577G	1,382	PCWP

 

 …. but it doesn’t even show up in the downgraded info, which may surprise you at first, including the parallel waits, which still shows parallel, not serial…

1              601         226         601         16992    1                                                                                 direct path write temp

2              714         233         601         17154    1              1              1              4              4              PX Deq Credit: send blkd

3              695         234         601         17156    1              1              1              4              4              PX Deq Credit: send blkd

4              730         231         601         17150    1              1              1              4              4              PX Deq Credit: send blkd

5              743         232         601         17152    1              1              1              4              4              PX Deq Credit: send blkd

6              681         39           681         30392    1                                                                                 SQL*Net message from client

7              613         64           681         30807    1              1              1              4              4              PX Deq: Execution Msg

8              679         63           681         30805    1              1              1              4              4              PX Deq: Execution Msg

9              630         46           681         30801    1              1              1              4              4              PX Deq: Execution Msg

10           866         59           681         30803    1              1              1              4              4              PX Deq: Execution Msg

11           739         259         739         22253    1                                                                               direct path write temp

12           799         179         739         16166    1              1              1              4              4              PX Deq Credit: send blkd

13           647         189         739         16168    1              1              1              4              4              PX Deq Credit: send blkd

14           559         69           739         16162    1              1              1              4              4              PX Deq Credit: send blkd

15           842         115         739         16164    1              1              1              4              4              PX Deq Credit: send blkd

Both of the processes experiencing this type of downgrade are showing the same preformance issue,

 no downgrade reported in the system, but it IS SHOWING in the explain plan and the performance of active sessions/memory workarea that it has downgraded to a serial process… 

STATEMENT                                                                                                                                      SQL_ID                                   SID               TEMP TBLSP     WAIT                                    MEM     PGA          TEMP

create table OWNR1.LD_5793_876758_1545370 tablespace tbl__data1            3hyuj7fz1sz2k                          739                                            LOAD WRITE BUFFERS     0              0

create table OWNR1.LD_5793_876758_1545370 tablespace tbl__data1            3hyuj7fz1sz2k                          739               TEMP_4             BUFFER                                21           95           4224

select a.Data_ID “M ID”, b.MID_2 “USER”, a.B                                                                    53j7g6yr175x8                      762                                           SORT (v2)                              0              0

select a.Data_ID “M ID”, b.MID_2 “USER”, a.B                                                                    53j7g6yr175x8                      543                                           SORT (v2)                              0              0

create table OWNR1.LD_NCOA2_875718_1545439 tablespace tbl__data            65rg8zjgz999u                       723                                           LOAD WRITE BUFFERS       0              0

select r.id_1_id “Mg Id”, m.MID_2 “USER”, r.mid_3                                                        6rv12jcj46k6j                        658                                            SORT (v2)                              0              0

select r.id_1_id “Mg Id”, m.MID_2 “USER”, r.mid_3                                                        6rv12jcj46k6j                        781                                            SORT (v2)                              0              0

select r.id_1_id “Mg Id”, m.MID_2 “USER”, r.mid_3                                                        6rv12jcj46k6j                        560                                            SORT (v2)                              0              0

create table proc_id_for_tbl_proc_id2 tablespace sj_data as sele                               88d60nh8p7p2u                   598                                            LOAD WRITE BUFFERS       0              0

create table proc_id_for_tbl_proc_id2 tablespace sj_data as sele                              88d60nh8p7p2u                   598                                           HASH-JOIN                         128         133

create table proc_id_for_tbl_proc_id2 tablespace sj_data as sele                               88d60nh8p7p2u                   598                                           WINDOW (SORT)                  0              0

create table OWNR1.LD_PROC_871297_1545102 tablespace tbl__data1             8whzq2fbsq751                       835                                          LOAD WRITE BUFFERS         0              0

create table OWNR1.LD_PROC_871297_1545102 tablespace tbl__data1             8whzq2fbsq751                      622                TEMP_1           WINDOW (SORT)                  14           95           736

create table OWNR1.LD_PROC_871297_1545102 tablespace tbl__data1             8whzq2fbsq751                      784                TEMP_3           WINDOW (SORT)                  14           95           736

create table OWNR1.LD_PROC_871297_1545102 tablespace tbl__data1             8whzq2fbsq751                     792                 TEMP_2           WINDOW (SORT)                  14           95           736

create table OWNR1.LD_PROC_871297_1545102 tablespace tbl__data1             8whzq2fbsq751                    618                  TEMP_4           WINDOW (SORT)                  14           95           736

create table OWNR1.LD_5583_868568_1545298 tablespace tbl__data1             gfvk0jw7qc5k7                   601                                             LOAD WRITE BUFFERS         0              0

create table OWNR1.LD_5583_868568_1545298 tablespace tbl__data1             gfvk0jw7qc5k7                   601                  TEMP_3            BUFFER                                   25           95           6672

 

 So what’s up?

The load as select in the last step of the CTAS is not parallelized-  no parallel dml has been enabled or parallel hinted in the beginning of the statement, so the statement has downgraded to serial to complete the final step. 

OK, now I know it’s time to look into ensuring the development group is enabling parallel DML next to spread across the temp tablespace groups more efficiently…