더미 테이블을 사용해서 장애를 만나는 경우

더미 테이블을 사용하는 이유

더미 테이블을 사용하지 않는 방법

 

포장마차에서 지인에게 재미있는 이야기를 들었다. 물론 공장 이야기 이다. 나는 이야기를 재미있게 들었지만, 지인의 입장에서는 머리가 쭈뼛쭈뼛 서는 심각한 일이었다. 사건은 2011년 겨울에 시작된다.

 

2011 1 1일 이른 아침, 갑자기 잘 돌아가던 시스템에 몇몇 프로그램들이 작동하지 않는 장애를 만났다. Y2K 버그도 아니고 2011 1 1일에 장애라니? 서버와 네트워크 그리고 Database는 정상이므로 관심의 화살은 개발팀으로 집중되었다. 개발팀에서 장애 프로그램을 조사해보니 지난 한 달간 프로그램 수정이 없다고 하였다. 결국 모든 것이 정상인데 프로그램만 돌아가지 않는 상황이다. 귀신이 곡할 노릇이 아닌가? 빨리 정상적인 서비스를 해야 하므로 1, 1초가 아쉬운 시점이었다. 모두들 땀을 흘리며 원인을 찾고 있었다. 프로그램 담당자는 장애를 일으킨 사람을 찾으면 죽여버리겠다고 소리쳤다.

 

여러분은 이런 장애에서 안전한가?

다행히 오래 걸리지 않고 원인을 찾았다. 돌아가지 않는 프로그램들의 공통점은 더미테이블을 사용한다는 것이었다. 즉 Copy_ymd를 사용한 것이다. 그 테이블을 조사해보니 일자가 2010년 까지만 들어가 있었다. 그래서 2011년이 되자마자 장애가 발생한 것이었다. 다시 말해, Copy_ymd 테이블에 2011년 데이터가 없으므로, 이 테이블과 조인하면 한 건도 나오지 않는 것이다. 생각해보니, 모든 시스템에 이런 일이 발생할 수 있다. 이야기를 듣는 필자의 간담이 갑자기 서늘해진다.

 

시스템을 구축한 업체에게 항의하려고 문서를 찾아보니 2001년에 Open한 시스템으로 2001년 기준으로 미래의 일자를 10년치 넣어 놓았다. 소프트웨어의 라이프 사이클을 고려한다면, 10년이면 충분하다고 생각했을 것이다. 하지만 운이 없게도 차세대 프로젝트를 하지 않고 10년간 유지보수를 하면서 사용한 것이다. 그리고 인수인계서에 2011년이 되기 전에 몇 년치의 데이터를 더 넣어놓으라고 명시되어 있었다. 시스템을 구축한 업체에게 항의할 수 도 없는 일이었다. 인수인계서를 보는 사람이 한 명이라도 있었을까?

 

왜 더미 테이블을 사용할까?

데이터베이스에 관심이 있는 개발자라면 Copy_ymd, Copy_ym, Copy_y, Copy_t 등 네 개의 더미테이블을 알 것이다. 많은 시스템에 이런 더미 테이블들이 있다. 과거에는 이런 테이블들을 사용해야만 했다. 하지만 2011년의 시점에서 새로운 프로젝트를 할 때 이런 테이블들이 필요할까? 필요한지 아닌지를 알려면 먼저 더미테이블의 용도를 알아야 한다. 이 테이블들의 용도 중에서 대표적인 것은 아래와 같이 세 가지로 볼 수 있다.

 

1. Copy: 같은 집합을 여러 번 복제하여 원하는 결과집합을 구한다.

2. 데이터 체크: 일자의 경우 입력된 값이 올바른지 확인한다. 예를 들면, 2 30일은 잘못된 일자이다.

3. 인덱스의 효율적 사용: 인덱스의 첫 번째 컬럼 혹은 중간 컬럼이 Where 조건에 사용되지 않을 때 더미 테이블을 이용하여 IN으로 공급해주면 인덱스를 효율적으로 사용할 수 있다.

 

물론, 다른 용도로 더미테이블을 사용할 수 도 있지만, 대부분은 위의 세가지 경우 때문에 더미테이블이 필요하다. 가끔 기준일자를 관리하는 테이블을 볼 수 있는데, 이것은 더미테이블이 아니라 business에 필요한 것이다. 더미테이블은 업무적인 것이 아니라, 성능적인 관점, 혹은 관리적인 목적으로 사용되는 것이다. 업무적인 데이터가 없으므로 차세대 시스템을 구축할 때 더미 테이블은 분석 대상에서 빠져도 된다. 이런 이유 때문에 모델러들도 더미테이블을 중요하게 생각하지 않는다.

 

더미 테이블의 단점

위의 세 가지를 더미 테이블을 사용하지 않고 처리할 수 있다면 굳이 사용할 필요는 없다. 왜냐하면 아래와 같은 단점이 있기 때문이다.

 

첫 번째, 더미 테이블이라고 해도 시스템 속성을 추가해야만 한다. 시스템 속성이란 입력자, 입력일시, 수정자, 수정일시 등을 의미한다. 모든 테이블에 이런 컬럼들이 4 ~ 6개 정도 존재한다. 많은 기업들이 메타시스템을 사용하고 있다. 메타시스템에 테이블에 시스템 속성이 없으면 등록할 수가 없는 경우가 많다. 심지어 자동으로 시스템속성을 추가하는 메타시스템도 있다.

 

그런데 더미테이블은 튜닝의 목적이 있으므로 매우 가벼워야 한다. 생각해보라. Copy_t에 존재하는 숫자컬럼의 length3 byte에 불과한데 시스템 속성 네 개가 48 byte를 차지한다. 3 byte를 위해서 건건이 48 byte를 낭비해야 한다. 테이블이 무거워 질 수 밖에 없다. 더미 테이블은 메타시스템으로 관리하지 말고 엑셀로 관리하면 된다고? 왜 추가적인 관리를 해야만 하는가?

 

두 번째, 누가 더미 테이블을 중요하게 생각하는가? 더미 테이블을 인수인계 시 중요항목으로 관리되고 있는가? 2011년이 가까이 다가와도, Copy_ymd에 데이터를 넣어줄 생각을 하는 사람은 아무도 없었다. 왜냐하면 10년간 담당자가 세 번이나 바뀌었고, 더미테이블은 인수인계 시 중요관심사가 아니었기 때문이다. 결국 더미테이블을 신경 쓰는 사람은 아무도 없을 수 있다. 시스템은 이렇게 중요 테이블이 아니더라도 조그만 블랙홀이 생기면 장애를 맞는다. 이런 일이 발생할 수 밖에 없는 걸까?

 

세 번째, 관리해야 할 DB 서버가 많다면 위험이 증가한다. DB 팀이 관리하는 DB30개라고 가정하자. 지금 30개의 DB에 대해서 더미테이블을 관리하고 있는가? Copy_ymd에 추가적인 데이터를 insert 해야 하는 시기를 알고 있는가? 관리하고 있지 않다면 장애를 맞을 가능성이 높다. 그렇다면, 신경 쓰지 않아도 되도록, 시간이 되면 자동으로 insert되는 프로그램을 고려해 보아야 하는가? 아니면 시스템마다 더미테이블 들을 뒤져서 안전하게 100년치를 넣을 것인가? 왜 그래야 하는가? 아예 더미테이블을 사용하지 않으면 될 것을

 

지금은 운영 중이기 때문에 SQL을 바꾸는 것이 어렵다고 하더라도, 차세대 시스템을 구축할 때는 테이블을 관리할 필요도 없고, 장애도 일으키지 않는 방법이 무엇인지 고려하기 바란다. 방법은 얼마든지 있다. 이미 똑똑한 개발자들은 아래의 방법을 사용하고 하고 있다.

 

1. Copy

Copy_t 대신에 Rollup, Cube, Grouping Sets를 활용하면 원하는 집합을 만들 수 있다. 사용방법은 해당 을 참고하라. 물론 내부적으로 쿼리변환이 발생되어 UNION ALL로 풀릴 수도 있으므로 성능이 저하되는지 실행계획의 확인은 필요하다. 이런 경우에도 COPY_T는 필요 없으며 DUAL + CONNECT BY LEVEL을 사용하면 된다. 또한 LEAD/LAG를 사용한다면 복제하지 않고도 전/후의 데이터를 비교할 수 있다.

 

2. 데이터 체크

데이터를 Insert 하기 전에 일자 컬럼을 체크하려고, DBMS Call을 해야만 하나? 다시 말해, 무슨 이유 때문에 DB에 불필요한 부하를 주어야 하는가? 비슷한 노력을 들이고도 DBMS Call을 하지 않을 수 있다. 화면 단에서 Java Script로 처리하던지, 아니면 Constraint를 걸면 Insert할 때에 자동으로 체크 되므로 별도의 DBMS Call은 필요 없다. Constraint에 대해서는 관련 을 참조하라.

 

3. 인덱스의 효율적 사용

INDEX SKIP SCAN 기능이 추가되었기 때문에 IN 서브쿼리를 사용해야 되는 경우는 많이 한정 되었다. 또한 IN 서브쿼리를 사용한다고 하더라도 Copy_t, Copy_ymd 대신에 Dual + Connect By를 사용하면, Pseudo 컬럼인 Level을 사용할 수 있다. 물론 주의사항은 있다. 해당 을 참조하라.

 

3번에 대해서 어느 개발자가 다음과 같이 질문한다.

 

질문1

개발자: 인덱스가 거래일자 + 고객번호 입니다. 거래일자에 Between 조건이 들어오고 고객번호에 = 조건이 들어온다고 칩시다. 인덱스의 선두 컬럼이 Range 조건이므로 똑똑한 고객번호를 인덱스로 액세스 할 수 없습니다. 이럴 때, Copy_ymd가 있어서 거래일자를 IN 서브쿼리로 공급할 수 있었습니다. 그런데 Copy_ymd 테이블 없이 Dual + Connect By + Level로 처리가 가능 한가요? Copy_tLevel로 처리가 가능하지만 일자는 Range 조건으로 만들기 힘들 것 같은데요.

필자: 됩니다.

개발자: 어떻게요?

 

질문2

개발자: INDEX SKIP SCAN은 인덱스가 A+B+C 로 되어있고, A 혹은 B Where 조건에서 생략될 때만 사용할 수 있는 것 아닙니까? , A 컬럼에 Range 조건이 오고 B = 조건이 오면 INDEX SKIP SCAN을 사용할 수 없는 걸로 알고 있습니다만.

필자: 꼭 그런 것은 아닙니다. A 컬럼에 조건이 Between이나 LIKE 조건이 오고 B 컬럼에 = 조건이 오더라도 INDEX SKIP SCAN이 발생합니다. , 선두나 중간 컬럼의 조건이 생략될 때만 INDEX SKIP SCAN이 발생하는 것은 아니며, 선두나 중간 컬럼에 조건이 Range로 들어올 때도 발생합니다.  

개발자: 그럴 리가요?

 

이제부터 두 가지 질문에 대해 대답해보자. 먼저 Sales 테이블에 인덱스를 하나 만들고 Copy_ymd를 만들자.

 

CREATE INDEX IDX_SALES_01 ON SALES (time_id, cust_id, prod_id) ;

 

CREATE TABLE COPY_YMD AS

SELECT TO_CHAR(ROWNUM + TO_DATE('19800101', 'YYYYMMDD'), 'YYYYMMDD') AS YMD_CHAR,

       ROWNUM + TO_DATE('19800101', 'YYYYMMDD') AS YMD_DT

  FROM SALES

WHERE ROWNUM <= 14600;

 

ALTER TABLE COPY_YMD ADD CONSTRAINT PK_COPY_YMD

PRIMARY KEY (YMD_CHAR) USING INDEX; 

 

CREATE UNIQUE INDEX IDX_COPY_YMD_01 ON COPY_YMD(YMD_DT);

 

Sales 테이블의 인덱스는 Time_id _+ cust_id + Prod_id 이다. 해당 매출테이블의 transaction이 많아서 인덱스를 변경할 수도, 생성할 수도 없는 상황이라고 가정한다. 이제 테스트를 시작해보자.

 

참고로 아래의 힌트는 INDEX SKIP SCAN을 방지할 목적으로 사용한 것이다. INDEX SKIP SCAN이 나오기 전에는 이렇게 INDEX RANGE SCAN으로 수행되었다.

 

SELECT /*+ NO_INDEX_SS(S IDX_SALES_01) INDEX_RS_ASC(S IDX_SALES_01) */ s.*

  FROM sales s

 WHERE time_id BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name         | Starts | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |              |      1 |      6 |00:00:00.01 |     209 |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      1 |      6 |00:00:00.01 |     209 |

|*  2 |   INDEX RANGE SCAN                 | IDX_SALES_01 |      1 |      6 |00:00:00.01 |     203 |

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"=53

              AND "TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("CUST_ID"=53)

 

과거에는 선두컬럼이 Between이나 Like등의 Range 조건이 들어오면 위의 실행통계에서 볼 수 있듯이 비효율이 심했다. 고작 6건을 출력하기 위해 209 블록이나 Scan했다. 왜냐하면, 똑똑한 조건인 고객번호가 선두컬럼의 Range 조건 때문에 Access 조건이 못되고 Filter로 빠졌기 때문이다. 이런 비효율을 없애기 위해 예전에는 아래와 같이 더미테이블을 이용한 서브쿼리를 사용하였다.

 

SELECT /*+ LEADING(C@SUB) USE_NL(S) */ s.*

  FROM sales s

 WHERE time_id IN ( SELECT /*+ QB_NAME(SUB) */ ymd_dt

                      FROM copy_ymd c

                     WHERE ymd_dt BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                                      AND TO_DATE('20011130', 'YYYYMMDD') )

   AND cust_id = 53;

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name            | Starts | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                 |      1 |      6 |00:00:00.01 |     136 |

|   1 |  NESTED LOOPS                       |                 |      1 |      6 |00:00:00.01 |     136 |

|   2 |   NESTED LOOPS                      |                 |      1 |      6 |00:00:00.01 |     130 |

|*  3 |    INDEX RANGE SCAN                 | IDX_COPY_YMD_01 |      1 |     61 |00:00:00.01 |       4 |

|*  4 |    INDEX RANGE SCAN                 | IDX_SALES_01    |     61 |      6 |00:00:00.01 |     126 |

