USE_NL HINT 사용방법
====================

PURPOSE
-------


Explanation
-----------

<의문 사항>

테스트용 테이블인 big_dept와 big_emp는 analyze되지 않았고
optimizer_goal은 session level에서 RULE인 경우
Personal Oracle 7.3.4에서 다음과 같은 네가지 상황으로 테스트한 결과
(1)번을 제외하고 나머지는 HASH JOIN으로 실행계획이 작성되었습니다.

(1) NESTED LOOP
SQL> explain plan for select /*+ ordered use_nl(d) */
2 * from big_emp e,big_dept d where d.deptno = e.deptno;

(2)HASH JOIN
SQL> explain plan for select /*+ ordered use_nl(e) */
2 * from big_emp e,big_dept d where d.deptno = e.deptno;

(3)HASH JOIN
SQL> explain plan for select /*+ use_nl(d) */
2 * from big_emp e,big_dept d where d.deptno = e.deptno;

(4)HASH JOIN
SQL> explain plan for select /*+ use_nl(e) */
2 * from big_emp e,big_dept d where d.deptno = e.deptno;

왜 그럴까 ?



<해답>
use_nl의 hint 와 ordered 가 어떻게 사용되는지는 다음과 같다.
Tuning reference guide 에 의하면 use_nl과 use_merge 는 모두 ordered hint 와 함께
사용되어져야 하며, 오라클은 이 hint 가 inner table 인 경우 사용되어진다고
언급하고 있다.

이를기본으로 하여 trace file 을 통해 검증해 보면 아래와 같다.

hint 의 사용되는 방안은 optimizer 의 mode 가 rule 인 경우와 first_rows 인 경우로
나누어 질수 있습니다.

일반적으로 optimizer mode 를 first_rows 를 사용하면 optimizer 가 cose 계산시
가중치가 다르기 때문에
다른 optimizer mode 와는 execution plan 이 풀리는 방법이 다릅니다.
이는 모든 case 의 cost 를 계산하지 않고 nested loop 에 가중치를 부여하여 비교하기
때문입니다.

(1) Optimizer 가 rule 인 경우 :

4개의 sample query 중 1번째만 hint 에 D table 이 inner table 이고 ordered 를 사용
였으므로 optimizer 는 nested loop 로 풀고 나머지 case 는 모든 case 의 cost 를 계산
해서 가장 적은 비용의 plan 을 이용해 explain plan 을 만들어냅니다.

(2) optimizer_mode=first_rows 인 경우 :

각 cost 의 계산은 ordered 가 있는 경우 그 순서에 의한 query 에 대해서만 cost 가
수집되고 그중 가장 작은 값이
execution plan 으로 사용됩니다.
ordered hint 를 사용하지 않은 경우는 use_nl 을 사용한 table 을 중심으로 한 순서에
의한 query 는 모든 경우의 case 에 대해 cost 가 수집되고 다른 순서는 NL의 cost 만
수집되어 결국 최소값을 무시하고 NL을 선택한다.

각 case 에 대한 trace file 과 상세 설명은 아래에 첨부합니다.

참고 하시기 바랍니다.


박경희(9573)


(1) Optimizer 가 rule 인 경우 :

4개의 sample query 중 1번째만 hint 에 D table 이 inner table 이고 ordered 를 사용
하였으므로
optimizer 는 nested loop 로 풀고 나머지 case 는 모든 case 의 cost 를 계산해서 가장
적은 비용의
plan 을 이용해 explain plan 을 만들어냅니다.


즉 2,3,4 번째의 query 문의 경우 아래의 trace file 처럼 가능한 모든 경우를 계산하고
있습니다.
그래서 계산 결과중 hash join 의 cost 가 가장 적어 이를 반영한 것입니다.
이때 hash_join_enabled=true 이면 hash 가 , false 이면 sort merge 가 적용되었습니다.


<1번sql 문의 trace file>

Join order[1]: BIG_EMP [ E] BIG_DEPT [ D]
Now joining: BIG_DEPT *******
NL Join
Outer table: cost: 18 cdn: 11197 rcz: 41 resp: 18
Inner table: BIG_DEPT
Access path: tsc Resc: 1
Join resc: 11215 Resp: 11215
Join cardinality: 22394 = outer (11197) * inner (228) * sel (8.7719e-03) [flag
=0]
Best NL cost: 11215 resp: 11215
Join result: cost: 11215 cdn: 22394 rcz: 63
Best so far: TABLE#: 0 CST:18 CDN:11197 BYTES: 459077
Best so far: TABLE#: 1 CST:11215 CDN:22394 BYTES: 1410822
Final:
CST: 11215 CDN: 22394 RSC: 11215 RSP: 11215 BYTES: 1410822
QBCCFRW set


