Use the EXPLAIN PLAN statement to see the execution plans for parallel queries. The EXPLAIN PLAN output shows optimizer information in the COST, BYTES, and CARDINALITY columns. You can also use the utlxplp.sql script to present the EXPLAIN PLAN output with all relevant parallel information.
There are several ways to optimize the parallel execution of join statements. You can alter system configuration, adjust parameters as discussed earlier in this chapter, or use hints, such as the DISTRIBUTION hint.
The key points when using EXPLAIN PLAN are to:
Verify optimizer selectivity estimates. If the optimizer thinks that only one row is produced from a query, it tends to favor using a nested loop. This could be an indication that the tables are not analyzed or that the optimizer has made an incorrect estimate about the correlation of multiple predicates on the same table. Extended statistics or a hint may be required to provide the optimizer with the correct selectivity or to force the optimizer to use another join method.
Use hash join on low cardinality join keys. If a join key has few distinct values, then a hash join may not be optimal. If the number of distinct values is less than the degree of parallelism (DOP), then some parallel query servers may be unable to work on the particular query.
Consider data skew. If a join key involves excessive data skew, a hash join may require some parallel query servers to work more than others. Consider using a hint to cause a BROADCAST distribution method if the optimizer did not choose it. The optimizer considers the BROADCAST distribution method only if the OPTIMIZER_FEATURES_ENABLE is set to 9.0.2 or higher. See "V$PQ_TQSTAT" for more information.
The following example illustrates how the optimizer intends to execute a parallel query:
explain plan for SELECT /*+ PARALLEL */ cust_first_name, cust_last_name FROM customers c, sales s WHERE c.cust_id = s.cust_id; ---------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | PX COORDINATOR | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | | 3 | NESTED LOOPS | | | 4 | PX BLOCK ITERATOR | | | 5 | TABLE ACCESS FULL | CUSTOMERS | | 6 | PARTITION RANGE ALL | | | 7 | BITMAP CONVERSION TO ROWIDS| | | 8 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | ---------------------------------------------------------- Note ----- - Computed Degree of Parallelism is 2 - Degree of Parallelism of 2 is derived from scan of object SH.CUSTOMERS