Skip to Main Content
  • Questions

  • Number of CPUs and Degree of Parallelism

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 28, 2009 - 1:09 am UTC

Last updated: October 27, 2020 - 1:34 am UTC

Version: 9.2.0.8 and 11.1.0.7

Viewed 50K+ times! This question is

You Asked

Hi Tom,

1) Is there or What is the relationship between number of CPUS the host has to the degree of Parallelism selected say while running DBMS_STATS.

2) Is there a relationship between DB sessions and Degree of Parallelism.


If I have say 4 CPUS and select degree of 4 When I run DBMS_STATs will it show

4 DB sessions and consume 4 CPUS ?

Or It will definitely show 4 DB sessions but no. of CPU depends upon availabilty from OS side. So even 2 CPUS will be used for 4 sessions ?

3) Is there always one to one relationship between a DB session ( from v$session) and OS process (seen with ps command )

4) DBMS_SCHEDULER does it need to have job_queue_processes set like the old days with dbms_job


Please answer with respect to 9208 and 11107 version.

and Tom said...

cpu_count is used to set other parallel related parameters, parallel_max_servers for example:

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams.htm#REFRN10158

the more cpu's you have, the higher the possible degree of parallelism.


As for the relationship between "db sessions" and degree of parallelism (DOP), see http://docs.oracle.com/cd/B19306_01/server.102/b14223/usingpe.htm#sthref2310

if you are allowing us to decide what the correct DOP is, yes, there is a relationship - the more sessions requesting parallel operations - the lower the DOP (you do not want to overwhelm the machine). Fewer sessions -> higher DOP.


As for "4 db sessions consume 4 cpus", the DOP will be higher than the number of cpu's (you do IO, you use CPU, you do not typically just use CPU so more than one thing can be 'done' at a time).


Is there a one to one relationship between sessions and processes? Absolutely not, there is a MANY to MANY relationship.

A single session can move from process to process (shared server)
A single process can be hosting many sessions (shared server, dedicated server)

see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4269591917792#1548275900346762755


The scheduler does not use job_queue_processes
http://docs.oracle.com/cd/B19306_01/server.102/b14231/schedadmin.htm#sthref3775

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DOP table attribute

Lise, February 24, 2010 - 1:29 pm UTC

Hi,
We have 9 CPUs and due to historical reasons are setting a DOP at table and index level of 6. Our parallel_threads_per_cpu is 2.
We set these at the start of our batch job.
We are currently on 9i and would like to change these dated and incorrect DOP settings as part of upgrade to 11gR1. RAT would be great for this type of testing, but I do not have access to this tool at the moment. I only have a limited set of weekends to test this.
I think we should switch parallel off since we do not run a datawarehouse and our tables are not large, however I am nervous as to what will happen.
Is there a better way to set the DOP say depending on table size? If small then low DOP, medium then medium DOP, etc.
Thanks
Tom Kyte
March 01, 2010 - 9:08 am UTC

.. I think we should switch parallel off since we do not run a datawarehouse and
our tables are not large...


Well, since this ia a BATCH, you should be able to run the batch with and without any degree set and compare - couldn't you? It isn't like trying to figure out what happens with interactive human beings - it sounds much easier?

Impact of DOP on index

Rizi DBA, May 05, 2011 - 2:42 am UTC

Hi Tom,

Can you please elaborate what is the impact of index and table's DOP on the overall performance of the database?

Thanks,


Tom Kyte
May 06, 2011 - 9:34 am UTC

I get tired of writing this, but,....

a) it might make it faster
b) it might make it slower
c) it might have no effect on the speed.

It depends.


Think about it. If you have ample resources freely available and sitting idle AND you have some really big things to do THEN increasing the degree of parallelism (DOP) might well have a profound effect on reducing the runtimes.

Else - it might not, probably would not, and might even increase the runtimes.

degree of parallelism

A reader, May 30, 2011 - 6:36 pm UTC 

Hi,

  Could you please explain why the degree of parallelism is defaulted to 1? 

FYI, the created table syntax does not have parallel option. I am scratching my head without solution.
Please advise.

Regards,
Boris

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


SQL> create table test ( id number);

Table created.

SQL> select table_name,degree from user_tables
  2   where
  3   table_name='TEST'
  4
SQL> /

TABLE_NAME                     DEGREE
------------------------------ ----------
TEST                                    1

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     160
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
SQL>

Tom Kyte
May 31, 2011 - 12:46 pm UTC

because 1 is basically "not parallel" and "not parallel" is the default.

your create table didn't include a tablespace either - but yet, it has a tablespace. The defaults are used.