|   5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES           |      6 |      6 |00:00:00.01 |       6 |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("YMD_DT">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "YMD_DT"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   4 - access("TIME_ID"="YMD_DT" AND "CUST_ID"=53)

       filter(("TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

 

서브쿼리를 사용하자 고객번호를 Access 조건으로 사용할 수 있게 되었다. 이에 따라 서브쿼리를 사용하지 않은 경우(209 블럭)보다는 Scan량이 줄어 136 블록이 되었지만 약간의 비효율이 있다. Copy_ymd 때문에 4블럭을 Scan 하였다. 이것을 해결하려면 아래처럼 Dual + Connect By Level을 사용하면 된다. 위의 SQL과 아래의 SQL의 답은 같으며 아래의 SQL은 질문1의 답변에 해당한다.  

 

SELECT s.*

  FROM sales s,

      ( SELECT TO_DATE('20011001', 'YYYYMMDD') + LEVEL - 1 AS time_id

          FROM dual

       CONNECT BY LEVEL <= TO_DATE('20011130', 'YYYYMMDD') - TO_DATE('20011001', 'YYYYMMDD') + 1) d

 WHERE s.time_id = d.time_id

   AND s.cust_id = 53; 

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name         | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |              |      1 |      6 |00:00:00.01 |     132 |

|   1 |  NESTED LOOPS                       |              |      1 |      6 |00:00:00.01 |     132 |

|   2 |   NESTED LOOPS                      |              |      1 |      6 |00:00:00.01 |     126 |

|   3 |    VIEW                             |              |      1 |     61 |00:00:00.01 |       0 |

|   4 |     CONNECT BY WITHOUT FILTERING    |              |      1 |     61 |00:00:00.01 |       0 |

|   5 |      FAST DUAL                      |              |      1 |      1 |00:00:00.01 |       0 |

|*  6 |    INDEX RANGE SCAN                 | IDX_SALES_01 |     61 |      6 |00:00:00.01 |     126 |

|   7 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      6 |      6 |00:00:00.01 |       6 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("D"."TIME_ID") AND "S"."CUST_ID"=53)

 

Dual을 사용했기 때문에 Block I/O가 없어졌다. 하지만 여기서 만족하면 안 된다. 왜냐하면 쓸모 없는 조인이 61번이나 시도되었고 이에 따라 126블록을 Scan하였기 때문이다. 따라서 SQL을 아래처럼 바꾸어야 한다.

 

SELECT /*+ INDEX_SS(S IDX_SALES_01) */ s.*

  FROM sales s

 WHERE time_id BETWEEN TO_DATE('20011001', 'YYYYMMDD')

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name         | Starts | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |              |      1 |      6 |00:00:00.01 |      70 |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES        |      1 |      6 |00:00:00.01 |      70 |

|*  2 |   INDEX SKIP SCAN                  | IDX_SALES_01 |      1 |      6 |00:00:00.01 |      64 |

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("TIME_ID">=TO_DATE(' 2001-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"=53

              AND "TIME_ID"<=TO_DATE(' 2001-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("CUST_ID"=53)

 

불필요한 조인도 없어졌으며 Block I/O도 서브쿼리를 사용할 때에 비해서 약 절반으로 줄어들었다. 이것이 질문 2에 대한 대답이다.

 

참고사항

위의 SQL들을 보면 인덱스가 cust_id + time_id로 되어 있는 것이 최적이지만 막상 튜너가 현장에 투입되면 인덱스를 변경/생성/삭제 하기는 대단히 어려우므로 위의 방법을 잘 알아놓아야 한다.

 

결론

Copy_ymd, Copy_ym, Copy_y, Copy_t는 구시대의 유물이다. 성능에도 좋지 않으며, 코드가 길어지고, 장애가 발생할 수 있음에도 여러 가지 이유를 대어 차세대 시스템에 더미 테이블들이 또 포함될 수 있다. 안타깝게도 관행이나 표준으로 생각하는 사람이 많기 때문이다. 이제는 바뀔 때가 되었다. 지금 운영되는 모든 시스템에서 더미테이블을 사용하는 SQL을 모조리 조사해서 고치라는 이야기가 아니다. 그렇게 하기는 힘들 것이다. 다만 모든 더미테이블을 찾아서 미래의 데이터를 미리 그리고 넉넉히 넣자는 이야기 이다. 그리고 앞으로 시작될 프로젝트에서 더미테이블을 사용하지 않았으면 하는 것이 나의 바램이다. 당신이 발 뻗고 잘 수 있도록
저작자 표시 비영리 동일 조건 변경 허락
신고

'Oracle > SQL Tuning' 카테고리의 다른 글

COPY_T 테이블 필요한가?  (6) 2011.04.04
Sort 부하를 좌우하는 두 가지 원리  (9) 2011.03.29
SQL튜닝 방법론  (17) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (6) 2010.02.11
Posted by extremedb

댓글을 달아 주세요

  1. finecomp 2011.04.06 00:43 신고  댓글주소  수정/삭제  댓글쓰기

    8i, 9i 등 ~i 이전 시대의 방법론들을 현재의 ~g시대에도 고수하려는 고집들은 대부분 말도 안되는 논리인 듯 보입니다.
    (물론, 예~~전엔 그 방법들이 최적일 때가 분명히 있었더랬죠...)

    DB모델링이나 SQL만의 현상은 아니더군요...항상 잘 보고, 느끼고 갑니다...^^;;;

  2. 에너자이져 2011.04.06 09:32 신고  댓글주소  수정/삭제  댓글쓰기

    예전에 유용하게 사용한 적이 있었는데 구시대의 유물이 되어버렸네요..
    좋은글 감사합니다.

  3. 최윤호 2011.04.06 11:32 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 항상 감사합니다.


select /*+ full(a) full(b)  leading(a) use_hash(b) */

a.col1, b.col2

  from tab1 a,

       tab1 b

  where a.col1 = b.col2 ;

 

오해와 현실

위의 SQL을 보면 from 절의 두 테이블은 동일하다. 그리고 건수가 많아서 힌트를 주었으므로, 둘 다 full table scan을 할 것이다. 따라서 위의 SQL을 실행하고 결과를 본다면, a b의 일량(block I/O)은 동일하다.”라고 알고 있는 사람이 많이 있다. a를 읽었더니 block I/O 량이 1000 블럭이라면 b를 읽을 때도 1000 블럭이 나올 것이라는 이야기다. 이런 주장이 사실일까? 결론부터 말하자면 사실이 아니다. b쪽이 더 많은 블럭을 scan 해야 한다. 그래서 b쪽을 scan할 때 더 느리다. b쪽에 더 많은 일량이 나온다면 버그라고 생각하는 사람도 있지만, 버그가 아니라 정상적인 결과이다.

 

이 글의 목적

위의 결론에 따르면 후행테이블을 scan 할 때 심각한 성능저하가 발생 할 수 있다. 이런 현상을 주위의 지인들에게 질문한 결과 적절한 이유나 원인을 말하는 사람은 거의 없었다. 성능문제의 원인을 모르면 튜닝을 할 수 없다. 그러므로 이 글에서는 성능이 저하되는 이유를 독자에게 제시하고, 비효율을 해결 할 수 있는 방법을 설명한다. 또한 이런 문제가 발생하지 않는 예외적인 경우도 살펴본다.

 

이제 테스트를 진행하기 위해 테이블을 하나 만들자.

 

create table test1 as

select lpad(level, 5, '0') as num,

       lpad(level, 60, '0') as num_txt

  from dual

connect by level <= 50000 ;

 

인덱스가 없음으로 앞으로 모든 실행계획은 full table scan이 될 것이다. 정확한 분석을 위해 test1 테이블의 full table scan 일량(logical reads)을 알아보자.

 

select count(*)

  from test1;

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |      1 |00:00:00.01 |     504 |

|   1 |  SORT AGGREGATE    |       |      1 |      1 |00:00:00.01 |     504 |

|   2 |   TABLE ACCESS FULL| TEST1 |      1 |  50000 |00:00:00.06 |     504 |

----------------------------------------------------------------------------- 

 

full table scan의 결과 일량은 504 블럭이다. 따라서 test1 테이블의 데이터가 변경되지 않는다면 항상 504 블럭이 나와야 한다. 정말 그렇게 될까?

 

아래 SQL의 조인 순서는 a--> b 이다.

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100'; --> substr의 인자 -5는 마지막 다섯 자리라는 뜻이다.

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.45 |    5998 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.45 |    5998 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.15 |    5494 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

 

무려 11배나 차이가 난다

선행테이블은 정상적으로 504블록이 나왔다. 하지만 이상하게도 선행테이블과 동일한 테이블인 후행테이블( b )의 일량이 약 11배나 많다. 수행시간도 후행테이블이 더 느리다. 같은 테이블을 동일한 방법으로 scan 했는데 왜 Block I/O 수가 11배나 차이가 날까?

 

힌트를 주어 조인 순서를 바꿔보자.

 

select /*+ leading(b a) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.34 |    5998 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.34 |    5998 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.11 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |    5494 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NUM"="B"."NUM")

   2 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

   3 - filter("A"."NUM">'00100')

  

array size가 원인이다

이번에는 반대로 a의 일량이 b보다 11배 많게 나왔다. 즉 일관성 있게 후행테이블의 일량이 11배가 많다. 그 이유는 툴(오렌지) array size 10 으로 되어있었기 때문이다. 다른 말로 바꾸면 array size 10 이기 때문에 49900건을 모두 출력하려면 4990 fetch 해야 한다. 즉 위의 일량 5494는 원래의 블록 수인 504 fetch 회수(4990 블럭)을 더한 것이다. 여기까지는 이해가 될 것인데 문제는 fetch 할 때마다 한 블록을 더 읽어야 하는가?이다.

 

Fetch 할 때마다 이전에 읽었던 1블럭을 더 읽어야 한다

한 블록에 20건이 들어있다고 가정하고, Array size 10 이라고 치자. 그러면 한 블럭의 데이터(20)를 모두 출력 하려면 동일한 블럭을 반복적으로 두 번 fetch 해야 한다. 바로 이것이 fetch 할 때마다 이미 읽었던 블럭(직전에 fetch 했던 block중 마지막 block)을 다시 Scan 할 수 밖에 없는 이유이다.

 

비효율을 없애려면 array size를 적정 수준으로 늘려라

 

set arraysize 100 --array size 100으로 변경

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.38 |    1507 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.38 |    1507 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.11 |    1003 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

  

array size를 올리자 logical read 5494 에서 1003 으로 변경되었다. 5배 이상 일량(logical reads )이 줄어들었다. 하지만 아직도 원래의 블록 수인 504 보다배정도 많다. 

 

set arraysize 1000 --array size 1000으로 변경

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.34 |    1058 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.34 |    1058 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.09 |     554 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

 

array size1000으로 올리자 logical read 1003 에서 554로 변경되었다. 이 정도면 원래의 블럭수인 504와 비슷하다. 554와 504의 차이는 50 블럭이므로 fetch를 50번 했다는 것을 알 수 있다.

 

해결방법
테스트의 결과는 fetch
가 발생할 때마다 직전 블럭을 읽어야 함을 알 수 있다. 따라서 array size를 적절히 늘리면 fetch 회수가 줄어들므로 이전 블럭을 읽는 횟수도 같이 줄어든다. 이에 따라 성능도 향상된다. 하지만 array size를 늘려도 선행테이블은 logical read의 변화가 없다. 왜냐하면 선행테이블은 fetch에 영향을 끼치지 못하며, 후행 테이블이 scan 되어 조인에 성공될 때만 데이터가 client로 전송(fetch) 되기 때문이다.

조인이 없을 때도 비효율은 발생한다
이런 현상은 full table scan과 해시조인의 조합에서만 발생하는 것은 아니다. 조인 없이 from 절에 테이블이 하나뿐일 때도 동일하게 발생한다. 아래의 SQL이 전형적인 예제이다.

 

array  size 10일 때       

 

select num

  from test1;

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 10

                  Long  Size = 80

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch     5001    0.328        0.219          0       5504          0      50000

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total     5003    0.328        0.219          0       5504          0      50000

 

Misses in library cache during parse: 0

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=5504 pr=0 pw=0 time=67049 us cost=143 size=300000 card=50000)

 

fetch를 5001 번 했기 때문에 원래의 블럭수( 504 )에 비해 logical read량도 약 5000 블럭이 늘었다. 
 


array
 size
100일 때

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 100

                  Long  Size = 80

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch      501    0.063        0.041          0       1004          0      50000

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total      503    0.063        0.041          0       1004          0      50000

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=1004 pr=0 pw=0 time=75254 us cost=143 size=300000 card=50000)

 

Array size 10인 경우(5504)에 비해 일량이 약 5배 정도 감소했다. 그 이유는 fetch 회수가 10배로 줄어들었기 때문이다.

 


array  size
1000 일 때

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 1000

                  Long  Size = 80

 

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch       51    0.031        0.016          0        554          0      50000

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total       53    0.031        0.017          0        554          0      50000

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=554 pr=0 pw=0 time=50383 us cost=143 size=300000 card=50000)        

 

무작정 크게 한다고 좋아지지 않는다

array size 1000으로 변경하니 array size가 10인 경우(5504 블럭)에 비해 일량이 약 10배 정도 감소했다. 하지만 array size 100 인 경우와 비교해 보면 일량이 고작 2배 정도만 줄어들었다. 다시 말해 여기서 array size를 더 크게 하더라도 얻는 이익은 별로 없다는 것이다. 따라서 무작정 array size를 늘려서는 안 된다. 메모리에 부하를 줄 뿐만 아니라 한번에 많은 데이터가 client로 전송되므로 네트웍 I/O가 과도 하게 늘어날 수 있다. 따라서 clientfetch 할 건수가 많고, 네트웍 망의 성능이 좋다면 1000~ 2000 정도를 유지하는 것이 적당하다. 물론 조회 프로그램에서는 페이징 처리를 하는 것이 가장 좋지만, 업무적으로 전체 건을 볼 수 밖에 없는 경우는 array size를 적절히 조절하는 것이 대안이 될 수 있다.


성능문제의 발생조건 
fetch의 비효율은 select문에서만 발생한다. 즉 insert–select CTAS(create table as select) 그리고 merge 문 등에서는 이런 종류의 성능저하가 발생하지 않는다. 왜냐하면 DML문은 select문과 달리 조회(데이터를 clientfetch) 할 필요가 없고, commit이 되면 바로 종료되기 때문이다.

모든 규칙에 예외는 있다

full table scan + sort merge join 의 조합에서는 fetch의 비효율이 발생하지 않는다. 왜냐하면 full table scan + sort merge join 조합은 hash join의 조합과 달라서 모든 데이터를 sort 해야하기 때문이다. 모든 데이터를 sort하려면 어차피 모든 블럭을 scan해야 하므로 fetch를 여러번 해야만 하는 array size를 사용할 필요가 없는 것이다.  그리고 fetch를 여러번 하지 않기 때문에 항상 일량이 일정하다.

또 다른 예외의 경우는
 1 블럭에 1 row만 저장되는 경우이다. 이런 경우는 블럭을 한번만 엑세스 해도 그 블럭의 모든 데이터를 한번에 fetch 할 수 있으므로, 같은 블록을 반복해서 읽을 필요가 없다. 따라서 array size를 변경해도 일량이 달라지지 않는다.

 

호기심이 있는 독자는 아래의 테이블을 만들고 위의 테스트를 똑같이 진행 해보기 바란다. 위의 test 결과와는 다를 것이다.

 

drop table test1 ;

 

create table test1 as

select lpad(level, 5, '0') as num,

       lpad(level, 7000, '0') as num_txt

  from dual

connect by level <= 50000 ;

 
array size 항상 나쁜가?
우리는 array size가 있음으로 해서 부분범위처리를 할 수있다. full table scan을 동반하는 해시조인의 경우에도 중간에 효율적으로 멈출 수 있다. 예를 들어 결과건수가 1억건이며, 만건을 먼저 조회한 후에 다음 만건을 보고 싶다고 할때, 운반단위(array size)가 1000 이라면 10번 fetch 하면 멈출 수 있다. 반면에 array size가 없다면 중간에 멈출 수 없으므로 1억건을 모두 fetch 한후에나 결과를 화면에서 볼 수 있다.

결론

같은 테이블을 두 번 full table scan 하고, 그 둘을 해시조인하면 대부분의 경우 후행 테이블의 I/O량이 더 많다. 그래서 후행테이블을 scan 할 때가 더 느리다. 왜냐하면 직전 fetch 때에 이미 읽었던 block의 데이터가 모두 fetch 되지 않을 수 있으므로 그 블럭을 한번 더 읽어보아야 확인 할 수 있기 때문이다. 이런 비효율이 많이 발생하는 경우는 array size가 작기 때문이다. 따라서 적절한 array size로 늘려주면 성능문제를 해결 할 수 있다. 

fetch의 비효율은 full table scan이나 full table scan + hash join 조합을 사용할 때만 발생하는 것은 아니다. index scan을 할때도 똑같이 비효율이 발생한다.(주1)  즉 fetch의 비효율 문제는 인덱스를 사용할때나 테이블을 scan할때를 가리지 않고 모두 발생한다. 이런 사실들로 미루어 볼때, 위에서 언급한 몇가지의 예외를 제외한다면, 우리는 다음과 같은 결말을 낼 수 있다.

"select문의 결과건수가 많음에도 불구하고, 페이징 처리가 되지 않고, array size가 작은 조회용 프로그램이라면 fetch의 비효율은 존재한다."



주1 : 인덱스 사용시 fetch의 비효율 문제는 이미 책으로 정리가 되어 있으므로 필자가 언급하지 않는다. 이 문제에  관심이 있는 사람은 조동욱 님의 책 Optimizing Oracle Optimizer를 참조하기 바란다.

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2011.01.14 12:45 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용 감사합니다.

    늦었지만 새해 복많이 받으시고, 올해 목표하시는 일 다 이루시길 바랍니다.

  2. Ejql 2011.01.17 15:58 신고  댓글주소  수정/삭제  댓글쓰기

    이런 문의가 종종있었나 보네요? 확실히 알고 갑니다. 감사합니다. 추가 원인이 그 이유였군요.

  3. 오라클완전초보 2011.01.18 17:18 신고  댓글주소  수정/삭제  댓글쓰기

    매일 매일 SQL 을 보면서 사는데
    왜 저는 이런걸 발견하지 못할까요.. 아무생각없이 튜닝을 해서 그런가 봅니다.
    반성하게 되네요

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.18 17:47 신고  댓글주소  수정/삭제

      너무 걱정하지 마시기 바랍니다.
      튜닝에 집중하다 보면 다른것은 보이지 않기 때문입니다.
      하나의 방법은 Q&A에서 답변을 자주하면 실력이 늡니다.
      답변을 하기위해 공부를 많이 해야하고, 원인을 찾아야 하고.... 하다보면 한단계 업그레드 되어있는 자신을 발견하실 것입니다.
      감사합니다.

  4. sid 2011.01.18 21:00 신고  댓글주소  수정/삭제  댓글쓰기

    “왜 fetch 할 때마다 한 블록을 더 읽어야 하는가?”
    이건 어디서 판단해야 하나요?
    이 부분이 잘 이해가 안가서 계속 보고 있는데, 어디서 블럭을 또 본다는 걸 파악해야 하는지 잘 이해가 안가서요.
    전체적으로 워낙에 잘 풀어쓰셔서 술술 이해가 되는데 그 부분만 막혀버려서, 답답해서 이렇게 질문 드립니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.19 09:36 신고  댓글주소  수정/삭제

      안녕하세요.
      select num from test1; 부분의 10046 trace 를 보시면 됩니다. 여기를 보시면 패치회수만큼 블럭을 더 읽는다는 것을 알 수 있습니다. 즉 array size가 10일때 5만건(결과건수)을 패치하려면 5천번을 실행해야 합니다. 이 정보가 10046 trace의 fetch에 나타납니다. 그리고 current에 블럭 i/o량이 나타납니다. trace 상의 굵은 글씨를 중점적으로 보시면 됩니다.

      즉 원래의 블럭량인 504와 패치횟수 5000을 더하면 logical read 량인 5504 가 나옵니다. 이해가 되셨나요?

  5. sid 2011.01.19 10:47 신고  댓글주소  수정/삭제  댓글쓰기

    화면상으론 확인할 수 없나 보군요 ㅎ
    네, 알겠습니다. 지금은 권한상 무리니까 집에가서 한번 테스트 해봐야겠네요.
    좋은 글 감사합니다 ^^

  6. salvationism 2011.01.23 20:41 신고  댓글주소  수정/삭제  댓글쓰기

    "select문의 결과건수가 많음에도 불구하고, 페이징 처리가 되지 않고, array size가 작은 조회용 프로그램이라면 fetch의 비효율은 존재한다."
    자연스럽게 고개를 끄덕이게 되는 단순 명료한 정의 같습니다. 좋은 글 감사합니다. ^^

  7. 나그네 2011.01.24 15:35 신고  댓글주소  수정/삭제  댓글쓰기

    궁금한 점이 있습니다. 어레이 사이즈로 인해 후행 테이블의 로지컬 리드가 높아졌다면 왜 선행 테이블의 로지컬 리드는 안 늘어 나는 건가요? 선행 테이블도 어레이 사이즈에 맞춰서 읽지 않는지요. 이 부분이 궁금합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.24 16:00 신고  댓글주소  수정/삭제

      반갑습니다.
      선행집합만 화면에 뿌리는 것은 의미가 없습니다. 다시말해,select 결과를 화면에 fetch 하려면 조인에 성공한 건만 해야 합니다. 어차피 후행집합이 조인에 성공한 후에 fetch가 시작되므로 성행집합에 성능이 저하되는 array size를 사용할 필요가 없는것 입니다. 이해가 되셨나요?

  8. 나그네 2011.02.16 20:23 신고  댓글주소  수정/삭제  댓글쓰기

    일량이 틀려요 => 일량이 달라요가 맞습니다.

    다르다 = different, 틀리다 = wrong

    우리나라 사람이 가장 잘못 사용하는 단어 중 하나라고 생각합니다.

    요즘 얼마 전 출간된 AWR 관련 서적을 읽고 있는데, 이 책의 저자는 '다르다'는 표현을 전부 '틀리다'로 써 놓으셨더군요.

    일상 대화 중에서야 그러려니 하겠지만, 전문서적에서 전부 잘못 써 놓으니 책 읽기가 싫어질 정도였습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.16 21:23 신고  댓글주소  수정/삭제

      좋은 의견입니다. 제목이 틀렸군요. 개발자에게 들은 것을 그대로 사용하면 안되겠네요.
      한글의 사용이 잘못되어 지적을 하는것은 중요합니다.

      마찬가지로 DB 실력향상도 중요합니다. 국어와 한글을 사랑하시어 댓글을 남기신 만큼,
      이번주에 올라간 분석함수 문제에도 어문규정 만큼 관심을 가져 주시고, 문제를 푸셔서 댓글로 남겨주세요.

      감사합니다.

  9. rose 2011.11.29 18:04 신고  댓글주소  수정/삭제  댓글쓰기

    이런 이유가 있었네요~ 재밌게 잘 읽었습니다 ^^

-대표적인 페이징 처리방법

-누적집계가 필요할 때 페이징(부분범위) 처리방법

-Pagination의 단점을 이용하는 방법

 

주의사항

이 글에서 사용되는 분석함수는 현재 row 까지의 누적집계(Cumulative total) 이다. 이와 반대로 전체집계(Grand Total)나 그룹집계(Sub total)는 부분범위처리를 할 수 없다. 왜냐하면 데이터를 모두 읽어야만 결과를 낼 수 있기 때문이다. 하지만 누적집계는 데이터가 sort 되어 있고, 이미 출력된 컬럼들의 값을 알 수 있다면 부분범위처리가 가능하다. 우리는 이점을 이용할 것이다.

 

상황

Time Out이 발생하여 개발자가 종이 한 장을 들고 급하게 뛰어왔다.

 

개발자: 페이징 처리를 했고, 최적의 인덱스도 존재하고, 그 인덱스를 잘 타는데도 Time Out이 발생합니다.

필자  : 그럴 리가요?

개발자: SQL입니다. 한번 봐주세요.

필자  : ….분석함수 때문에 전체 건을 읽고, 전체 건을 sort하는 군요. 페이징 처리방법을 약간 변경하면 됩니다.

개발자: 이 방법은 SQL 작성 가이드에 나온 방법입니다. 이 방법을 쓰지 않으면 사수에게 혼납니다.

필자  : 이 방법을 사용하지 말라는 이야기가 아니라, 분석함수의 위치만 옮기라는 이야기 입니다.

개발자: 그렇군요. 감사합니다.

 

이렇게 해서 장애상황은 없어졌다. 이후에 SQL 작성가이드에 페이징 처리시 누적집계가 있는 경우의 처리방법을 추가하였다고 한다.

 

Pagination SQL

개발자가 사용한 페이징 처리용 SQL은 아래와 같았다.

 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (         ) a --> 여기에 order by 가 포함된 SQL 을 넣는다.

         WHERE ROWNUM <= :v_max_row )

 WHERE rnum >= :v_min_row ;

        

 

인라인뷰 a SQL을 넣기만 하면 페이징 처리가 된다. 물론 조회시 정렬이 필요하다면 order by가 포함된 SQL을 넣어야 한다. 이 방법은 토마스 카이트가 제시하였다. 이 기법은 약간의 비효율이 있다. 첫 페이지에서는 최적이지만, 뒤쪽 페이지를 읽을 때는 이전 페이지의 데이터를 모두 scan 해야 한다.(화면에 출력되지는 않는다.) 하지만 경험적으로 볼 때 비효율이 크지 않다. 왜냐하면 우리가 구글이나 네이버로 검색을 할 때 통상적으로 앞쪽의 몇 페이지만 보고 검색을 끝내기 때문이다. 만약 네이버에서 트위터라는 단어로 검색을 했더니 5729 페이지가 나왔다고 치면, 대부분 첫 페이지 혹은 두 번째, 세 번째 페이지에서 찾고자 하는 정보를 볼 수 있을 것이다. 5729 페이지를 모두 넘겨본 사람은 거의 없을 것이다. (만약 있다면 존경스럽다.) 따라서 위의 방법을 사용한다고 해도 성능저하는 거의 발생하지 않는다.

 

그런데 인라인뷰 a에 포함될 SQL에 누적집계용 분석함수가 포함될 때는 위의 방법에 약간의 변형을 가해야 한다. 그렇지 않고 위의 방법을 그대로 사용하면 심각한 성능저하가 발생할 수 있다. 즉 분석함수가 존재한다면 위의 방법은 무늬만 페이징 처리가 되며 실제로는 전체범위를 처리하여 Time Out이 발생 할 수 있다. 이 글에서는 누적집계용 분석함수가 있는 경우에 기존방법의 문제점을 제시하고 효과인 페이징 처리방법에 대해 논의 한다.

 

테스트를 위해 테이블과 인덱스를 생성한다.

 

CREATE TABLE sales_t AS SELECT * FROM sales;

 

CREATE INDEX ix_prod ON sales_t (prod_id);

 

먼저 인라인뷰 a 에 들어갈 SQL을 보자.

 

SQL1

 

SELECT   /*+ INDEX(S IX_PROD) */

         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

         SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

    FROM sales_t s

   WHERE s.prod_id = :v_prod_id  --> 30 대입

ORDER BY s.cust_id, s.channel_id, s.time_id ;

 

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |      1 |  29282 |00:00:00.12 |     424 |          |

|   1 |  WINDOW SORT                 |         |      1 |  29282 |00:00:00.12 |     424 | 1621K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T |      1 |  29282 |00:00:00.10 |     424 |          |

|*  3 |    INDEX RANGE SCAN          | IX_PROD |      1 |  29282 |00:00:00.03 |      60 |          |

----------------------------------------------------------------------------------------------------

 

고객별로 channel_idtime_id sort하여 누적합계를 구하는 SQL이다. 위의 SQL은 페이징 처리(부분범위 처리)가 되지 않은 것이다. 따라서 29282건이 결과로 출력되었고 424 블럭을 Scan 하였다. WINDOW SORT라는 operation이 존재하는 이유는 분석함수 때문이다. SQL order by가 있지만 별도의 SORT ORDER BY operation이 존재하지 않는다. 그 이유는 WINDOW SORT order by가 할 일을 대신해 주고 있기 때문이다. WINDOW SORT operation 때문에 PGA 1621K만큼 사용하였다.

 

이제 페이징 처리를 해보자. 먼저 차이를 보여주기 위하여 분석함수를 제거하고 페이징 처리를 하였다.

 

SQL2

 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT   /*+ INDEX(S IX_PROD) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) a

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name    | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  1 |  VIEW                           |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  2 |   COUNT STOPKEY                 |         |      1 |     20 |00:00:00.02 |     424 |          |

|   3 |    VIEW                         |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  4 |     SORT ORDER BY STOPKEY       |         |      1 |     20 |00:00:00.02 |     424 | 2048  (0)|

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T |      1 |  29282 |00:00:00.13 |     424 |          |

|*  6 |       INDEX RANGE SCAN          | IX_PROD |      1 |  29282 |00:00:00.03 |      60 |          |

-------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   4 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

페이징 처리를 하였음에도 똑같이 전체 블록인 424 블럭을 scan 하였다. 그 이유는 전체 건을 읽어서 정렬작업을 해야 하기 때문이다. 반면에 PGA의 사용은 2048에 불과하다. 왜냐하면 부분범위를 처리할 때는 전체 건을 sort하는 것이 아니라, 20 row 짜리 배열을 만들고 그 배열만 관리하면 되기 때문이다. 자세한 내용은 관련 을 참조하라.

 

이제 분석함수를 추가해 보자.

 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT   /*+ INDEX(S IX_PROD) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

                         SUM(amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) a

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name    | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |         |      1 |     20 |00:00:00.03 |     424 |          |

|*  1 |  VIEW                           |         |      1 |     20 |00:00:00.03 |     424 |          |

|*  2 |   COUNT STOPKEY                 |         |      1 |     20 |00:00:00.03 |     424 |          |

|   3 |    VIEW                         |         |      1 |     20 |00:00:00.03 |     424 |          |

|   4 |     WINDOW SORT                 |         |      1 |     20 |00:00:00.03 |     424 | 1621K (0)|

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T |      1 |  29282 |00:00:00.15 |     424 |          |

|*  6 |       INDEX RANGE SCAN          | IX_PROD |      1 |  29282 |00:00:00.03 |      60 |          |

-------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

성능저하의 원인은 분석함수

분석함수를 사용하자 PGA사용량이 급격히 늘었다. 분석함수가 없는 경우와 비교해보면 무려 791배나 차이가 난다. SQL1 PGA 사용량과 위 실행계획의 PGA 사용량을 비교해 보면 분석함수의 PGA 사용량은 페이징 처리를 하지 않았을 때와 똑같다. 즉 페이징 처리를 하였지만 분석함수의 영향으로 전체범위 처리가 되어버린 것이다. 바로 이점이 페이징 처리를 하였음에도 Time-Out이 발생하는 이유였다. 어떻게 하면 비효율을 제거할 수 있을까? 아래의 SQL이 정답이다.

 

SELECT *

  FROM (SELECT s.*, ROWNUM rnum,

               SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

          FROM (SELECT   /*+ INDEX(S IX_PROD) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) s

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

 

--------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name    | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  1 |  VIEW                            |         |      1 |     20 |00:00:00.02 |     424 |          |

|   2 |   WINDOW BUFFER                  |         |      1 |     20 |00:00:00.02 |     424 | 2048  (0)|

|*  3 |    COUNT STOPKEY                 |         |      1 |     20 |00:00:00.02 |     424 |          |

|   4 |     VIEW                         |         |      1 |     20 |00:00:00.02 |     424 |          |

|*  5 |      SORT ORDER BY STOPKEY       |         |      1 |     20 |00:00:00.02 |     424 | 2048  (0)|

|   6 |       TABLE ACCESS BY INDEX ROWID| SALES_T |      1 |  29282 |00:00:00.14 |     424 |          |

|*  7 |        INDEX RANGE SCAN          | IX_PROD |      1 |  29282 |00:00:00.04 |      60 |          |

--------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RNUM">=:V_MIN_ROW)

   3 - filter(ROWNUM<=:V_MAX_ROW)

   5 - filter(ROWNUM<=:V_MAX_ROW)

   7 - access("S"."PROD_ID"=:V_PROD_ID)

 

분석함수는 인라인뷰 밖으로 빼라

분석함수를 뷰의 외부로 위치를 바꾸자 PGA를 거의 사용하지 않는다. 분석함수가 추가되었음에도 PGA 사용량이 분석함수를 사용하지 않은 경우(SQL2)와 비슷하다. 그 이유는 20건에 대해서만 분석함수가 실행되었기 때문이다. ID 2번에서 사용한 PGA SORT를 위한 것이 아니다. 왜냐하면 이미 인라인뷰 내에서 SORT가 되었으므로 같은 작업을 반복할 필요가 없기 때문이다. 이런 경우는 order by절의 컬럼과 분석함수 OVER절의 컬럼이 일치한 경우만 나타난다. 이에 따라 OperationWINDOW SORT가 아니라 WINDOW BUFFER로 바뀌었다. 20 row로 구성된 배열만 관리하면 된다. Order by 작업 또한 전체 건을 sort하지 않고 페이징 처리된 20건에 대해서 배열만 관리한 것이다.

 

절반의 성공

위의 실행계획이 best 인가 하면 그렇지는 않다. 왜냐하면 페이징 처리가 되지 않은 SQL1의 실행계획을 보면 29282건을 모두 읽었고, 페이징 처리가 된 위의 SQL 또한 마찬가지 이다. 다시 말해 위의 SQL은 결과적으로 20건만 출력되므로 비효율적인 전체범위를 처리한 것이다. PGA 사용(Sort)의 관점에서는 부분범위 처리가 되었지만 Block I/O의 관점에서는 전체범위를 처리하고 말았다.

 

이제 Block I/O 문제를 해결하기 위해 인덱스를 생성해보자.


CREATE UNIQUE INDEX PK_SALES_T ON SALES_T(PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID);


이제 위의 인덱스를 이용하여 페이징 처리되지 않은 SQL을 실행해 보자.

 

SELECT /*+ INDEX(S PK_SALES_T) */

       s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

       SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

  FROM sales_t s

 WHERE s.prod_id = :v_prod_id  --> 30 대입

 ORDER BY s.cust_id, s.channel_id, s.time_id ;

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |            |      1 |  29282 |00:00:00.11 |   28337 |          |

|   1 |  WINDOW BUFFER               |            |      1 |  29282 |00:00:00.11 |   28337 | 1495K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.12 |   28337 |          |

|*  3 |    INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

-------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("S"."PROD_ID"=:V_PROD_ID)

 

28337 블록을 scan 하였고 PGA 1495K나 사용하였다. WINDOW BUFFER operation을 본다면 전체 건을 sort한 것은 아니다. 하지만 배열(WINDOW)의 크기가 20건이 아니라 29282건이나 되므로 전체 건을 sort한 경우와 PGA 사용량이 비슷해져 버렸다. 전체 건을 sort SQL1 PGA 사용량이 1621K 이므로 비슷하다고 할 수 있다.

 

페이징 처리를 해도...

이런 현상은 페이징 처리를 해도 분석함수를 인라인뷰 외부로 이동하지 않으면 마찬가지로 발생한다. 아래의 SQL을 보자.

 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT   /*+ INDEX(S PK_SALES_T) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold,

                         SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) a

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

----------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |            |      1 |     20 |00:00:00.04 |   28337 |          |

|*  1 |  VIEW                           |            |      1 |     20 |00:00:00.04 |   28337 |          |

|*  2 |   COUNT STOPKEY                 |            |      1 |     20 |00:00:00.04 |   28337 |          |

|   3 |    VIEW                         |            |      1 |     20 |00:00:00.04 |   28337 |          |

|   4 |     WINDOW BUFFER               |            |      1 |     20 |00:00:00.04 |   28337 | 1495K (0)|

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |  29282 |00:00:00.13 |   28337 |          |

|*  6 |       INDEX RANGE SCAN          | PK_SALES_T |      1 |  29282 |00:00:00.03 |     118 |          |

----------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RNUM">=:V_MIN_ROW)

   2 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

부분범위 처리가 아니라 전체범위 처리이다

많은 이들이 착각하는 것이 위의 SQL이다. 다시 말해 “order by와 분석함수의 over절에 최적화된 인덱스를 생성하면 부분처리가 되겠지라고 생각한다. 하지만 사실은 이와 다르다. 인덱스의 영향으로 Plan상에 sort order by window sort operation이 없으므로 부분범위 처리가 된 것으로 판단하면 안 된다. 20건을 읽은 것이 아니라 전체 건인 29282건을 읽었으며 PGA 사용량도 전체 건을 sort했던 경우(SQL1)와 비슷하다.

 

이런 상황에서도 해결방법은 분석함수를 밖으로 빼는 것이다. 아래의 SQL을 보자.

 

SELECT *

  FROM (SELECT s.*, ROWNUM rnum,

               SUM (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id) AS sum_amt

          FROM (SELECT   /*+ INDEX(S PK_SALES_T) */

                         s.prod_id, s.cust_id,  s.channel_id, s.time_id, amount_sold

                    FROM sales_t s

                   WHERE s.prod_id = :v_prod_id  --> 30 대입

                ORDER BY s.cust_id, s.channel_id, s.time_id

               ) s

         WHERE ROWNUM <= :v_max_row --> 20 대입

       )

 WHERE rnum >= :v_min_row ;         --> 1 대입

 

 

----------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name       | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |            |      1 |     20 |00:00:00.01 |      23 |          |

|*  1 |  VIEW                           |            |      1 |     20 |00:00:00.01 |      23 |          |

|   2 |   WINDOW BUFFER                 |            |      1 |     20 |00:00:00.01 |      23 | 2048  (0)|

|*  3 |    COUNT STOPKEY                |            |      1 |     20 |00:00:00.01 |      23 |          |

|   4 |     VIEW                        |            |      1 |     20 |00:00:00.01 |      23 |          |

|   5 |      TABLE ACCESS BY INDEX ROWID| SALES_T    |      1 |     20 |00:00:00.01 |      23 |          |

|*  6 |       INDEX RANGE SCAN          | PK_SALES_T |      1 |     20 |00:00:00.01 |       3 |          |

----------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RNUM">=:V_MIN_ROW)

   3 - filter(ROWNUM<=:V_MAX_ROW)

   6 - access("S"."PROD_ID"=:V_PROD_ID)

 

정확히 20건에 대해서만 WINDOW BUFFER operation 이 발생하였다. 이에 따라 PGA 사용량도 최적이 되었다. 또한 Block I/O 관점에서도 최상이다. 28337 Block scan한 것이 아니라 고작 23 Block scan 하였다. 분석함수의 위치가 성능에 얼마나 큰 영향을 미치는지 알 수 있는 장면이다.

 

결론

페이징 처리가 되었음에도 Time Out이 발생한다면 누적집계용 분석함수를 의심해보아야 한다. 만약 분석함수가 존재한다면 인라인뷰 밖으로 빼야 한다. 그렇게 한다면 분석함수의 실행이 최소화되며 이에 따라 성능이 향상된다. 또한 order by와 분석함수에 최적화된 인덱스를 만든다면 전체 건을 읽지 않아도 되며 sort의 부하 또한 없어질 것이다. 다시 말해 비효율이 없는 페이징 처리가 가능하다.

 

원리는 따로 있다

이 글의 결론까지 보았음에도 한가지 의문점을 떠올리지 못한다면 핵심원리를 놓친 것이다. 의문점이란 분석함수를 인라인뷰 밖으로 빼도 답이 달라지지 않는가?” 이다. 분석함수를 인라인뷰 밖으로 빼는 방법이 가능한 이유가 뭐라고 생각하는가? 답을 보기 전에 잠시 이유를 생각해보기 바란다. 답은 아래에 있다.

 

답을 보려면 아래의 글을 마우스로 드래그 하시오

 

이 글의 처음에 언급했던 페이징 처리시 약간의 비효율 있다고 했는데 이것이 원리이다. Tomas Kyte가 제시한 pagination 방법을 사용하면 뒤쪽 페이지를 읽을 때는 이전 페이지의 데이터를 모두 scan 해야 만 한다. 이 비효율을 이용하는 것이 핵심이다. 왜냐하면 한 페이지의 누적집계를 구하려면 이전 페이지의 값들을 모두 알아야 하기 때문이다. 예를 들어 홍길동 고객의 실적이 1 페이지와 2 페이지에 걸쳐서 나온다고 할 때, 1 페이지 있는 홍길동의 실적과 2페이지에 있는 홍길동의 실적을 더해야만 2 페이지의 누적집계를 구할 수 있다. 그런데 위의 방법을 사용하면 분석함수를 인라인뷰 밖으로 빼더라도 이전 페이지의 값을 보존하기 때문에 누적집계의 값은 정확하다.

 

 페이징 처리시 누적집계용 분석함수를 인라인뷰 밖으로 빼라고 누군가에게 guide할 때 단점(비효율)을 장점으로 이용했음을 같이 알려주기 바란다. 그것이 원리이자 핵심이기 때문이다.

 

PS

즐거운 성탄절을 보내시기 바랍니다.

지난 1년간 이 블로그를 이용해 주셔서 감사합니다.


저작자 표시 비영리 동일 조건 변경 허락
신고

'Oracle > SQL Tuning' 카테고리의 다른 글

Sort 부하를 좌우하는 두 가지 원리  (9) 2011.03.29
SQL튜닝 방법론  (17) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (6) 2010.02.11
USE_CONCAT 힌트 제대로 알기  (5) 2009.07.17
Posted by extremedb

댓글을 달아 주세요

  1. Ejql 2010.12.23 18:32 신고  댓글주소  수정/삭제  댓글쓰기

    글 잘봤습니다. 튜닝업무를 하면서 분석함수가 존재하는 부분범위처리가 튜닝이 쉽지가 않았는데 한가지는 해결되는것 같습니다.
    감사합니다. -- 해피 크리스마스 되세요 --

  2. 똥꽃 2010.12.24 08:20 신고  댓글주소  수정/삭제  댓글쓰기

    멋진 크리스마스 선물이네요.

  3. 김시연 2010.12.27 08:27 신고  댓글주소  수정/삭제  댓글쓰기

    한해동안 블로그를 통해 새로운 지식을 많이 얻었습니다. 감사드리고요. ^^ 한해 마무리 잘해세요~

  4. 2010.12.27 16:10  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  5. 에너자이져 2010.12.28 16:01 신고  댓글주소  수정/삭제  댓글쓰기

    언제나 감동이 있습니다..
    한해 마무리 잘하시고.. 건강하세요^^

  6. 라튜니 2010.12.29 10:49 신고  댓글주소  수정/삭제  댓글쓰기

    올 한해는 거의 매일 동규님 블로그를 하루에도 몇 차례를 방문한거 같네요. 새로운 포스트에 대한 기대감에 더 자주 방문했던 거 같습니다. 항상 좋은 정보, 내용의 포스트를 올려 주셔서 감사합니다. 내년에도 올해처럼 변함없는 포스팅 부탁 드립니다.^^ 항상 건강하시고 좋은 일만 가득하길 기원합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.29 16:56 신고  댓글주소  수정/삭제

      말씀하신 것처럼 내년에도 블로그 활동을 계속 하겠습니다. 좋은 내용이 되어야 할 텐데 걱정이 됩니다. ^^ 새해 복 많이 받으시고 소원 성취하세요.
      감사합니다.

  7. 최상운 2010.12.29 15:05 신고  댓글주소  수정/삭제  댓글쓰기

    SQL 가이드에 paging 처리를 추가할려고 했는데, 참 유용한 글이었어요.
    늦었지만, 오수석도 Merry Christmas 하시고 Happy New Year 해요!!!!

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.29 16:58 신고  댓글주소  수정/삭제

      그렇군요. 다행입니다.
      저번에 회사 워크샵에서 최수석님을 뵙지 못해서 아쉽습니다. 언제 한번 얼굴 봅시다.
      올해 마무리 잘하시고 새해 소원성취하세요.

  8. feelie 2010.12.30 20:25 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보를 보고 한해를 마무리하니 기분이 참좋네요.
    일년동안 고생하셨구요.. 내년에도 좋은 내용 기대하겠습니다.

  9. 백면서생 2010.12.31 14:00 신고  댓글주소  수정/삭제  댓글쓰기

    감사드리고, 마무리 잘하시고 행복한 신묘년 되시길 바랍니다.

  10. 시그너스 2011.01.03 13:51 신고  댓글주소  수정/삭제  댓글쓰기

    이글을 읽고 감동받아서 책을 구매 했습니다

  11. 오라클완전초보 2011.01.10 13:46 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요.
    수석님의 글을 읽고 현재 진행중인 차세대 일부 SQL 에 대해서 테스트를 해봤는데
    테이블을 하나만 사용했을 경우는 수석님의 글처럼 PGA 를 현저히 적게 쓰는게 확인이 되었으나
    테이블을 2개이상 조인 했을 경우 analytic function 을 밖으로 빼는거와 기존처럼 사용하나
    동일한 결과값이 나왔습니다. 아직 테스트 진행중이라 정확히 어떠한 방식때문인지는 확인을 못했으나
    혹시 테이블이 n 개 일 경우에도 실적용 사례가 있으신지요?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.01.10 14:34 신고  댓글주소  수정/삭제

      안녕하세요. 조인을 한다고 해서 부분범위 처리가 불가능 하지는 않습니다.
      혹시 아래의 경우가 아닌지 해당 SQL을 다시 점검 해보시기 바랍니다.
      1.nested loop join 이 아닌 hash 조인이나 sort merge 조인을 사용한다.

      2.적절한 인덱스가 없다. 위의 예제에서 보면 order by의 컬럼 순서와 일치해야 합니다.

      3.order by 절과 와 분석함수의 over절의 컬럼 순서가 일치 해야 합니다.

      4.전체건이 몇건인지? 페이징 처리를 했을때 20건이고 하지 않았을때 30건이라면 별로 효율이 나지 않습니다.

      다시한번 확인해보시기 바랍니다.

  12. 아삽 2011.04.07 16:13 신고  댓글주소  수정/삭제  댓글쓰기

    유익한 정보 감사히 읽었습니다.^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.04.07 16:54 신고  댓글주소  수정/삭제

      아삽님 오랜만입니다.

      더 좋은 해법이 있습니다.
      분석함수에 NOSORT + STOPKEY 을 적용하는 방법입니다. 아래 링크의 제일 마지막 부분을 보시면 됩니다.

      하지만 분석함수를 밖으로 빼는 위의 방법도 의미가 있습니다. NOSORT가 불가능 한 경우이거나 분석함수 두개가 각자 서로 다른 OPERATION을 사용한다면 위의 방법이 유일한 대안 입니다.

      http://scidb.tistory.com/entry/분석함수의-성능개선-그-결과는

      감사합니다.

  13. Favicon of http://www.topreplicawatchesstore.com/dolce-gabbana-watches-c-135.html BlogIcon idolreplicas 2011.09.20 12:41 신고  댓글주소  수정/삭제  댓글쓰기

    하지만 분석함수를 밖으로 빼는 위의 방법도 의미가 있습니다. NOSORT가 불가능 한 경우이거나 분석함수 두개가 각자 서로 다른 OPERATION을 사용한다면 위의 방법이 유일한 대안 입니다

  14. Favicon of http://www.hotefashion.com/ BlogIcon gucci hangbags 2011.09.20 12:41 신고  댓글주소  수정/삭제  댓글쓰기

    분석함수에 NOSORT + STOPKEY 을 적용하는 방법입니다. 아래 링크의 제일 마지막 부분을 보시면 됩니다.

-동적인 조회조건에서 SQL 작성법
-
다양한 검색조건에서 SQL 튜닝방법

-쿼리변환의 부정적 측면 해결

 

아래는 신입사원과 김대리의 대화내용이다. 신입사원이 머리를 긁고 있다. 문제가 어려운 모양이다.

 

신입사원: 상황에 따라서 조회조건이 달라지는데 어떻게 처리하죠?

김대리: 각각의 상황에 대해 union all로 처리하고 서로 다른 SQL로 처리하면 되.

신입사원: 네 알겠습니다. (조금 후에) 김대리님, 그렇게 하면 SQL이 너무 길어서 복잡해져요.

          6가지의 조건이 상황에 따라 달라지기 때문이죠.  

김대리: 그럼 방법이 없지. Dynamic SQL로 작성해. Dynamic SQL을 쓰되 바인드 변수를 사용해야 돼.

신입사원: 그건 어떻게 사용하죠? 제가 Dynamic SQL 사용법을 몰라서 그럽니다.

김대리: 내가 조금 있다가 가르쳐 줄게.

신입사원: 감사합니다.

 

이런 상황에서는 Union all로 여러 개의 SQL을 작성하는 것 보다는 Dynamic SQL을 사용하는 것이 해결책이 될 수 있다. 또한 많은 사람들이 그렇게 하고 있다. 하지만 꼭 둘 중에 하나만 골라야 한다는 생각은 버려야 한다. 그렇지 않으면 Union all을 사용하여 SQL이 매우 길어지거나 C JAVA 언어의 도움을 받아 IF Then ELSE 로직으로 SQL을 동적으로 생성하는 불리함을 감수해야 한다. 따라서 이보다 더 쉽고 간단한 방법이 있다면 그것을 사용하면 된다.

 

환경

DBMS: Oracle11g R2

각 테이블의 PK 인덱스는 이미 존재하므로 추가적인 인덱스만 설명한다.

EMP_MGR_HR_DT_IX: employee( manager_id + hire_date )

EMP_DEPT_IX : employee( department_id )

EMP_JOB_IX : employee( job_id )

 

다양한 조회조건을 제외하면 SQL은 다음과 같이 단순하다.

 

SELECT e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

 

여기까지는 SQL이 쉽다. 하지만 여기서부터는 까다로운 요구사항 때문에 SQL에 분기가 발생한다. 원래는 6가지의 where 조건을 적용해야 하지만 지면관계상 요구사항은 네 가지로 한정한다.

 

업무 요구사항

l  네 가지 패턴으로 조회조건이 들어온다. 각각의 패턴들은 :v_delimit(구분자)로 식별이 가능하다.

l  패턴 1  :v_delimit = 1 인 경우는 j.job_id = :v_job 조건으로 조회한다.

l  패턴 2  :v_delimit = 2 인 경우는 e.manager_id = :v_emp AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to 조건으로 조회한다.

l  패턴 3  :v_delimit = 3 인 경우는 d.department_id = :v_dept 조건으로 조회한다.

l  패턴 4  :v_delimit = 4 인 경우는 l.location_id = :v_loc 조건으로 조회한다. 

l  모든 패턴 1~4 filter 조건 d.manager_id > 0 가 공통적으로 적용되어야 한다.

 

성능 요구사항

여기까지는 업무팀의 요구사항이지만 개발자의 요구사항도 있다. where 조건이 패턴에 따라 동적으로 변경되면서도 각 패턴의 실행계획을 튜너의 마음대로 조정할 수 있어야 한다. 즉 네 가지 패턴의 SQL에 대해 서로 다른 힌트를 사용할 수 있어야 한다.

 

이런 까다로운 요구사항을 보고 가장 먼저 떠올릴 수 있는 생각은 Union all로 분기하는 것이다. 하지만 이 방법은 SQL이 길어지므로 코딩량을 증가시킨다. 두 번째로 생각할 수 있는 방법은 Dynamic SQL을 사용하는 것이다. 하지만 이 경우는 Where 조건뿐만 아니라 Select 절도 동적으로 변경되어야 한다. 왜냐하면 구분자의 값에 따라 힌트를 동적으로 만들어야 하기 때문이다. 따라서 우리는 이런 방법들을 사용하지 않을 것이다.

아래의 SQL을 실행할 때는 구분자인
:v_delimit의 값에 1을 대입해야 한다. 즉 패턴 1의 경우이다. 따라서 :v_job :v_delimit를 제외한 나머지 변수 값은 모두 null이다.

 

SELECT /*+ USE_CONCAT */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND (   ( :v_delimit = 1 AND j.job_id = :v_job )               --> :v_delimit = 1 입력, :v_job = 'SA_MAN' 입력

        OR ( :v_delimit = 2 AND e.manager_id = :v_emp

                            AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to )

        OR ( :v_delimit = 3 AND d.department_id = :v_dept )

        OR ( :v_delimit = 4 AND l.location_id = :v_loc   )

       )

   AND d.manager_id > 0;

 

OR를 Union all로 바꿔서 생각한다면 이해가 빠를 것이다. 복잡한 요구사항을 만족하면서도 SQL이 매우 가벼워졌다. Union all을 사용한 경우와 SQL을 비교해 보기 바란다. 길이는 많이 짧아졌지만 Union all을 사용할 때와 성능상 동일하다. 다시 말해 실행시점에서 하나의 SQL 4개의 SQL로 분리될 것이다. (이를 OR-Expansion 이라 부른다) 이 정도 길이의 SQL 이라면 Union all로 구분하여 SQL을 각각 작성하는 방법이나 Dynamic SQL을 일부러 사용할 필요는 없다. 주의사항은 각 패턴 별로 적절한 인덱스가 있어야 한다는 것이다. 그렇지 않으면 구분자의 의미는 사라질 것이다. 이제 실행계획을 보자.

 

------------------------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name             | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |                  |      1 |      5 |00:00:00.03 |      19 |          |

|   1 |  CONCATENATION                    |                  |      1 |      5 |00:00:00.03 |      19 |          |

|*  2 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|*  3 |    HASH JOIN                      |                  |      0 |      0 |00:00:00.01 |       0 |  988K (0)|

|   4 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   5 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   6 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   7 |        TABLE ACCESS BY INDEX ROWID| LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|*  8 |         INDEX UNIQUE SCAN         | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|*  9 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 10 |         INDEX RANGE SCAN          | DEPT_LOCATION_IX |      0 |      0 |00:00:00.01 |       0 |          |

|* 11 |       INDEX RANGE SCAN            | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |          |

|  12 |      TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|  13 |     TABLE ACCESS FULL             | JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 14 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|* 15 |    HASH JOIN                      |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  16 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  17 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|* 18 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 19 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |          |

|* 20 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|* 21 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|  22 |      TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|* 23 |       INDEX RANGE SCAN            | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |          |

|  24 |     TABLE ACCESS FULL             | JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 25 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|  26 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  27 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  28 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  29 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  30 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|* 31 |         INDEX RANGE SCAN          | EMP_MGR_HR_DT_IX |      0 |      0 |00:00:00.01 |       0 |          |

|  32 |        TABLE ACCESS BY INDEX ROWID| JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 33 |         INDEX UNIQUE SCAN         | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|* 34 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 35 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |          |

|* 36 |      INDEX UNIQUE SCAN            | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|* 37 |     TABLE ACCESS BY INDEX ROWID   | LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|* 38 |   FILTER                          |                  |      1 |      5 |00:00:00.03 |      19 |          |

|* 39 |    HASH JOIN                      |                  |      1 |      5 |00:00:00.03 |      19 |  360K (0)|

|* 40 |     HASH JOIN                     |                  |      1 |      5 |00:00:00.01 |      11 |  385K (0)|

|  41 |      NESTED LOOPS                 |                  |      1 |      5 |00:00:00.01 |       4 |          |

|  42 |       TABLE ACCESS BY INDEX ROWID | JOB              |      1 |      1 |00:00:00.01 |       2 |          |

|* 43 |        INDEX UNIQUE SCAN          | JOB_ID_PK        |      1 |      1 |00:00:00.01 |       1 |          |

|* 44 |       TABLE ACCESS BY INDEX ROWID | EMPLOYEE         |      1 |      5 |00:00:00.01 |       2 |          |

|* 45 |        INDEX RANGE SCAN           | EMP_JOB_IX       |      1 |      5 |00:00:00.01 |       1 |          |

|* 46 |      TABLE ACCESS FULL            | DEPARTMENT       |      1 |     11 |00:00:00.01 |       7 |          |

|* 47 |     TABLE ACCESS FULL             | LOCATION         |      1 |     23 |00:00:00.03 |       8 |          |

------------------------------------------------------------------------------------------------------------------

 

4개의 SQL이 각각 다른 조건의 인덱스로 Driving 되었다. 실행계획도 최적이다. 다시 말해 :v_delimit = 1 이 아닌 경우의 SQL은 전혀 실행되지 않았다. 하지만 만약 Hash Join이 맘에 걸린다면 아래처럼 힌트를 추가할 수 있다. Global Hint를 사용하면 하나의 SQL에는 하나의 힌트만 사용한다는 제약을 극복할 수 있다.

 

SELECT /*+ USE_CONCAT LEADING(@SEL$1_1 l d e j) USE_NL(@SEL$1_1 d e j)

                      LEADING(@SEL$1_2 d e l j) USE_NL(@SEL$1_2 e l j)

                      LEADING(@SEL$1_3 e d l j) USE_NL(@SEL$1_3 d l j)

                      LEADING(@SEL$1_4 j e d l) USE_NL(@SEL$1_4 e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND (   ( :v_delimit = 1 AND j.job_id = :v_job )               --> :v_delimit = 1 입력, :v_job = 'SA_MAN' 입력

        OR ( :v_delimit = 2 AND e.manager_id = :v_emp

                            AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to )

        OR ( :v_delimit = 3 AND d.department_id = :v_dept )

        OR ( :v_delimit = 4 AND l.location_id = :v_loc   )

       )

   AND d.manager_id > 0;

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name             | Starts | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |                  |      1 |      5 |00:00:00.01 |      20 |

|   1 |  CONCATENATION                    |                  |      1 |      5 |00:00:00.01 |      20 |

|*  2 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|   3 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|   4 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|   5 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|   6 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|   7 |        TABLE ACCESS BY INDEX ROWID| LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|*  8 |         INDEX UNIQUE SCAN         | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|*  9 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 10 |         INDEX RANGE SCAN          | DEPT_LOCATION_IX |      0 |      0 |00:00:00.01 |       0 |

|  11 |       TABLE ACCESS BY INDEX ROWID | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 12 |        INDEX RANGE SCAN           | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |

|* 13 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  14 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 15 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|  16 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|  17 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|  18 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|  19 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|* 20 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 21 |         INDEX UNIQUE SCAN         | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |

|  22 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 23 |         INDEX RANGE SCAN          | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |

|* 24 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|* 25 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|* 26 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  27 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 28 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|  29 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|  30 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|  31 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|  32 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|  33 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 34 |         INDEX RANGE SCAN          | EMP_MGR_HR_DT_IX |      0 |      0 |00:00:00.01 |       0 |

|* 35 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 36 |         INDEX UNIQUE SCAN         | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |

|* 37 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|* 38 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|* 39 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  40 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 41 |   FILTER                          |                  |      1 |      5 |00:00:00.01 |      20 |

|  42 |    NESTED LOOPS                   |                  |      1 |      5 |00:00:00.01 |      20 |

|  43 |     NESTED LOOPS                  |                  |      1 |      5 |00:00:00.01 |      15 |

|  44 |      NESTED LOOPS                 |                  |      1 |      5 |00:00:00.01 |      13 |

|  45 |       NESTED LOOPS                |                  |      1 |      5 |00:00:00.01 |       6 |

|  46 |        TABLE ACCESS BY INDEX ROWID| JOB              |      1 |      1 |00:00:00.01 |       2 |

|* 47 |         INDEX UNIQUE SCAN         | JOB_ID_PK        |      1 |      1 |00:00:00.01 |       1 |

|* 48 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      1 |      5 |00:00:00.01 |       4 |

|* 49 |         INDEX RANGE SCAN          | EMP_JOB_IX       |      1 |      5 |00:00:00.01 |       2 |

|* 50 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      5 |      5 |00:00:00.01 |       7 |

|* 51 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      5 |      5 |00:00:00.01 |       2 |

|* 52 |      INDEX UNIQUE SCAN            | LOC_ID_PK        |      5 |      5 |00:00:00.01 |       2 |

|* 53 |     TABLE ACCESS BY INDEX ROWID   | LOCATION         |      5 |      5 |00:00:00.01 |       5 |

-------------------------------------------------------------------------------------------------------

 

힌트에 쿼리블럭명을 사용하였다. 각각의 쿼리블럭명은 DBMS_XPLAN.DISPLAY_CURSOR 함수에 +ALIAS 옵션을 추가하면 조회할 수 있다. 아래의 예제가 그것이다.

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +ALIAS' ));

 

중간생략

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1 

   7 - SEL$1_1 / L@SEL$1

   8 - SEL$1_1 / L@SEL$1

   9 - SEL$1_1 / D@SEL$1

  10 - SEL$1_1 / D@SEL$1

  11 - SEL$1_1 / E@SEL$1

  12 - SEL$1_1 / E@SEL$1

  13 - SEL$1_1 / J@SEL$1

  14 - SEL$1_1 / J@SEL$1

  20 - SEL$1_2 / D@SEL$1_2

  21 - SEL$1_2 / D@SEL$1_2
중간생략

  53 - SEL$1_4 / L@SEL$1_4

중간생략

 

가장 좌측의 번호는 Plan 상의 id에 해당한다. 쿼리블럭명은 ‘/’을 기준으로 좌측이다. SEL$1_1부터 SEL$1_4까지 쿼리블럭명들을 볼 수 있다. 이것들을 힌트에 사용하면 조건절에 OR로 분기된 SQL이 아무리 많아도 원하는 SQL(쿼리블럭)만을 콕 집어서 실행계획을 변경시킬 수 있다.

 

OR-Expansion  VS  Union All

이제 OR를 이용한 경우와 Union all을 사용한 경우를 비교해보자. 아래의 SQLUnion all로 분기한 경우인데 두가지 단점이 있다. 특히 Oracle11g R2를 사용하는 사람은 눈 여겨 보아야 한다. 여기서도 구분자에는 1을 대입한다. 네가지 SQL의 힌트가 서로 다름을 주목하자.

 

SELECT /*+ leading(j e d l) use_nl(e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> ‘SA_MAN’ 입력

   AND d.manager_id > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND e.manager_id = :v_emp

   AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to

   AND d.manager_id > 0

   AND :v_delimit = 2

UNION ALL

SELECT /*+ leading(d e l j) use_nl(e l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND d.department_id = :v_dept

   AND d.manager_id > 0

   AND :v_delimit = 3

UNION ALL

SELECT /*+ leading(l d e j) use_nl(d e j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND l.location_id = :v_loc 

   AND d.manager_id > 0  

   AND :v_delimit = 4 ;

 

단점 1: SQL의 길이가 너무 길다

구분자 별로 OR를 사용할 때보다 SQL이 많이 길어졌다. Union을 사용하는 방법의 단점은 SQL의 길이뿐만이 아니다. Oracle11g R2 에서는 개발자의 의도를 무시하는 결과가 발생할 수 있다. 개발자의 의도란 :v_delimit = 1 인 경우의 SQL만 실행하는 것이다. :v_delimit의 값이 2~4인 경우는 한 블록도 Scan해서는 안 된다. 과연 그렇게 되는지 아래의 Plan을 보자.

 

------------------------------------------------------------------------------------------------------------

| Id  | Operation                            | Name               | Starts | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |                    |      1 |      5 |00:00:00.01 |      22 |

|   1 |  UNION-ALL                           |                    |      1 |      5 |00:00:00.01 |      22 |

|*  2 |   FILTER                             |                    |      1 |      5 |00:00:00.01 |      20 |

|   3 |    NESTED LOOPS                      |                    |      1 |      5 |00:00:00.01 |      20 |

|   4 |     NESTED LOOPS                     |                    |      1 |      5 |00:00:00.01 |      15 |

|   5 |      NESTED LOOPS                    |                    |      1 |      5 |00:00:00.01 |      13 |

|   6 |       NESTED LOOPS                   |                    |      1 |      5 |00:00:00.01 |       6 |

|   7 |        TABLE ACCESS BY INDEX ROWID   | JOB                |      1 |      1 |00:00:00.01 |       2 |

|*  8 |         INDEX UNIQUE SCAN            | JOB_ID_PK          |      1 |      1 |00:00:00.01 |       1 |

|   9 |        TABLE ACCESS BY INDEX ROWID   | EMPLOYEE           |      1 |      5 |00:00:00.01 |       4 |

|* 10 |         INDEX RANGE SCAN             | EMP_JOB_IX         |      1 |      5 |00:00:00.01 |       2 |

|* 11 |       TABLE ACCESS BY INDEX ROWID    | DEPARTMENT         |      5 |      5 |00:00:00.01 |       7 |

|* 12 |        INDEX UNIQUE SCAN             | DEPT_ID_PK         |      5 |      5 |00:00:00.01 |       2 |

|* 13 |      INDEX UNIQUE SCAN               | LOC_ID_PK          |      5 |      5 |00:00:00.01 |       2 |

|  14 |     TABLE ACCESS BY INDEX ROWID      | LOCATION           |      5 |      5 |00:00:00.01 |       5 |

|* 15 |   FILTER                             |                    |      1 |      0 |00:00:00.01 |       0 |

|  16 |    NESTED LOOPS                      |                    |      0 |      0 |00:00:00.01 |       0 |

|  17 |     NESTED LOOPS                     |                    |      0 |      0 |00:00:00.01 |       0 |

|  18 |      NESTED LOOPS                    |                    |      0 |      0 |00:00:00.01 |       0 |

|  19 |       NESTED LOOPS                   |                    |      0 |      0 |00:00:00.01 |       0 |

|  20 |        TABLE ACCESS BY INDEX ROWID   | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

|* 21 |         INDEX RANGE SCAN             | EMP_MGR_HR_DT_IX   |      0 |      0 |00:00:00.01 |       0 |

|* 22 |        TABLE ACCESS BY INDEX ROWID   | DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 23 |         INDEX UNIQUE SCAN            | DEPT_ID_PK         |      0 |      0 |00:00:00.01 |       0 |

|  24 |       TABLE ACCESS BY INDEX ROWID    | LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 25 |        INDEX UNIQUE SCAN             | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|* 26 |      INDEX UNIQUE SCAN               | JOB_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|  27 |     TABLE ACCESS BY INDEX ROWID      | JOB                |      0 |      0 |00:00:00.01 |       0 |

|  28 |   MERGE JOIN                         |                    |      1 |      0 |00:00:00.01 |       2 |

|  29 |    TABLE ACCESS BY INDEX ROWID       | JOB                |      1 |      1 |00:00:00.01 |       2 |

|  30 |     INDEX FULL SCAN                  | JOB_ID_PK          |      1 |      1 |00:00:00.01 |       1 |

|* 31 |    SORT JOIN                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  32 |     VIEW                             | VW_JF_SET$B71A25AA |      1 |      0 |00:00:00.01 |       0 |

|  33 |      UNION-ALL                       |                    |      1 |      0 |00:00:00.01 |       0 |

|* 34 |       FILTER                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  35 |        NESTED LOOPS                  |                    |      0 |      0 |00:00:00.01 |       0 |

|  36 |         NESTED LOOPS                 |                    |      0 |      0 |00:00:00.01 |       0 |

|* 37 |          TABLE ACCESS BY INDEX ROWID | DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 38 |           INDEX UNIQUE SCAN          | DEPT_ID_PK         |      0 |      0 |00:00:00.01 |       0 |

|  39 |          TABLE ACCESS BY INDEX ROWID | LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 40 |           INDEX UNIQUE SCAN          | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|  41 |         TABLE ACCESS BY INDEX ROWID  | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

|* 42 |          INDEX RANGE SCAN            | EMP_DEPT_IX        |      0 |      0 |00:00:00.01 |       0 |

|* 43 |       FILTER                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  44 |        NESTED LOOPS                  |                    |      0 |      0 |00:00:00.01 |       0 |

|  45 |         NESTED LOOPS                 |                    |      0 |      0 |00:00:00.01 |       0 |

|  46 |          NESTED LOOPS                |                    |      0 |      0 |00:00:00.01 |       0 |

|  47 |           TABLE ACCESS BY INDEX ROWID| LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 48 |            INDEX UNIQUE SCAN         | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|* 49 |           TABLE ACCESS BY INDEX ROWID| DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 50 |            INDEX RANGE SCAN          | DEPT_LOCATION_IX   |      0 |      0 |00:00:00.01 |       0 |

|* 51 |          INDEX RANGE SCAN            | EMP_DEPT_IX        |      0 |      0 |00:00:00.01 |       0 |

|  52 |         TABLE ACCESS BY INDEX ROWID  | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

------------------------------------------------------------------------------------------------------------

 

단점 2 : 불필요한 쿼리블럭을 Scan 하며 힌트가 무시된다

Join factorization(1) 이라는 쿼리변환이 발생하여 불필요한 두 블록(Plan의 빨강색 부분) Scan 하였다. : v_delimit = 3 인 경우와 :v_delimit = 4인 경우의 SQL이 실행되어 버린 것이다. 확률은 많지 않겠지만 만약 테이블이 대용량이라면 index full scan과 그에 따른 테이블로의 접근은 성능에 치명적일 것이다. 또한 쿼리변환으로 인해 개발자가 작성한 힌트도 무시되어 sort merge join이 발생되었다.

의도하지 않은 쿼리변환을 경계하라
이렇게 다양한 검색조건에서 Union을 사용하는 경우는 11g R2부터 발생되는 Join factorization의 악영향에 주의해야 한다. 왜냐하면 :v_delimit = 1에 해당하는 SQL만 실행되어야 하지만 Join factorization으로 인해 인라인뷰 외부로 빠진 쿼리블럭은 구분자(:v_delimit )의 값에 영향을 받지 않기 때문이다.

 

그런데 Join factorization을 발생시키지 않을 목적으로 SQL 마다 rownum을 사용하는 사람이 있다. 아래의 SQL이 그것인데 그럴 필요 없다.

 

SELECT /*+ leading(j e d l) use_nl(e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> 'SA_MAN' 입력

   AND d.manager_id > 0

   AND ROWNUM > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND e.manager_id = :v_emp

   AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to

   AND d.manager_id > 0

   AND ROWNUM > 0 

   AND :v_delimit = 2

UNION ALL

중간생략

 

Rownum을 네 번 사용하면 Join factorization이 방지 되기는 하지만 SQL마다 조건절을 추가해야 하므로 막노동에 가깝고 SQL이 길어진다. 가장 쉬운 방법은 쿼리변환을 방지하는 힌트를 사용하는 것이다. 가장 위쪽 SQL의 힌트에 NO_FACTORIZE_JOIN(@SET$1)을 추가하면 된다. SQL마다 힌트를 추가할 필요는 없다. 아래의 예제를 보자.

 

SELECT /*+ leading(j e d l) use_nl(e d l) NO_FACTORIZE_JOIN(@SET$1) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> 'SA_MAN' 입력

   AND d.manager_id > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

중간생략

 

위처럼 힌트를 한번만 추가하여 쿼리변환을 방지하면 하면 불필요한 블록을 Scan하지 않으며, 개발자가 작성한 힌트를 무시하지 않는다. Oracle11g R2를 사용한다면 직접 실행계획을 확인해보기 바란다.

 

결론 

동적인 검색조건이 많지 않아 Union all을 사용할 때에도 쿼리변환을 조심해야 한다. 원하지 않는 블록을 Scan할 수 있기 때문이다. 이때 쿼리변환을 방지할 목적으로 Rownum을 사용하는 것은 좋지 않다. 왜냐하면 Join factorization을 막을 수는 있지만 또 다른 쿼리변환인 FPD(2) JPPD(3)등의 쿼리변환도 같이 막혀버린다. 따라서 NO_FACTORIZE_JOIN 힌트를 사용하는 것이 적절하다.

오라클의 버전이 올라갈수록 쿼리변환의 기능이 많아진다. 하지만 기능이 많아질수록 어두운 측면도 부각된다. 물론 쿼리변환의 문제점은 자주 발생하지는 않으며 예외적인 경우이다. 하지만 그 예외가 발생된다면 위의 SQL처럼 원하지 않을 때도 쿼리변환이 발생하여 문제가 될 것이다. 지금은 CBQT의 태동기이므로 앞으로 문제가 개선될 것으로 기대한다.  

 

검색조건이 동적으로 바뀔 때는OR로 분기하는 방법을 사용하라. 이 방법을 적절히 사용하면 Union all을 사용하는 방법의 단점인 SQL이 길어지는 것을 피할 수 있다. 또한  Dynamic SQL처럼 힌트와 where절을 동적으로 교체할 필요 없이 명시적으로 작성할 수 있다. Where 절에 OR를 사용하는 것이 항상 나쁜 것은 아니며 분명 뭔가 남다른 장점이 있다. 우리는 그 점을 이해해야 한다.  

 

1: JF(Join factorization)을 간단히 설명하면 Union / Union All 사용시 공통으로 사용하는 테이블을 분리시키는 것이다. 즉 아래와 같이 SQL1 SQL2로 변경되는 기능이다.

SQL1

SELECT /*+ USE_HASH(c s)  */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 3

UNION ALL

SELECT /*+ USE_HASH(c s) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 9 ;

 

SQL2

SELECT s.prod_id prod_id, s.cust_id cust_id, s.quantity_sold,

       s.amount_sold, vw_jf_set$0a277f6d.item_2 channel_desc

  FROM (SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 3

        UNION ALL

        SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 9) vw_jf_set$0a277f6d, --> JF 가 발생하면 인라인뷰vw_jf ~ 가 생성된다.

       sales s                                       --> sales 테이블을 인라인뷰 외부로 분리시킴

 WHERE vw_jf_set$0a277f6d.item_1 = s.channel_id ;

 

2: FPD(Filter Push Down)는 뷰/인라인뷰 외부의 조건이 뷰 내부로 파고드는 기능이다.

3: JPPD(Join Predicate Push Down)는 뷰/인라인뷰 외부의 조인조건이 뷰 내부로 파고드는 기능이다. FPD JPP의 차이는 FPD는 상수조건이 파고드는 것이며 JPPD는 조인절이 파고든다는 점이다.

참고: JF JPPD CBQT(Cost Based Query Transformation)이며 FPDHQT(Heuristic Query Transformation)이다. HQT Rule Based Query Transformation 이라고 부르기도 한다.


 

신고
Posted by extremedb

댓글을 달아 주세요

  1. Ejql 2011.01.17 16:16 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽었습니다. 감사합니다.


부제 : min/max값을 안전하고 빠르게 구하는 방법


최종일자, 최종순번을 구하기 위한 전통적인 방법은 index desc 힌트와 rownum = 1 조합이었다. 하지만 이것은 대단히 위험한 방법이다. 왜냐하면 튜닝을 하기 전에 값이 맞아야 하며, 성능튜닝은 그 이후의 문제이기 때문이다. 위의 방법은 인덱스의 구성컬럼이 변경 혹은 삭제되거나, 인덱스명이 바뀌면 max 값을 구하지 못한다. 즉 성능을 향상시키기 위해 값이 틀릴 수 있는 가능성을 열어놓은 것이다. 이런 방법은 어떤 이유로도 받아들여져서는 안 된다. 나 또한 예전에 이런 방법을 사용했지만 이는 필자의 명백한 잘못이었다.

 

올바른 값을 얻어야 하고 성능도 충족해야 하므로 오라클은 first_row(min/max) operation을 내놓았다. 따라서 우리는 index_desc + rownum 대신에 first_row(min/max)을 사용해야 한다. 그런데 항상 first_row(min/max)를 사용해야 할까? first_row(min/max)가 비효율적인 경우는 index_desc + rownum 조합을 생각해 볼 수 있다. 하지만 인덱스가 변경 및 삭제될 때 성능이 느려질지언정 답이 틀리면 안 된다. 만약 max가 아닌 잘못된 값으로 update 되었다고 상상해보라. 큰일이다. 원복시키기도 어렵다. update 가 여러번 되었을 수 있기 때문이다.

 

환경 :Oracle11g R2

 

CREATE INDEX ix_cust_channel_time ON SALES (CUST_ID, CHANNEL_ID, TIME_ID)  ;

CREATE INDEX ix_cust_time_channel ON SALES (CUST_ID, TIME_ID, CHANNEL_ID)  ;
CREATE INDEX ix_time_cust_channel ON SALES (TIME_ID, CUST_ID, CHANNEL_ID)  ;

 

인덱스와 where 조건이 완벽할 때

 

SELECT /*+ gather_plan_statistics INDEX(s ix_cust_channel_time) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust   --30777 대입

   AND channel_id = 2;

 

------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Starts | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |      1 |      1 |00:00:00.01 |       3 |

|   1 |  SORT AGGREGATE              |                      |      1 |      1 |00:00:00.01 |       3 |

|   2 |   FIRST ROW                  |                      |      1 |      1 |00:00:00.01 |       3 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_CUST_CHANNEL_TIME |      1 |      1 |00:00:00.01 |       3 |

------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

 

인덱스가 CUST_ID + CHANNEL_ID + TIME_ID로 되어 있는 경우는 문제가 없다. first_row(min/max) operation을 사용할 수 있고 비효율이 없기 때문에 값이 틀려질 수 있는 index_desc + rownum을 사용해선 안 된다.

 

where 조건에 인덱스의 중간 컬럼이 빠졌을 때  

 

SELECT /*+ gather_plan_statistics INDEX(S IX_CUST_TIME_CHANNEL) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust   --30777 대입

   AND channel_id = 2;

 

------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Starts | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |      1 |      1 |00:00:00.01 |       3 |

|   1 |  SORT AGGREGATE              |                      |      1 |      1 |00:00:00.01 |       3 |

|   2 |   FIRST ROW                  |                      |      1 |      1 |00:00:00.01 |       3 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_CUST_TIME_CHANNEL |      1 |      1 |00:00:00.01 |       3 |

------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("CUST_ID"=:V_CUST)

       filter("CHANNEL_ID"=2)

 

인덱스가 CUST_ID + TIME_ID + CHANNEL_ID 로 되어 있는 경우를 보자. 인덱스의 중간컬럼이 where절에 빠져있지만  CUST_ID의 선택도가 워낙 좋으므로 문제가 되지 않는다. first_row(min/max) operation을 그대로 사용하면 된다.

 

인덱스의 선두 컬럼이 where 조건에서 빠지는 경우

 

SELECT /*+ gather_plan_statistics INDEX(S IX_TIME_CUST_CHANNEL) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust  --30777 대입

   AND channel_id = 2;

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.02 |     755 |

|   1 |  SORT AGGREGATE             |                      |      1 |      1 |00:00:00.02 |     755 |

|   2 |   FIRST ROW                 |                      |      1 |      1 |00:00:00.02 |     755 |

|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.02 |     755 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

인덱스가 TIME_ID + CUST_ID + CHANNEL_ID로 구성되어 있을때 인덱스의 선두 컬럼이 where 조건에서 빠졌다. 그로 인해 Index full scan이 발생하여 쓸모 없는 752블록을 Scan하였다. 즉 인덱스를 끝부분부터 계속 scan하다가 운 좋게 755 블록을 scan해보니 cust_id = 30777 channel_id  = 2을 만족하는 값을 모두 처리한 것이다. 운이 나쁘면 인덱스를 모조리 읽어야 할 수도 있다.

 

서브쿼리나 인라인뷰를 이용하여 집합을 추가하자는 의견에 대해

인덱스의 선두 컬럼이 where 조건에서 빠지는 경우는 강제로 집합을 추가하자는 의견이 있다. 아래의 SQL이 그것이다.

 

SELECT  TIME_ID

  FROM  ( SELECT /*+ LEADING(C) INDEX_DESC(S IX_TIME_CUST_CHANNEL)  */  S.time_id

            FROM sales S,

                 (SELECT TRUNC(SYSDATE) - LEVEL + 1 AS time_id

                   FROM DUAL

                CONNECT BY LEVEL <= 7300 ) C

           WHERE S.cust_id = :v_cust   --30777

             AND S.channel_id = 2

             AND S.time_id = C.time_id )

 WHERE ROWNUM = 1;

 

---------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name                 | Starts | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |                      |      1 |      1 |00:00:00.02 |     512 |

|*  1 |  COUNT STOPKEY                  |                      |      1 |      1 |00:00:00.02 |     512 |

|   2 |   NESTED LOOPS                  |                      |      1 |      1 |00:00:00.02 |     512 |

|   3 |    VIEW                         |                      |      1 |   3484 |00:00:00.02 |       0 |

|   4 |     CONNECT BY WITHOUT FILTERING|                      |      1 |   3484 |00:00:00.01 |       0 |

|   5 |      FAST DUAL                  |                      |      1 |      1 |00:00:00.01 |       0 |

|*  6 |    INDEX RANGE SCAN DESCENDING  | IX_TIME_CUST_CHANNEL |   3484 |      1 |00:00:00.01 |     512 |

---------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("C"."TIME_ID") AND "S"."CUST_ID"=:V_CUST AND

              "S"."CHANNEL_ID"=2)

 

이렇게 하니 Scan한 블럭수가 1/3 정도 줄어들었다. 하지만 불필요한 조인이 3484번이나 발생하였다. 이것이 최적은 아니다. 또한 명시적으로 max값을 보장하게 작성된 SQL도 아니다.

 

Index_ss 힌트를 사용했다. 하지만……

위의 예에서 보듯이 인덱스의 선두 컬럼이 조건절에 없을때 이빨이 빠진 집합을 추가하는 것과 first_row(min/max)를 사용하는 것은 둘다 비효율적이다. 그러므로 index_desc + rownum을 사용하되 값이 바뀌지 않도록 해야 한다. 그런데 인덱스의 첫 번째 컬럼이 조건 절에서 빠졌으므로 index_ss_desc + rownum을 사용해야 한다. 이것이 가능할까? SQL을 바꾸지 않으면 불가능하다. 아래의 SQL을 보자.

 

SELECT /*+ gather_plan_statistics INDEX_SS(s ix_time_cust_channel) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust     --30777

   AND channel_id = 2;

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.02 |     755 |

|   1 |  SORT AGGREGATE             |                      |      1 |      1 |00:00:00.02 |     755 |

|   2 |   FIRST ROW                 |                      |      1 |      1 |00:00:00.02 |     755 |

|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.02 |     755 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

Min/Max Index Skip Scan을 동시에 사용할 수 없다

min 혹은 max 함수를 사용했을 때 Oracle9i 버전과는 달리 10g 11g에서는 index skip scan을 사용할 수 없다. 힌트를 추가해도 마찬가지이다. 아래의 10053 trace를 보자.

 

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for SALES[S]

  ColGroup (#1, Index) IX_TIME_CUST_CHANNEL

    Col#: 2 3 4    CorStregth: 185.95

  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:

  Table: SALES  Alias: S

    Card: Original: 918843.000000  Rounded: 33  Computed: 32.54  Non Adjusted: 32.54

kkofmx: index filter:"S"."CUST_ID"=:B1

 

kkofmx: index filter:"S"."CHANNEL_ID"=2

 

  Access Path: index (Min/Max)

    Index: IX_TIME_CUST_CHANNEL

    resc_io: 3.00  resc_cpu: 21564

    ix_sel: 1.000000  ix_sel_with_filters: 0.000035

 ***** Logdef predicate Adjustment ******

 Final IO cst 0.00 , CPU cst 50.00

 ***** End Logdef Adjustment ******

 ***** Logdef predicate Adjustment ******

 Final IO cst 0.00 , CPU cst 50.01

 ***** End Logdef Adjustment ******

    Cost: 5.28  Resp: 5.28  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IX_TIME_CUST_CHANNEL

         Cost: 5.28  Degree: 1  Resp: 5.28  Card: 1.00  Bytes: 0

***************************************

 

힌트를 사용했지만 Index Skip Scan은 고려조차 되지 않는다. 위의 Trace를 보면 "first row(Min/Max)가 가능하다면 Index Skip Scan을 고려하지 않는 로직이 10g 11g의 옵티마이져에 존재한다라고 추론할 수 있다. 인덱스와 where 절이 일치하지 않는 상태라 하더라도 비효율적인 index full scan (Min/Max)에 만족할 수는 없다. 바로 이럴 때 index_desc rownum 조합을 답이 틀려질 수 없도록 사용하면 된다.

 

아래처럼 max 함수를 제거하면 Index Skip Scan을 사용할 수는 있다.

 

SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */

       time_id

  FROM sales s

 WHERE cust_id = :v_cust  --30777

   AND channel_id = 2

   AND ROWNUM = 1;

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.01 |     264 |

|*  1 |  COUNT STOPKEY              |                      |      1 |      1 |00:00:00.01 |     264 |

|*  2 |   INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.01 |     264 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   2 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

       filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

Max를 없애면 index skip scan을 사용할 수 있다. 하지만.....
블록수가 755에서 264 1/3으로 줄어들었다. 하지만 인덱스가 수정 및 삭제되면 답이 틀릴 수 있으므로 위험하긴 마찬가지 이다. 따라서 다음의 SQL처럼 사용해야 한다.

 

안정적이고 성능을 고려한 SQL  

 

SELECT MAX(time_id)

  FROM  ( SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */ time_id

            FROM sales S

           WHERE cust_id = :v_cust                                             --30777

             AND channel_id = 2

           ORDER BY time_id DESC)

 WHERE ROWNUM = 1; 

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name                 | Starts | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                      |      1 |      1 |00:00:00.01 |     264 |

|   1 |  SORT AGGREGATE               |                      |      1 |      1 |00:00:00.01 |     264 |

|*  2 |   COUNT STOPKEY               |                      |      1 |      1 |00:00:00.01 |     264 |

|   3 |    VIEW                       |                      |      1 |      1 |00:00:00.01 |     264 |

|*  4 |     INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.01 |     264 |

-------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM=1)

   4 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

       filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

