오라클도 역사의 저편으로 사라지나?
HDB, NDB 등의 DBMS 는 아직까지 일부 시스템에서 사용하고 있으나 대세는 이미 RDB 로 넘어갔다.
RDBMS 는 90 대부터 약 20년간 꾸준히 사용되어 왔다. 하지만 오라클 같은 RDBMS 도 HDB 나 NDB 처럼 역사의 저편으로 갈날이 얼마 남지 않은것 같다. 미래에는 SQL 을 사용하며 오라클과 같은 RDBMS 를 사용하는것이 NDB 나 HDB 를 사용했던 것 처럼 구식이 되어버릴 것이다.

근거가 있냐고?
물론 있다. 아래의 2가지가 이미 실현되고 있기 때문이다. 필자는 아래의 2가지에 대해서는 전문가도 아니고 잘 알지도 못하지만 RDBMS 관련 일을 하는 사람으로서 이런 것들은 오라클과 같은 RDBMS 의 입장에서는 아주 위협적이라고 생각한다.

1. NoSQL 운동 
   SQL 과 DBMS 를 사용하지 않는 운동이다. 허무 맹랑해 보이지만 이미 많은 회사들이 오랫동안 웹 2.0 개발자들이 애용해 온 오픈소스 MySQL을 버리고 NoSQL을 대안으로 선택했는데, NoSQL의 장점은 그냥 지나치기에는 매우 우수하기 때문 이라고 한다. 더이상 RDBMS 를 사용하는것이 아니라 데이터 스토어 엔진인 카산드라(Cassandra) 를 이용하여 새로운 검색 기능을  개발 했다고 한다. 카산드라는 0.12ms 만에 50GB에 이르는 데이터 스토어를 디스크에 기록할 수 있는데, 이는 MySQL보다 2,500배 빠른 것이라 한다.


상세한 관련기사는 아래의 링크에서 확인 할수 있다.
출처 : "SQL 반대?" 부상하는 반 데이터베이스 운동


2. 구글의 Fusion Tables
    Fusion Tables 기능의 핵심은  데이터 스페이스 기술이다. 이 기술은 이른바 "바벨탑 문제" 를 해결 하였는데 바벨탑 문제란 서로 다른 포맷과 형식으로 저장된 데이터에 대한 액세스를 제공하고 인덱스를 생성하는 것을 말한다. RDBMS 에는 이런 기능이 없다.

데이터 스페이스 기술을 이용해 전통적인 2차원 데이터베이스 테이블에 3차원 좌표를 추가해 제품 리뷰나 블로그 포스트, 트위터 메시지 등의 요소를 수용할 수 있어야 하고, 여기에 실시간 업데이트라는 4차원 좌표도 구현해야 한다.

기술 분석가인 스테판 아놀드에 따르면  “이제 우리에게 4차원 공간인 하이퍼큐브가 있고, 이 공간에서 새로운 제품과 시장을 창출할 수 있는 새로운 종류의 쿼리를 수행할 수 있다”며, “IBM이나 오라클, 마이크로소프트에겐 최고의 악몽이 시작되는 것이다. 구글은 데이터 스페이스를 자동으로 구축하고 새로운 종류의 쿼리를 도입할 계획”이라고 덧붙였다.
 
Fusion Tables 관련기사는 아래의 링크에서 확인해볼수 있다.
출처 : 구글, 클라우드 기반 신개념 데이터베이스 테스트 중


문제는 상업화및 활성화의 시기 라는것
프로젝트를 하면서 오라클 같은 RDBMS를 사용하고 SQL을 작성 하는것이 당연하다고 생각 할것이다.하지만 미래에는 이러한 생각이 달라질수도 있다. 위의 것들이 상업화및 대중화에 성공한다면 말이다. 하지만 최소한 5~10년간은 위의 일들을 걱정할필요는 없는듯 하다. 오라클의 경우 상업화에 성공하는데만 약 20년 가까이 걸렸기 때문이다. 아무리 구글이라 할지라도 2~3년 내에 DBMS 시장에서 주류에 편입 되진 못할것이다.

여러분들은 위의 2가지에 대해서  어떻게 생각하는가?
RDBMS 에게 위협적인가? 아니면 의례적인 Anti 운동으로 보는가?

'Oracle > News' 카테고리의 다른 글

볼륨매니저는 역사 속으로  (12) 2009.09.30
Science of DataBase 1주년  (22) 2009.07.02
오라클 공룡기업으로 거듭나는가?  (0) 2009.04.21
Posted by extremedb
,

문제는 수동이라는 점
예전에 필자가 Full Table Scan 의 비밀 이라는 글에서 _serial_direct_read 파라미터를 true 로 수정하여 Full Table Scan의 성능을 빠르게 한것을 기억하는가? 이것은 Full Table Scan시에 Disk 에서 데이터를 Scan 하여 SGA의 Buffer Cache에 적재한 후에 Logical Reads로 Fetch 를 하게되므로 성능이 저하될 수 있다. 이때 Disk 에서 Buffer Cache를 거치지 않고 바로 Fetch를 하면 성능향상이 가능한데 문제는 10g 까지는 항상 수동으로 파라미터를 수정하여야 한다는 것이다.

11g 부터 자동으로 바뀌어..
또한 테이블의 Size 가 중간크기인 경우 Direct I/O 를 하는것이 좋은지  Buffer Cache I/O 를 하는것이 좋은지 테스트를 해야만 한다. 하지만 Oracle 11g 부터 Direct I/O 를 수동으로 Control 할 필요가 없게 되었다. 11g 부터는 CBO(혹은 Cost Based 분석모듈)가 모든것을 알아서 한다. 이기능은 공식적으로 Cost Based Analysis of Direct I/O Access 라고 불린다.

