We have below parameter set for one of our database which is on 11.2.0.4 and is 4-NODE RAC. And suddenly we saw we have had multiple queries running in large number of threads(DOP was 192 with "Servers Allocated=384") at same time. These query were using PARALLEL hints with no degree in them since long back. And were running slower this time due to concurrent executions, so i want to revisit the Parallel setup
I have few questions as below
1)If its good idea to put hard limit on the values like "parallel_degree_limit" rather than default? And in current setup i.e. "parallel_degree_limit" for each node set as CPU and "cpu_count" as 32, so here the DOP will be restricted to 32 and the "Servers Allocated" could be 32*2=64, is my understanding is correct on this?
2)In above case even the query is running on node-4 then as per the DOP calculation, it should have executed the query with max DOP = (parallel_threads_per_cpu * SUM(cpu_count))= 2*32=64, However i see the query was running with DOP-192, so is it because we have set the parameter "parallel_force_local" as FALSE, and so its taking all the 4-nodes CPU into consideration while calculating the DOP= 2*(32*4)= 256, is my understanding correct here?
3)Currently many times we are seeing the "GC" waits during the execution and i see we have "parallel_force_local" set as 'FALSE" which is again default. So is it good practice to set that as TRUE, so that the parallel threads for a specific query will be aligned to same node only? and also it will minimize the DOP?
| NAME | VALUE |
| cpu_count | 32 |
| parallel_server | TRUE |
| parallel_server_instances | 8 |
| parallel_max_servers | 128 |
| parallel_degree_policy | MANUAL |
| parallel_threads_per_cpu | 2 |
| parallel_min_time_threshold | AUTO |
| parallel_degree_limit | CPU |
| parallel_force_local | FALSE |
| parallel_servers_target | 128 |