Skip to Main Content
  • Questions
  • Max value to be used in a parallel hint

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 03, 2016 - 7:16 am UTC

Last updated: December 19, 2018 - 1:48 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hi,

I am working on Oracle Database 11gR2 Enterprise Edition, Single Instance and i want to use parallel hint in one of the query.

Below are the values taken from v$parameter :

parallel_min_time_threshold AUTO
parallel_max_servers 40
parallel_servers_target 16
parallel_server FALSE
parallel_degree_limit CPU
parallel_degree_policy MANUAL
parallel_threads_per_cpu 2
cpu_count 1

I see that, Degree of Parallelism (DOP) = PARALLEL_THREADS_PER_CPU x CPU_COUNT, i.e., 2 * 1 = 2.

So, when using parallel hint on a query (considering that the environment is idle), the max value that i can use is,
a. DOP value = 2, or
b. parallel_servers_target=16, or
c. parallel_max_servers=40

Thanks in Advance,
Raja.

and Connor said...

parallel_max_servers is a cap on the number you can use instance-wide, so you will not be able to exceed that.

Other than that, choose a parallel setting that balances the needs of the query that will be run in parallel, with the needs of the impact that would have on other sessions/users on your system.

Because parallel *by definition* is basically - suck up more system resources to more quickly get to a result. If you suck them *all* up, then yes, your query might be nice and fast...but you might also have 100 other database users knocking on your door asking why *their* stuff is so slow :-)

Rating

  (4 ratings)

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

Comments

default

George Joseph, April 03, 2016 - 6:19 pm UTC

Would it be possible to let the database make up that decision of using the degree of parallelism for a query on the basis of what is available?

maxvalue - bug or feature

Boris, December 14, 2018 - 4:29 pm UTC

I am using oracle 11g R2 version. could you please explain why the partition "p_sg_2006" below picks up the JP data?

Is this bug or feature?

Sample:


create table range_part
(country varchar2(10) not null,
year number not null, data_1 varchar2(20))
partition by range (country, year)
(
partition p_in_2006 values less than ('IN',2007),
partition p_in_2007 values less than ('IN',2008),
partition p_in_2008 values less than ('IN',2009),
partition p_sg_2006 values less than ('SG',2007),
partition p_sg_2007 values less than ('SG',2008),
partition p_sg_2008 values less than ('SG',2009),
partition p_max values less than (MAXVALUE, MAXVALUE)
)



insert into range_part values ('IN',2007,'Row 1');
insert into range_part values ('IN',2008,'Row 2');
insert into range_part values ('JP',2007,'Row 3');
insert into range_part values ('JP',2015,'Row 4');
insert into range_part values ('US',2006,'Row 5');
insert into range_part values ('US',2009,'Row 6');

commit;


select * from range_part partition (p_sg_2006);

JP 2007 Row 3
JP 2015 Row 4

Why query is why the p_sg_2006 captures the JP values?

Thanks in advance for your valuable service to the oracle community.

Connor McDonald
December 17, 2018 - 2:24 am UTC

Multi-column range partitions are not a "matrix" or "multi-dimensional" concept but a tie-breaker concept.

So when you insert say: IN,2009 the database says:

"OK, where does IN go. Is there an *exact* partition that it belongs in?"

The answer is No...so then we look at the next column.

"OK, I've narrowed it down to 3 possible partitions. What does the 2009 tell me? Ah, that means, p_in_2008"

So in your example - you insert JP,2007 (or JP anything).

"OK, where does JP go. Is there an *exact* partition that it belongs in?"
"Yes...JP is more than IN and less than SG, so it goes in p_sg_2006"

Hope that makes sense.

max value confusion

Boris, December 17, 2018 - 4:38 pm UTC

Thanks Connor for your response. I am bit confused after reading this

So in your example - you insert JP,2007 (or JP anything).

"OK, where does JP go. Is there an *exact* partition that it belongs in?"
"Yes...JP is more than IN and less than SG, so it goes in p_sg_2006"

My understanding is that when we insert JP,2007, oracle will look for the country code='JP'(left most column match) and then look for the year ranges with the JP country code based
partition. on the top, there is no partition defined for the country code 'JP. Technically JP,2007 would have been inserted into p_max partition.

Could you please expand how "JP is more than IN and less than SG"? Thanks for your time and patience in educating the oracle concepts.

Connor McDonald
December 18, 2018 - 2:08 am UTC

*Conceptually* I like to think about the multi-columns as a concatenated value - sort of like my partitions are equivalent to:

1) values less than IN2007
2) values less than IN2008
3) values less than IN2000
4) values less than SG2007

So when I give you the value JP1234, I can see that it sorts higher than 3 and lower than 4, so it goes in partition 3.

Unreiliable

A reader, December 18, 2018 - 6:39 am UTC

And what thé hell post About partitions la To do with "Max value to be used in a parallel hint" ?
Connor McDonald
December 19, 2018 - 1:48 am UTC

Agreed. We should have rejected this one and forced a new question.

But man, one star is a bit harsh :-)