2010 년 부터는 Oracle 11g 가 대세 라는데... 
세계적인 전문가들도 용어는 물론이며 이러한 기능의 허와 실을 모르고 있다. 전세계에서 손에 꼽는 사람들 까지도 ... 이번기회에 확실히 용어와 개념을 잡아서 선구자가 되길 바란다. 2010 년 부터는 Oracle 11g로 가는것이 대세가 될것이며 누구나 이기능을 사용하게 될것이기 때문이다.
그럼이제 11g 에서 Direct I/O가 어떻게 수행되는지 알아보자.

Oracle 11g 에서 Direct I/O 을 수행하기 위한 일반적인 SQL 의 수행과정은 아래와 같다.

1. SQL을 실행한다.

2. 옵티마이져가 DATA 를 FETCH 하기 위하여 최종 PLAN 을 만든다.

3. Direct I/O 의 후보인지 판별한다.
   예를 들면 full table scan 인가? , 테이블의 블럭사이즈가 _db_block_buffers 의 10% 보다 큰가? 등을 만족한다면 후보 이다.
   참고로 _small_table_threshold 파라미터는 아무상관도 없다. 값을 줄이거나 증가시켜서 테스트 해보기 바란다.

4. Direct I/O 의 후보라면 Cost Based 분석 모듈을 call 하여 Buffer Cache 를 엑세스 시의 Cost 와 Direct I/O 시의 Cost 를 비교하여 비용이 낮은것으로 선택한다. 4번의 로직은 중요하므로 아래의 Cost Based 분석 모듈에서 자세히 설명된다.

5. 4번의 비교에 의하여 Direct I/O를 한다고 판단되었다면 해당 테이블의 Commit 되지 않은 데이터가 Buffer Cache 에 있는경우 데이터를 Disk 로 내려쓴다.

6. 데이터를 Fetch 한다. 이때 Direct I/O 일 경우가 아니라면 Buffer Cache 에서 Fetch 하고 Cost Based 분석 모듈을 끝낸다.
   Direct I/O 일 경우 Disk 에서 Fetch 된다.  

7. Direct I/O 일 경우 4~6을 Fetch 단위마다 반복한다. Buffer Cache를 I/O 일 경우는 계속하여 Fetch만 하게된다.

이것이 전부이다. 7번 까지의 로직은 크게 어렵지 않은 것을 알수 있다. 다만 Direct I/O 를 수행할 경우 경우 잘못이해 하는 부분이 있는데 Disk 의 데이터를 PGA 로 내려쓰지 않는다는 것이다. _kdli_sio_pga 파라미터가 Default 로 False 이기 때문이다.

이제 4번에서 소개되었던 가장 중요한 Cost Based 분석 모듈을 소개할 차례다. 

이 모듈이 CBO 내의 모듈인지는 확실치 않다. 만약 아니라면 다양한 통계정보를 사용하여 Cost 를 구하는 최초의 모듈이 될것이다. 거의 CBO 의 기능에 준한다고 할수 있다. 과연 이것이 가능할까? 이점은 필자로서도 의문이다.
RAC의 경우 이모듈의 특징은 각 노드마다 모듈이 존재한다는 점이다. 각 노드마다 Cost 를 구해야 되기 때문이다.

401. SQL 이 Buffer 에 CACHING 하기 좋은 후보인지 알아본다.
     예를 들면 Buffer CACHE SIZE 가 충분한가? 자주 엑세스 되는가? 적어도 작은 부분이 CACHE 에 있는가?
     (자주 엑세스 되지만 CACHE 에 없을수 있다)

402. Buffer 에 CACHING 하기 좋은 후보라면 Cost Based 분석 모듈을 끝낸다. 이후로 Buffer Cache를 I/O 하게된다.

403. Direct I/O 가 NETWORK I/O 를 증가 시킨다면 Cost Based 분석 모듈을 끝낸다. 이후로 Buffer Cache를 I/O 하게된다.
     왜냐하면 Direct I/O를 해봐야 Fetch Call에 의해서 수행속도가 매우 저하되므로 Direct I/O를 할필요가 없는것이다.
     다행히 Insert~Select, 혹은 CTAS(Create Table as Select), Delete, Update 등은 NETWORK I/O 를 증가시키지
     않으므로 Direct I/O로 동작할 것이다. 이부분을 테스트 해보라. ROWNUM 을 계속 변경해서 테스트 하다보면 어느시점에
     Direct I/O 가 사라지게 된다. 필자가 Heurastic Rule 이 아닌 Cost Based 분석 모듈임을 확신하게 만든 대목이다.

404. Buffer Cache를 엑세스 하는 Cost가 Direct I/O 의 Cost + Commit 되지않은 데이터를 디스크에 쓰는 Cost 보다 큰지
      비교한다.

405. Buffer Cache 엑세스의 Cost가 크다면 Direct I/O 를 해야 하므로 Commit 되지않은 데이터를 디스크에 쓰고
      Cost Based 분석 모듈을 종료한다. 이때 Commit 된 데이터도 Buffer Cache에 남아 있을수 있는데 이 데이터 또한 Disk 로
      내려쓴다. Cost Based 분석 모듈이 체크포인트를 좋아하는 이유가 이것이다. 체크포인트를 만나면 디스크로 내려쓰므로
      Direct I/O 의 Cost 가 줄어들기 때문이다. 참고로 Log File Switch 가 발생하면 같은 효과를 볼수 없다.
      필자의 기억으로 (9i 혹은 8i 마지막 버젼 부터) Log File Switch 발생시 체크포인트가 발생하지 않을수 있기 떄문이다.

406. Commit 되지않은 데이터를 디스크에 쓰는 작업이 끝났으면 Cost Based 분석 모듈을 종료한다.

407. 만약 404번에서 Buffer Cache를 엑세스 하는 Cost가 비용이 적게든다면 이번에는 Direct I/O 의 Cost와 비교한다.

