No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48209
Total active users1645
Act. users last 24h6
Act. users last hour0
Registered user hits last week186
Registered user hits last month812
Go up

Impact of parallelism on session_per_user limit
Next thread: SLLoader direct path and unusable indices
Prev thread: ORA-01499: table/index cross reference failure - see trace file

Message Score Author Date
Hi, We have model environment where session_per_u...... M KUMAR Feb 28, 2018, 07:39
Hello, where does the "<I>including child pro...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Feb 28, 2018, 08:42
where does the "including child processes" come fr...... M KUMAR Feb 28, 2018, 08:49
Hi, well, your case is interesting because it r...... Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts Ales Kavsek Feb 28, 2018, 15:55
Thanks Ales, that's very helpful. Is there anyw...... M KUMAR Feb 28, 2018, 18:03
Hi, I don't think so. I'm not aware of any work...... Ales Kavsek Mar 01, 2018, 11:27

Follow up by mail Click here


Subject: Impact of parallelism on session_per_user limit
Author: M KUMAR, India
Date: Feb 28, 2018, 07:39, 113 days ago
Os info: Linux 6
Oracle info: 12cR1
Message: Hi,
We have model environment where session_per_user is set to 10. That means no user will be able create more than 10 sessions including child processes.

When I executed the below command it spawned double processes i.e.32

SELECT /*+ PARALLEL(16) */ * FROM DBA_TAB_COLUMNS A, DBA_TAB_COLUMNS B
WHERE A.COLUMN_NAME=B.COLUMN_NAME;

I was expecting, it would only spawn 10 processes.

Is there any parameter in the db which override session_per_user limit.
Please help.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Impact of parallelism on session_per_user limit
Author: Bruno Vroman, Belgium
Date: Feb 28, 2018, 08:42, 113 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello,


where does the "including child processes" come from?

The limit is about SESSIONS and your amazement is about PROCESSES...

You might have a look at the following thread (askTom: DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS)
   https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5671284058977

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Impact of parallelism on session_per_user limit
Author: M KUMAR, India
Date: Feb 28, 2018, 08:49, 113 days ago
Message: where does the "including child processes" come from?
Whenever a query runs in parallel mode there is QC(Parent) and Slaves(Children--> consumer/producer). Plz don't go into that just look at the issue i am facing. I am very well aware of sessions/processes/connection- thanks anyways for the link. I may be using these term interchangeably. Please help why the query is spawning 32 children processes.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Impact of parallelism on session_per_user limit
Author: Ales Kavsek, Slovenia
Date: Feb 28, 2018, 15:55, 113 days ago
Score:   Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts
Message: Hi,

well, your case is interesting because it reveals a "funny" part of internal Oracle development:

Up to 12cR1, sessions_per_user indeed limited parallel slaves in addition to "usual" user sessions.

Starting with 12cR1 this is no more true. For a full story, refer to reply from Oracle on this (you'll have to login to MOS):

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2273312_1.html

But basically, according to Oracle, the old behavior (up to 12cR1) was not ok, a quote from Oracle:

"The past behavior could have been wrong and should not take account, since
the documentation does not relate in any form such parameters."


What an excuse, if you ask me!? :)

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Impact of parallelism on session_per_user limit
Author: M KUMAR, India
Date: Feb 28, 2018, 18:03, 113 days ago
Message: Thanks Ales, that's very helpful.

Is there anyway we can restrict number of session including parallelism as a whole user wise like sessions_per_user used to work prior 12CR1?

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Impact of parallelism on session_per_user limit
Author: Ales Kavsek, Slovenia
Date: Mar 01, 2018, 11:27, 112 days ago
Message: Hi,

I don't think so. I'm not aware of any workaround for this changed behavior. Personally, I prefer an old approach, but there were some customers that demanded from Oracle to fix this "bug", which prevented PQ slaves to run because of the sessions_per_user settings. Of course, you can limit PQ slaves at instance level as well as with the resource manager, but this is, imho, not a proper substitute for old behavior.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here