이번 글에서 NL 조인의 비용 계산에 대해 살펴본 후 optimizer_index_caching, optimizer_index_cost_adj 파라미터가 NL 조인 비용 계산에 미치는 영향에 대해 살펴보겠습니다.
준비
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1) AS SELECT ROWNUM FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 (c1, c2, c3) AS
SELECT ROWNUM, CEIL (ROWNUM / 100), NVL (NULLIF (MOD (ROWNUM, 100), 0), 100) FROM XMLTABLE ('1 to 10000');
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t2_x2 ON t2 (c2);
CREATE INDEX t2_x3 ON t2 (c3);
아래는 *_TAB_COLUMNS 뷰에서 t1, t2 테이블 칼럼의 NDV를 조회한 결과입니다. t1 테이블의 c1 칼럼과 t2 테이블의 c1, c2, c3 칼럼의 조인 선택도(selectivity)는 각각 1, 0.01, 0.01입니다.
-- 2
SELECT table_name, column_name, num_distinct
FROM user_tab_columns
WHERE table_name IN ('T1', 'T2');
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ----------- ------------
T1 C1 10000
T2 C1 10000 -- 1.00 = 10000 / 10000
T2 C2 100 -- 0.01 = 100 / 10000
T2 C3 100 -- 0.01 = 100 / 10000
4 rows selected.
아래는 *_INDEXES 뷰에서 t2 테이블의 인덱스를 조회한 결과입니다. blevel은 루트 블록을 제외한 B*-Tree 인덱스의 깊이, avg_leaf_blocks_per_key 값은 고유한 값이 가리키는 평균 리프 블록 수, avg_data_blocks_per_key 값은 고유한 값이 가리키는 평균 테이블 블록 수를 나타냅니다.
-- 3
SELECT index_name, blevel, avg_leaf_blocks_per_key, avg_data_blocks_per_key
FROM user_indexes
WHERE table_name = 'T2';
INDEX_NAME BLEVEL AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ------ ----------------------- -----------------------
T2_X1 1 1 1
T2_X2 1 1 1
T2_X3 1 1 21
3 rows selected.
기본 비용 계산 공식
아래는 NL 조인의 기본 비용 계산 공식입니다.
NL 조인 비용 = 아우터 IO 비용 + (이너 IO 비용 * 아우터 로우 수) + 전체 CPU 비용
아래 예제는 t1 테이블의 c1 칼럼과 t2 테이블의 c1 칼럼을 조인합니다. 아우터 IO 비용은 단일 테이블의 IO 비용와 동일하게 계산되지만 이너 IO 비용은 단일 인덱스의 IO 비용과 다른 방식으로 계산됩니다. 이너 IO 비용에서 인덱스 IO 비용은 blevel + avg_leaf_blocks_per_key - 1 값, 테이블 IO 비용은 avg_data_blocks_per_key 값을 사용합니다. NL 조인 비용의 기본 비용 계산 공식으로 비용을 계산하면 비용이 20,009(= 7 + (2 * 10,000) + 2)으로 계산됩니다.
-- 4: 20009 = 7 + (2 * 10000) + 2
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 20009 (1)|
| 1 | NESTED LOOPS | | 10000 | 20009 (1)|
| 2 | NESTED LOOPS | | 10000 | 20009 (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)| -- 7
|* 4 | INDEX RANGE SCAN | T2_X1 | 1 | 1 (0)| -- 1 = blevel + avg_leaf_blocks_per_key - 1
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2 (0)| -- 1 = avg_data_blocks_per_key
-------------------------------------------------------------------
아래는 앞선 예제의 실행계획이 저장된 PLAN_TABLE 테이블을 조회한 결과입니다. 전체 비용 20,009 중 IO 비용이 20,007, CPU 비용이 2인 것을 확인할 수 있습니다. CPU 비용 계산은 이 글의 주제에서 벗어나므로 무시해도 무방합니다.
-- 5
SELECT id, operation, options, object_name, cost, io_cost, cpu_cost
FROM plan_table;
ID OPERATION OPTIONS OBJECT_NAME COST IO_COST CPU_COST
-- ---------------- -------------- ----------- ----- ------- ---------
0 SELECT STATEMENT 20009 20007 156471229
1 NESTED LOOPS 20009 20007 156471229
2 NESTED LOOPS 20009 20007 156471229 -- 1.94 = 156471229 / (6710.30 * 12 * 1000)
3 TABLE ACCESS FULL T1 7 7 1642429 -- 0.02
4 INDEX RANGE SCAN T2_X1 1 1 8171 -- 0.00
5 TABLE ACCESS BY INDEX ROWID T2 2 2 15483 -- 0.00
6 rows selected.
조인 선택도를 반영한 비용 계산 공식
아래 예제는 t1 테이블의 c1 칼럼과 t2 테이블의 c2 칼럼을 조인합니다. 아우터 IO 비용과 이너 IO 비용이 앞선 예제와 동일하지만 NL 조인의 비용이 앞선 예제의 비용인 20,007보다 작은 10,111로 계산됩니다.
-- 6: 10111 != 7 + (2 * 10000) + ?
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B) */
*
FROM t1 a, t2 b
WHERE b.c2 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 10111 (1)|
| 1 | NESTED LOOPS | | 10000 | 10111 (1)|
| 2 | NESTED LOOPS | | 1000K| 10111 (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)| -- 7
|* 4 | INDEX RANGE SCAN | T2_X2 | 100 | 1 (0)| -- 1
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2 (0)| -- 1
-------------------------------------------------------------------
아래는 V$SYSTEM_FIX_CONTROL 뷰에서 3120429 Fix Control을 조회한 결과입니다. 설명 중 join key sparsity는 조인 선택도(= 이너 조인 키의 NDV / 아우터 조인 키의 NDV)를 의미합니다. 아우터 조인 키의 NDV가 이너 조인 키의 NDV보다 크면 아우터 로우 중 일부 로우(= 아우터 로우 * 조인 선택도)만 이너 테이블을 액세스하고 나머지 로우는 이너 인덱스에서 필터링될 가능성이 높습니다. 이를 NL 조인의 비용 계산에 적용하기 위해 10.1.0.3 버전부터 조인 선택도가 1보다 작은 경우 이너 테이블 IO 비용에 조인 선택도를 곱하여 이너 테이블의 IO 비용을 감소시킵니다.
-- 7
SELECT bugno, value, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 3120429;
BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
------- ----- --------------------------------------------------------------- ------------------------
3120429 1 account for join key sparsity in computing NL index access cost 10.1.0.3
1 row selected.
아래는 조인 선택도를 반영한 NL 조인의 비용 계산 공식입니다. 조인 선택도는 이너 조인 키의 NDV / 아우터 조인 키의 NDV로 계산할 수 있습니다.
NL 조인 비용 = 아우터 IO 비용 + (이너 인덱스 IO 비용 *아우터 로우 수) + (이너 테이블 IO 비용 * 아우터 로우 수 * MIN (조인 선택도, 1)) + 전체 CPU 비용
조인 선택도를 반영한 NL 조인의 비용 계산 공식으로 비용을 계산하면 비용이 10,111(= 7 + (1 * 10,000) + (1 * 10,000 * 0.01) + 4)으로 계산됩니다.
-- 8: 10111 = 7 + (1 * 10000) + (1 * 10000 * 0.01) + 4
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B) */
*
FROM t1 a, t2 b
WHERE b.c2 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 10111 (1)|
| 1 | NESTED LOOPS | | 10000 | 10111 (1)|
| 2 | NESTED LOOPS | | 1000K| 10111 (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)| -- 7
|* 4 | INDEX RANGE SCAN | T2_X2 | 100 | 1 (0)| -- 1
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2 (0)| -- 1
-------------------------------------------------------------------
OPT_PARAM 힌트로 3120429 Fix Control을 비활성화하면 비용이 앞선 예제의 비용인 20,007와 유사한 20,014(= 7 + (2 * 10,000) + 7)로 계산됩니다.
-- 9: 20014 = 7 + (2 * 10000) + 7
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B)
OPT_PARAM('_fix_control' '3120429:0') */
*
FROM t1 a, t2 b
WHERE b.c2 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 20014 (1)|
| 1 | NESTED LOOPS | | 10000 | 20014 (1)|
| 2 | NESTED LOOPS | | 1000K| 20014 (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)| -- 7
|* 4 | INDEX RANGE SCAN | T2_X2 | 100 | 1 (0)| -- 1
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2 (0)| -- 1
-------------------------------------------------------------------
아래 예제는 t1 테이블의 c1 칼럼과 t2 테이블의 c3 칼럼을 조인합니다. 이너 테이블 IO 비용이 앞선 예제의 1보다 큰 21이므로 비용이 앞선 예제의 비용인 10,111보다 큰 12,111(= 7 + (1 * 10,000) + (21 * 10,000 * 0.01) + 4)으로 계산됩니다.
-- 10: 12111 = 7 + (1 * 10000) + (21 * 10000 * 0.01) + 4
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B) */
*
FROM t1 a, t2 b
WHERE b.c3 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 12111 (1)|
| 1 | NESTED LOOPS | | 10000 | 12111 (1)|
| 2 | NESTED LOOPS | | 1000K| 12111 (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)| -- 7
|* 4 | INDEX RANGE SCAN | T2_X3 | 100 | 1 (0)| -- 1
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 22 (0)| -- 21
-------------------------------------------------------------------
OPT_PARAM 힌트로 3120429 Fix Control을 비활성화하면 비용이 220,031(= 7 + (22 * 10,000) + 24)로 계산됩니다. NL 조인 의 비용 계산 공식에 조인 선택도를 반영함으로써 이너 IO 비용의 증가폭이 감소한 것을 알 수 있습니다.
-- 11: 220031 = 7 + (22 * 10000) + 24
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B)
OPT_PARAM('_fix_control' '3120429:0') */
*
FROM t1 a, t2 b
WHERE b.c3 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 220K (1)|
| 1 | NESTED LOOPS | | 10000 | 220K (1)|
| 2 | NESTED LOOPS | | 1000K| 220K (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)|
|* 4 | INDEX RANGE SCAN | T2_X3 | 100 | 1 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 22 (0)|
-------------------------------------------------------------------
optimizer_index_caching 파라미터
optimizer_index_caching 파라미터는 IN-List Iterator와 NL 조인의 이너 IO의 블록에 대한 캐싱 비율을 설정합니다. 기본값은 0으로 캐싱이 안된 것으로 가정하며 이 값을 높게 설정하면 IN-List Iterator와 NL 조인의 비용이 감소합니다. 아래 예제는 OPT_PARAM 힌트로 optimizer_index_caching 값을 90으로 설정합니다. 이너 IO 비용이 감소하여 비용이 원래 비용인 12,111의 0.17배인 2,110(= 7 + (0 * 10,000) + (21 * 10,000 * 0.01) + 3)로 계산됩니다. 이너 인덱스 IO 비용이 0이 되면 아우터 로우 수만큼의 비용이 감소하므로 NL 조인의 비용이 예상보다 훨씬 낮아질 수 있습니다.
-- 12: 2110 = 7 + (0 * 10000) + (21 * 10000 * 0.01) + 3
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B)
OPT_PARAM('optimizer_index_caching' 90) */
*
FROM t1 a, t2 b
WHERE b.c3 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 2110 (1)| -- 2110 / 12111 = 0.17
| 1 | NESTED LOOPS | | 10000 | 2110 (1)|
| 2 | NESTED LOOPS | | 1000K| 2110 (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)| -- 7
|* 4 | INDEX RANGE SCAN | T2_X3 | 100 | 0 (0)| -- 0
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 21 (0)| -- 21
-------------------------------------------------------------------
optimizer_index_cost_adj 파라미터
optimizer_index_cost_adj 파라미터는 Multiblock IO 비용에 대한 Single Block IO 비용의 비율을 설정합니다. 기본값은 100으로 Multiblock IO 비용과 Single Block IO 비용이 동일한 것으로 가정하며 이 값을 낮게 설정하면 인덱스 IO 비용이 감소합니다. 아래 예제는 OPT_PARAM 힌트로 optimizer_index_cost_adj 값을 25으로 설정합니다. 이너 IO 비용이 감소했지만 조인 선택도 대신 optimizer_index_cost_adj 값이 적용되어 비용이 원래 비용인 12,111의 4.55배인 55,013(= 7 + (22 * 10,000 * 0.25) + 6)로 계산됩니다. optimizer_index_cost_adj 값이 기본값이 아닌 경우 optimizer_index_cost_adj 값이 조인 선택도보다 크면 오히려 비용이 높아지므로 주의가 필요합니다.
-- 13: 55013 = 7 + (22 * 10000 * 0.25) + 6
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B)
OPT_PARAM('optimizer_index_cost_adj' 25) */
*
FROM t1 a, t2 b
WHERE b.c3 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 55013 (1)| -- 55013 / 12111 = 4.55
| 1 | NESTED LOOPS | | 10000 | 55013 (1)|
| 2 | NESTED LOOPS | | 1000K| 55013 (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)|
|* 4 | INDEX RANGE SCAN | T2_X3 | 100 | 1 (0)| -- unused
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 6 (0)| -- unused
-------------------------------------------------------------------
아래 예제는 OPT_PARAM 힌트로 optimizer_index_caching, optimizer_index_cost_adj 파라미터를 각각 90, 25로 설정합니다. optimizer_index_caching 파라미터를 적용한 실행 계획에 optimizer_index_cost_adj 값을 적용되어 비용이 원래 비용인 12,111의 4.34배인 52,513(= 7 + (21 * 10,000 * 0.25) + 6)로 계산됩니다.
-- 14: 52513 = 7 + (21 * 10000 * 0.25) + 6
SELECT /*+ LEADING(A B) USE_NL(B) INDEX(B)
OPT_PARAM('optimizer_index_caching' 90)
OPT_PARAM('optimizer_index_cost_adj' 25) */
*
FROM t1 a, t2 b
WHERE b.c3 = a.c1;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 52513 (1)| -- 52513 / 12111 = 4.34
| 1 | NESTED LOOPS | | 10000 | 52513 (1)|
| 2 | NESTED LOOPS | | 1000K| 52513 (1)|
| 3 | TABLE ACCESS FULL | T1 | 10000 | 7 (0)|
|* 4 | INDEX RANGE SCAN | T2_X3 | 100 | 1 (0)| -- unused
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 (0)| -- unused
-------------------------------------------------------------------
참고