Rafiq
Degree is set to 1 as changing to Default or higher cause multiple session comes and hence cause resmgr:cpu event due to CPU restriction been set using Resource Manager. Even with Forcing session level parallel DML/DDL to 4 is causing multiple processes starts where both Insert and Select involved tables are a partitioned table.Â
Sanjay
On Saturday, August 18, 2018, 11:25:51 AM EDT, Mohammad Rafiq <***@hotmail.com> wrote:
Just one thing came in my mind.Just check the parallel degree of tables involved. If it is set to DEFAULT please change it 4 or whatever degree you want.Â
Regards Rafiq
ThanksRafiq
On Aug 15, 2018, at 7:51 PM, Mark W. Farnham <***@rsiz.com> wrote:
#yiv6248568970 #yiv6248568970 -- _filtered #yiv6248568970 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv6248568970 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv6248568970 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv6248568970 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv6248568970 {font-family:New;panose-1:0 0 0 0 0 0 0 0 0 0;}#yiv6248568970 #yiv6248568970 p.yiv6248568970MsoNormal, #yiv6248568970 li.yiv6248568970MsoNormal, #yiv6248568970 div.yiv6248568970MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:New;}#yiv6248568970 a:link, #yiv6248568970 span.yiv6248568970MsoHyperlink {color:blue;text-decoration:underline;}#yiv6248568970 a:visited, #yiv6248568970 span.yiv6248568970MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv6248568970 p.yiv6248568970MsoAcetate, #yiv6248568970 li.yiv6248568970MsoAcetate, #yiv6248568970 div.yiv6248568970MsoAcetate {margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv6248568970 p.yiv6248568970msonormal, #yiv6248568970 li.yiv6248568970msonormal, #yiv6248568970 div.yiv6248568970msonormal {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:New;}#yiv6248568970 p.yiv6248568970msochpdefault, #yiv6248568970 li.yiv6248568970msochpdefault, #yiv6248568970 div.yiv6248568970msochpdefault {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:New;}#yiv6248568970 span.yiv6248568970msohyperlink {}#yiv6248568970 span.yiv6248568970msohyperlinkfollowed {}#yiv6248568970 span.yiv6248568970emailstyle17 {}#yiv6248568970 p.yiv6248568970msonormal1, #yiv6248568970 li.yiv6248568970msonormal1, #yiv6248568970 div.yiv6248568970msonormal1 {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv6248568970 span.yiv6248568970msohyperlink1 {color:blue;text-decoration:underline;}#yiv6248568970 span.yiv6248568970msohyperlinkfollowed1 {color:purple;text-decoration:underline;}#yiv6248568970 span.yiv6248568970emailstyle171 {color:#1F497D;}#yiv6248568970 p.yiv6248568970msochpdefault1, #yiv6248568970 li.yiv6248568970msochpdefault1, #yiv6248568970 div.yiv6248568970msochpdefault1 {margin-right:0in;margin-left:0in;font-size:10.0pt;font-family:New;}#yiv6248568970 span.yiv6248568970BalloonTextChar {}#yiv6248568970 span.yiv6248568970EmailStyle29 {color:#1F497D;}#yiv6248568970 .yiv6248568970MsoChpDefault {font-size:10.0pt;} _filtered #yiv6248568970 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv6248568970 div.yiv6248568970WordSection1 {}#yiv6248568970
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-***@freelists.org [mailto:oracle-l-***@freelists.org]On Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC)
Sent: Wednesday, August 15, 2018 5:43 PM
To: dmarc-***@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 <***@rsiz.com> wrote:
Â
Â
connections or sessions? Did you also enable parallel dml for the session?
Â
From:oracle-l-***@freelists.org [mailto:oracle-l-***@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