반응형
| 뷰 머징과 같은 쿼리변환(Query Transformation)으로 힌트가 적용되지 않는 경우 확인 및 해결 법 |
SQL 수행
-- 테스트 뷰 생성 CREATE VIEW HR.DP_V AS SELECT DP.* FROM(SELECT * FROM HR.DEPARTMENTS UNION ALL SELECT * FROM HR.DEPARTMENTS_TMP ) DP, EMPLOYEES E, LOCATIONS L WHERE DP.DEPARTMENT_ID = E.DEPARTMENT_ID AND DP.LOCATION_ID = L.LOCATION_ID ; -- SQL SELECT E.* FROM HR.EMPLOYEES E, HR.DP_V D WHERE 1=1 AND E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_NAME LIKE 'Shi%' ; --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4050 |00:00:00.01 | 550 | | 1 | NESTED LOOPS | | 1 | 4050 |00:00:00.01 | 550 | | 2 | NESTED LOOPS | | 1 | 90 |00:00:00.01 | 190 | | 3 | NESTED LOOPS | | 1 | 2 |00:00:00.01 | 8 | | 4 | VIEW | | 1 | 2 |00:00:00.01 | 6 | | 5 | UNION-ALL | | 1 | 2 |00:00:00.01 | 6 | | 6 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS | 1 | 1 |00:00:00.01 | 3 | |* 7 | INDEX RANGE SCAN | DEPARTMENTS_IX01 | 1 | 1 |00:00:00.01 | 2 | |* 8 | TABLE ACCESS FULL | DEPARTMENTS_TMP | 1 | 1 |00:00:00.01 | 3 | |* 9 | INDEX UNIQUE SCAN | LOC_ID_PK | 2 | 2 |00:00:00.01 | 2 | | 10 | TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES | 2 | 90 |00:00:00.01 | 182 | |* 11 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 90 |00:00:00.01 | 92 | |* 12 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 90 | 4050 |00:00:00.01 | 360 | ---------------------------------------------------------------------------------------------------------------
조인 순서를 e -> d 로 변경해보자
SELECT /*+ leading(e d) */ E.* FROM HR.EMPLOYEES E, HR.DP_V D WHERE 1=1 AND E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_NAME LIKE 'Shi%' ; -- 힌트가 적용되지 않았다 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4050 |00:00:00.01 | 550 | | 1 | NESTED LOOPS | | 1 | 4050 |00:00:00.01 | 550 | | 2 | NESTED LOOPS | | 1 | 90 |00:00:00.01 | 190 | | 3 | NESTED LOOPS | | 1 | 2 |00:00:00.01 | 8 | | 4 | VIEW | | 1 | 2 |00:00:00.01 | 6 | | 5 | UNION-ALL | | 1 | 2 |00:00:00.01 | 6 | | 6 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS | 1 | 1 |00:00:00.01 | 3 | |* 7 | INDEX RANGE SCAN | DEPARTMENTS_IX01 | 1 | 1 |00:00:00.01 | 2 | |* 8 | TABLE ACCESS FULL | DEPARTMENTS_TMP | 1 | 1 |00:00:00.01 | 3 | |* 9 | INDEX UNIQUE SCAN | LOC_ID_PK | 2 | 2 |00:00:00.01 | 2 | | 10 | TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES | 2 | 90 |00:00:00.01 | 182 | |* 11 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 90 |00:00:00.01 | 92 | |* 12 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 90 | 4050 |00:00:00.01 | 360 | --------------------------------------------------------------------------------------------------------------- -- 원인은 Outline Data MERGE(@"SEL$2" >"SEL$1") 부분에서 확인 가능(쿼리 변환 발생) Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$F5BB74E1 4 - SET$1 / DP@SEL$2 5 - SET$1 6 - SEL$3 / DEPARTMENTS@SEL$3 7 - SEL$3 / DEPARTMENTS@SEL$3 8 - SEL$4 / DEPARTMENTS_TMP@SEL$4 9 - SEL$F5BB74E1 / L@SEL$2 10 - SEL$F5BB74E1 / E@SEL$1 11 - SEL$F5BB74E1 / E@SEL$1 12 - SEL$F5BB74E1 / E@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SEL$4") OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SEL$F5BB74E1") MERGE(@"SEL$2" >"SEL$1") <-- 뷰 머징이 발생 하였다 OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") NO_ACCESS(@"SEL$F5BB74E1" "DP"@"SEL$2") INDEX(@"SEL$F5BB74E1" "L"@"SEL$2" ("LOCATIONS"."LOCATION_ID")) INDEX_RS_ASC(@"SEL$F5BB74E1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$F5BB74E1" "E"@"SEL$1") INDEX(@"SEL$F5BB74E1" "E"@"SEL$2" ("EMPLOYEES"."DEPARTMENT_ID")) LEADING(@"SEL$F5BB74E1" "DP"@"SEL$2" "L"@"SEL$2" "E"@"SEL$1" "E"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "L"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "E"@"SEL$1") USE_NL(@"SEL$F5BB74E1" "E"@"SEL$2") FULL(@"SEL$4" "DEPARTMENTS_TMP"@"SEL$4") INDEX_RS_ASC(@"SEL$3" "DEPARTMENTS"@"SEL$3" ("DEPARTMENTS"."DEPARTMENT_NAME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "DEPARTMENTS"@"SEL$3") END_OUTLINE_DATA */
반응형
no_merge 힌트로 뷰머징이 발생하지 않도록 변경
SELECT /*+ no_merge(d) leading(e d) */ E.* FROM HR.EMPLOYEES E, HR.DP_V D WHERE 1=1 AND E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_NAME LIKE 'Shi%' ; -- 조인 순서 변경 힌트가 잘 적용되었다 ------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4050 |00:00:00.01 | 106 | | | | |* 1 | HASH JOIN | | 1 | 4050 |00:00:00.01 | 106 | 932K| 932K| 1232K (0)| | 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 |00:00:00.01 | 6 | | | | | 3 | VIEW | DP_V | 1 | 90 |00:00:00.01 | 100 | | | | | 4 | NESTED LOOPS | | 1 | 90 |00:00:00.01 | 100 | | | | | 5 | NESTED LOOPS | | 1 | 2 |00:00:00.01 | 8 | | | | | 6 | VIEW | | 1 | 2 |00:00:00.01 | 6 | | | | | 7 | UNION-ALL | | 1 | 2 |00:00:00.01 | 6 | | | | | 8 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS | 1 | 1 |00:00:00.01 | 3 | | | | |* 9 | INDEX RANGE SCAN | DEPARTMENTS_IX01 | 1 | 1 |00:00:00.01 | 2 | | | | |* 10 | TABLE ACCESS FULL | DEPARTMENTS_TMP | 1 | 1 |00:00:00.01 | 3 | | | | |* 11 | INDEX UNIQUE SCAN | LOC_ID_PK | 2 | 2 |00:00:00.01 | 2 | | | | |* 12 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 90 |00:00:00.01 | 92 | | | | -------------------------------------------------------------------------------------------------------------------------------------------
DP_V 뷰 내부의 조인 순서를 변경해 보자(dp->l->e) -> (l->dp->e)
SELECT /*+ no_merge(d) leading(e d) leading(d.l d.dp d.e) */ E.* FROM HR.EMPLOYEES E, HR.DP_V D WHERE 1=1 AND E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_NAME LIKE 'Shi%' ; -- (d.dp)와 같은 글로벌 힌트를 사용해서 뷰 내부 오브젝트에 힌트를 적용할 수 있다 ------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4050 |00:00:00.01 | 105 | | | | |* 1 | HASH JOIN | | 1 | 4050 |00:00:00.01 | 105 | 932K| 932K| 1208K (0)| | 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 |00:00:00.01 | 6 | | | | | 3 | VIEW | DP_V | 1 | 90 |00:00:00.01 | 99 | | | | | 4 | NESTED LOOPS | | 1 | 90 |00:00:00.01 | 99 | | | | |* 5 | HASH JOIN | | 1 | 2 |00:00:00.01 | 7 | 2546K| 2546K| 1485K (0)| | 6 | INDEX FULL SCAN | LOC_ID_PK | 1 | 23 |00:00:00.01 | 1 | | | | | 7 | VIEW | | 1 | 2 |00:00:00.01 | 6 | | | | | 8 | UNION-ALL | | 1 | 2 |00:00:00.01 | 6 | | | | | 9 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS | 1 | 1 |00:00:00.01 | 3 | | | | |* 10 | INDEX RANGE SCAN | DEPARTMENTS_IX01 | 1 | 1 |00:00:00.01 | 2 | | | | |* 11 | TABLE ACCESS FULL | DEPARTMENTS_TMP | 1 | 1 |00:00:00.01 | 3 | | | | |* 12 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 90 |00:00:00.01 | 92 | | | | -------------------------------------------------------------------------------------------------------------------------------------------
뷰(dp_v) 내부 인라인뷰(dp)의 DEPARTMENTS 테이블에 힌트를 적용해보자(index -> table full scan)
SELECT /*+ no_merge(d) leading(e d) leading(d.l d.dp d.e) full(d.dp.DEPARTMENTS) */ E.* FROM HR.EMPLOYEES E, HR.DP_V D WHERE 1=1 AND E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_NAME LIKE 'Shi%' ; -- INDEX RANGE SCAN -> TABLE ACCESS FULL 변경됨 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4050 |00:00:00.01 | 109 | | | | |* 1 | HASH JOIN | | 1 | 4050 |00:00:00.01 | 109 | 932K| 932K| 1232K (0)| | 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 |00:00:00.01 | 6 | | | | | 3 | VIEW | DP_V | 1 | 90 |00:00:00.01 | 103 | | | | | 4 | NESTED LOOPS | | 1 | 90 |00:00:00.01 | 103 | | | | |* 5 | HASH JOIN | | 1 | 2 |00:00:00.01 | 11 | 2546K| 2546K| 1689K (0)| | 6 | INDEX FULL SCAN | LOC_ID_PK | 1 | 23 |00:00:00.01 | 1 | | | | | 7 | VIEW | | 1 | 2 |00:00:00.01 | 10 | | | | | 8 | UNION-ALL | | 1 | 2 |00:00:00.01 | 10 | | | | |* 9 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 1 |00:00:00.01 | 7 | | | | |* 10 | TABLE ACCESS FULL| DEPARTMENTS_TMP | 1 | 1 |00:00:00.01 | 3 | | | | |* 11 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 90 |00:00:00.01 | 92 | | | | -------------------------------------------------------------------------------------------------------------------------
반응형
'DBMS > SQL튜닝' 카테고리의 다른 글
| rollup, grouping sets, cube, grouping 함수 (0) | 2023.07.30 |
|---|---|
| oracle ABS 함수, 절대값을 구하는 함수 (0) | 2023.07.18 |
| oracle hint 위치별 적용여부 테스트 (0) | 2023.07.04 |
| ORDER BY 컬럼명이 가리키는 컬럼은? (0) | 2023.07.03 |
| 펑션 위치별 실행 횟 수 (0) | 2023.07.02 |
댓글