[오수영]덧셈뺄셈도 제일 처음엔 어려웠었다

 

 엑셈에 입사한지도 어느덧 1년이 훌쩍 지나버린 지금 나는 최근에 한가지 느낀 점이 있다.
 
그것은 어떠한 지식일지라도 내가 처음에 접한 것이라면 그것은 마치 절대 부서지지 않을 듯한
거대한 바위와도 같아 보인다는 것이다.
 
만약 우리가 처음 접한 문제를 보고 "이것을 지금 감히 내가 어떻게?" 라고 생각하고 피하거나 포기한다면
그것은 결국 내 의지에 따라 내가 결코 넘을 수 없는 바위가 될 뿐이다.
   
약간 시간을 거슬러 내려가 소꿉장난을 하던 어릴 적 시절로 시간여행을 해보았다.
그곳에는 처음으로 알게 된 한자리 수 덧셈을 가지고 큰 벽에 부딪친 듯 얼굴이 상기되어
손가락을 쥐었다 피었다 하면서 얼굴이 상기된 나의 모습을 볼 수가 있었다.

물론 지금은 나는 한 자릿수 덧셈 정도는 단숨에 대답할 수 있다.
이젠 손가락을 사용하지 않고도 셈을 할 수 있으니 실로 나는 무궁한 발전을 이룬 셈이다. ^^V

하지만 현실로 돌아와 보면 나는 이제 덧셈이 아닌 오라클을 공부하며 많은 새로운 문제에 부딪치고   
다시 덧셈을 하는 어린아이처럼 손가락을 쥐었다 피었다 하고 있다.

 이런 모든 생각들은 불과 몇 달 전 Stored Outline을 공부하면서 느끼게 되었다.
 
 처음으로 Stored Outline을 것을 접하고 흥미가 생겼을 때는 지금으로부터 반년 전쯤이었다.
 하지만 그때 모르는 것 투성이고 심지어는 어디서 어떤 자료를 찾아야 할지도 막막했다
 수많은 수집 끝에 자료를 구하고 그 내용을 수 차례 읽어 봐도 전혀 이해 할 수 없었다
 내가 이해 할 수 없기에 나는 이것이 마치 대단한 고급기술이고 내가 이해하기는 대단히 어렵다는 생각을 했다..

 생각에 여기까지 다다르자. ‘나는 아직은 이것을 이해하기엔 실력이 부족하구나.’ 판단해 버렸고
 아직은 내가 배울 단계가 아닌 것 같다라는 생각을 당연하다는 듯이 받아 들이게 되었다.
 
 ‘지금 모르는 것을 나중에 과연 알게 될까?’ 하는 막연한 두려움은 있었지만 그 두려움을 피하고자 나는 Stored  Outline은 나에게 있어 아주 먼 후에나 이해 할 수 있는 것으로 결론 지어 버렸다.
 
 하지만 피해 다니는 것도 끝은 있는 법이라고 했던가?...
 아이러니 하게도 결국 사내 스터디에서 내가 발표해야 부분이 stored outline이었다.
 이때 참 많은 걱정거리가 생겼다. 그때도 못한 것을 불과 몇 달이 지난 자금은 할 수 있을까? 라는 두려움에 질려
 실습은 배제하고 그냥 이론적으로 설명하고 싶은 마음이 정말 굴뚝 같았다.

 실습을 하면 나 자신에게도 그리고 같은 동료에게도 많은 도움이 될 것 같지만
 당시에는 노력한다고 될 것 같은 분위기도 아니라고 생각했기에 더욱 갈등은 커져만 갔다.

  하지만 그때와 달리 이번에는 기필코 배우리라는 뚜렷한 목적 의식을 가지고 공부를 했다.
 인터넷도 찾고 오라클 서적에 정리된 문서를 찾아가며 하나하나 수행해 보고 경험해 보니
 오랜 시간이 지나서부터 어렴풋하게 묘한 느낌이라는 것이 찾아 왔다.
 그때의 짜릿함은 이루 말할 수 없었고 잘 하면 이것을 내가 할 수도 있겠다는 느낌이 강렬하게 다가 왔다.

 그때부터는 오히려 재미를 느끼기 시작했고 결국 몇 시간 후에 사용법은 터득할 수 있었다. 기분은 최고였고
 내가  해냈다는 생각에 상당히 기분이 들뜨기 까지 했다. .
 하지만 여기서 그치지 않고. 예전에 공부를 해도 전혀 무슨 말인지 알 수 없었던 내용인 Stored Outline을 이용하  여 실행계획을 강제로 바꾸어 적용하고 싶은 용기까지 아니 욕심까지 생겼다.
 이날은 내게 신이 들렸는지 그것 마저 쉽게 내 것으로 소화 할 수 있었다.
 드디어 커다란 바위가 더 이상 바위가 아닌 존재로 다가 오게 된 것이다.
 이날은 정말 나에겐 최고의 날이었다.

 모든 것을 속 시원히 알고 난 후에 예전의 일들을 뒤 돌아 보면 왜 이렇게 간단한 것을 힘들다고 포기 했을까라는
 생각까지 들었다.
 이제 와서 마치 더하기 뺄셈이 더 이상 어렵지 않아 그땐 왜 몰랐지? 하는 것처럼 말이다.

 나는 이때 내가 말한 거대한 바위란 것이 나의 두려움 때문에 생긴 존재가 아닐까라는 생각을 했다.
 솔직히 이 글을 읽은 많은 분들께서 이런 기초적일걸 대단한 것처럼 썼냐며 돌을 던질 수도 있을 것 같아
 약간 두렵다.  >.<

 솔직히 내가 말하는 것은 인터넷에 모두 나와있는 이야기고 조금만 열심히 읽어보면 알 수 있는 이야기인 것 또한
 사실이다. 이런 대단치 않을 수 있는 주제를 가지고 내가 이야기 하는 이유는……
 그 당시에 있어 나에게 이 문제 는 넘볼 수 없을 것 같은 커다란 바위였다는 것이 사실이기 때문이다.

 난 이번 기회를 통해 내 스스로 정하는 한계라는 것이 제대로 해 보지 않았기 때문에 끝을 알 수 없어 느끼는
 두려움 때문이 아닐까? 라는 생각이 들기도 한다.

 그래서 난 종종 어려운 문제가 생겨 쉽게 포기기하고 싶을 때 이런 생각을 하곤 한다..
 덧셈 뺄셈도 제일 처음엔 어려웠었다 라고 말이다.
 

