OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Parallel queries.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Parallel queries. [message #212136]

Wed, 03 January 2007 23:27

shahnazurs
Messages: 240
Registered: June 2005
Location: India

Senior Member

Dear All,
Please have a look at the following query

select /*+ parallel(emp,4) */ count(*) from emp;

My understanding about that above query is:-
Processor spawns 4 slave processes, this processes reads the table emp parallelly.

My question is

how much slave processes can be used i.e. max limit; here it is 4 and then what type of queries we can use this hint?

Thanks,
Shahnaz

Report message to a moderator

Re: Parallel queries. [message #212158 is a reply to message #212136]

Thu, 04 January 2007 00:58

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You don't need to specify a number at all. If you leave the number out, Oracle will work out a suitable number for you.

The maximum is system dependent. If you specify a number too big, Oracle will automatically adjust it down.

Parallel query will work on any query that performs a Full table or Full index scan. Low volume SQL that probes table via an index will not use Parallel Query.

Ross Leishman

Report message to a moderator

Re: Parallel queries. [message #212246 is a reply to message #212158]

Thu, 04 January 2007 05:55

shahnazurs
Messages: 240
Registered: June 2005
Location: India

Senior Member

Thanks a lot Ross.

Report message to a moderator

Re: Parallel queries. [message #212562 is a reply to message #212158]

Fri, 05 January 2007 17:46

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

Hi,

I was thinking about this question, and came up with the following:
It doesn't make much sense to supply a number bigger then the number of CPU's you have. This because that is the maximum parallelity (is that an english word?) you can get. (Probably even that is much to optimistic, since a system has a lot more to do then oracle alone)

Could you please comment on this statement. I'm just wondering whether there is some truth in it, and if not why not.

I understand your remark that Oracle can come up with it's own number very well, so the above is a bit theoretical Smile

Best Regards,
Martijn

Report message to a moderator

Re: Parallel queries. [message #212900 is a reply to message #212562]

Mon, 08 January 2007 15:19

artmt
Messages: 32
Registered: October 2006
Location: Boston

Member

A single cpu can serve more than one process.
In a single-user environment you should be ok with 2 slave processes per cpu.

Report message to a moderator

Previous Topic:	MERGE but no PK on the table
Next Topic:	Hints index combine

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 11:27:20 CST 2024