| Parallel query and RAC [message #74882] |
Tue, 16 March 2004 23:36  |
Mike Wielonski
Messages: 2
Registered: March 2004
|
Junior Member |
|
|
I am seeing some strange behavior with regards to parallel processing in a 2 node cluster and wanted to know if anyone else had seen something similar. When running a query from node1:
sql> select /*+ parallel(t,4) full(t) */ count(*) from t;
Four parallel slaves on node2 become active for the duration of the query. At no time during the above query (via monitoring v$px_process) do any of the parallel slaves on node1 go active. If the same query is run from node2, it still uses only node2 parallel slaves. We are running 9.2.0.4 RAC. The following of both nodes is true:
- this is a test system, therefore my session is the only one on either cluster
- no parallel slaves are active prior to above sql
- table t has degre=1 and instances=1
- parallel_max_servers=16
- parallel_min_servers=8
- explain plan for query shows intent to use parallel processing
- cpu_count=64
- parallel_automatic_tuning=FALSE
- parallel_adaptive_multiuser=FALSE
- parallel_server=TRUE
Any explanations as to why this might be happening or similar findings would be greatly appreciated.
Thanks,
Mike
|
|
|
|
| Re: Parallel query and RAC [message #74885 is a reply to message #74882] |
Tue, 23 March 2004 02:11  |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
Hi,
This might have something to do with your INIT.ORA parameter settings for INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP. Please let us know your settings for both instances.
Best regards.
Frank
|
|
|
|
| Re: Parallel query and RAC [message #74886 is a reply to message #74885] |
Tue, 23 March 2004 06:21 |
Mike Wielonski
Messages: 2
Registered: March 2004
|
Junior Member |
|
|
Neither of these parameters are set in this environment:
SQL> show parameter group
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups string
parallel_instance_group string
|
|
|
|
| Re: Parallel query and RAC [message #74888 is a reply to message #74886] |
Fri, 26 March 2004 03:16 |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
Hi,
This beats me as well. You can maybe try to set INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP to control where your parallel slaves will execute.
Best regards.
Frank
|
|
|
|
| Re: Parallel query and RAC [message #136385 is a reply to message #74888] |
Wed, 07 September 2005 20:41 |
tinojam
Messages: 6
Registered: April 2005
Location: Florida
|
Junior Member |
|
|
This behaviour might be normal. It might be that node2 is the least loaded instance. Does both of the node have the same # of CPUs? You may use INSTANCE_GROUPS with ALTER SESSION SET PARALLEL_INSTANCE_GROUP = ‘<INSTANCE_GROUPS>’; to set the query to node1 but that might defeat the purpose of load balancing especially if node1 is over-burdened... I know that this is an old thread but feedback would be great...
NB:
PX is automatically controlled on RAC systems. Parallel execution does not allocate slaves randomly across the available instances, but rather will start by allocating on the least loaded instance. The goal is to both minimize inter-node traffic and at the same time try to minimize any imbalance of work across the instances.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