ORDER BY를 사용했지만 인덱스의 영향으로 SORT를 하지 않으므로 성능저하도 없다. 또한 인라인뷰 내에서 ORDER BY를 사용하고 외부에서 ROWNUM을 사용했기 때문에 인덱스가 수정 및 삭제되더라도 성능이 느려질 뿐 값이 틀려질 수는 없다. 위의 SQL에서 마지막에 max 함수를 사용한 이유는 where조건에 만족하는 건수가 없더라도 null을 출력해야하기 때문이다. 앞으로 index_desc + rownum 조합을 사용할 것이라면 위의 방법을 사용하길 바란다.
 

결론

1. Index_desc + rownum을 사용하지 말고 first_row(min/max)를 사용하라

2. 1번이 비효율적인 경우에만 index_desc(혹은 index_ss_desc) + order by를 사용하고 뷰로 감싸라.
    그리고 뷰 외부에서 rownum을 사용하라.

3. first_row(min/max)를 사용할 수 있는 환경에서는 index skip scan을 사용할 수 없다. 꼭 사용하려면 집계 함수를 제거하라.

이렇게 하면 성능과 안정성을 동시에 고려할 수 있다.

 

글의 배경
이런 이야기를 하는 이유는 비판적 사고의 필요성 때문이다. 개발자에게 인라인뷰와 Order by가 없는 Index_desc + Rownum의 위험성을 설명해주었더니 나에게 책을 가져온다. 나는 본적이 없지만 아주 좋은 SQL 튜닝 책이라고 한다. 그것도 어려운 영문 책이다. 열심히 공부하는 사람임에 틀림없다. 하지만 개발자의 한마디 때문에 그사람의 인상이 바뀌어 버렸다. 그 한마디는 이 책에 Index_desc + Rownum을 사용하라고 되어있습니다.” 였다. 그것이 얼마나 위험한 것인지 여러번 증명하고 설득해 보았으나 맘을 바꾸기는 불가능 하였다. 이래서는 곤란하다. 책을 성경이나 불경처럼 여기고, 저자를 종교의 교주로 여겨서는 안 된다. 이론은 반론이 증명되면 폐기될 수 있다. 하지만 가치관이 개입된 믿음이나 신념은 좀처럼 바뀌지 않는다. 어떠한 증거를 내놓아도 그렇다. 신념은 종교생활에 사용했으면 한다.


