Re: Parallel queries in RAC environment

Hi James
  Parameters instance_group and parallel_instance_group parameter determines which instance(s) will participate in parallel executions.   For example, for this setup:

           inst1.instance_group='inst1','all'
           inst2.instance_group='inst2','all'

  Then if parallel_instance_group at session or instance level is set to 'all' then both instances will participate in parallel execution. If parallel_instance_group is set to 'inst1' then only inst1 will participate in parallel execution. So you would setup these parameters correctly to control node participation.   What you saw is a common issue. Parallel execution doesn't scale well with inter-instance parallelism. It is possible to improve performance playing with few PX related hints, but it isn't worthwhile though.

Cheers
Riyaj
The Pythian Group: http://www.pythian.com Personal blog: http://orainternals.wordpress.com

James Barton wrote:
> 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
>
>
>
>

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