PARALLEL_FORCE_LOCAL=FALSE? I Don't Think So....

A troubling parallel processing default value keeps constantly cropping up in my various client RAC installations.  Called "parallel_force_local", the default value is false.  It sounds innocuous enough, until it causes some issues and it's daunting implications are soon realized.  Here is a description of the parameter and it's values from the Oracle 11gR2 documentation:

PARALLEL_FORCE_LOCAL

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	true | false
Basic	No

PARALLEL_FORCE_LOCAL controls parallel execution in an Oracle RAC environment. By default, the parallel server processes selected to execute a SQL statement can operate on any or all Oracle RAC nodes in the cluster. By setting PARALLEL_FORCE_LOCAL to true, the parallel server processes are restricted so that they can only operate on the same Oracle RAC node where the query coordinator resides (the node on which the SQL statement was executed on).

As stated above, a default value of "false" allows a SQL statement which "qualifies"* for parallel processing to unrestricted processing across the entire cluster.  The theory is that a SQL statement can be executed faster with cooperative processing across many nodes, each with multiple CPU and large amounts of memory.  Just like two or three people can complete a large jigsaw puzzle faster than one, more resources should get a task done faster.  However, considering the overhead involved in managing the vast resources of a RAC cluster, and maintaining status of such complex processing as a SQL statement, finding the right "blend" of resources to deliver consistently faster results can be a challenge.  Add in the fact that there are quite a few parallel processing parameters, the fact that they can be different for each node, and that they may not always be appropriate for a large query on any one of the nodes, you have to wonder if attempting such an activity by default is truly appropriate.  

I could go into the details of how the cache resource manager and block processing resources for parallel processing across nodes are not always efficient, but needless to say, it's been my experience, with overwhelming proof via demonstration, that only in the rarest cases will a query complete faster by being parallelized across nodes.  This is even true, in most cases, on an Exadata machine.  

Needless to say, since in most cases, this parameter will not achieve desired results, and can often result in deliterious performance of an entire cluster for a particularly complete query, I would always set this value to TRUE, rather than the default!

* SQL statements which "qualify" for parallel processing will either have a 'hint' of parallel processing, or an object with a parallel degree setting of 2 or higher.  It would be better to set parallel_force_local=false at the session level in those rare cases where it can be proven that a given query or user process (such as an ETL load) can benefit from this setting.

Your mileage may vary.  Objects in mirror are closer than they appear.  Always test for every case and condition.

Have a good day!