408. 407번에서 Buffer Cache를 엑세스 하는 Cost가 크다면 Direct I/O 의 후보 FLAG 를 Y 로 한다.
     이작업은 Cost Based 분석 모듈이 종료된 이후에 5~7번에서 FLAG 값을 이용하기 위함이다.

409. 408번에서 Direct I/O 의 후보 FLAG 를 Y 로 했다면 Buffer Cache를 엑세스 하기위한 준비를 하고 분석을 끝낸다.
     이경우는 Direct I/O 가 좋은지 Buffer Cache I/O 가 좋은지 확실히 알수 없기 때문에 일단 Buffer Cache I/O를 하고 다음번
     Fetch 시에 재평가 하게된다.

410. 407번에서 Buffer Cache를 엑세스 하는 Cost가 비용이 적게든다면 Buffer Cache를 엑세스 하기 위한 준비를 하고 분석을
       끝낸다.

Cost Based 분석 모듈의 모든기능을 설명하였다. 문제는 이렇게 구해진 Cost 가 Plan 상이나 10053 Event 의 Trace 상에 반영되지 않는다는 점이다. Plan 상에서는 Direct I/O를 로 수행되건 Buffer Cache I/O 로 수행되건 간에 Cost의 변화가 전혀 없는데 이것은 매우 아쉬운 부분이다.

Cost 가 반영되지 않을것 이라면 따로 보여주던지...
Cost 는 수행시간에 비례하는 개념인데 엑세스 방식이 바뀜에 따라 수행시간이 바뀜에도 불구하고 Cost 는 아무런 변화가 없는 것이다. 그럴것이라면 위에서 구한 Cost 라도 따로 보여 주었으면 하는것이 필자의 바램이다. 그렇지 않다면 Cost 와 현실과의 괴리감이 11g 에서 증폭될 것이기 때문이다. Plan 상이나 10053 Event 의 Trace 상에 변화가 나타나지 않으므로 Direct I/O를 확인 할수 있는 제일 위운 방법은 10046 이벤트 Trace에서 Direct Path Read 이벤트를 확인 하는것이다.
 
이제 Cost를 구하기 위해 참조되는 통계정보는 어떤것이 있는지 알아보자.

아래와 같이 CBO 혹은 CBAM(Cost Based Analysis Module)은 여러가지 통계정보들을 사용한다.

-디스크에 미쳐 쓰지못한 BUFFER CACHE 에 존재하는 데이터의 블럭수
-반드시 엑세스 해야하는 데이터의 BLOCK 수
-해당 테이블의 데이터가 Buffer Cache에 존재하는 데이터의 BLOCK 수
-RAC 의 경우 분산된 INSTANCE 의 DISK 블럭수와 CACHE 의 블럭수
-Direct I/O 시 하나의 블럭을 읽는 속도
-Buffer Cache I/O 시 하나의 블럭을 읽는 속도
-RAC 의 경우 다른 인스탄스의 CACHE 된 BLOCK 과 DISK 에 있는 BLOCK 을 가져오는 속도

지면의 한계상 Cost 를 구하는 공식(Formular)은 생략한다. 그것은 다음 기회에...
지면이 길어지면 도망가는 독자를 많이 보아 왔다.^^    

결론 :
우리는 이제 Oracle11g 의 새기능인 Cost Based Analysis of Direct I/O Access 에 대하여 알아보았으며 Cost Based 분석모듈이 어떻게 동작하는지도 알게 되었다. 한가지더 첨언 하자면 위의 기능이 끝이 아니다 라는 사실을 강조 하고 싶다.

11g 부터 Parallel 힌트를 사용한 Select 문이 Serial 하게 수행될수있다. 기준은 테이블의 블럭수가 _small_table_threshold
파라미터 보다 작아야 한다. 필자는 이기능을 더 좋아한다. 왜냐하면 덩치가 작은 테이블은 사실상 Parallel Query 가 필요없으며 오히려 수행속도가 저하되는 경우를 많이 보아왔기 떄문이다.

기억하자!
11g 부터는 Parallel SQL이 Serial로 수행될수 있으며 Parallel 을 사용하지 않는 Full Table Scan 이 Direct Path Read 가 가능하다는 사실을..

P.S
작년 봄부터 이글을 쓸지 고민하다가 이제서야 올리게 되었다. 위의 글이 난위도가 있기 때문에 쉽게 쓰려고 고민 한 것이다. 어려운 개념이지만 단 한명이라도 이글을 보고 이해한다면 필자의 기쁨이 될것이다.

아래의 link 는 유명한 오라클 Guru 인 Doug Burns의 블로그인데 몇년째 위에서 설명한 사실들을 몰라서 고생하고 있다.
누가 대신 설명해주길 바란다.(필자는 영어가 짧아서....)

http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html
http://oracledoug.com/serendipity/index.php?/archives/1320-Parallel-Query-and-11g-Part-2.html

Posted by extremedb
,

글의 내용이 가슴에 와닫지 않는다는 독자가 있다. 필자의 잘못이다. 독자들을위하여 일부 내용을 댓글에 추가 하였으므로 반드시 확인하기 바란다. (2009.07.17)

대분분 튜닝을 해본 사람이면 USE_CONCAT 힌트를 잘 알고 있다고 생각할 것 이다. 하지만 문제는 얼마나 정확히 아는가가 중요하다. IN 이나 OR 조건이 있는 SQL에 USE_CONCAT 힌트를 사용하면 OR_Expansion(Union All 로 분리되는 쿼리변환) 이 발생한다는 것은 누구나 알것이다. 이것은 개발자들에 대한 일반적인 튜닝 가이드인것은 분명하다. 메뉴얼에는 분명히 이렇게 되어있다.