책의 내용 중에 잘못된 것이 있으니 나쁜 책이라고 말하는게 아니다. 필자의 서적을 포함해서 모든 책의 내용은 틀릴 수 있다. 정작 나쁜 것은 책이나 저자가 종교화될 때이다. 그런 무 비판적 종교는 이공계 사람에게 치명적이다. 비판적 사고는 엔지니어와 과학자의 버팀목이자 과학기술을 발전시키는 핵심이기 때문이다. 널리 알려진 과학 논쟁인 쿤과 포퍼의 대결에서도 이러한 언급은 드러난다. 두명 모두 비판적 사고는 반드시 필요하다고 하였다. 다만 시기의 문제일 뿐이다.

답답한 마음에 글을 올려보았다. 앞으로 이런 글을 다시 쓰고 싶지 않다.

"신앙은 믿음으로 이루져야한다. 하지만 과학은 비판과 증명으로 이루어져야 한다." 


신고
Posted by extremedb

댓글을 달아 주세요

  1. 왕만두 2010.11.09 15:44 신고  댓글주소  수정/삭제  댓글쓰기

    First Rows 가 나온지 수년이 지났지만 아직도 개발자들이 index_desc 를 사용하는 경향이 매우 높더군요.
    아마도 copy & paster 의 잔재가 아닌가 싶습니다.
    오수석님같은 분들께서 이런 글을 자주 연재해주고면서 분위기를 만들어가야 개발자들이 조금씩 변할것 같습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.11.09 17:14 신고  댓글주소  수정/삭제

      왕만두님 반갑습니다.
      index_desc + rownum 조합은 아직도 많이 사용합니다.
      copy의 영향도 있을 것입니다.
      위험성을 알리는게 급선무입니다.^^

  2. 라튜니 2010.11.10 12:32 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 정보 감사합니다.

    비슷한 사례로
    페이징 처리(ROWNUM) 시 인라인 뷰안에서 ROWNUM으로 건수를 제한할 경우도
    같은 경우로 볼 수 있겠네요. 인덱스가 변경되거나 삭제될 경우 정렬순서를 보장할 수 가 없으니까요.

    인덱스를 사용하여 정렬을 대신하는 경우라도 페이징 처리시 인라인 뷰에서 반드시 ORDER BY를 명시하고
    ROWNUM은 인라인뷰 밖에서 WHERE 조건으로 처리토록 하여야 할 것 같습니다.
    TOP-N 쿼리와 같은 형식으로 말이죠. 실제 오라클은 정렬을 대신하는 인덱스가 있다면 OBYE 로 정렬을 제거하고
    TOP-N 처리가 아닌 페이징 처리로 처리를 할 테니까요.
    만약 정렬을 대신하는 인덱스가 없다면 정렬을 할 것이고 TOP-N쿼리로 처리될 테니까 말이죠.
    (물론 정렬부하 때문에 처리시간은 더 소요가 되겠지요)

    결론적으로 어떠한 경우에도 Hint 여부 때문에 최종 결과SET이 달라져서는 안되도록 쿼리를 작성하는 습관을 가져야 할 거 같습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.11.10 23:16 신고  댓글주소  수정/삭제

      라튜니님 처음 뵙네요.
      말씀하신대로 페이징 처리할 때도 같은 원리를 적용해야 안전할 것입니다.
      OBYE라고 하시는걸 보니 logical optimizer를 잘 아시는 분 같습니다.

  3. salvation 2010.11.15 09:21 신고  댓글주소  수정/삭제  댓글쓰기

    앞으로 위의 케이스안에서도 min/max가 일어나지 않는 케이스를 다루면 더욱 좋을거 같습니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.11.15 09:48 신고  댓글주소  수정/삭제

      min/max 가 발생하지 않는 케이스에도 order by를 사용하고 뷰로 감싸고 뷰 외부에서 rownum을 사용하는 방법을 쓰면 해결이 가능합니다.

      본문의 세가지 케이스는 모두 min/max 가 발생한 케이스 네요. min/max 가 발생하지 않는 케이스를 고려하지 않았군요. 좋은 의견 감사합니다.

  4. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.05.13 09:53 신고  댓글주소  수정/삭제  댓글쓰기

    저희 회사 상사분께서 항상 애기하시던게 생각나네요

    내가 한 거 곧이 곧대로 믿지 말라고..

    믿지 않으려면 의심을 해야 하고, 한번 더 머리속에서 정리를 통해 완전히 이해됐고, 제 스스로 판단했을 때도 맞을 때만 믿게 되더라구요.

  5. feelie 2011.07.22 17:32 신고  댓글주소  수정/삭제  댓글쓰기

    first_rows operation 이 안나와서 이런저런 테스트를 해봤습니다.
    1. 인덱스 스캔만 한경우에 first_rows operation 이 나옴
    2. 인덱스 스캔-table access을 한경우는 first_rows operation 이 안나옴
    인덱스 스캔인 경우만 first_rows operation 이 나오는건가요???

    테이블스 Access을 해도 first_rows operation이 나오는데 문제가 없을것 같은데요...

  6. JK 2011.07.28 23:00 신고  댓글주소  수정/삭제  댓글쓰기

    저도 얼마전에 index_desc + rownum 을 활용하여 튜닝을 한 경험이 있었습니다. 이 튜닝이 얼마나 위험성이 있는지를 알려주셔서 감사 합니다. 좋은 것 하나 배워갑니다.

  7. 구로동 2011.09.01 08:50 신고  댓글주소  수정/삭제  댓글쓰기

    dba출신 pm이 항상 index_desc + rownum 으로 튜닝하는 모습을 종종 보고 따라했는데 위험한 것이었군요.
    실험해보니 실제 값이 잘못 나오는 것을 눈으로 확인했습니다.
    감사합니다 잘배워갑니다.

  8. 김영석 2013.05.04 10:15 신고  댓글주소  수정/삭제  댓글쓰기

    index_desc + rownum 구문을 보면서 저도 동일한 우려를 했는데,
    이렇게 해답을 제시해 주셔서 감사합니다.

  9. Favicon of http://tastegod.co.kr BlogIcon TasteGod 2017.06.16 09:17 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 질문 좀 드려도 될까요? IX_TIME_CUST_CHANNEL 생성시 time_id 에 desc 옵션을 안주고 생성해도 되는것이죠?