#부록
 아래는 소스는 제가 Stored Outline을 이용한 실행계획을 바꿀 수 있는 테스트 예제입니다.
 

테스트 시나리오 :
   1. oulln을 실행할 수 있는 권한 및 outline을 수정하기 위한 권한을 scott user에게 부여
   2. empno를 pk index로 가진 outemp 테이블 생성, deptno을 pk index가진 outdept 테이블 생성
   3. 두 테이블을 조인하는 sql을 수행 후 플랜 확인 NL join 으로 풀림을 확인한 후 sotred outline에 저장
   4. 이번에는 힌트를 사용하여 hash join을 한 sql을 stored outline에 등록 
   5. outline 강제 수정
   6. 결과 비교

--outlin을 실행할 수 있는 권한 및 수정권한을 scott유저에게 부여한다.

sqlplus '/ as sysdba'
grant connect, resource, create any outline to scott;
grant execute on dbms_outln to scott;
grant execute on dbms_outln_edit to scott;

-- 실제 작업을 할 scott 세션 접속
connect scott/tiger
set line 200

--필요한 테이블 생성 후 인덱스 생성

create table outemp as select * from emp;
alter table outemp add constraint outemp_pk primary key(empno);

create table outdept as select * from dept;
alter table outdept add constraint outdept_pk primary key(deptno);

--통계정보를 생성한다..
exec dbms_stats.gather_table_stats(user,'outemp', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user,'outdept', cascade=>true, no_invalidate=>false);

--NL Join을 수행하는 SQL을 실행하고 실행 계획을 조회하여 본다.
set autotrace traceonly explain
select e.ename from outemp e, outdept d where e.deptno = d.deptno;

-- 의도한대로 실행계획이 수립되어 있는지 확인
 (나중에 SQL은 동일하나 Stored outline을 이용해 Hash Join으로 변경할 것임)
---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    14 |   168 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |            |    14 |   168 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| OUTEMP     |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| OUTDEPT_PK |     1 |     3 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

-- 현재 수행된 SQL을 public outline 생성한다.

set autotrace off

create or replace outline  pub_out2
on
select e.ename from outemp e, outdept d where e.deptno = d.deptno;

-- 해당 SQL에 Hash 힌트를 주어 hash 조인으로 실행계획을 수행하도록 하는 SQL작성 후 플랜 확인
set autotrace traceonly explain
select /*+ use_hash(d) */ e.ename from outemp e, outdept d where e.deptno = d.deptno;

-- 실행계획이 hash join으로 수립된 것을 확인하였다면 이것을  pub_out3에 저장
   이는 추후에 pub_out2의 요약본과 대체하기 위해서 임시적으로 저장해 놓은 것임.

create or replace outline  pub_out3
on
select /*+ use_hash(d) */ e.ename from outemp e, outdept d where e.deptno = d.deptno;

--  이제 대상본과 수정할 내용이 저장되어 있음으로 Stored outline을 수정하기 위하여 outln 유저로 이동

conn outln/outln

            ===================================================================
            -- pub_out3의 요약 본 내용을 pub_out2의 내용으로 덮어 쓰는 과정 수행
            ====================================================================

-- OutLine을 ol$에서 확인. => 아래에는 pub_out2와 pub_out3 두 개가 존재함을 확인.