힌트 정확히 알기
이 힌트는 인자가 2개가 필요한데 튜닝을 전담하는 사람들까지도 이런 사실을 모르고 있으니 큰일이 아닐수 없다. 하지만 정확한 용법을 모르고 SQL을 튜닝을 하는 사람들을 비난할수는 없다. 그어떤 문서에도 USE_CONCAT의 용법이 자세히 나온 것이 없기 때문이다. 이럴경우 다양한 연구및 테스트를 진행 해보는수 밖에 없다. 아니면 SR 이라는 방법이 있기는 하다. 하지만 이러한 경우 SR 의 답변 성공률은 아주 낮다.

그럼 이제부터 용법을 하나씩 살펴보자.
환경 : 오라클 11.1.0.6
인덱스 상황 : employees(manager_id) , departments(department_id)

OR_Expansion 이 발생하지 않는 상황
아래의 SQL 은 departments 테이블에서 department_id 컬럼에 대해서 IN 조건이 있고 employees 테이블에서 manager_id 컬럼에 대해서 IN 조건이 있다. 따라서 USE_CONCAT 힌트를 사용한다면 UNION ALL 로 구분될수 있는 SQL 이 4개(departments 2개, employees 2개)가 나올것이다.

explain plan for
SELECT /*+ QB_NAME(MAIN) */
             e.employee_id, e.first_name, e.last_name,  d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND (     d.department_id IN (:v_dept1, :v_dept2)
           OR e.manager_id IN (:v_manager1, :v_manager2)
          ) ;

---------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    19 |   798 |     6  (17)|
|   1 |  MERGE JOIN                  |             |    19 |   798 |     6  (17)|
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |    27 |   432 |     2   (0)|
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK1 |    27 |       |     1   (0)|
|*  4 |   FILTER                     |             |       |       |            |
|*  5 |    SORT JOIN                 |             |   107 |  2782 |     4  (25)|
|   6 |     TABLE ACCESS FULL        | EMPLOYEE    |   107 |  2782 |     3   (0)|
---------------------------------------------------------------------------------

 
Plan 을 보면 OR_Expansion 이 발생하지 않았는데 옵티마이져는 Union All 로 분리하지 않는것이 가장 비용이 저렴했다는 것을 알수 있다.

그럼이제 USE_CONCAT 힌트를 사용해보자.

explain plan for
SELECT /*+ QB_NAME(MAIN) USE_CONCAT */
       e.employee_id, e.first_name, e.last_name,  d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND (   d.department_id IN (:v_dept1, :v_dept2)
        OR e.manager_id IN (:v_manager1, :v_manager2)
       ) ;


------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |    29 |  1218 |     9  (12)|
|   1 |  CONCATENATION                  |                   |       |       |            |
|   2 |   MERGE JOIN                    |                   |    12 |   504 |     5  (20)|
|   3 |    TABLE ACCESS BY INDEX ROWID  | DEPARTMENT        |    27 |   432 |     2   (0)|
|   4 |     INDEX FULL SCAN             | DEPT_ID_PK1       |    27 |       |     1   (0)|
|*  5 |    SORT JOIN                    |                   |    12 |   312 |     3  (34)|
|   6 |     INLIST ITERATOR             |                   |       |       |            |
|   7 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |    12 |   312 |     2   (0)|
|*  8 |       INDEX RANGE SCAN          | EMP_MANAGER_IX    |    12 |       |     1   (0)|
|   9 |   NESTED LOOPS                  |                   |       |       |            |
|  10 |    NESTED LOOPS                 |                   |    17 |   714 |     4   (0)|
|  11 |     INLIST ITERATOR             |                   |       |       |            |
|  12 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |     2 |    32 |     2   (0)|
|* 13 |       INDEX UNIQUE SCAN         | DEPT_ID_PK1       |     2 |       |     1   (0)|
|* 14 |     INDEX RANGE SCAN            | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|
|* 15 |    TABLE ACCESS BY INDEX ROWID  | EMPLOYEE          |     9 |   234 |     1   (0)|
------------------------------------------------------------------------------------------

 
위의 Plan 을 보면 Id 기준으로 2번에 CONCATENATION 이 발생하였으므로 Union ALL 로 분리가 된것이다.
즉 옵티마이져는 SQL 을 아래와 같이 변형시킨것이다.

SELECT  e.employee_id, e.first_name, e.last_name, e.email, d.department_name
  FROM employee e, department d
 WHERE e.department_id = d.department_id
   AND e.manager_id IN (:v_manager1, :v_manager2)
UNION ALL  
SELECT  e.employee_id, e.first_name, e.last_name, e.email, d.department_name
  FROM employee e, department d
 WHERE e.department_id = d.department_id
   AND d.department_id IN (:v_dept1, :v_dept2)
   AND ( LNNVL(e.manager_id=:v_manager1) AND LNNVL(e.manager_id=:v_manager2) ) ;

Union All 로 분리된 아래쪽의 SQL 에서 LNNVL을 사용한 이유는 윗쪽의 SQL이 이미 e.manager_id IN (:v_manager1, :v_manager2) 조건을 만족하는 데이터에 대하여 SELECT 를 하였으므로 아래쪽에서 또다시 SELECT 되는것을 막기위함이다.

Inlist 에 대해서는 Union All 로 분리되지 않았다.
위의 PLAN 을 자세히 보면 e.manager_id IN (:v_manager1, :v_manager2) 조건에 대해서는 Union All 로 분리되지 않았다. d.department_id IN (:v_dept1, :v_dept2)  조건 또한 마찬가지이다. 하지만 이것은 전통적인 OR_Expansion 변환이 아니다. USE_CONCAT 힌트 대신에 RULE 힌트를 사용할 경우 Plan은 아래와 같다.
 