부제: Cardinality Feed Back의 개념과 사용예제

이번 글은 난이도가 높으므로 익숙하지 않은 사람은 Cardinality Feedback의 개념 정도만 이해하기 바란다. 물론 이 블로그를 꾸준히 구독한 독자라면 어려움 없이 볼 수 있다.

 

현재 많은 시스템이 Oracle11g로 옮겨가고 있다. 11g는 새로운 기능이 많이 추가되었다. 하지만 새롭고 좋은 기능이라도 완벽하지 못하면 문제가 될 수 있다. 오늘은 11g의 새 기능 때문에 성능문제가 발생하는 경우를 소개한다.

시스템이 운영 중에 있을 때 가장 곤욕스러운 경우 중 하나는 SQL의 실행계획이 갑자기 바뀌어 성능이 나빠지는 것이다. SQL과 인덱스 그리고 통계정보가 모두 바뀌지 않아도 실행계획은 바뀔 수 있다. 예를 들면 Oracle11g의 기능인 Cardinality Feedback을 사용함으로 해서 얼마든지 실행계획이 바뀔 수 있는 것이다. 이번 시간에는 실행계획이 변경되는 원인 중 하나인 Cardinality Feedback 의 개념과 작동방식에 대해 알아보고 이것이 언제 문제가 되는지 분석해 보자. 이번에 소개할 예제는 종합적이다. Cardinality Feedback + Cost Based Query Transformation + Bloom Filter가 결합된 것이다. 이를 놓친다면 이들이 어떻게 결합되는지 알 수 없을 뿐만 아니라 성능이 악화된 원인을 파악할 수 없다.

 

