So I have a hash-partitioned table T1 based on ALL_OBJECTS which I then re-partition and supply different PQ_DISTRIBUTE options to the corresponding CTAS statements. The gather_plan_statistics hint is included just so the x.sql script doesn’t report a message that query is missing gather_plan_statistics hint. For parallel execution gather_plan_statistics is almost meaningless since only query coordinator statistics is reported. The tq_stat.sql script displays the data from V$PQ_TQSTAT plus rows distribution between slaves in a set. Test output of running it on Oracle 11.2.0.2 @ Linux x86-64 is here:

CTAS #1 uses NONE as a target for PQ_DISTRIBUTE. That means data distribution is eliminated and data load to the new table was done by the same slaves that read original table – the fastest method in this case as reported by A-Time column in the plan. No re-distribution means V$PQ_TQSTAT view is of no help.

CTAS #2 uses PARTITION, which is not recommended in my case since there’s data skew. And the statistical data from V$PQ_TQSTAT only confirms it – on the load step there’s a big difference in the number of rows processed by Consumers of the table queue #0 – from 1 to 51%. It was also the slowest query, and that is what usually happens when slaves are unevenly loaded with their work.

CTAS #3 uses RANDOM, which is the best option from the data re-distribution perspective, and it’s also confirmed by the report data.

CTAS #4 uses RANDOM_LOCAL, which is almost identical to the RANDOM in both performance and data distribution between slaves. Although not stated clearly in the documentation, I’ve a feeling that suffix LOCAL is for RAC world and it means that Oracle will try to use RANDOM distribution confined to slaves on the same node, which will eliminate inter-node PX communication. Since I don’t have 11gR2 RAC at the moment, I can’t confirm my guess.