------------------------------------------------------------
| Id  | Operation                      | Name              |
------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |
|   1 |  CONCATENATION                 |                   |
|   2 |   NESTED LOOPS                 |                   |
|   3 |    NESTED LOOPS                |                   |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |
|*  5 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |
|*  6 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|   7 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|   8 |   NESTED LOOPS                 |                   |
|   9 |    NESTED LOOPS                |                   |
|  10 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |
|* 11 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |
|* 12 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|  13 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|  14 |   NESTED LOOPS                 |                   |
|  15 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|* 16 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|* 17 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |
|* 18 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |
|  19 |   NESTED LOOPS                 |                   |
|  20 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |
|* 21 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |
|* 22 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |
|* 23 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |
------------------------------------------------------------

 
RULE 힌트를 적용한 Plan은 우리가 예상한대로 UNION ALL 로 구분될수 있는 SQL이 4개(departments 2개, employees 2개)가 생성 되었다. 결국 CBO는 Inlist Plan을 사용할수 있는 경우에는 OR_Expansion 변환을 수행하지 않음을 알수 있다. 하지만 이렇게 결말을 내기에는 너무 싱겁다.

PLAN 고정 시키기
CBO 상황에서 조건절에 Inlist 가 있을 경우 항상 OR_Expansion 변환을 수행하지 않게 PLAN을 고정 하려면 어떻게 하면 될까?
그냥 /*+ USE_CONCAT*/ 이렇게만 사용하면 되는 걸까?
위의 질문들을 해결하려면 DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용해서 Outline Data 정보를 보면 된다.
 

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) USE_CONCAT */
            e.employee_id, e.first_name, e.last_name,  d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND (     d.department_id IN (:v_dept1, :v_dept2)
           OR e.manager_id IN (:v_manager1, :v_manager2)
          ) ;

     
위의 SQL 에 대한 PLAN 은 USE_CONCAT 을 사용하였으므로 이미 수행한 SQL의 PLAN 과 동일하므로 생략하고  Outline Data만 보기로 하자.      
 
 Outline Data
-------------
 
  /*+
      ...중간생략
      USE_CONCAT(@"MAIN" 8)
      ...중간생략
  */
 
숫자 8의 의미
오라클이 내부적으로 USE_CONCAT 힌트에 인자 2개를 사용한것을 알수 있다. 첫번째 인자는 쿼리블럭명이고 두번째 인자인 8의 의미는 Inlist 를 사용할수 있는 경우에는 Union All 로 분리하지 말것을 강제하는 힌트이다.


자존심이 허락지 않는다.
여기서 한단계 더 나아가 보자. 이번에는 거꾸로 Inlist 를 사용한 경우에도 무조건 Union All 로 분리되게 할수 있을까?  RULE 힌트를 사용하라고? 그것은 언제 어떤버젼에서 없어질지 알수없는 아주 위험한 힌트이다.
또한 CBO 상황에서 이러한 힌트를 사용한다는 것은 자존심이 허락지 않는다.
아래의 SQL 을 보자.

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) USE_CONCAT(@MAIN 1) */
            e.employee_id, e.first_name, e.last_name,  d.department_name
  FROM employees e, departments d
 WHERE e.department_id = d.department_id
   AND (     d.department_id IN (:v_dept1, :v_dept2)
           OR e.manager_id IN (:v_manager1, :v_manager2)
           ) ;

숫자 1의 의미
USE_CONCAT 힌트에 숫자 1을 사용하였다. 이것이 의미하는 바는 가능한 경우 모두 Union All 로 분리하라는 뜻이다. 이제 Plan 을 보자.

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  CONCATENATION                 |                   |      3 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                 |                   |      0 |00:00:00.01 |       1 |
|   3 |    NESTED LOOPS                |                   |      0 |00:00:00.01 |       1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      0 |00:00:00.01 |       1 |
|*  5 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |      0 |00:00:00.01 |       1 |
|*  6 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      0 |00:00:00.01 |       0 |
|   7 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      0 |00:00:00.01 |       0 |
|   8 |   NESTED LOOPS                 |                   |      0 |00:00:00.01 |       1 |
|   9 |    NESTED LOOPS                |                   |      0 |00:00:00.01 |       1 |
|  10 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      0 |00:00:00.01 |       1 |
|* 11 |      INDEX RANGE SCAN          | EMP_MANAGER_IX    |      0 |00:00:00.01 |       1 |
|* 12 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      0 |00:00:00.01 |       0 |
|  13 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      0 |00:00:00.01 |       0 |
|  14 |   NESTED LOOPS                 |                   |      2 |00:00:00.01 |       6 |
|  15 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      1 |00:00:00.01 |       2 |
|* 16 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      1 |00:00:00.01 |       1 |
|* 17 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |      2 |00:00:00.01 |       4 |
|* 18 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      2 |00:00:00.01 |       2 |
|  19 |   NESTED LOOPS                 |                   |      1 |00:00:00.01 |       4 |
|  20 |    TABLE ACCESS BY INDEX ROWID | DEPARTMENT        |      1 |00:00:00.01 |       2 |
|* 21 |     INDEX UNIQUE SCAN          | DEPT_ID_PK1       |      1 |00:00:00.01 |       1 |
|* 22 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |      1 |00:00:00.01 |       2 |
|* 23 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------

인자를 1로 바꾸자 IN 조건 혹은 OR 조건에 대하여 모두 Union All 로 분리 되었다. 이제 모든 궁금증이 해소 되었다.

결론 :
인자없이 힌트를 USE_CONCAT(@MAIN)으로 사용 한다면 모든경우의 수를 다 고려하여 가장 비용이 적게드는 Plan 을 선택 할것이다. 심지어 USE_CONCAT 힌트를 사용 하였지만 분리되지 않는 경우가 있는데 이것은 힌트를 무시한 것이 아니라 옵티마이져 입장에서 비용계산을 기준으로 가장 저렴한 PLAN 을 선택한것이다. 만약 힌트를 사용하였지만 Union ALL 로 분리가 안되며 이것 때문에 성능이 문제가 된다면 USE_CONCAT 힌트의 숫자 인자(1혹은 8)를 활용하여 적절하게 튜닝할수 있어야 한다.

