SQL query with PARALLEL hint spawns many more processes than specified
I have a query that looks like this:
SELECT /*+ PARALLEL(8) */ * FROM …
The query is quite complex. When I look at the execution plan, I see the PX COORDINATOR four times. What appears to be happening is that for each PX COORDINATOR, this request allocates 8 processes. When I run the request, it starts with 8 processes. Then, after a while, the subtasks of the first PX COORDINATOR are executed, and the second is launched, again allocating 8 processes, without letting go of others. 8. At the final stage, 32 processes are allocated, of which only 8 are in use (that is, active), Moreover, if during execution Oracle cannot allocate 8 processes at a time (since it reaches the maximum server limit), it continues to work in one process (which leads to very long execution times).
Is there a way to optimize this query so that Oracle releases unused processes while the query is being executed, or caps the query to 8 without wasting CPU time on inactive processes?
EDIT 2:
From my limited testing, it seems that these PX COORDINATORS are dependent on CTEs (sub-queries defined in the WITH clause). For such subqueries that are concatenated, Oracle will process them under the PX COORDINATOR command. UNNEST
and the MERGE
hints don't help much.
EDIT:
Request:
WITH covh AS (
SELECT covh.cntr_id
, covh.covh_valid_from_dt AS vf
, svar_id
, svar.variable_name
, covh.covh_value_alpha AS alpha
, covh.covh_value_boolean AS bool
, covh.covh_value_numeric AS num
, covh.covh_value_pointer_id AS pid
, covh.covh_value_date AS dt
, NVL(ora_hash(ora_hash(covh_value_alpha) || ora_hash(covh_value_boolean) || ora_hash(covh_value_numeric) || ora_hash(covh_value_pointer_id) || ora_hash(covh_value_date)), -1) AS hash
FROM SOME_TABLE covh
INNER JOIN SOME_TABLE_2 svar USING (svar_id)
), covh_lag AS (
SELECT covh.*
, LAG(hash, 1, -2) OVER (PARTITION BY cntr_id, svar_id ORDER BY vf ASC NULLS LAST) lagging_hash
FROM covh
), fch AS (
SELECT
covh_lag.*
, NVL(LEAD(vf, 1) OVER (PARTITION BY cntr_id, svar_id ORDER BY vf ASC NULLS LAST), TO_DATE('01-JAN-9999', 'DD-MM-YYYY')) AS vt
FROM covh_lag
WHERE NOT (
hash = lagging_hash
OR (hash = -1 AND lagging_hash = -2)
)
), base AS (
SELECT cntr_id
, vf
, LEAD(vf, 1) OVER (PARTITION BY cntr_id ORDER BY vf ASC NULLS LAST) AS vt
FROM ( SELECT DISTINCT cntr_id, vf FROM fch )
), subs AS (
SELECT cntr_id, subs_id
FROM SOME_TABLE_3
), merge_step1 AS (
SELECT
base.cntr_id
, subs.subs_id
, base.vf
, base.vt
, week.num AS DEL_AT
, freq.alpha AS FREQ
, sure.pid AS SURE_ID
, frsu.pid AS FRSU_ID
FROM base
LEFT OUTER JOIN subs ON (base.cntr_id = subs.cntr_id)
LEFT OUTER JOIN fch week ON (week.cntr_id = base.cntr_id AND week.vf <= base.vf AND week.vt > base.vf AND week.variable_name = 'A')
LEFT OUTER JOIN fch freq ON (freq.cntr_id = base.cntr_id AND freq.vf <= base.vf AND freq.vt > base.vf AND freq.variable_name = 'B')
LEFT OUTER JOIN fch sure ON (sure.cntr_id = base.cntr_id AND sure.vf <= base.vf AND sure.vt > base.vf AND sure.variable_name = 'C')
LEFT OUTER JOIN fch frsu ON (frsu.cntr_id = base.cntr_id AND frsu.vf <= base.vf AND frsu.vt > base.vf AND frsu.variable_name = 'D')
), merge_step2 AS (
SELECT
merge_step1.*
, pred.pid AS PRED_ID
, taco.pid AS TACO_ID
, dura.pid AS DURA_ID
, tarf.pid AS TARF_ID
FROM merge_step1
LEFT OUTER JOIN fch pred ON (pred.cntr_id = merge_step1.cntr_id AND pred.vf <= merge_step1.vf AND pred.vt > merge_step1.vf AND pred.variable_name = 'E')
LEFT OUTER JOIN fch taco ON (taco.cntr_id = merge_step1.cntr_id AND taco.vf <= merge_step1.vf AND taco.vt > merge_step1.vf AND taco.variable_name = 'F')
LEFT OUTER JOIN fch dura ON (dura.cntr_id = merge_step1.cntr_id AND dura.vf <= merge_step1.vf AND dura.vt > merge_step1.vf AND dura.variable_name = 'G')
LEFT OUTER JOIN fch tarf ON (tarf.cntr_id = merge_step1.cntr_id AND tarf.vf <= merge_step1.vf AND tarf.vt > merge_step1.vf AND tarf.variable_name = 'H')
), merged AS (
SELECT
merge_step2.*
, prca.pid AS PRCA_ID
, sufo.pid AS SUFO_ID
, rpot.pid AS RPOT_ID
, dela.alpha AS AREA
FROM merge_step2
LEFT OUTER JOIN fch prca ON (prca.cntr_id = merge_step2.cntr_id AND prca.vf <= merge_step2.vf AND prca.vt > merge_step2.vf AND prca.variable_name = 'I')
LEFT OUTER JOIN fch sufo ON (sufo.cntr_id = merge_step2.cntr_id AND sufo.vf <= merge_step2.vf AND sufo.vt > merge_step2.vf AND sufo.variable_name = 'J')
LEFT OUTER JOIN fch rpot ON (rpot.cntr_id = merge_step2.cntr_id AND rpot.vf <= merge_step2.vf AND rpot.vt > merge_step2.vf AND rpot.variable_name = 'K')
LEFT OUTER JOIN fch dela ON (dela.cntr_id = merge_step2.cntr_id AND dela.vf <= merge_step2.vf AND dela.vt > merge_step2.vf AND dela.variable_name = 'L')
)
SELECT /*+ PARALLEL(8) STATEMENT_QUEUING */
merged.cntr_id
, merged.subs_id
, merged.vf
, merged.vt
, merged.DEL_AT
, merged.FREQ
, merged.SURE_ID
, merged.FRSU_ID
, merged.PRED_ID
, merged.TACO_ID
, merged.DURA_ID
, merged.TARF_ID
, merged.PRCA_ID
, merged.SUFO_ID
, merged.RPOT_ID
, merged.AREA
FROM merged;
Exec plan (2x PX COORDINATOR -> 2x8 processes):
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 209M| 208G| | 5898K (2)| 00:14:31 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 209M| 13G| | 777K (2)| 00:01:55 | Q1,03 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D75E2_25751CCB | | | | | | Q1,03 | PCWP | |
| 5 | WINDOW SORT | | 209M| 13G| 18G| 777K (2)| 00:01:55 | Q1,03 | PCWP | |
| 6 | PX RECEIVE | | 209M| 13G| | 382K (2)| 00:00:57 | Q1,03 | PCWP | |
| 7 | PX SEND HASH | :TQ10002 | 209M| 13G| | 382K (2)| 00:00:57 | Q1,02 | P->P | HASH |
|* 8 | VIEW | | 209M| 13G| | 382K (2)| 00:00:57 | Q1,02 | PCWP | |
| 9 | WINDOW SORT | | 209M| 10G| 15G| 382K (2)| 00:00:57 | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 209M| 10G| | 52342 (4)| 00:00:08 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 209M| 10G| | 52342 (4)| 00:00:08 | Q1,01 | P->P | HASH |
| 12 | VIEW | | 209M| 10G| | 52342 (4)| 00:00:08 | Q1,01 | PCWP | |
| 13 | VIEW | | 209M| 39G| | 52342 (4)| 00:00:08 | Q1,01 | PCWP | |
|* 14 | HASH JOIN | | 209M| 8802M| | 52342 (4)| 00:00:08 | Q1,01 | PCWP | |
| 15 | PX RECEIVE | | 24 | 408 | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10000 | 24 | 408 | | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 17 | PX BLOCK ITERATOR | | 24 | 408 | | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 18 | TABLE ACCESS FULL | SOME_TABLE_2 | 24 | 408 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 19 | PX BLOCK ITERATOR | | 209M| 5401M| | 51979 (3)| 00:00:08 | Q1,01 | PCWC | |
| 20 | TABLE ACCESS FULL | SOME_TABLE | 209M| 5401M| | 51979 (3)| 00:00:08 | Q1,01 | PCWP | |
| 21 | PX COORDINATOR | | | | | | | | | |
| 22 | PX SEND QC (RANDOM) | :TQ20016 | 209M| 208G| | 5120K (2)| 00:12:36 | Q2,16 | P->S | QC (RAND) |
|* 23 | HASH JOIN RIGHT OUTER BUFFERED | | 209M| 208G| 5301M| 5120K (2)| 00:12:36 | Q2,16 | PCWP | |
| 24 | PX RECEIVE | | 209M| 39G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 25 | PX SEND HASH | :TQ20002 | 209M| 39G| | 159K (1)| 00:00:24 | Q2,02 | P->P | HASH |
|* 26 | VIEW | | 209M| 39G| | 159K (1)| 00:00:24 | Q2,02 | PCWP | |
| 27 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,02 | PCWC | |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,02 | PCWP | |
|* 29 | HASH JOIN RIGHT OUTER | | 209M| 169G| 1825M| 4536K (2)| 00:11:10 | Q2,16 | PCWP | |
| 30 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 31 | PX SEND HASH | :TQ20003 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,03 | P->P | HASH |
|* 32 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,03 | PCWP | |
| 33 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,03 | PCWC | |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,03 | PCWP | |
|* 35 | HASH JOIN RIGHT OUTER | | 209M| 157G| 1825M| 4028K (2)| 00:09:55 | Q2,16 | PCWP | |
| 36 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 37 | PX SEND HASH | :TQ20004 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,04 | P->P | HASH |
|* 38 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,04 | PCWP | |
| 39 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,04 | PCWC | |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,04 | PCWP | |
|* 41 | HASH JOIN RIGHT OUTER | | 209M| 145G| 1825M| 3544K (2)| 00:08:44 | Q2,16 | PCWP | |
| 42 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 43 | PX SEND HASH | :TQ20005 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,05 | P->P | HASH |
|* 44 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,05 | PCWP | |
| 45 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,05 | PCWC | |
| 46 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,05 | PCWP | |
|* 47 | HASH JOIN RIGHT OUTER | | 209M| 133G| 1825M| 3084K (3)| 00:07:36 | Q2,16 | PCWP | |
| 48 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 49 | PX SEND HASH | :TQ20006 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,06 | P->P | HASH |
|* 50 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,06 | PCWP | |
| 51 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,06 | PCWC | |
| 52 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,06 | PCWP | |
|* 53 | HASH JOIN RIGHT OUTER | | 209M| 121G| 1825M| 2647K (3)| 00:06:31 | Q2,16 | PCWP | |
| 54 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 55 | PX SEND HASH | :TQ20007 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,07 | P->P | HASH |
|* 56 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,07 | PCWP | |
| 57 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,07 | PCWC | |
| 58 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,07 | PCWP | |
|* 59 | HASH JOIN RIGHT OUTER | | 209M| 109G| 1825M| 2233K (3)| 00:05:30 | Q2,16 | PCWP | |
| 60 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 61 | PX SEND HASH | :TQ20008 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,08 | P->P | HASH |
|* 62 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,08 | PCWP | |
| 63 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,08 | PCWC | |
| 64 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,08 | PCWP | |
|* 65 | HASH JOIN RIGHT OUTER | | 209M| 97G| 1825M| 1844K (3)| 00:04:33 | Q2,16 | PCWP | |
| 66 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 67 | PX SEND HASH | :TQ20009 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,09 | P->P | HASH |
|* 68 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,09 | PCWP | |
| 69 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,09 | PCWC | |
| 70 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,09 | PCWP | |
|* 71 | HASH JOIN RIGHT OUTER | | 209M| 85G| 1825M| 1478K (3)| 00:03:39 | Q2,16 | PCWP | |
| 72 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 73 | PX SEND HASH | :TQ20010 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,10 | P->P | HASH |
|* 74 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,10 | PCWP | |
| 75 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,10 | PCWC | |
| 76 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,10 | PCWP | |
|* 77 | HASH JOIN RIGHT OUTER | | 209M| 73G| 1825M| 1135K (3)| 00:02:48 | Q2,16 | PCWP | |
| 78 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 79 | PX SEND HASH | :TQ20011 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,11 | P->P | HASH |
|* 80 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,11 | PCWP | |
| 81 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,11 | PCWC | |
| 82 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,11 | PCWP | |
|* 83 | HASH JOIN OUTER | | 209M| 61G| 3201M| 816K (3)| 00:02:01 | Q2,16 | PCWP | |
|* 84 | HASH JOIN OUTER | | 209M| 22G| 1676M| 520K (3)| 00:01:17 | Q2,16 | PCWP | |
|* 85 | HASH JOIN RIGHT OUTER | | 209M| 10G| | 302K (3)| 00:00:45 | Q2,16 | PCWP | |
| 86 | PX RECEIVE | | 23M| 538M| | 16751 (4)| 00:00:03 | Q2,16 | PCWP | |
| 87 | PX SEND HASH | :TQ20012 | 23M| 538M| | 16751 (4)| 00:00:03 | Q2,12 | P->P | HASH |
| 88 | PX BLOCK ITERATOR | | 23M| 538M| | 16751 (4)| 00:00:03 | Q2,12 | PCWC | |
| 89 | TABLE ACCESS FULL | SOME_TABLE | 23M| 538M| | 16751 (4)| 00:00:03 | Q2,12 | PCWP | |
| 90 | PX RECEIVE | | 209M| 6201M| | 285K (3)| 00:00:43 | Q2,16 | PCWP | |
| 91 | PX SEND HASH | :TQ20013 | 209M| 6201M| | 285K (3)| 00:00:43 | Q2,13 | P->P | HASH |
| 92 | VIEW | | 209M| 6201M| | 285K (3)| 00:00:43 | Q2,13 | PCWP | |
| 93 | WINDOW SORT | | 209M| 4401M| | 285K (3)| 00:00:43 | Q2,13 | PCWP | |
| 94 | PX RECEIVE | | 209M| 4401M| | 285K (3)| 00:00:43 | Q2,13 | PCWP | |
| 95 | PX SEND HASH | :TQ20001 | 209M| 4401M| | 285K (3)| 00:00:43 | Q2,01 | P->P | HASH |
| 96 | VIEW | | 209M| 4401M| | 285K (3)| 00:00:43 | Q2,01 | PCWP | |
| 97 | SORT GROUP BY | | 209M| 2800M| 4820M| 285K (3)| 00:00:43 | Q2,01 | PCWP | |
| 98 | PX RECEIVE | | 209M| 2800M| | 285K (3)| 00:00:43 | Q2,01 | PCWP | |
| 99 | PX SEND HASH | :TQ20000 | 209M| 2800M| | 285K (3)| 00:00:43 | Q2,00 | P->P | HASH |
| 100 | SORT GROUP BY | | 209M| 2800M| 4820M| 285K (3)| 00:00:43 | Q2,00 | PCWP | |
| 101 | VIEW | | 209M| 2800M| | 159K (1)| 00:00:24 | Q2,00 | PCWP | |
| 102 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,00 | PCWC | |
| 103 | TABLE ACCESS FULL| SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,00 | PCWP | |
| 104 | PX RECEIVE | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 105 | PX SEND HASH | :TQ20014 | 209M| 11G| | 159K (1)| 00:00:24 | Q2,14 | P->P | HASH |
|*106 | VIEW | | 209M| 11G| | 159K (1)| 00:00:24 | Q2,14 | PCWP | |
| 107 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,14 | PCWC | |
| 108 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,14 | PCWP | |
| 109 | PX RECEIVE | | 209M| 39G| | 159K (1)| 00:00:24 | Q2,16 | PCWP | |
| 110 | PX SEND HASH | :TQ20015 | 209M| 39G| | 159K (1)| 00:00:24 | Q2,15 | P->P | HASH |
|*111 | VIEW | | 209M| 39G| | 159K (1)| 00:00:24 | Q2,15 | PCWP | |
| 112 | PX BLOCK ITERATOR | | 209M| 14G| | 159K (1)| 00:00:24 | Q2,15 | PCWC | |
| 113 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75E2_25751CCB | 209M| 14G| | 159K (1)| 00:00:24 | Q2,15 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
If I materialize each merge_steps
with a hint /*+ MATERIALIZE */
(to save temporary space), I get one PX COORDINATOR for each materialized step. I can't add a request here: the question body is limited to 30k characters.
+3
source to share
1 answer
I think Oracle runs 8 parallel processes for each of the 4 main queries - 32 processes in total. Oracle cannot reuse the first 8 processes to query another sql while the result of the first 8 processes will be used later.
Increase productivity:
a) I would try to rewrite the query. The merge_step1, merge_step2, and merge requests look like they can be processed in a single request. My time is limited, I'm afraid, otherwise I would try it myself.
b) Do you have the ability to create a materialized view that stores the query result? If so, it can significantly improve performance. I would consider this option even if a) was successful.
0
source to share