현상
병렬(Parallel)로 수행되는 Group by일 경우 최적화를 위해 이중 Group by를 실행하는 Plan을 만드는 경우가 있습니다.
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
SQL
복사
원인
Optimizer가 GROUP BY 실행으로 인해 줄어드는 row 비율이 _OPT_PGROUPBY_PUSH_RATIO 퍼센트 이하라고 판단하면 이중 GROUP BY를 유도하는 방식인데, 기본값이 100으로 설정되어 있어 항상 이중 GROUP BY가 발생하게 되었습니다.
해결
OPT_PARAM(_OPT_PGROUPBY_PUSH_RATIO 0) 힌트를 이용하여 병렬 Group By 처리 최적화 제어하여 해결할 수 있습니다.
참고
오라클의 경우 PX_GROUP_BY_PUSH_DOWN 파라미터로 조절합니다.
병렬 쿼리 실행 시 GROUP BY 연산을 각 병렬 프로세스(Slave)에서 먼저 수행할지(푸시 다운) 여부를 _OPT_PGROUPBY_PUSH_RATIO 값으로 제어할 수 있습니다.
•
_OPT_PGROUPBY_PUSH_RATIO=100 → 각 슬레이브가 먼저 GROUP BY) 수행 (이중 GROUP BY 발생)
•
_OPT_PGROUPBY_PUSH_RATIO=0 → 모든 데이터 병합 후 GROUP BY 수행
일반적으로 Parallel 쿼리는 대용량이기 때문에 GROUP BY가 효율적일 수 있습니다. 예측 그룹 수가 많을 수록 push down 하지 않고 적을 수록 push down을 진행합니다.
그러나 첫 번째 GROUP BY 단계에서 레코드 수를 충분히 줄이지 못하는 경우에는 _OPT_PGROUPBY_PUSH_RATIO 파라미터 값을 낮추거나, 사용하지 않을 경우 0으로 설정하여 Single GROUP BY로 처리되도록 설정합니다.