힌트를 제대로 아는 것이 얼마나 중요한지 가슴에 와 닿아야 할것이다. 생각해보라 의사들이 수술용 칼이나 마취용 주사 같은 것을 규정에 맞게 아주 정밀하고 세밀하게 사용하지 않고 대충 사용한다면 큰일이 날수 도 있을 것이다. 힌트도 마찬가지로 생각해야 한다.  

Posted by extremedb
,

DBMS를 이용하여 프로젝트를 진행하는 사람들에게 영원한 이슈는 무엇일까?
아마도 모델링 또는 튜닝 혹은 데이터 품질 등일 것이다. 그중에서도 DBMS 지식을 가장 많이 요구하면서도 지속적으로 지식을 Upgrade 해야 하는 분야를 꼽으라면 당연히 DBMS 튜닝분야 이다. 모델링이나 데이터 품질에는 DBMS 종류나 버젼과는 상관이 없는 부분이 많지만 튜닝분야는 전혀 다르다. 튜닝은 그특성상 특정 DBMS에 정통해야 하며 특정기능의 사용여부는 프로젝트에서 사용하는 DBMS의 특정 버젼에 종속될수 밖에 없기 떄문이다. 따라서 DBMS의 버젼이 올라가면 공부해야 할 튜닝관련 도서들도 달라져야 한다. 이런 의미에서 이미 Old Fashioned Love Book이 되어버린 Tomas Kyte의 Efective Oracle By Design이나 Jonathan Lewis의 (Practical Oracle 8i)의 명맥을 이을 Oracle 11g의 튜닝서적의 서평을 적어보려한다.

 

사용자 삽입 이미지

얼마나 좋은 책 이기에?
이번에 소개할 책은 스위스 엔지니어인 Christian Antognini가 저술한 Trouble Shooting Oracle Performance 이다. 2008년 11월에 출간 되었으나 한동안 관심이 없다가 2009년 2월에야 이 책을 보게된 계기는 추천사를 쓴 엔지니어 두사람 때문이었다.

오라클을 잘아는 사람이라면 진정한 Guru인 Cary Millsap과  Jonathan Lewis를 잘알것이다. Cary Millsap은 21 세기형 Oracle DBMS 의 튜닝방법론인 Method-R 을 집대성한 사람이다. Jonathan Lewis는 유명한 저서 Cost-Based Oracle: Fundamentals로 널리 알려진 사람이다. 이두사람이 추천한 책이라면 최소한 책값(?)은 하기 때문이다.


스위스 사람이 영어로 책을 써?

미국이나 영국 사람이 아니므로 영어가 이상할 것이라고 생각하는 사람이 있을수 있다. 하지만 필자는 역으로 생각한다. 영어권이 아닌 사람이 영어로 책을 쓸경우 오히려 이해하기가 훨씬 편하다. 철학적인 문구나 이해하기 어려운 문구를 넣는 경우가 극히 드물기 때문이다.



책의 내용을 살펴보자
이 책은 4개의 부분으로 나눌수 있다.

part1 : 기본
여기서는 거시적인 관점에서 문제 발생시 기본적인 접근방법론과 튜닝시 필요한 여러가지의 개념을 다루고 있다.
또한 프로젝트 방법론에 따른 튜닝 방법을 제시하고 있는데 약간은 고전적인 Water Fall 방법론을 따르고 있다.
튜닝 프로젝트에서 이책에서 제시한 대로만 한다면 아주 완벽하게 성공할수 있을것이다.
하지만 이론과 현실의 괴리감은 항상 있는법. 이책의 내용대로 라면 요구사항 분석단계에서 튜닝팀이 이미 존재하고 있어야 한다.

하지만 현실은? 대부분 설계단계(물리모델링시) 혹은 개발단계(개발자가 SQL을 작성하는 단계)에서 튜닝팀이 투입되고 심지어 마지막 통합 Test 단계에서도 종종 투입되기도 한다. 참으로 안타까운 아쉬운 현실이다.
저자가 이야기 하는 또하나의 중요한 점은 문제 발생시 항상 Business 관점에서 접근하라는 것이다.
필자도 이점에 동의 한다. 시스템적으로 접근하는 것은 항상 한계가 있기 마련이다.
Business를 이해하고 문제를 분석한다면 SQL 이 쉽게 이해될 것 이며 튜닝 작업이 더욱 쉬워질것이다.
Metod R 방법론 에서도 이점을 강조하고 있다는 것을 잊지말기 바란다.

part2 : 문제의 발견
저자는 문제의 분석시 반드시 모든구간에서 분석하라고 말하고 있다. 병목구간이 만약 AP 서버이거나 네트웍 구간이라면 DB의 문제가 아니라고 말할수 있다. Loop 로 처리된 SQL이 아니라면...
물론 이렇게 하려면 DB 모니터링 툴이 아닌 APM Tool이 존재 해야한다.

part2에서 필자가 가장 맘에드는 것은 TVD$XTAT 라는 멋진 분석 TOOL 을 제공 하고 있다는 점이다. TKPROF가 주지못하는 정보를 이 TOOL에서 제공한다. 꼭 사용해보기 바란다. 이 Tool 은 GUI Tool이 아니라 Command Line Tool 이다. 오해하지 말기 바란다.

part3 : 옵티마이져
part2 가 문제 발생시 접근 방법이라면 part3는 튜닝시 꼭 필요한 옵티마이져 관련지식이라고 할수 있다.  
일반적인 튜닝책이라면 통계정보의 생성이나 성능관련 파라미터의 설정법등이 대충설명 되어있음에 실망하게 될것이다. 대부분의 경우 통계정보나 파라미터 보다는 SQL 튜닝 테크닉이나 실행계획을 보는 방법등을 상세히 설명하고 있는데 이책에서는 모든것을 언급하고 있다.