(3번의 trace file )
:2,4 번도 모두 이와 유사하게 모든 경우를 계산하고 있습니다.
아래 계산 결과중 hash join 의 cost 가 가장 적어 이를 반영한 것입니다.
이때 hash_join_enabled=true 이면 hash 가 , false 이면 sort merge 가 적용되었습니다.

NL Join
Outer table: cost: 1 cdn: 228 rcz: 22 resp: 1
Inner table: BIG_EMP
Access path: tsc Resc: 18
Join resc: 4105 Resp: 4105
Join cardinality: 22394 = outer (228) * inner (11197) * sel (8.7719e-03) [flag
=0]
Best NL cost: 4105 resp: 4105
SM Join
Outer table:
resc: 1 cdn: 228 rcz: 22 deg: 1 resp: 1
Inner table: BIG_EMP
resc: 18 cdn: 11197 rcz: 41 deg: 1 resp: 18
Sort statistics
Sort width:13 Area size:49152 Degree: 1
Blocks to Sort:4 Row size:35 Cardinality:228
Initial runs:1 Merge passes:1 Cost / pass:5
Total sort cost: 5 Response: 5
Sort statistics
Sort width:13 Area size:49152 Degree: 1
Blocks to Sort:310 Row size:56 Cardinality:11197
Initial runs:7 Merge passes:1 Cost / pass:334
Total sort cost: 644 Response: 644
Merge join cost: 668 resp: 668

HA Join
Outer table:
resc: 1 cdn: 228 rcz: 22 deg: 1 resp: 1
Inner table: BIG_EMP
resc: 18 cdn: 11197 rcz: 41 deg: 1 resp: 18
Hash join -- resp: 19 deg: 1
mem: 64 r: 65 s: 290 c: 2
Join result: cost: 54 cdn: 22394 rcz: 63
Best so far: TABLE#: 0 CST:1 CDN:228 BYTES:5016
Best so far: TABLE#: 1 CST:54 CDN:22394 BYTES: 1410822

(2) optimizer_mode=first_rows 인 경우 :

각 cost 의 계산은 ordered 가 있는 경우 그 순서에 의한 query 에 대해서만 cost 가
수집되고 그중 가장 작은 값이
execution plan 으로 사용됩니다.
ordered 가 없는 경우 ordered 순서에 의한 query 는 모든 경우의 case 에 대해 cost 가
수집되고 다른 ordered 순서는
NL의 cost 만 수집되어 결국 최소값을 무시하고 NL을 선택한다.

(1번째sql 문의 trace file 분석)
inner table 을 use_nl에 사용하고 ordered 를 사용하였으므로 Join order: BIG_DEPT [ D] BIG_EMP[E] 의 NL만 계산되어진다.

(2번째 sql 문의 trace file)
ordered hint 가 사용되었으므로 Join order[2]: BIG_EMP [ E] BIG_DEPT [ D] 만 계산되고
그중 가장 cost 가 작은
hash join 이 선택 되어짐.

(3번 trace file)
use_nl을 D table 을 사용하였으므로
Join order[1]: BIG_DEPT [ D] BIG_EMP [ E] 인 경우만 NL, SMJ, HASH 를 계산하고
Join order[2]: BIG_EMP [ E] BIG_DEPT [ D] 는 NL만 계산하여 이중 또 nested loop 를 선택

(4번 trace)

3번 trace file 과는 반대로 Join order[2]: BIG_EMP [ E] BIG_DEPT [ D] 경우 모든 case
에 대해 계산하나
NL을 선택합니다.


Example
-------
<참고>
SQL> set autotrace traceonly;
SQL> alter session set optimizer_goal=rule;

Session altered.

SQL> @a.sql

22380 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=11215 Card=22394 Bytes
=1410822)

1 0 NESTED LOOPS (Cost=11215 Card=22394 Bytes=1410822)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=18 Card=11197 Byt
es=459077)

