Degree of parallelism (DOP) is the number of logical parallel sets of producers
and consumers that get started (parallel servers). For join queries there can
be multiple sets at that degree of parallelism, so the number of “sessions” can
grow pretty quickly.
The easiest way to get a start understanding it is to look at a sqlmonitor
graphical report and you’ll see the sets of producers and consumers, and then
read the various concepts sections about how it works. Controlling the maximum
number of things spawned and things like downgrades is kindly described as a
bit baroque, but at the bottom is the idea that Oracle really wants to achieve
figuring out the degree and number of servers for you automatically.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC)
Sent: Wednesday, August 15, 2018 5:43 PM
To: dmarc-noreply@xxxxxxxxxxxxx; 'Oracle-L Freelists'; Mark W. Farnham
Subject: Re: RE: Oracle Partitioning and parallel DML
Mark
It is the session which is visible and yes Parallel DML was enabled for the
session. Trying to understand as how Oracle create so many session which might
be due to parallel queries and how they are related or how Oracle decide to
start the number of parallel slaves.
Is it depending solely on enable PARALLEL DML setting like I had 5 here?
Is it depending on Partition in the involved table like for INSERT and SELECT?
Thanks
Sanjay
On Wednesday, August 15, 2018, 5:34:22 PM EDT, Mark W. Farnham <mwf@xxxxxxxx>
wrote:
connections or sessions? Did you also enable parallel dml for the session?
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC)
Sent: Wednesday, August 15, 2018 4:41 PM
To: Oracle-L Freelists
Subject: Oracle Partitioning and parallel DML
Can someone help to understand the Parallel DML doing INSERT and partitions? I
had two partitioned table with 7 partitions in one table where Insert is going
and 5 partitions in the second table which is used in SELECT
Using session level force Paralle DML 5
INSERT /APPEND/ into 7_part_table select from 5Part_table
This is Oracle Exadata, 2 two node RAC with 12.1. I can see that the above
insert create more than 20 connections and causing a high bottleneck to the
system Resource and affecting the other process. Is it suppose to create these
many processes.
DOP is manual
Table/corresponding Index Level Degree is set as 1
TIA
Sanjay
