Document Type | Troubleshooting
Category | Tuning
Applicable Product Version | 7FS02
Document Number | TTUTS003
Issue
In the case of a Group by operation performed in parallel, there are cases where the optimizer creates a plan that executes a double Group by for optimization.
SQL> SELECT /*+ PARALLEL(sales 8) pt_param(_OPT_PGROUPBY_PUSH_RATIO 0) */ region, SUM(amount) FROM sales GROUP BY region; 2 3 4 5 6 REGION SUM(AMOUNT) ---------- ----------- WEST 500000000 EAST 200000000 NORTH 200000000 SOUTH 300000000 4 rows selected. SQL ID: a3gt3t9j6bvvc Child number: 99156 Plan hash value: 149695669 Execution Plan -------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 PE MANAGER (Cost:0, %%CPU:0, Rows:1) 2 PE SEND QC (RANDOM) (Cost:0, %%CPU:0, Rows:1) 3 GROUP BY (HASH) (Cost:22, %%CPU:0, Rows:1) 4 PE RECV (Cost:0, %%CPU:0, Rows:1) 5 PE SEND (HASH) (Cost:0, %%CPU:0, Rows:1) 6 PE BLOCK ITERATOR (Cost:22, %%CPU:0, Rows:1) 7 TABLE ACCESS (FULL): SALES (Cost:22, %%CPU:0, Rows:1) NAME VALUE ------------------------------ ---------- db block gets 32 consistent gets 351 physical reads 0 redo size 0 sorts (disk) 0 sorts (memory) 0 rows processed 4 SQL> SELECT /*+ PARALLEL(sales 8) pt_param(_OPT_PGROUPBY_PUSH_RATIO 100) */ region, SUM(amount) FROM sales GROUP BY region; 2 3 4 5 6 REGION SUM(AMOUNT) ---------- ----------- EAST 200000000 WEST 500000000 NORTH 200000000 SOUTH 300000000 4 rows selected. SQL ID: dapwawgj6r2a5 Child number: 99162 Plan hash value: 3859239679 Execution Plan -------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 PE MANAGER (Cost:0, %%CPU:0, Rows:4) 2 PE SEND QC (RANDOM) (Cost:0, %%CPU:0, Rows:4) 3 GROUP BY (SORT) (Cost:0, %%CPU:0, Rows:4) 4 PE RECV (Cost:0, %%CPU:0, Rows:4) 5 PE SEND (HASH) (Cost:0, %%CPU:0, Rows:4) 6 GROUP BY (HASH) (Cost:299, %%CPU:2, Rows:4) 7 PE BLOCK ITERATOR (Cost:291, %%CPU:0, Rows:202153) 8 TABLE ACCESS (FULL): SALES (Cost:291, %%CPU:0, Rows:202153) Note -------------------------------------------------------------------------------------------------------------------------------------------------------------- 8 - dynamic sampling used for this table (95 blocks) NAME VALUE ------------------------------ ---------- db block gets 458 consistent gets 450 physical reads 0 redo size 0 sorts (disk) 0 sorts (memory) 4 rows processed 4
Cause
If the optimizer judges that the row reduction ratio due to GROUP BY execution is less than or equal to the
_OPT_PGROUPBY_PUSH_RATIO percentage, it induces a double GROUP BY. Since the default value is set to 100, a double GROUP BY always occurs.Solutions
OPT_PARAM(_OPT_PGROUPBY_PUSH_RATIO 0) hint can be used to control and optimize parallel Group By processing to resolve this.Note
In Oracle, this is controlled by the PX_GROUP_BY_PUSH_DOWN parameter.
When executing parallel queries, whether the GROUP BY operation is performed first on each parallel process (Slave) (push down) can be controlled by the _OPT_PGROUPBY_PUSH_RATIO value.
_OPT_PGROUPBY_PUSH_RATIO=100โ Each slave performs GROUP BY first (Double GROUP BY occurs)
_OPT_PGROUPBY_PUSH_RATIO=0โ Perform GROUP BY after merging all data
Generally, Parallel queries involve large volumes of data, so GROUP BY can be efficient. The higher the predicted number of groups, the less push down is performed; the fewer the groups, the more push down is performed.
However, if the first GROUP BY stage does not sufficiently reduce the number of records, set the
_OPT_PGROUPBY_PUSH_RATIO parameter value lower or set it to 0 if not used, so that processing is done with a single GROUP BY.