OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.18.0.0
방법 : 오라클 19c leading use_nl 힌트 제어 테스트
오라클 19c 에서 쿼리에 힌트를 사용할 때
leading(테이블 나열) + use_nl(테이블 모두 나열) 방식과
leading(테이블 나열) + use_nl(테이블) + use_nl(테이블) + use_nl(테이블) 방식에 차이가 있는지 테스트 해봄
샘플 테이블 생성
|
1
2
3
4
5
6
7
|
SQL>
create table emp2 as select * from emp;
create index emp2_ix01 on emp2(empno, ename);
create table emp3 as select * from emp;
create index emp3_ix01 on emp3(empno, ename);
create table emp4 as select * from emp;
create index emp4_ix01 on emp4(empno, ename);
|
leading(테이블 나열) + use_nl(테이블 모두 나열) 방식 쿼리 실행
d->c->c->a 방식으로 조인하게끔 힌트 작성함
|
1
2
3
4
5
6
|
SQL>
select /*+ gather_plan_statistics leading(d c b a) use_nl(c b a) */ *
from emp a, emp2 b, emp3 c, emp4 d
where a.empno=b.empno
and b.empno = c.empno
and c.empno = d.empno;
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 |
| 1 | NESTED LOOPS | | 1 | 1 | 348 | 4 (0)| 00:00:01 | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 1 | 1 | 348 | 4 (0)| 00:00:01 | 0 |00:00:00.01 |
| 3 | NESTED LOOPS | | 1 | 1 | 261 | 4 (0)| 00:00:01 | 0 |00:00:00.01 |
| 4 | NESTED LOOPS | | 1 | 1 | 174 | 3 (0)| 00:00:01 | 0 |00:00:00.01 |
| 5 | TABLE ACCESS FULL | EMP4 | 1 | 1 | 87 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP3 | 0 | 1 | 87 | 1 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 7 | INDEX RANGE SCAN | EMP3_IX01 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | EMP2 | 0 | 1 | 87 | 1 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 9 | INDEX RANGE SCAN | EMP2_IX01 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 10 | INDEX UNIQUE SCAN | PK_EMP | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 |
| 11 | TABLE ACCESS BY INDEX ROWID | EMP | 0 | 1 | 87 | 0 (0)| | 0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("C"."EMPNO"="D"."EMPNO")
9 - access("B"."EMPNO"="C"."EMPNO")
10 - access("A"."EMPNO"="B"."EMPNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------
1 - SEL$1
- leading(d c b a)
6 - SEL$1 / C@SEL$1
- use_nl(c b a)
8 - SEL$1 / B@SEL$1
- use_nl(c b a)
10 - SEL$1 / A@SEL$1
- use_nl(c b a)
|
정상적으로 emp4, emp3, emp2, emp 순으로 조인됨
Hint Report에도 모두 정상이라고 나옴
leading(테이블 나열) + use_nl(테이블 모두 나열) 방식 쿼리 실행2
d->c->c->a 방식으로 조인하게끔 힌트 작성함
where 절을 모두 a.empno = 로 변경 후 다시 확인
|
1
2
3
4
5
6
|
SQL>
select /*+ gather_plan_statistics leading(d c b a) use_nl(c b a) */ *
from emp a, emp2 b, emp3 c, emp4 d
where a.empno=b.empno
and a.empno = c.empno
and a.empno = d.empno;
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 |
| 1 | NESTED LOOPS | | 1 | 1 | 348 | 6 (0)| 00:00:01 | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 1 | 1 | 348 | 6 (0)| 00:00:01 | 0 |00:00:00.01 |
| 3 | NESTED LOOPS | | 1 | 1 | 261 | 6 (0)| 00:00:01 | 0 |00:00:00.01 |
| 4 | NESTED LOOPS | | 1 | 1 | 174 | 4 (0)| 00:00:01 | 0 |00:00:00.01 |
| 5 | TABLE ACCESS FULL | EMP4 | 1 | 1 | 87 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 6 | TABLE ACCESS FULL | EMP3 | 0 | 1 | 87 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 7 | TABLE ACCESS FULL | EMP2 | 0 | 1 | 87 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 8 | INDEX UNIQUE SCAN | PK_EMP | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 |
| 9 | TABLE ACCESS BY INDEX ROWID| EMP | 0 | 1 | 87 | 0 (0)| | 0 |00:00:00.01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("A"."EMPNO"="D"."EMPNO")
filter(("A"."EMPNO"="B"."EMPNO" AND "A"."EMPNO"="C"."EMPNO"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------
1 - SEL$1
- leading(d c b a)
6 - SEL$1 / C@SEL$1
- use_nl(c b a)
7 - SEL$1 / B@SEL$1
- use_nl(c b a)
8 - SEL$1 / A@SEL$1
- use_nl(c b a)
|
where 절에 join을 모두 a 테이블로 하는데 a 테이블을 가장 마지막에 읽어
다른테이블이 모두 full scan 하긴 했지만 leading 와 use_nl이 모두 원하는대로 동작함
Hint Report에도 모두 정상이라고 나옴
leading(테이블 나열) + use_nl(테이블) + use_nl(테이블) + use_nl(테이블) 방식 쿼리 실행
|
1
2
3
4
5
6
|
SQL>
select /*+ gather_plan_statistics leading(d c b a) use_nl(c) use_nl(b) use_nl(a) */ *
from emp a, emp2 b, emp3 c, emp4 d
where a.empno=b.empno
and b.empno = c.empno
and c.empno = d.empno;
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 |
| 1 | NESTED LOOPS | | 1 | 1 | 348 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 1 | 1 | 348 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 3 | NESTED LOOPS | | 1 | 1 | 261 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 4 | NESTED LOOPS | | 1 | 1 | 174 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 5 | TABLE ACCESS FULL | EMP4 | 1 | 1 | 87 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP3 | 0 | 1 | 87 | 0 (0)| | 0 |00:00:00.01 |
|* 7 | INDEX RANGE SCAN | EMP3_IX01 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | EMP2 | 0 | 1 | 87 | 0 (0)| | 0 |00:00:00.01 |
|* 9 | INDEX RANGE SCAN | EMP2_IX01 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 |
|* 10 | INDEX UNIQUE SCAN | PK_EMP | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 |
| 11 | TABLE ACCESS BY INDEX ROWID | EMP | 0 | 1 | 87 | 0 (0)| | 0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("C"."EMPNO"="D"."EMPNO")
9 - access("B"."EMPNO"="C"."EMPNO")
10 - access("A"."EMPNO"="B"."EMPNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------
1 - SEL$1
- leading(d c b a)
6 - SEL$1 / C@SEL$1
- use_nl(c)
8 - SEL$1 / B@SEL$1
- use_nl(b)
10 - SEL$1 / A@SEL$1
- use_nl(a)
|
정상적으로 emp4, emp3, emp2, emp 순으로 조인됨
Hint Report에도 모두 정상이라고 나옴
leading(테이블 나열) + use_nl(테이블) + use_nl(테이블) + use_nl(테이블) 방식 쿼리 실행2
d->c->c->a 방식으로 조인하게끔 힌트 작성함
where 절을 모두 a.empno = 로 변경 후 다시 확인
|
1
2
3
4
5
6
|
SQL>
select /*+ gather_plan_statistics leading(d c b a) use_nl(c) use_nl(b) use_nl(a) */ *
from emp a, emp2 b, emp3 c, emp4 d
where a.empno=b.empno
and a.empno = c.empno
and a.empno = d.empno;
|
플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 |
| 1 | NESTED LOOPS | | 1 | 1 | 348 | 6 (0)| 00:00:01 | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 1 | 1 | 348 | 6 (0)| 00:00:01 | 0 |00:00:00.01 |
| 3 | NESTED LOOPS | | 1 | 1 | 261 | 6 (0)| 00:00:01 | 0 |00:00:00.01 |
| 4 | NESTED LOOPS | | 1 | 1 | 174 | 4 (0)| 00:00:01 | 0 |00:00:00.01 |
| 5 | TABLE ACCESS FULL | EMP4 | 1 | 1 | 87 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 6 | TABLE ACCESS FULL | EMP3 | 0 | 1 | 87 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 7 | TABLE ACCESS FULL | EMP2 | 0 | 1 | 87 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 8 | INDEX UNIQUE SCAN | PK_EMP | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 |
| 9 | TABLE ACCESS BY INDEX ROWID| EMP | 0 | 1 | 87 | 0 (0)| | 0 |00:00:00.01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("A"."EMPNO"="D"."EMPNO")
filter(("A"."EMPNO"="B"."EMPNO" AND "A"."EMPNO"="C"."EMPNO"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------
1 - SEL$1
- leading(d c b a)
6 - SEL$1 / C@SEL$1
- use_nl(c)
7 - SEL$1 / B@SEL$1
- use_nl(b)
8 - SEL$1 / A@SEL$1
- use_nl(a)
|
where 절에 join을 모두 a 테이블로 하는데 a 테이블을 가장 마지막에 읽어
다른테이블이 모두 full scan 하긴 했지만 leading 와 use_nl이 모두 원하는대로 동작함
Hint Report에도 모두 정상이라고 나옴
결론 :
leading(테이블 나열) + use_nl(테이블 모두 나열) 방식과
leading(테이블 나열) + use_nl(테이블) + use_nl(테이블) + use_nl(테이블) 방식 둘다 잘 작동함
하지만 nl 조인이 아닌 다른 방식과 결합해서 사용할 경우에는 2번 방식을 사용해야함
참조 :
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c not in 절에 null 허용 컬럼 관련 테스트 (0) | 2023.09.21 |
|---|---|
| 오라클 19c 병렬 dml 실행계획 확인 테스트 (0) | 2023.03.05 |
| 오라클 19c nl 조인 순서 제어 테스트 (0) | 2023.03.03 |
| 오라클 19c 옵티마이저에 영향을 주는 파라미터 목록 (0) | 2023.01.30 |
| 오라클 19c insert append, append_values 힌트 extent 테스트 (0) | 2022.11.06 |