NO Costing in CBO

CBO(Cost Based Optimizer) 라고 하면 DBMS 종류를 막론하고 비용이 가장 낮은 실행계획을 선택하는것이라 할수있다.
보통 CBO 에서 문제가 되는것은 (스키마통계정보 or 시스템통계)의 부재나 부족으로 인한 잘못된 Cost를 계산해서 잘못된 실행계획을 선택하는것 이라고 볼수있다.
하지만 항상 그런것은 아니다.
CBO 가 높은 Cost 를 선택해서 끔찍한 일을 저지를 수도 있다는 것이다.
어떤경우에 No Costing 이 발생하는지 아래를 참조하자.

1.환경 :
버젼 : 10gR2(10.2.0.3)
Optimizer mode :all_rows
계정 : scott/tiger

2.임시 부서 테이블 생성
CREATE TABLE TEMP_DEPT AS SELECT * FROM DEPT; --> DEPT 임시테이블 생성

3.통계정보 생성
EXEC dbms_stats.gather_table_stats(user,'TEMP_DEPT',cascade=>true);
EXEC dbms_stats.gather_table_stats(user,'EMP',cascade=>true); --> EMP 는 그대로 사용.

4. Hash join Cost

SQL> explain plan for
2 select /*+ USE_HASH(d e) */ e.ename, d.dname
3 from emp e , temp_dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';

Explained.

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 5 --> cost 가 5이다.
* 1 HASH JOIN 5 90 5
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
3 TABLE ACCESS FULL EMP 14 98 2
--------------------------------------------------------------------

4. NL join Cost

SQL> explain plan for
2 select /*+ USE_NL(d e) */ e.ename, d.dname
3 from emp e , dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';

Explained.

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 4 --> cost 가 4이다.
1 NESTED LOOPS 5 90 4
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
* 3 TABLE ACCESS FULL EMP 5 35 2
--------------------------------------------------------------------

-- NL 조인이 COST 가 더작은걸 알수 있다.

5.CBO 는 과연 어떤 조인을 선택 할것인가?

SQL> explain plan for
2 select /*+ ALL_ROWS */ e.ename, d.dname
3 from emp e , dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 5 --> COST 가 높은 HASH 조인선택
* 1 HASH JOIN 5 90 5
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
3 TABLE ACCESS FULL EMP 14 98 2
--------------------------------------------------------------------

개인적인 생각으로 all_rows 에서는 비용이 높더라도 NL 조인시 FULL SCAN 을 반복하는것을 피하는 어떤 rule 이 있는것 같다.

6.결론:
FIRST_ROWS 나 FIRST_ROWS(1) 로 힌트를 사용하면 NL 조인으로 풀리는 것이 관찰 되었다.
그러나 FIRST_ROWS 시리즈는 완전한 Cost Base 라고 볼수 없고 NL 조인을 선호하는 Rule 이
포함될수 밖에 없다.
10g Default 로 all_rows 이며 부분범위 Rule 이 적용이 배제된 완전한 CBO 모드인 ALL_ROWS 에서 어처구니 없이 CBO 가 COST 가 높은 JOIN 을 선택하는것을 볼수 있다.
물론 TEMP_DEPT 테이블에 PK 혹은 UK를 만들면 이런현상은 사라진다.
하지만 현실(실제 프로젝트)에서는 이러한 PK 없는 임시작업 테이블들을 많이 사용하고 있다.
이런경우 CBO 가 항상 낮은 COST 를 선택하는 것은 아니므로 임시 테이블이나 global temp table 등을 사용시에는 주의할 필요가 있다.