통계정보의 경우  dbms_stats 패키지가 11g 에서 달라진 부분까지 상세히 설명하고 있으며 파라미터의 경우 로드맵 까지 제시하고 있다. 특히 chater 4(System and Object Statistics) 와 chapter5(Configuring the Query Optimizer)의 경우 여타의 튜닝책에서 찾아볼수 없는 부분이다.

한가지 오해하지 말아야 할것은  Physical Optimizer(비용을 계산 하여 최적의 엑세스 Path및 조인 Method를 선택하는 것을 담당함) 혹은 Logical Optimizer(Query Transformation 을 담당함)등을 직접적으로 설명한 책이 아니라는 것이다. Physical Optimizer나 Logical Optimizer는 각각 1권의 두터운 책으로 나와도 지면이 부족하다는 것을 알아야 한다.

part4 : 최적화
팔자가 가장 좋아하는 부분이다.
Parsing 을 최소화 하는방법, 데이터 access 및 Join 최적화, Advanced Technic, Physical Design 등을 다루고 있는데 가장 마음에 드는 부분은 Physical Design 부분이다. 이것은 사실상 Modeling 책에서나 나올법한 주제이지만 이책에서는 일반적인 물리 모델링 방법론을 다루는 것이 아니라 다른 Modeling책에서 다루지 않는 부분을 아주 세밀하게 다루고 있다.

예를들면 컬럼순서가 중요한 이유, 데이터 타입이 옵티마이져에 미치는 영향, 테이블의 컬럼이255 개를 넘기지 말아야 할 이유등이 그것이다. 아래의 예제를 보면 데이터 타입의 최적화가 얼마나 중요한지 알수 있다.

SQL> CREATE TABLE t (n1 NUMBER, n2 NUMBER(*,2));

SQL> INSERT INTO t VALUES (1/3, 1/3);

SQL> SELECT * FROM t;


결과:

N1                                                             N2
------------------------------------------ ----
.3333333333333333333333333333333333333333   .33

위에서 데이터 한건을 insert 하고 밑에서 컬럼값을 Byte 로 환산하고 있다.

SQL> SELECT vsize(n1), vsize(n2) FROM t;

결과 :

VSIZE(N1)   VSIZE(N2)
---------- ----------
21               2

컬럼값의 Size가 무려 10배 이상 차이가 난다. 바로 이것이 물리모델링시 Width가 없는 Number형을 쓰지 말아야 할 이유이다.

뭐? 모델러가 튜닝 책을 봐야 한다고?
이러한 물리적 특성은 물리모델링을 고민하는 모델러들이 반드시 알아야 한다. 필자는 논리 모델은 잘되어 있지만 물리 모델이 최적화 되어 있지 않은 경우를 숫하게 보아왔다. Oracle을 이용하여 물리모델링 을 하는 사람들은 반드시 이런 종류의 책을 읽기 바란다. 왜냐하면 일반적인 모델링책의 경우 물리모델링시 오라클에 최적화 시키는 방법은 언급이 없기 때문이며 이러한 이유 때문에 튜닝책을 모델러들이 보아야만 하는 것이다.

결론:
필자가 이 책을 좋아하는 이유는 위에서와 같이 NUMBER형을 쓰지말아야 할 이유를 테스트를 통해서 비교해 주기 때문이다.  이 책에는 위와 같은 예제가 많이 등장한다. 마치 Tomas Kyte의 책(Efective Oracle By Design)이나 Jonathan Lewis의 (Practical Oracle 8i)과 같은 책을 보는듯 하다. Oracle 튜닝의 교과서라 할수 있는 이런 책들은 튜닝을 적용한 경우와 그렇지 않은 경우를 번갈아 보여주며 차이점을 극명하게 대조시키는 방법을 사용하고있다.
다행스럽게도 오늘 필자가 리뷰한 Trouble Shooting Oracle Performance 또한 이러한 방법을 쓰고있다.
여러가지 면에서 Oracle 튜닝에 관해 진지한 고민을 해본 사람들에게 추천하고 싶은 책이다. 여러분들도 한번 고민해보지 않겠는가?

P.S : 이책과 관련된 또다른 서평을 아마존(Amazon)에서 참조하기 바란다.
Amazon 서평

Posted by extremedb
,

pdf 첨부파일에 일부 오류가 있어 수정해서 다시올립니다. 또한 Rollup 의 개념을 추가 하였습니다.
댓글을 참조 바랍니다. (2009.07.17)


테스트의 이유
SQL이 절차적 언어와 비교할때 가장 두드러 지는 특징은 두가지 이다.
첫번째는 집합처리가 된다는 점이며 두번째로는 처리순서및 로직이 필요 없다는 것이다.
오늘은 첫번째 특징인 집합처리에 대해서 간단한 테스트를 진행하려 한다.
이 테스트를 통과한 사람은 집합개념이 확실한 사람이라고 보면 된다.
필자가 이러한 테스트를 하는 이유는 실무에서 Group By Extension(Rollup, Cube, Grouping Sets) 를 잘사용할경우 Union 등을 없애고 같은 테이블을 반복해서 Scan 하는 비효율을 줄일수 있기 때문이다. 이렇게 되려면 최소한 Group By Extension들의 개념을 확실히 알필요가 있다.

먼저 문법을 알아야 한다.
먼저 이 테스트를 진행하기전에 RollUp, Cube, Grouping Sets 를 모른다면 테스트를 진행할수 없으므로 문법책을 먼저 보고오기 바란다. 위의 3가지 문법을 모두 다 안다고 보고 테스트를 진행할것이다.

먼저 답을 내는 방법을 알아보자.
아래와 같이 문제에 답을 하면 된다.

문제 : 아래의 SQL 을 논리적으로 풀어서 나타내시오.

SELECT A, B, SUM(C)
  FROM T1
GROUP BY ROLLUP(A, B)



답변 :

SELECT A, B, SUM(C)
  FROM T1
