RE: RE: Oracle Partitioning and parallel DML

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC) Sent: Wednesday, August 15, 2018 5:43 PM To: dmarc-noreply_at_freelists.org; '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_at_rsiz.com> wrote:    

connections or sessions? Did you also enable parallel dml for the session?  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

--
http://www.freelists.org/webpage/oracle-l