Home » RDBMS Server » Performance Tuning » Parallel queries.
Parallel queries. [message #212136] Wed, 03 January 2007 23:27 Go to next message
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
Re: Parallel queries. [message #212158 is a reply to message #212136] Thu, 04 January 2007 00:58 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Parallel queries. [message #212246 is a reply to message #212158] Thu, 04 January 2007 05:55 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks a lot Ross.
Re: Parallel queries. [message #212562 is a reply to message #212158] Fri, 05 January 2007 17:46 Go to previous messageGo to next message
martijn
Messages: 283
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
Re: Parallel queries. [message #212900 is a reply to message #212562] Mon, 08 January 2007 15:19 Go to previous message
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.
Previous Topic: MERGE but no PK on the table
Next Topic: Hints index combine
Goto Forum:
  


Current Time: Mon Dec 05 14:53:37 CST 2016

Total time taken to generate the page: 0.16294 seconds