GROUP BY A, B
UNION ALL
SELECT A, NULL, SUM(C)
  FROM T1
GROUP BY A
UNION ALL
SELECT NULL, NULL, SUM(C)
 FROM T1
GROUP BY NULL



위의 답은 문제의 SQL 을 논리적으로 풀어서 재작성 한것이다. 바로 Rollup 의 개념을 물어본 것이다.
이런식으로 답을 하면 된다. 간단하지 않은가?

이제 시작해보자.
문제는 4가지이며 모두 논리적으로 풀어서 작성하면 된다. 모든 문제는 3줄짜리 SQL 이다.
제약조건은 한문제를 푸는데 3분을 넘기면 안된다.

문제1 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY C, GROUPING SETS(A, B)

문제2 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY CUBE(  (A, B), C )

문제3 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY GROUPING SETS(A,B), GROUPING SETS(C,D)

문제4:
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY C, GROUPING SETS( ROLLUP(A), ROLLUP(B) )



모두 맟추면 집합개념이 확실한 사람...
위의 4가지 문제를 모두 쉽게 푸는 사람은 집합개념이 확실한 사람이라고 보면 된다.
만약 쉽게 떠오르지 않거나 머리가 아프다면 아직 문법을 모르거나 집합개념이 확실히 서지 않은 사람이라고 보면 된다.
문제가 어렵다고 너무걱정 하지말자. 아래의 자료에서 문제풀이를 보면서 개념정립을 하면 되기 때문이다.
답은 아래에 첨부된 파일에 존재한다.
개인적으로 Group By Extension을 정리한 문서이므로 자유로이 배포해도 된다.
하지만 배포 할때는 꼭 출처를 밝혀주기 바란다.
참고로 아래의 그림은 첨부된 파일의 한부분 이며 1번문제의 모범답안 이다.


사용자 삽입 이미지


invalid-file

Group By Extension 개념정리





편집후기 :
"과연 이런것 까지 알아야 하나" 라는 질문이 들어 왔다. 여러분들은 어떻게 생각하는가?
다음과 같은 한마디로 답변을 대신하고 싶다.
"SQL 문법과 집합개념은 SQL 튜닝의 시작점이다."

Posted by extremedb
,

Science of DataBase가 운영을 시작한지 1년이 지났습니다.
정확히 2008년 6월 부터 시작했으니 이제 만으로 1년이 되었습니다.
블로그를 만들 당시의 의도는 데이터 모델링, 데이터 품질, 데이터 모델 패턴, Performance Tuning 등을 모두 다루려고 하였으나 어쩌다 보니 Performance Tuning 위주로 나가게 되었습니다. 프로젝트에서 이슈된 사항들 위주로 하다보니 그렇게 된것인데 추후에 모델링 프로젝트시 이슈가 생기면 데이터 모델링,  데이터 모델 패턴등도 올리도록 하겠습니다.

Science of DataBase를 방문해주시는 분들 중에는 이방면에서 유명한 분들도 많이 계십니다.

Optimizing Oracle Optimizer 의 저자 조동욱님

Transaction Internals in Oracle 10gR2 의 저자 김시연님

오라클 성능 고도화 원리와 해법 I 의 저자 조시형님

비용기반의 오라클 원리 의 역자 김정삼님

이분들은 아주 실력이 뛰어나고 성품 또한 아주 훌륭한 분들 이어서 본받을점이 많이 있습니다.
이분들과 때때로 토론과 논쟁을 하는 과정에서 오류를 발견하고 수정하여 블로그가 많이 발전한것 같습니다.
사실 개인적인 바램은 이런 고수 분들이 회사 차원을 떠나 사심없이 모여서 한국의 OAKTABLE NETWORK  를 만드는 것입니다.  이것이 가능 할지는 모르겠습니다. 물론 어렵겠지요. 한분 한분의 허락을 받아야 되기 때문 입니다.

Science of DataBase는 개인 블로그 입니다.
저는 개인공간에 회사 차원의 메세지를 올리는것을 무척 싫어합니다. 앞으로도 상업적인 메세지는 올리지 않도록 노력 하겠습니다. 하지만 지금 집필중인 튜닝 책은 예외로 할 작정입니다. 회사차원이라기 보다는 개인차원의 노력이 많이 필요하기 때문에 책이 나오면 블로그에 소개를 올릴려고 합니다.

사실 요즘 블로그 관리가 힘들어 졌습니다.
맡은 프로젝트 + 집필작업 + 블로그 관리 + 개인적인 Study 의 4중고(?)에 시달리고 있습니다. 하지만 방문해주시는 분들을 위해서라도 꾸준히 주평균 1회 정도 글을 올릴려고 노력 하고 있습니다. Science of DataBase는 다은 블로그와는 달리 화려하지도 않고 방문자수도 많지 않습니다. 하지만 방문해주신 모든 분들이 DataBase를 사랑하시는 분들입니다. 방문 해주시는 한분 한분이 저에게는 소중하신분들 이십니다.

작년 6월달에 하루 평균 방문자수 3명으로 시작하여 요즈음은 평일 평균 120 명정도로 꾸준히 방문자수가 늘고 있습니다. 블로그는 독자들과의 소통이 중요한데 혹시 Science of DataBase 에게 바라는 점,  아쉬운점, 개선사항 등이 있는 분은 주저하지 마시고 코멘트를 남겨주시기 바랍니다.
독자와 함께 만들어 가는 Blog야 말로 진정한 의사소통의 장소가 되지 않을까요?

앞으로 더 좋은글로 보답 하겠습니다.
감사합니다.

오동규 배상

'Oracle > News' 카테고리의 다른 글

볼륨매니저는 역사 속으로  (12) 2009.09.30
오라클은 몇년이나 갈까?  (4) 2009.07.31
오라클 공룡기업으로 거듭나는가?  (0) 2009.04.21
Posted by extremedb
,