Parallel execution in RAC

Wrong usage of parallelism it is a strong enemy for query performance in Oracle RAC.
If you use RAC with two or more nodes and some of your TABLES or INDEXES have a "DEFAULT" value for DEGREE and INSTANCES parameters then you have troubles :).

To check if you have "DEFAULT" values for tables / indexes:

SELECT TABLE_NAME, OWNER, DEGREE, INSTANCES 
FROM DBA_TABLES WHERE INSTANCES = 'DEFAULT';

SELECT INDEX_NAME,TABLE_NAME, OWNER, DEGREE, INSTANCES 
FROM DBA_INDEXES WHERE INSTANCES = 'DEFAULT'; 

Oracle calculates DEFAULT degree of parallelism for a query by multiply the following instance parameters:

for 3 nodes RACs with 16 cores per node:
parallel_server_instances = 3
cpu_count=16
parallel_threads_per_cpu=2

So we have DEGREE= 16* 2 * 3 = 96 !!!

Here is the example of performance difference for simple query:

SQL> select degree,instances from user_indexes where index_name='IDX_TEST_2312';

   DEGREE         INSTANCES
--------------        -----------------
DEFAULT             DEFAULT

1. The query use degree with level 96.












2. The same query but with the hint to set the degree to 1.

So keep an eye the "DEFAULT" value for your tables / indexes if you use RAC with more then one node. 

That is all!