예측, 실행, 비교, 그리고 전달

소 잃고 외양간 고친다는 말이 있다. 이미 늦었다는 이야기 이지만 좋은 말로 바꾸면 실수를 다시 하지 않겠다는 의지이다. cardinality feedback(이후 CF)도 이와 비슷한 개념이다. 예를 들어 col1 = ‘1’ 이라는 조건으로 filter되면 백만 건이 return된다고 옵티마이져가 예측해서 full table scan을 했다. 하지만 예측과 달리 실행결과가 100건이 나왔다면? 해당 SQL을 다시 실행할 때는 full table scan보다는 index scan이 유리할 것이다. 그런데 같은 SQL을 두 번째 실행할 때 "실제로는 백만 건이 아니라 100건 뿐이야"라는 정보를 옵티마이져에게 알려주는 전달자가 필요하다. 그 전달자가 바로 CF이다. CF가 없으면 결과가 100건 임에도 SQL을 실행 할 때마다 full table scan을 반복할 것이다. 결국 CF는 악성 실행계획을 올바로 수정하는 것이 목적이며 매우 유용한 기능임을 알 수 있다. CF의 단점은 최초에 한번은 full table scan이 필요하다는 것이다. 왜냐하면 실행해서 결과가 나와야만 실제 분포도(건수)를 알 수 있기 때문이다.

 