select ol_name, sql_text, category, hintcount from outln.ol$;

OL_NAME        SQL_TEXT                                    CATEGORY              HINTCOUNT
----------------   ---------------------------------------------------   -----------------------      ---------------------------
PUB_OUT3     select /*+ use_hash(d) */ e.ename from o           DEFAULT                 8
PUB_OUT2     select e.ename from outemp e, outdept d          DEFAULT                 8

--PUB_OUT3의 내용을 PUB_OUT2에 적용하기 함에 있어 힌트 카운트를 맞추기 위해 PUB_OUT2의 HINTCOUNT를PUB_OUT3의 HINTCOUNT로 변경한다.

update outln.ol$ set hintcount = (select hintcount from outln.ol$ where ol_name='PUB_OUT3')
where ol_name='PUB_OUT2';
 

-- PUB_OUT2, PUB_OUT3에 대한 실제 요약 본 확인한다.

select ol_name, hint#, hint_text from ol$hints;
OL_NAME                             HINT# HINT_TEXT
------------------------------ ---------- ----------------------------------------
PUB_OUT2                                1 USE_NL(@"SEL$1" "D"@"SEL$1")
PUB_OUT2                                2 LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
PUB_OUT2                                3 INDEX(@"SEL$1" "D"@"SEL$1" ("OUTDEPT"."DEPTNO"))
PUB_OUT2                                4 FULL(@"SEL$1" "E"@"SEL$1")
PUB_OUT2                                5 OUTLINE_LEAF(@"SEL$1")
PUB_OUT2                                6 ALL_ROWS
PUB_OUT2                                7 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
PUB_OUT2                                8 IGNORE_OPTIM_EMBEDDED_HINTS
PUB_OUT3                                2 LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
PUB_OUT3                                3 FULL(@"SEL$1" "E"@"SEL$1")
PUB_OUT3                                4 INDEX(@"SEL$1" "D"@"SEL$1" ("OUTDEPT"."DEPTNO"))
PUB_OUT3                                5 OUTLINE_LEAF(@"SEL$1")
PUB_OUT3                                6 ALL_ROWS
PUB_OUT3                                7 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
PUB_OUT3                                8 IGNORE_OPTIM_EMBEDDED_HINTS
PUB_OUT3                                1 USE_HASH(@"SEL$1" "E"@"SEL$1")

-- NL 실행계획 요약본은 이제 더 이상 필요 없음으로  PUB_OUT2의 힌트들은 삭제
delete from ol$hints where ol_name = 'PUB_OUT2';

-- PUB_OUT2의 힌트를 삭제 후 PUB_OUT3번의 힌트들이 PUB_OUT2의 힌트가 되도록 OL_NAME을 바꿔 줌.

update ol$hints set ol_name = 'PUB_OUT2' where ol_name='PUB_OUT3';

-- ol$nodes뷰를 확인하여 봄 2개에 대한 내용이 있으나 더 이상 PUB_OUT2의 내용은 필요 없다..

select * from ol$nodes;
OL_NAME                        CATEGORY                          NODE_ID  PARENT_ID  NODE_TYPE NODE_TEXTLEN NODE_TEXTOFF NODE_NAME
------------------------------ ------------------------------ ---------- ---------- ---------- ------------ ------------ ---------------------------------
PUB_OUT2                       DEFAULT                                 1          0          3        65               1 SEL$1
PUB_OUT3                       DEFAULT                                 1          0          3        84               1 SEL$1

--따라서 PUB_OUT2는 삭제한다.
delete from ol$nodes where ol_name ='PUB_OUT2';

-- PUB_OUT2의 힌트를 삭제 후 PUB_OUT3번의 힌트들을 PUB_OUT2의 힌트가 되도록 OL_NAME을 바꿔 버림

update ol$nodes set ol_name = 'PUB_OUT2' where ol_name ='PUB_OUT3' ;

commit;

---확인

-- 다시 scott user로 복귀

conn scott/tiger

alter session set use_stored_outlines=true;

set autotrace traceonly explain

select e.ename from outemp e, outdept d where e.deptno = d.deptno;

-- 같은 SQL문장이지만 실행계획은 Hash Join 으로 수행됨을 확인 가능함.
---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    14 |   168 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |            |    14 |   168 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | OUTDEPT_PK |     4 |    12 |     1   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| OUTEMP     |    14 |   126 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("E"."DEPTNO" IS NOT NULL)

Note
-----
   - outline "PUB_OUT2" used for this statement

 마지막으로 정리를 하면
 위와 같은 작업들은 SQL들이 프로그램안에 있고 소스 수정이 불가능하여 SQL을 수정할 수 없는 상황에서
Hint를 사용할 경우 성능 개선이 가능하다면 outline을 위와 같이 적용을 하여 사용하면 매우 유용하게 사용 할 수 있습니다.