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
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,
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>
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
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!
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!
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.
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
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
October 27, 2020 - 1:34 am UTC
thanks for getting back to us