CF는 어떻게 실행되나?

CF는 같은 SQL을 두 번 이상 실행했을 때 적용된다. 그 이유는 아래의 CF 적용순서를 보면 알 수 있다.

1. 최초의 실행계획을 작성할 때(Hard Parsing 시에) 예측 분포도가 계산된다.

2. SQL이 실행된다. 한번은 실행 해봐야 예측 분포도와 실제 분포도를 비교할 수 있다.

3. 예측 분포도와 실제 분포도의 값이 차이가 크다면 실제 분포도를 저장한다.

4. 두 번째 실행될 때 CF에 의해 힌트의 형태로 옵티마이져에게 전달되어 실제 분포도가 적용된다. 이때 분포도뿐만 아니라 실행계획이 바뀔 수 있다. 두 번째 이후로 실행될 때는 CF가 계속 적용된다.

 

CF를 발생시켜보자

실행환경 :Oracle 11.2.0.1

 

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SESSION SET "_OPTIMIZER_USE_FEEDBACK" = TRUE; -- CF를 활성화 한다. default true이다.

 

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(c)  */

       c.cust_id, c.cust_first_name, c.cust_last_name,

       s.prod_cnt, s.channel_cnt, s.tot_amt

  FROM customers c,

       (SELECT   s.cust_id,

                 COUNT (DISTINCT s.prod_id) AS prod_cnt,

                 COUNT (DISTINCT s.channel_id) AS channel_cnt,

                 SUM (s.amount_sold) AS tot_amt

            FROM sales s

        GROUP BY s.cust_id) s

 WHERE c.cust_year_of_birth = 1987

   AND s.cust_id = c.cust_id ;

 

----------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name              | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                   |        |     23 |00:00:00.15 |    5075 |          |

|*  1 |  HASH JOIN                     |                   |    162 |     23 |00:00:00.15 |    5075 | 1215K (0)|

|   2 |   JOIN FILTER CREATE           | :BF0000           |    162 |    151 |00:00:00.01 |     148 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS         |    162 |    151 |00:00:00.01 |     148 |          |

|   4 |     BITMAP CONVERSION TO ROWIDS|                   |        |    151 |00:00:00.01 |       2 |          |

|*  5 |      BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |        |      1 |00:00:00.01 |       2 |          |

|   6 |   VIEW                         |                   |   7059 |     55 |00:00:00.15 |    4927 |          |

|   7 |    SORT GROUP BY               |                   |   7059 |     55 |00:00:00.15 |    4927 |88064  (0)|

|   8 |     JOIN FILTER USE            | :BF0000           |    918K|   7979 |00:00:00.12 |    4927 |          |

|   9 |      PARTITION RANGE ALL       |                   |    918K|   7979 |00:00:00.11 |    4927 |          |

|* 10 |       TABLE ACCESS FULL        | SALES             |    918K|   7979 |00:00:00.09 |    4927 |          |

----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):        

---------------------------------------------------        

   1 - access("S"."CUST_ID"="C"."CUST_ID")               

   5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)               

  10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID")) --> Bloom Filter 적용     

                

SQL 실행결과 sales 테이블의 예측 분포도는 918K건이며 실제 분포도는 Bloom Filter가 적용되어 7979건이다. 그리고 group by operation(ID 7)의 예측 분포도는 7059건이며 실제 분포도는 55건이다. 예측과 실제의 분포도 차이는 두 경우 모두 100배 이상이다. 따라서 CF가 적용될 것이다. 이와는 반대로 customers 테이블의 예측 분포도와 실제 분포도는 162 152로 크게 다르지 않으므로 CF가 적용되지 않을 것이다. 이제 위의 SQL을 재 실행한다면 CF가 적용되어 실제 분포도가 적용될 것이다.

 

--> CF를 발생시키기 위해 위의 SQL 다시 실행               

                

----------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name              | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                   |        |     23 |00:00:05.61 |    5075 |          |

|   1 |  SORT GROUP BY                 |                   |     55 |     23 |00:00:05.61 |    5075 |75776  (0)|

|*  2 |   HASH JOIN                    |                   |    270 |   3230 |00:00:05.60 |    5075 | 1201K (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS         |    162 |    151 |00:00:00.01 |     148 |          |

|   4 |     BITMAP CONVERSION TO ROWIDS|                   |        |    151 |00:00:00.01 |       2 |          |

|*  5 |      BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |        |      1 |00:00:00.01 |       2 |          |

|   6 |    PARTITION RANGE ALL         |                   |   7979 |    918K|00:00:02.82 |    4927 |          |

|   7 |     TABLE ACCESS FULL          | SALES             |   7979 |    918K|00:00:00.98 |    4927 |          |

----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):               

---------------------------------------------------

   2 - access("S"."CUST_ID"="C"."CUST_ID")

   5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

 

Note

-----

   - cardinality feedback used for this statement --> CF가 발생되었음을 나타냄.

 

두 번째 실행 할 때 CF가 적용되어 예측 분포도가 7979로 바뀌었고 group by 분포도는 55건으로 바뀌었다. 이에 따라 실행계획도 바뀌었다. CF에 의해서 쿼리변환(Complex View Merging)이 발생된 것이다. 그리고 note CF가 적용되었다고 친절히 설명된다.

 

이제 더 자세한 분석을 위하여 10053 Trace의 내용을 보자. 두 번째 실행된 SQL 10053 Trace에 따르면 쿼리변환전의 SQL은 다음과 같다.

 

SELECT /*+ LEADING (C) */

       c.cust_id, c.cust_first_name, c.cust_last_name,

       s.prod_cnt, s.channel_cnt, s.tot_amt tot_amt

  FROM tlo.customers c,

       (SELECT   /*+ OPT_ESTIMATE (GROUP_BY ROWS=55.000000 ) OPT_ESTIMATE (TABLE S ROWS=7979.000000 ) */

                 s.cust_id cust_id, COUNT (DISTINCT s.prod_id) prod_cnt,

                 COUNT (DISTINCT s.channel_id) channel_cnt, SUM (s.amount_sold) tot_amt

            FROM tlo.sales s

        GROUP BY s.cust_id) s

 WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

 

CF에 의해서 OPT_ESTIMATE 힌트가 적용되었다. 실제 건수로 적용하는 것이므로 일견 문제가 없어 보인다. 하지만 쿼리변환과정(Complex View Merging)을 거치면 문제가 생긴다. 10053 Trace에서 나타난 쿼리변환 후의 SQL은 다음과 같다.

 

SELECT   /*+ OPT_ESTIMATE (GROUP_BY ROWS=55.000000 ) LEADING (C) OPT_ESTIMATE (TABLE S ROWS=7979.000000 ) */

         c.cust_id cust_id, c.cust_first_name cust_first_name,

         c.cust_last_name cust_last_name, COUNT (DISTINCT s.prod_id) prod_cnt,

         COUNT (DISTINCT s.channel_id) channel_cnt, SUM (s.amount_sold) tot_amt

    FROM tlo.customers c, tlo.sales s

   WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id

GROUP BY s.cust_id, c.ROWID, c.cust_last_name, c.cust_first_name, c.cust_id ;

 

CF의 문제점은?

위의 SQL은 두 가지 문제점이 있다. 두 문제 모두 쿼리변환에 의해 발생된다. 첫 번째 문제는 Bloom Filter와 관련된 것이다. CF의 영향으로 원본 SQL에 존재했던 Group By (Complex View)가 사라졌다. 뷰가 없어짐으로써 Bloom Filter가 적용되지 않는다. Filter가 사라졌음에도 불구하고 Filter가 존재했던 Cardinality 7979를 적용해 버렸다. 이에 따라 CF를 적용했음에도 7979건과 실제건수인 91 8천 건과는 엄청난 차이가 나고 말았다. Bloom Filter가 사라질 때는 CF를 적용하면 안 된다는 이야기이다. 비유하자면 Filter가 없는데도 불구하고 Filter가 존재할 때의 건수를 적용시킨 것이다.

 

두 번째 문제는 쿼리변환 후 힌트의 상속과 관련된다. 쿼리변환전의 CF의 의한 힌트를 보면 Group By된 뷰의 건수는 55건이다. 그런데 이 힌트는 오직 sales 테이블에 대한 것이다. 그런데 쿼리변환후의 힌트를 보면 그대로 55건이 적용되어 되어버렸다. Group by가 외부로 빠져 나옴으로 해서 GROUP_BY ROWS는 전체건수와 마찬가지가 되어버렸다. sales 테이블의 Group By건수는 55건이 맞다. 하지만 쿼리변환 때문에 조인 후에 Group By 하게 된다면 cardinality를 다시 계산해야 한다. 조인이 없는 테이블의 Group By건수와 조인후의 Group By건수가 어떻게 같을 수 있나?

 

두 가지의 문제점은 Cost를 계산할 때 그대로 적용되어 버린다. 10053 trace를 보자.

 

Access path analysis for SALES

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for SALES[S]

  Table: SALES  Alias: S

    Card: Original: 918843.000000    >> Single Tab Card adjusted from:918843.000000 to:7979.000000

  Rounded: 7979  Computed: 7979.00  Non Adjusted: 918843.00

  Access Path: TableScan

    Cost:  1328.68  Resp: 1328.68  Degree: 0

      Cost_io: 1321.00  Cost_cpu: 155262306

      Resp_io: 1321.00  Resp_cpu: 155262306

 

Bloom Filter가 없음에도 불구하고 Sales 테이블의 건수(Cardinality) 7979로 적용되어 버렸다. 이제 Group By가 적용된 건수를 보자.

 

GROUP BY cardinality:  270.000000, TABLE cardinality:  270.000000

>> Query Blk Card adjusted from 270.000000  to: 55.000000

    SORT ressource         Sort statistics

      Sort width:         583 Area size:      510976 Max Area size:   102340608

      Degree:               1

      Blocks to Sort: 3 Row size:     69 Total Rows:            270

      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0

      Total IO sort cost: 0      Total CPU sort cost: 20302068

      Total Temp space used: 0


Group By Cardinality와 관련된 Trace 내용이다. 여기서도 잘못된 Group By건수인 55를 적용시키고 있다. 조인 후에 Group By할 때는 Cardinality를 다시 계산해야 옳다. 이래서는 제대로 된 Cost가 나올 수 없다. 여기에 밝혀진 문제점은 SQL 하나에서 나온 것이므로 실전에서는 두 가지 문제뿐만 아니라 더 많을 것이다. 물론 옵티마이져가 모든 경우에 완벽할 수는 없다.


해결책
CF
문제의 해결방법을 생각해보자. 갑자기 실행계획이 바뀌어 성능문제가 발생했을 때 dbms_xplan.display_cursor의 note나 10053 Trace의 실행계획 부분을 보면 CF가 적용되었는지 아닌지 알 수 있다. 만약 CF가 적용되었다면 일단 의심해보아야 한다. 아래는 10053 trace의 실행계획 부분이다.

-----------------------------------------------------------+------------------------

| Id  | Operation                       | Name             | Rows  | Bytes | Cost  |

-----------------------------------------------------------+------------------------

| 0   | SELECT STATEMENT                |                  |       |       |  1368 |

| 1   |  SORT GROUP BY                  |                  |    55 |  2915 |  1368 |

| 2   |   HASH JOIN                     |                  |   270 |   14K |  1367 |

| 3   |    TABLE ACCESS BY INDEX ROWID  | CUSTOMERS        |   162 |  5832 |    38 |

| 4   |     BITMAP CONVERSION TO ROWIDS |                  |       |       |       |

| 5   |      BITMAP INDEX SINGLE VALUE  | CUSTOMERS_YOB_BIX|       |       |       |

| 6   |    PARTITION RANGE ALL          |                  |  7979 |  132K |  1329 |

| 7   |     TABLE ACCESS FULL           | SALES            |  7979 |  132K |  1329 |

-----------------------------------------------------------+------------------------

Predicate Information:

----------------------

2 - access("S"."CUST_ID"="C"."CUST_ID")

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

 

Content of other_xml column

===========================

nodeid/pflags: 7 17nodeid/pflags: 6 17  cardinality_feedback: yes --> CF가 적용됨

...이후 생략


만약 CF가 문제가 된다면 해당 SQL을 시작하기 전에 세션단위로 _optimizer_use_feedback = false를 적용하거나 opt_param 힌트를 사용하면 된다. 이렇게 하면 CF가 방지되어 쿼리변환의 원인이 제거된다. 따라서 Bloom Filter도 보존할 수 있다. 또 다른 방법은 인라인뷰에 no_merge 힌트를 적용하여 쿼리변환을 방지하면 문제는 해결된다. 이 두 가지 방법은 결국 쿼리변환을 방지하는 것이다.

 

결론

CF란 건수를 예측하고, 실행해서 실제건수와 예측건수를 비교하여 차이가 많다면 다음 번에 실행할 때 옵티마이져에게 실제건수를 전달해주는 역할을 한다. CF의 개념을 정리 했으므로 이제 큰 그림을 그려보자. 위의 예제에서 성능이 악화된 직접적인 이유는 Bloom Filter가 사라졌기 때문이다. 하지만 그렇게 된 이유는 쿼리변환 때문이며 쿼리변환의 이유는 CF 때문이다. 직접적인 원인을 찾았다고 해도 포기해선 안 된다. 꼬리에 꼬리를 무는 원인이 있을 수 있기 때문이다. 이를 도식화 하면 다음과 같다.

사용자 삽입 이미지


옵티마이져의 설계관점에서 개선해야 될 사항을 논의 해보자. 옵티마이져가 CBQT를 고려할 때는 두 가지의 경우로 판단한다. 쿼리변환을 적용하기 전(Iteration 1) Cost와 적용 후(Iteration 2) Cost를 비교해야 되기 때문이다. 쿼리변환전의 Cost를 구할 때는 CF를 적용시키고 반대로 쿼리변환 후에는 CF를 적용하지 않는 것이 더 좋은 Cost를 구할 수 있다. 왜냐하면 비록 답이 같다고 하더라도 형태가 전혀 다른 SQL에 대해 CF를 적용시킬 이유는 없기 때문이다. 물론 이렇게 해도 여전히 문제가 될 수는 있다. 하지만 문제의 발생확률은 많이 줄어들지 않겠는가?

 


신고
Posted by extremedb

댓글을 달아 주세요

  1. 윤상원 2010.10.25 16:15 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 정보 감사합니다.
    글을 읽고 궁금한 점이 있어 이렇게 질문을 남깁니다.
    bind 변수를 사용할 경우는 Cardinality Feedback이 적용되지 않나요?
    bind 변수를 사용할 경우는 실행할 때마다 결과가 달라지므로 Cardinality Feedback이 적용되기 어려울꺼 같은데요.
    bind 변수를 사용할 경우는 Adaptive Cursor Sharing가 적용되는 것이고 상수를 사용할 경우는
    Cardinality Feedback이 적용되는 것인가요?
    답변 부탁드립니다~

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.10.25 18:09 신고  댓글주소  수정/삭제

      상수를 변수로 바꾸어 테스트 해도 똑같이 cf가 발생합니다.
      하지만 변수값이 바뀌었을 때는 좀더 깊은 연구가 필요합니다.
      감사합니다.

  2. salvation 2010.10.25 16:15 신고  댓글주소  수정/삭제  댓글쓰기

    글 시작할때 어렵다고 하셨는데 전혀 어렵지않으면 어느정도 수준인가요? 자랑이 아니라 정말궁금...

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.10.26 00:05 신고  댓글주소  수정/삭제

      1.CF
      2.Query Transformation
      3.Bloom Filter

      1,2,3 을 다 안다면 어렵지 않습니다.
      만약 컨설턴트라면 수준이 높다고 할 수 없지만
      DBA라면 옵티마이져를 어느정도 아시는 분이라고 할 수 있습니다. 즉 개발자, DBA, 컨설턴트는 기대치가 다르므로 다르게 판단되어야 할 것 같습니다.

    • 혈기린 2010.10.26 15:24 신고  댓글주소  수정/삭제

      컨설턴트가 될려면 어느정도의 수준이 되어야 할까요?
      멀고먼 길이네요 -.-;;

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.10.26 19:16 신고  댓글주소  수정/삭제

      어려운 질문입니다.
      기술은 기본만 되어 있다면 가능합니다. 제가 컨설팅을 시작할 당시(지금도 마찬가지 이지만)는 실력이 좋지 않았습니다. 실력이란 목표가 있으면 따라 옵니다.
      구체적인 목표가 있어야 훌륭한 컨설턴트가 될 것 같습니다.

      많은 분들이 열정을 이야기 하시는데 열정은 불꽃 같은 것이라 사그라 들 수 있습니다. 큰 목표하나를 세우시고 세부적인 목표를 세우신다면 좋은 성과가 있을것 입니다. 큰 목표가 40 이나 50세에 뭘 하고 있을건지 에 대한 대답이라면 세부적인 목표는 큰꿈을 이루기 위해 이번주에 할일이 무엇인지를 묻는것 입니다. 목표가 세워지면 열정은 따라 옵니다. 도움 되셨나요?

    • 혈기린 2010.10.27 16:02 신고  댓글주소  수정/삭제

      조언 감사 드립니다
      목표를 세워서 열씨미 노력해야겠네요 ^^

  3. salvation 2010.10.25 20:49 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사합니다. 위의 댓글은 갤럭시S로 쓴겁니다. ㅎㅎ
    저는 개발자도, DBA도, 컨설턴트도 아닌... 어중이 떠중이 입니다.
    저의 수준에 의심이 들어 질문을 던진 것 이였습니다.

    상수를 변수로 바꾸었을때에 CF가 발생한다 말씀 하셨는데..
    바인드 피킹이 off인 상태에서는 발생하지 않을것으로 보이는데..
    바인드 피킹이 on 상태라면 결국 CF라는게 결국 어느 한범주에 속할거 같다는 생각이 듭니다.
    Adaptive Cursor Sharing, CF 이런게 결국 어느 한 범주에 속하는 것이 아닐까요?
    말 그대로 logical optimizer라 하면 너무 큰 범주구요..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.10.26 01:01 신고  댓글주소  수정/삭제

      Adaptive Cursor Sharing, CF, Dynamic Sampling 등은 Physical 옵티마이져를 보완합니다. SQl Plan Baseline, SQL Profile, Stored OutLine 이놈들은 실행계획을 고정시키는 놈들입니다.

      옵티마이져의 기능이라고 볼 수도 있겠지만 옵티마이져의 실수를 막아주는 모듈이라고 보는 것이 더 정확합니다. 이 모든 것은 옵티마이져 서포터즈들 입니다. 즉 옵티마이져는 Logicl + Physical 이 존재하는 상태이지만 옵티마이져가 완벽하지 못하므로 이를 보완하는 것들 입니다. 하지만 아이러니 하게도 서포터즈들이 문제를 일으킬 수 도 있다는 겁니다.^^

      위의 놈들 중에서 11g의 SQl Plan Baseline이 가장 강력합니다. Adaptive Cursor Sharing, CF, Dynamic Sampling 등이 실행계획을 변경시킬 수 있지만 SQl Plan Baseline이라는 최후의 보루를 두어서 안심할 수 있을것입니다.

  4. ExtraOdinary 2010.11.03 15:52 신고  댓글주소  수정/삭제  댓글쓰기

    아직 11g로 운영중인 사이트가 별로 없었는데, 동규님 블로그를 통해 11g의 좋은 기능들과 함정(?)들을 미리 이해할 수 있어 항상 감사합니다.


