Re: Parallel queries in RAC environment

You can use a combination of the init.ora parameters instance_groups and parallel_instance_groups. A simple example would be have the values equal to the instance_number i.e. instance_number=1 , instance_group=1, parallel_instance_group=1 etc.

In this example all parallel processes would be spawned on the node they were requested from.

The usage isn't limited to a one to one mapping i.e. nodes 1 and 2 could have instance_groups = 'node 1 and 2' and parallel_instance_groups= 'node 1 and 2' and the parallel processes would be spawned on nodes 1 and/or 2.

You can also control which instance_groups the parallel processes are pointed to by issuing an alter session set parallel_instance_groups = '....' command and I believe it is even hintable.

I also understand that in 11g these parameters have be supceded by the use of db_services which controls it automatically but I haven't tried this yet.

Hope this helps,

Cheers,

Ian

|---------+----------------------------->


|         |           james.barton_at_marki|

|         |           t.com             |

|         |           Sent by:          |

|         |           oracle-l-bounce_at_fr|

|         |           eelists.org       |

|         |                             |

|         |                             |

|         |           15/10/2008 14:03  |

|         |           Please respond to |

|         |           james.barton      |

|         |                             |

|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       oracle-l_at_freelists.org                                                                       |
  |       cc:                                                                                                    |
  |       Subject:  Parallel queries in RAC environment                                                          |
  >--------------------------------------------------------------------------------------------------------------|

4-node RAC, 10.2.0.3

I have a long-running batch process. I suspect that some of the queries it contains would run faster with multiple read processes, and I'm trying to test this out.

I've hinted the relevant queries with

/*+ parallel (table_alias 6) */

and I can see that six read processed do start. The problem is that they are all on another instance than the original batch process. This results in a lot of block swapping and I actually see a slight degradation in performance. Currently I'm starting the batch process as a scheduled job, and the job class it belongs to has a service that is available on only one instance. Still, the child processes are on another instance - they don't seem to inherit the service from the parent process.

How can I force all of the child query processes to be on the same instance as the parent process?

Cheers,
James

The content of this e-mail is confidential and may be privileged. It may be read, copied and used only by the intended recipient and may not be disclosed, copied or distributed. If you received this email in error, please contact the sender immediately by return e-mail or by telephoning +44 20 7260 2000, delete it and do not disclose its contents to any person. You should take full responsibility for checking this email for viruses. Markit reserves the right to monitor all e-mail communications through its network.
Markit and its affiliated companies make no warranty as to the accuracy or completeness of any information contained in this message and hereby exclude any liability of any kind for the information contained herein. Any opinions expressed in this message are those of the author and do not necessarily reflect the opinions of Markit. For full details about Markit, its offerings and legal terms and conditions, please see Markit's website at http://www.markit.com < http://www.markit.com/> .

--
http://www.freelists.org/webpage/oracle-l



This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
case of problems, please call your organisation’s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.


For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^