A reader, June 01, 2011 - 9:09 pm UTC

Hi Tom,

Thanks for your reply. My understanding is that the keyword "degree" is always related to parallel option. I am curious to know how this default value is derived? Is this based on database parameter settings?

Thanks a million.

Regards,
Boris
Tom Kyte
June 02, 2011 - 8:53 am UTC

degree = 1 is "not parallel" by the very definition of a degree of parallelism.

by default, things are "not parallel". degree = 1 is "not parallel"


I'm not sure why this is perplexing? Degree = 1 is a way of saying "no parallel for me"

A reader, June 02, 2011 - 9:09 pm UTC

Thanks for the clarification. Much appreciated.

Regards,
Boris

doro, February 24, 2012 - 3:12 pm UTC

Hi Tom,

If PARALLEL_THREADS_PER_CPU shows me a default 2 value and PARALLEL_MAX_SERVERS a value of 40 (test DB) it meens that

a) even if i set a higher degree of parallelism on a statement then the maximum i can get is a degree of 2?

b)what happens if several parallel statements are run at the same time? those statements will get the parallel servers processes from the pool, but will them be runed in a queue or somehow simultaneously considering the above example of only 2 threads for CPU !

Thank you!
Tom Kyte
February 25, 2012 - 6:33 am UTC

a) no, http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams156.htm

it is *per cpu*

you presumably have more than one of those

b) depends on the version and how YOU have configured the server. what would you like to have happen and what version are you running...

doro, February 25, 2012 - 9:41 am UTC

sorry for not giving you all the details !
my simple test DB runs on a regular pc with only one CPU: PARALLEL_THREADS_PER_CPU=2/PARALLEL_MAX_SERVERS=40/cpu_count=1/ 10gR2(no queuing)

if i run "select /*+ parallel(sales,16) */ * from test.sales;"
and if i query V$PX_SESSION view i see that i requested 16 parallel server processes and the degree of parallelism being used by the server set it's also 16 even if PARALLEL_THREADS_PER_CPU=2.
What's the relation between threads per CPU and the number of server processes serving user statements? Also: how are these parallel processes named at the operatin system level (linux env) ? I only see some ora_p000...ora_p0018 that i guees are some slave processes that serv my dedicated server proces.

Thanks again!


Tom Kyte
February 26, 2012 - 12:00 am UTC

if you only have one cpu, you better forget about parallel 16 immediately. Anything above parallel 2 or maybe 4 is out of the question.


ora_pNNN are the parallel execution servers, yes.

parallel threads per cpu is a setting used to derive other settings, it isn't a limit on anything.

Question

Mark, February 16, 2013 - 3:51 pm UTC

Hello Tom

My question is:

If I have parallel_max_server set to 160 on a 8 CPU machine, does that mean that all the concurrent sessions cannot have parallel slave count (cumulative) more than 160? For ex. if I have 22 concurrent sessions, 10 are running parallel DML (degree=8, considering no indexes for simplicity), 10 are running parallel query (degree=8) and rest 2 are serial (no parallel), 10*8+10*8+2*1=162, (I'm sure background is also counted here?), if it's 10g (not psq), will it force one of those parallel queries into serial or maybe lower DOP considering limit is 160 (instance wide)?

What if I force DOP as 8 for queries, will it still lower the DOP considering the limit is reached or serialize them?

Please help me understand if I'm getting it right.

Thanks for all the help.
Tom Kyte
February 19, 2013 - 9:12 am UTC

with 160 set as the max degree of parallelism, we'll start no more than 160 parallel execution servers. processes that need anything above that will either not run or run with a reduced degree or parallelism or be placed into a queue to await processing when resources become available. depending on your your DBA set things up.

http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm#VLDBG0102

parallel min servers

Mark, February 25, 2013 - 3:43 pm UTC

Thanks for the reply Tom,

Can you tell me why do i see 10 p00 process while I have my parallel_min_servers set to 20, this is what I see:

SQL> !ps -ef | grep -i p00
  oracle  4587724        1   0   Feb 01      - 633:54 ora_p005_db20upa
  oracle  5636322        1   0   Feb 01      - 575:52 ora_p004_db20upa
  oracle  5701808        1   0   Feb 01      - 314:17 ora_p002_db20upa
  oracle  5767346        1   0   Feb 01      - 307:35 ora_p001_db20upa
  oracle  5832886        1   0   Feb 01      - 300:05 ora_p000_db20upa
  oracle  5963866        1   0   Feb 01      - 596:41 ora_p003_db20upa
  oracle  6684876        1   0   Feb 01      - 51:20 ora_p006_db20upa
  oracle  6750414        1   0   Feb 01      - 50:36 ora_p007_db20upa
  oracle  6815952        1   0   Feb 01      - 28:19 ora_p008_db20upa
  oracle  6947028        1   0   Feb 01      - 27:43 ora_p009_db20upa


Seems all of them started the time DB was started:

SQL> !ps -ef | grep -i pmon
  oracle  5898436        1   0   Feb 01      -  6:26 ora_pmon_db20upa
  oracle   655866 34144392   1 15:38:16  pts/0  0:00 grep -i pmon


Wondering why not 20 and why 10?

SQL> show parameter parallel

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -------
fast_start_parallel_rollback         string                           LOW
parallel_adaptive_multi_user         boolean                          TRUE
parallel_automatic_tuning            boolean                          FALSE
parallel_degree_limit                string                           CPU
parallel_degree_policy               string                           MANUAL
parallel_execution_message_size      integer                          4096
parallel_force_local                 boolean                          FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean                          FALSE
parallel_max_servers                 integer                          400
parallel_min_percent                 integer                          0
parallel_min_servers                 integer                          20
parallel_min_time_threshold          string                           AUTO
parallel_server                      boolean                          FALSE
parallel_server_instances            integer                          1
parallel_servers_target              integer                          160
parallel_threads_per_cpu             integer                          2
recovery_parallelism                 integer                          0

Tom Kyte
February 25, 2013 - 4:25 pm UTC 




ops$tkyte%ORA11GR2> !ps -ef | grep ora_p0
ora11gr2 28975     1  0 17:24 ?        00:00:00 ora_p000_ora11gr2
ora11gr2 28977     1  0 17:24 ?        00:00:00 ora_p001_ora11gr2
ora11gr2 28979     1  0 17:24 ?        00:00:00 ora_p002_ora11gr2
ora11gr2 28981     1  0 17:24 ?        00:00:00 ora_p003_ora11gr2
ora11gr2 28983     1  0 17:24 ?        00:00:00 ora_p004_ora11gr2
ora11gr2 28985     1  0 17:24 ?        00:00:00 ora_p005_ora11gr2
ora11gr2 28987     1  0 17:24 ?        00:00:00 ora_p006_ora11gr2
ora11gr2 28989     1  0 17:24 ?        00:00:00 ora_p007_ora11gr2
ora11gr2 28991     1  0 17:24 ?        00:00:00 ora_p008_ora11gr2
ora11gr2 28993     1  0 17:24 ?        00:00:00 ora_p009_ora11gr2
ora11gr2 28995     1  0 17:24 ?        00:00:00 ora_p010_ora11gr2
ora11gr2 28997     1  0 17:24 ?        00:00:00 ora_p011_ora11gr2
ora11gr2 28999     1  0 17:24 ?        00:00:00 ora_p012_ora11gr2
ora11gr2 29001     1  0 17:24 ?        00:00:00 ora_p013_ora11gr2
ora11gr2 29003     1  0 17:24 ?        00:00:00 ora_p014_ora11gr2
ora11gr2 29005     1  0 17:24 ?        00:00:00 ora_p015_ora11gr2
ora11gr2 29007     1  0 17:24 ?        00:00:00 ora_p016_ora11gr2
ora11gr2 29009     1  0 17:24 ?        00:00:00 ora_p017_ora11gr2
ora11gr2 29011     1  0 17:24 ?        00:00:00 ora_p018_ora11gr2
ora11gr2 29013     1  0 17:24 ?        00:00:00 ora_p019_ora11gr2



you looked for p00 - try looking for just p0!

parallel query 12.1.0.2

A reader, June 24, 2015 - 10:57 pm UTC

Hi Tom ,
What's new in 12c parallel query ?
Any new features ?
Best regards.
Kais

Why more parallel slaves are not spawned

Apr, October 25, 2020 - 1:27 am UTC

Hi,

I have 2 database instances in different servers - with identical settings for parallelism and session parameters etc

When I give a parallel hint with degree 32 - in one of the instance, these 32 parallel slaves are getting spawned whereas in the other instance only 5 parallel processes are getting spawned at the most whatever I give as the degree of parallelism. What could be restricting this parallel slaves to 5 in the second database instance ? Almost all the init.ora parameters are same between these 2 instances. Oracle 12c

thank you

kindly ignore above

Apr, October 25, 2020 - 11:44 am UTC

The answer to the previous question was in the error message itself.
sessions_per_user limit is set
Connor McDonald
October 27, 2020 - 1:34 am UTC

thanks for getting back to us
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here