책 (The Logical Optimizer)의 Part 4에 대한 PPT가 완성되었다. 이제 본문의 모든 내용이 PDF로 요약 되었다. 책을 쓴 저자의 의무를 어느 정도 한것 같다.

Part 4는 CBQT (Cost Based Query Transformation)의 내부원리에 대한 내용이다. 즉 쿼리변환(Query Transformation)에 대한 내용이 아니라 옵티마이져의 원리에 대한 내용이다. 본문 내용중에서 가장 난위도가 있는 부분이기도 하다.

사용자 삽입 이미지
사용자 삽입 이미지


Tstory의 용량제한 때문에 할 수 없이 파일을 2개로 나눠(분할압축) 올린다.

압축  프로그램 7zip

THE LOGICAL OPTIMIZER (양장)
국내도서>컴퓨터/인터넷
저자 : 오동규
출판 : 오픈메이드 2010.04.05
상세보기



저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 리베 2010.10.04 10:35 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 자료 감사합니다. 오동규님 덕분에 실력이 쑤~~~욱 올라가고 있는듯... ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.10.04 12:38 신고  댓글주소  수정/삭제

      안녕하세요. 리베님
      실력이 향상되었다면 참으로 다행스런 일 입니다.
      제가 이제 좀 쉬었으니 슬슬 다음 주제를 준비해야 할 단계가 온것 같습니다.^^

  2. feelie 2010.10.07 12:39 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 감사합니다

  3. 김시연 2010.10.26 14:15 신고  댓글주소  수정/삭제  댓글쓰기

    오늘 컨설팅 복귀하고, 자료 다운받아서 쭉 보고 있습니다. PPT 만드는게 보통일이 아닌데, 수고 많으셨습니다.
    그리고 혹시 Logical Optimizer에 대한 세미나나 교육 계획이 있으신가요?
    그럼 갑자기 추워진 날씨에 감기 조심하세요~!

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.10.26 14:53 신고  댓글주소  수정/삭제

      시연님 오랜만 입니다. 복귀하셨군요. 고생하셨습니다. 교육에 관하여 말씀 드리겠습니다.
      올해부터 HP 교육센터를 오픈메이드가 운영하게 됨에 따라 logical optimizer 교육은 준비중입니다. 아마도 주말(토, 일)을 이용한 4일 과정이 될것 같습니다. 혹시 짧은 세미나나 출장교육은 수고스럽더라도 저에게 메일로 문의해 주시기 바랍니다.
      감사합니다.

  4. 2010.11.30 09:55 신고  댓글주소  수정/삭제  댓글쓰기

    귀한 자료네요... 책도 읽었는데 이렇게 또 볼수 있어서 좋습니다. 감사합니다.

  5. Favicon of http://blog.naver.com/genisu BlogIcon 김승욱 2013.01.07 10:55 신고  댓글주소  수정/삭제  댓글쓰기

    책을 읽다 놀란것이 의무감에 대한 말씀을 하신거에 대해 참 감동받았는데
    PPT까지 올려주시다니...정말...대단하신것 같습니다.감사합니다!!!


PDF 파일의 95 페이지에 타이틀이 잘못되어 수정해서 다시 올림(2010-09-15 오후 6시)

책 (The Logical Optimizer)의 Part 3에 대한 PPT가 완성되었다. Oracle 10g 부터 시작된 CBQT (Cost Based Query Transformation)에 대한 내용이다. 파워포인트 작업을 할때는 몰랐는데 완성하고 보니 130 페이지가 넘어가고 파일크기도 30MB가  넘는다. Tstory의 용량제한 때문에 할 수 없이 파일을 3개로 나눠(분할압축) 올린다. Part 3의 내용을 이해하는데 도움이 되었으면 한다.

사용자 삽입 이미지
사용자 삽입 이미지

압축  프로그램 7zip





PS
Part 4 도 작업이 완료되는 대로 올릴 예정이다.
신고
Posted by extremedb

댓글을 달아 주세요

  1. 윤상원 2010.09.15 17:07 신고  댓글주소  수정/삭제  댓글쓰기

    파트3, 기다리고 있었는데 감사합니다!
    책 내용을 정리하는데 많은 도움이 될 거 같습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.09.15 17:20 신고  댓글주소  수정/삭제

      반갑습니다.
      의외로 파워포인트를 기다리는 분들이 많이 계시는군요.
      Part 4도 힘을 내서 빨리 작업을 해야겠습니다.
      감사합니다.

  2. 윤상원 2010.09.15 17:30 신고  댓글주소  수정/삭제  댓글쓰기

    방금 보는중에 PDF파일 95페이지에 보니 갑자기 3.8 CVM 내용이 나오네요. 앞내용이 3.15 GBPD인데 말이죠. 카피되는 중에 잘못 들어간거 같습니다~

가능한가?

SQL문은 실행순서가 있다. 일반적으로 Order By가 가장 마지막에 수행된다. 만약 Order By가 없다면 마지막에 실행되는 것은 Group By이다. 데이터를 처리하는 작업은 Order By Group By 작업을 수행하기 이전에 끝난다. 상식적으로 보더라도 데이터를 읽는 작업과 조인작업을 먼저 처리해야 전체 데이터를 Grouping 할 수 있기 때문이다. 그런데 만약 데이터를 처리하는 작업이 Group By 이후에 발생한다면? 이런 일은 논리적으로 발생할 수 없다. 하지만 오늘 한가지 경우를 보여주려 한다.

 

먼저 오라클 SH 스키마의 Sales 테이블에 Local Partition 인덱스를 하나 생성한다.

Sales 테이블은 Time_id로 분기별 Range Partition이 되어있다.

 

CREATE INDEX SALES_TIME_CHANNEL_IX ON SALES

(TIME_ID, CHANNEL_ID) LOCAL;

                                 

먼저 정상적으로 처리되는 경우를 보자. IN 조건을 Pair로 여러개 주어본다. 

 

SELECT /*+ gather_plan_statistics */ TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

  

위의 SQL에서 주의해야 될 것은 IN 조건이 TIME_ID CHANNEL_ID 로 동시에 공급된다는 것이다.

 

TIME_ID  COUNT(TIME_ID)

-------- --------------

98/02/14            391

1 row selected.

 

-------------------------------------------------------------------------------------

| Id  | Operation               | Name                  | Starts | A-Rows | Buffers |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |                       |      1 |      1 |       3 |

|   1 |  SORT GROUP BY NOSORT   |                       |      1 |      1 |       3 |

|   2 |   PARTITION RANGE SINGLE|                       |      1 |    391 |       3 |

|*  3 |    INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    391 |       3 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("TIME_ID"=TO_DATE('19980214'))

       filter(("CHANNEL_ID"=2 OR "CHANNEL_ID"=3 OR "CHANNEL_ID"=4))


정상적으로 Grouping 되어 1건이 나왔고 Plan상에 Group By도 마지막에 수행되었다.

 
INLIST ITERATOR ! 니가 나를 배신하다니...

이제 1998 2 12일의 데이터를 추가로 공급해보자.

 

SELECT /*+ gather_plan_statistics */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

-------- --------------

98/02/12             50

98/02/14             54

98/02/14            287

98/02/14             50

 

4 rows selected.

 

답이 틀리다(Wrong Result Bug)

뭔가 이상하다. TIME_ID Grouping 하였으므로 결과건수는 1998 2 12일과 1998 2 14일로 두건만 나와야 한다. 그런데 2 14일 데이터가 Grouping 되지 않고 3건이 나와버렸다. 답이 틀리므로 이것은 버그이다.

 

왜 이런 일이 발생할까?

비밀은 실행계획에 있다.


---------------------------------------------------------------------------------------

| Id  | Operation                 | Name                  | Starts | A-Rows | Buffers |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |                       |      1 |      4 |       7 |

|   1 |  INLIST ITERATOR          |                       |      1 |      4 |       7 |

|   2 |   PARTITION RANGE ITERATOR|                       |      4 |      4 |       7 |

|   3 |    SORT GROUP BY NOSORT   |                       |      4 |      4 |       7 |

|*  4 |     INDEX RANGE SCAN      | SALES_TIME_CHANNEL_IX |      4 |    441 |       7 |

---------------------------------------------------------------------------------------

  

상식적으로는 Grouping을 가장 마지막에 한번만 해야 한다. 하지만 실행계획을 보면 그렇지 못하고 INLIST ITERATOR 작업 이전에 Grouping을 해버린다. ID 기준으로 3번이 그것인데 INLIST의 개수만큼 SORT GROUP BY가 반복된다. Starts 항목이 그것을 증명한다. 개념적으로 말하면 각각의 WHERE 조건마다 Group By를 수행하고 그 결과들을 Union 한 것이다. 

 

항상 버그가 발생하는 것은 아니다

파티션과 IN 조건이 만나야 버그가 발생한다. 또한 WHERE 조건이 변경되지 않고 INLIST ITERATOR가 발생해야 버그가 발생한다. 이 글에서 최초로 실행 시킨 SQL WHERE 조건은 TIME_ID = TO_DATE('19980214') AND CHANNEL_ID IN (2,3,4) 로 바뀌어 INLIST ITERATOR로 처리되지 않고 INDEX RANGE SCAND으로 처리되었으므로 버그가 없다.

 

해결책은 처리순서를 변경하는 것

이런 경우의 해결책은 아주 간단하다. 우리의 상식대로 하면 된다. 즉 데이터를 모두 처리하고 Grouping을 가장 마지막에 실행하는 것이다. 아래의 SQL이 그것이다.

 

SELECT TIME_ID, COUNT(*)

  FROM ( SELECT /*+ NO_MERGE */  TIME_ID

           FROM SALES

           WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                             (TO_DATE('19980214'), 2),

                                             (TO_DATE('19980214'), 3),

                                             (TO_DATE('19980214'), 4) )

       )

GROUP BY TIME_ID; 

 

TIME_ID  COUNT(TIME_ID)

-------- --------------

98/02/12             50

98/02/14            391

 

2 rows selected.

 

----------------------------------------------------------------------------------------

| Id  | Operation                  | Name                  | Starts | A-Rows | Buffers |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |                       |      1 |      2 |       9 |

|   1 |  SORT GROUP BY NOSORT      |                       |      1 |      2 |       9 |

|   2 |   INLIST ITERATOR          |                       |      1 |    441 |       9 |

|   3 |    PARTITION RANGE ITERATOR|                       |      4 |    441 |       9 |

|   4 |     VIEW                   |                       |      4 |    441 |       9 |

|*  5 |      INDEX RANGE SCAN      | SALES_TIME_CHANNEL_IX |      4 |    441 |       9 |

----------------------------------------------------------------------------------------


 

예상대로 Group By는 가장 마지막에 처리되었으므로 결과도 정상적으로 두건이 출력되었다. SQL의 실행순서만 알고 있으면 이 정도의 버그는 패치가 없어도 해결이 가능하다. NO_MERGE 힌트를 사용한 이유는 View Merge(뷰 해체)를 방지하기 위함이다. Simple View 이므로 힌트를 빼면 무조건 인라인뷰가 해체되어 버그가 재생된다.

 

또 다른 해결책을 보자.

 


SELECT /*+ INDEX(SALES SALES_TIME_CHANNEL_IX) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE    (TIME_ID = TO_DATE('19980214') AND CHANNEL_ID BETWEEN 2 AND 4 )

       OR (TIME_ID = TO_DATE('19980212') AND CHANNEL_ID =4 )         

 GROUP BY TIME_ID; 

 

 

TIME_ID  COUNT(TIME_ID)

-------- --------------

98/02/12             50

98/02/14            391

 

2 rows selected.

 


--------------------------------------------------------------------------------------

| Id  | Operation                | Name                  | Starts | A-Rows | Buffers |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT         |                       |      1 |      2 |       5 |

|   1 |  HASH GROUP BY           |                       |      1 |      2 |       5 |

|   2 |   CONCATENATION          |                       |      1 |    441 |       5 |

|   3 |    PARTITION RANGE SINGLE|                       |      1 |    391 |       3 |

|*  4 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    391 |       3 |

|   5 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|*  6 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

--------------------------------------------------------------------------------------


 

SQL WHERE절을 수정하여 INLIST ITERATOR가 아닌 CONCATENATION으로 유도 하였다. 위의 실행계획 또한 Group By가 가장 마지막이 실행되므로 버그가 발생되지 않는다. SQL을 수정하기 싫고 힌트만으로 해결하려면 아래의 SQL을 보라. 

 


SELECT /*+ USE_CONCAT(1) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

-------- --------------

98/02/12             50

98/02/14            391

 

2 rows selected.

 

--------------------------------------------------------------------------------------

| Id  | Operation                | Name                  | Starts | A-Rows | Buffers |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT         |                       |      1 |      2 |       9 |

|   1 |  HASH GROUP BY           |                       |      1 |      2 |       9 |

|   2 |   CONCATENATION          |                       |      1 |    441 |       9 |

|   3 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|*  4 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

|   5 |    PARTITION RANGE SINGLE|                       |      1 |    287 |       3 |

|*  6 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    287 |       3 |

|   7 |    PARTITION RANGE SINGLE|                       |      1 |     54 |       2 |

|*  8 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     54 |       2 |

|   9 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|* 10 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

--------------------------------------------------------------------------------------


위의 SQL 또한 정상적으로 2건이 출력된다.

버그를 유발하는 원인을 제거하라
마지막으로 볼 해결책은 파티션과 INLIST ITERATOR의 불편한 만남(?)을 제거하는 것이다.


SELECT /*+ INDEX_COMBINE(a) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES a

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

-------- --------------

98/02/12             50

98/02/14            391

 

2 rows selected.

 

--------------------------------------------------------------------------------------------

| Id  | Operation                           | Name              | Starts |A-Rows | Buffers |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                   |      1 |     2 |      61 |

|   1 |  PARTITION RANGE INLIST             |                   |      1 |     2 |      61 |

|   2 |   HASH GROUP BY                     |                   |      1 |     2 |      61 |

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |      1 |   441 |      61 |

|   4 |     BITMAP CONVERSION TO ROWIDS     |                   |      1 |   441 |      17 |

|   5 |      BITMAP OR                      |                   |      1 |     1 |      17 |

|   6 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|*  7 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|*  8 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

|   9 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|* 10 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 11 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

|  12 |       BITMAP AND                    |                   |      1 |     1 |       5 |

|* 13 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 14 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     2 |       3 |

|  15 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|* 16 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 17 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

--------------------------------------------------------------------------------------------

 

Bitmap Operation으로 인하여 INLIST ITERATOR가 사라졌으므로 버그는 보이지 않는다.

 

결론

이 버그는 Oracle 10g 에서 발견되었지만 11gR2 버전까지 해결되지 않고 있다. 파티션과 Local Index를 사용하고 Pair IN 조건이 INLIST ITERATOR로 풀리면 발생된다. 해결방법은 SQL을 수정하여 Group By를 마지막에 수행하거나 INLIST ITERATOR를 제거하면 된다. 버그패치가 없다고 해서 잘못된 결과를 보여줄 수는 없다. 패치가 나올 때 까지는 다른 해결책을 강구해야 한다. 잘 생각해보면 위의 경우처럼 원리만 안다면 상식 선에서 해결할 수 있는 문제가 매우 많다.

 

버그, 오라클에 알려야 해
대부분의 경우 버그는 사용자가 오라클에 보고해야 패치를 만들게 된다. 그러므로 버그가 발견되면 반드시 SR을 진행하여 오라클에 알려야 한다. 버그를 발견한 사람은 SQL을 수정하여 버그를 피할 수 있지만 동료나 다른 사이트에 있는 모든 사람들은 해결책을 모를 수 있고 심지어 버그가 있는지 조차 모를 수 있다. 하지만 많은 수의 사람들은 버그를 피해가는 방법만 발견된다면 내 문제는 해결 되었으므로 오라클에 버그를 통보하지 않고 조용히 넘어갈 것이다. 혹시 위의 버그가 몇년간 남아 있는 이유도 조용한(?) 사람들 때문이 아닐까?

한가지 방법은 오라클사에서 버그리포팅을 하는 사람에게 작은 선물을 주는 것이다. 제품의 품질도 높이고 고객의 참여를 유도하는 Win Win 전략이 될 수 있다.


저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

(The Logical Optimizer) 내용중 Part 2 부분의 PPT 파일이 완성되어 올립니다.
Tstory
10MB보다 큰 파일은 올릴 수 없게 되어있군요. 파일의 사이즈가 커서 분할 압축하여 올립니다
.
압축을 푸시면 아래그림처럼 3개의 파일이 됩니다. 각각 10MB 정도 되는군요.


사용자 삽입 이미지


첫 번째 파일(The Logical Optimizer_Part II_1) Basic 부분(2.A ~2.16)까지 입니다.
두 번째 파일(The Logical Optimizer_Part II_2) Subquery부분(2.17~2.29)까지 입니다.
세 번째 파일(The Logical Optimizer_Part II_2) Data Warehouse부분(2.30~Part2 마무리)까지 입니다.

PPT
파일로 다시 한번 정리하시기 바랍니다.
압축  프로그램 7zip
감사합니다.

사용자 삽입 이미지
사용자 삽입 이미지
사용자 삽입 이미지
신고
Posted by extremedb

댓글을 달아 주세요

  1. 썸바디 2010.08.13 09:41 신고  댓글주소  수정/삭제  댓글쓰기

    늘 좋은 정보 감사합니다~~
    근데 다운받은 파