3 1 TABLE ACCESS (FULL) OF 'BIG_DEPT'




Statistics
----------------------------------------------------------
434 recursive calls
22409 db block gets
57825 consistent gets
177 physical reads
0 redo size
1991878 bytes sent via SQL*Net to client
16895 bytes received via SQL*Net from client
1495 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22380 rows processed


SQL> @b.sql

22380 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=207 Card=22394 Bytes=1
410822)

1 0 HASH JOIN (Cost=207 Card=22394 Bytes=1410822)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=18 Card=11197 Byt
es=459077)

3 1 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=228 Bytes
=5016)





Statistics
----------------------------------------------------------
8783 recursive calls
1096 db block gets
5953 consistent gets
6 physical reads
107578 redo size
1991878 bytes sent via SQL*Net to client
16895 bytes received via SQL*Net from client
1495 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22380 rows processed

SQL> @c.sql

22380 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=54 Card=22394 Bytes=14
10822)

1 0 HASH JOIN (Cost=54 Card=22394 Bytes=1410822)
2 1 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=228 Bytes
=5016)

3 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=18 Card=11197 Byt
es=459077)





Statistics
----------------------------------------------------------
0 recursive calls
9 db block gets
1780 consistent gets
5 physical reads
0 redo size
1991878 bytes sent via SQL*Net to client
16887 bytes received via SQL*Net from client
1495 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22380 rows processed

SQL>
SQL> @d.sql

22380 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=54 Card=22394 Bytes=14
10822)

1 0 HASH JOIN (Cost=54 Card=22394 Bytes=1410822)
2 1 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=228 Bytes
=5016)

3 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=18 Card=11197 Byt
es=459077)





Statistics
----------------------------------------------------------
0 recursive calls
9 db block gets
1780 consistent gets
3 physical reads
0 redo size
1991878 bytes sent via SQL*Net to client
16887 bytes received via SQL*Net from client
1495 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22380 rows processed

SQL> spool off

SQL> analyze table big_emp estimate statistics;

Table analyzed.

SQL> analyze table big_dept estimate statistics;

Table analyzed.

SQL> @a.sql

22380 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=11215 Card=22394
Bytes=1410822)

1 0 NESTED LOOPS (Cost=11215 Card=22394 Bytes=1410822)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=18 Card=11197 Byt
es=459077)

3 1 TABLE ACCESS (FULL) OF 'BIG_DEPT'




Statistics
----------------------------------------------------------
119 recursive calls
22407 db block gets
57806 consistent gets
7 physical reads
0 redo size
1991878 bytes sent via SQL*Net to client
16895 bytes received via SQL*Net from client
1495 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22380 rows processed

SQL> @b.sql

22380 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=207 Card=22394 B
ytes=1410822)

1 0 HASH JOIN (Cost=207 Card=22394 Bytes=1410822)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=18 Card=11197 Byt
es=459077)

3 1 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=228 Bytes
=5016)





Statistics
----------------------------------------------------------
10034 recursive calls
1116 db block gets
7282 consistent gets
6 physical reads
108860 redo size
1991878 bytes sent via SQL*Net to client
16895 bytes received via SQL*Net from client
1495 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22380 rows processed

SQL> @c.sql

22380 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=11215 Card=22394
Bytes=1410822)

1 0 NESTED LOOPS (Cost=11215 Card=22394 Bytes=1410822)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=18 Card=11197 Byt
es=459077)

3 1 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=228 Bytes
=5016)





Statistics
----------------------------------------------------------
0 recursive calls
22407 db block gets
57776 consistent gets
4 physical reads
0 redo size
1991878 bytes sent via SQL*Net to client
16887 bytes received via SQL*Net from client
1495 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22380 rows processed

SQL>
SQL> @d.sql

22380 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4105 Card=22394
Bytes=1410822)

1 0 NESTED LOOPS (Cost=4105 Card=22394 Bytes=1410822)
2 1 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=228 Bytes
=5016)

3 1 TABLE ACCESS (FULL) OF 'BIG_EMP'




Statistics
----------------------------------------------------------
0 recursive calls
1598 db block gets
66476 consistent gets
686 physical reads
0 redo size
1991878 bytes sent via SQL*Net to client
16887 bytes received via SQL*Net from client
1495 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22380 rows processed

SQL>
SQL> spool off