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

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

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

 

포장마차에서 지인에게 재미있는 이야기를 들었다. 물론 공장 이야기 이다. 나는 이야기를 재미있게 들었지만, 지인의 입장에서는 머리가 쭈뼛쭈뼛 서는 심각한 일이었다. 사건은 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' 카테고리의 다른 글

Sort 부하를 좌우하는 두 가지 원리  (11) 2011.03.29
SQL튜닝 방법론  (20) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (7) 2010.02.11
Posted by extremedb
,

SQL 튜닝책을 세 권정도 읽은 신입사원이 SQL 튜닝방법론을 요청하였다. 이유는 튜닝책에 방법론이 없다는 것이다. 튜닝 방법론이란 “SQL을 튜닝 해달라고 요청 받았을 때 내가 무엇 무엇을 해야 하나?” 이다. SQL 튜닝시의 To-Do 리스트(체크리스트)를 요구한 것이다.

 

SQL 튜닝을 자주 하면서도, 그 안에 몇 가지 작업이 있는지 생각하지 못했다. 누가 그랬던가? 일상을 낯설게 느껴보라고… SQL 튜닝요청을 받았을 때 내가 어떤 일을 하는지 가르쳐 주면 되겠구나 하는 생각이 들었다. 그 결과 7가지 방법이 결론으로 도출되었다. 만약 7가지 방법을 모두 적용할 수 있는 경우임에도 불구하고 하나라도 빠진다면 최적화된 SQL을 만들 수 없다.

 

아래는 필자와 신입사원의 대화이다.

 

신입사원 : SQL 튜닝의 원칙 몇 가지를 저에게 일러 주실 수 있나요? 튜닝책도 몇 가지 보았고, 강의도 많이 들었지만 이 원칙만 지키면 100점 만점에 90점은 맞는다.” 는 원칙 같은 것은 없더군요. 저는 이제 입문하는 단계이므로 모든 경우에 100점을 맞을 필요는 없습니다.

 

필자 : 온라인 SQL이냐 대용량 배치 SQL이냐에 따라 튜닝방법이 달라지므로 설명하기가 힘들군요.

 

신입사원 : 걱정 하실 것 없습니다. 대용량 배치는 프로그램이 많지 않으므로 제외하고, 온라인 SQL 튜닝 원칙을 몇 가지 일러주세요.

 

필자 : 온라인 SQL이라 하더라도 관점에 따라 튜닝방법이 다릅니다. 예를 들어 Peak Time Insert 문이나 Update , Select문이 집중적으로 몰릴 때의 튜닝방법이 있고, 단순히 SQL 하나에 에 집중해서 응답시간을 최소화 하는 튜닝방법이 있습니다.

 

신입사원 : 그런 것을 지금 모두 알아야 할 필요는 없습니다. 제가 튜닝 프로젝트에 투입되었다고 가정하고, 성능이 느린 Select문 하나를 받았을 때 튜닝을 어떻게 해야 하는지에 대해서만 설명해주시면 됩니다.

 

고단수 신입사원

이렇게 해서 신입사원에게 말려들게 되었다. , 초보라도 몇 가지 원칙만 지키면 온라인 Select문에 대한 튜닝을 100점 만점에 90점을 맞을 수 있는 방법을 요구하는 것이다. 사실 이런 질문에 가장 적합한 답변은 “SQL 튜닝책을 읽어보라는 것이다. 그런데 신입사원이 필자와 대화과정(튜닝책도 몇 가지 보았고 ~)에서 이런 답변을 못하도록 교묘히 막고 있다. 고단수이다. 몇 가지 방법만 알게 된다면 90점을 받는다고? 처음부터 그런 방법은 없다고 할 걸 그랬나? 후회가 된다. 어찌되었든 약속처럼 되어버렸으므로 이 글을 쓰게 되었다. …..머리가 아파온다.

 

온라인 Select문 튜닝 방법론

온라인 SQL의 튜닝방법은 여러 가지가 있을 수 있다. 하지만 그 중에서 가장 기초적이고, 기본적인 방법을 공개한다. 아래의 7가지 항목을 점검하고 약한 곳을 보강하면 된다. 이 글은 SQL 튜닝책을 두 권 정도 본 사람들을 위한 것이다. 튜닝에 자신있는 사람들은 볼 필요가 없다.

 

1. 적절한 인덱스를 사용하여 Block I/O를 최소화 하라

조인이 없는 경우는 적절한 인덱스를 사용하는 것 만으로도 상당한 효과를 볼 수 있다. 조인이 있는 경우는 특히 Driving(선행) 집합에 신경을 써야 한다. 왜냐하면 Nested Loop 조인을 사용했고, 선행집합의 건수가 많다면, 후행집합의 조인의 시도횟수가 증가하므로 성능이 느려진다. 따라서 적절한 인덱스를 이용하여 선행집합의 건수를 줄인다면, 혹은 가장 적은 집합을 선행으로 놓는다면, 후행집합으로의 조인건수는 줄어든다. 물론 이때에도 후행집합의 적절한 인덱스는 필수 조건이다. Driving 집합의 Block I/O를 줄이기 위하여 최적화된 인덱스가 없다면 생성하고, 있다면 그것을 사용하라. 다시 말해 최적의 Access Path를 만들어라.

 

운영중인 시스템이라면 최적의 Access Path를 위해 인덱스를 변경하거나 생성할 때는 주의해야 한다. 현재 튜닝하고 있는 SQL에 최적화된 인덱스를 생성하더라도 다른 SQL에 악영향을 줄 수 있기 때문이다. 인덱스를 생성하거나 변경할 때는 그 테이블을 사용하는 다른 SQL의 실행계획이 변경되지 않는지 각별히 신경을 써야 한다. 이런 이유 때문에 개발과정에서 효율적인 인덱스 설계가 중요시 된다.

 

2. 조인방법과 조인순서를 최적화 하라

온라인에서 사용하는 Select문은 좁은 범위를 검색하는 경우가 많다. 이럴 때는 대부분 Nested Loop Join이 유리하다. 그러므로 조인건수가 소량인 SQL Hash Join이나 Sort Merge Join이 발견되면 Nested Loop Join으로 변경하는 것이 더 유리한지 검토해야 한다. 물론 여기서도 Nested Loop 조인에 관해서만 다룬다.

 

Nested Loop 조인에서 가장 중요한 것은 조인순서이다. From절에 테이블(집합)이 두 개라면 후행집합의 관점에서는 적절한 인덱스만 존재한다면 그것으로 족하다. 만약 From절에 테이블(집합)이 세 개 이상이라면 조인순서를 변경할 수 있는지에 대한 두 가지 원리를 사용하라. 두 가지 원리는 아래의 단락에서 소개된다. 아무리 조인할 집합이 많다고 하더라도 이 두 가지의 원리는 동일하게 적용될 수 있다. 두 가지 원리를 이용할 때 필요하다면 Leading 힌트를 사용해야 한다.

 

첫 번째, 후행집합에 적절한 인덱스가 없는 경우에 조인순서를 바꾸면, 최적의 인덱스를 사용할 수 있는 경우가 많다. 예컨대, 튜닝전의 조인순서가 Aà B à C 라고 하면, 중간집합인 B에 적절한 인덱스가 없고 오히려 C에 적절한 인덱스가 존재하는 경우가 있다. 이럴 때는 B에 인덱스를 무작정 생성하지 말고, 조인순서를 A à C à B로 바꿀 수 있는지, 바꾸는 것이 더 효율적인지 검증하라. 조인순서만 바꾸어 주어도 일량이 획기적으로 줄어드는 경우가 많다. 만약 조인순서를 바꿀 수 없거나, C를 중간집합으로 하는 것이 비효율적이라면, B를 중간집합으로 유지하고 적절한 인덱스를 사용해야 한다.

 

두 번째, 조인되는 집합 중 특정 인덱스에서 Block I/O가 증가하는 경우에 조인순서의 변경을 검토하라. 이때 10046 Trace DBMS_XPLAN.Display_Corsor를 이용하면 조인집합들의 Block I/O량을 관찰할 수 있다. 예를 들어, 튜닝전의 조인순서가 Aà B à C 라고 하고, 집합 B에서 Block I/O량이 증가하면 A à C à B로 바꾸면 일량이 줄어드는 경우가 많다. C를 먼저 조인(Filter)하여 선행집합(B의 입장에서는 C가 선행이다)의 건수를 줄이고 B에 조인하면 성능이 향상된다.

 

3. Table Access(Random Access)를 최소화 하라

Random Access rowid로 테이블을 엑세스하는 것을 말한다. 1번과 2번을 최적화 했다면 Random Access도 자동으로 많이 줄어들었을 것이다. 하지만 그것이 끝은 아니다. 여전히 성능이 만족스럽지 못하다면 Random Access 횟수를 줄이는 것을 간과해서는 안 된다.

 

인덱스를 사용하면 rowid가 자동으로 획득된다. 만약 인덱스에 없는 컬럼을 Select 해야 한다면 rowid로 테이블을 엑세스 해야 한다. 이때 테이블로 엑세스 해야 할 건수가 많고, 인덱스의 컬럼순으로 테이블이 sort되어있지 않다면 성능이 매우 저하된다. 왜냐하면 테이블이 인덱스 기준으로 sort되어 있지 않기 때문에 테이블을 방문할 때마다 서로 다른 블럭을 읽어야 하기 때문이다.

 

비유적으로 설명해보자. 우리가 심부름을 할 때 세 군대의 상점(A,B,C)을 들러야 한다고 치자. 그 상점들이 모두 한 건물 내부에 존재한다면 얼마나 좋겠는가? 그 심부름은 매우 빠른 시간에 끝날 것이다. 하지만 반대로 상점 A는 부산에 있고 상점 B는 대구에 있고, 상점 C는 서울에 있다면? 만약 당신의 성격이 매우 좋아서 그 심부름을 한다고 해도 시간이 많이 걸릴 것이다. Random Access도 마찬가지이다. 인덱스의 rowid로 테이블을 방문할 때, 테이블이 인덱스기준으로 sort되어 상점처럼 다닥다닥 붙어있다면 성능은 매우 빠르고, 흩어져 있을수록 성능이 느려진다. (오라클에서는 테이블이 인덱스 기준으로 sort 되어 있는 정도를 Clustering Factor라고 한다.) 바로 이런 이유 때문에 index scan보다는 Table Scan이 느린 것이다. 따라서 우리는 Random Access의 부하를 최소화 해야 한다.

 

Random Access의 부하를 줄이는 방법은 네 가지이다. 첫 번째, 테이블의 종류를 변경하는 방법이다. IOT나 클러스터를 이용하면 Clustering Factor가 극단적으로 좋아진다. 또한 파티션을 이용하면 같은 범위의 데이터를 밀집시킬 수 있다. 두 번째, 효율적인 인덱스를 사용하거나 조인방법과 순서를 조정하여 Table Access를 최소화 하는 방법이다. 이 방법은 1번과 2번에서 이미 설명 되었다. 세 번째, 인덱스에 컬럼을 추가하여 Table Access를 방지하는 방법이다. 예를 들어 Select절의 특정 컬럼 때문에 테이블이 엑세스 된다면, 인덱스의 마지막에 그 컬럼을 추가하면 된다. 네 번째, 인덱스만 엑세스 하고 테이블로의 엑세스는 모든 조인을 끝내고 마지막에 시도하여 Random Access의 횟수를 줄이는 방법이다. 해당 을 참조하라. 

 

4. Sort Hash 작업을 최소화 하라

1,2,3번을 통하여 최적의 Access Path Join을 사용했다면, Block I/O의 관점에서는 튜닝이 끝난 것이다. 하지만 1,2,3번이 모두 해결되었다 해도 Order by Group By 때문에 성능이 저하 될 수 있다. 특히 결과가 많은 경우, sort는 치명적이다.

 

인덱스가 sort 되어있다는 특성을 이용하면 order by 작업을 대신할 수 있다.  Group By sort 가 발생하는데 group by 단위와 인덱스의 컬럼이 동일 하다면 sort는 발생하지 않는다. 최적의 인덱스를 사용하면 Access Path를 개선하는 효과뿐만 아니라 Sort의 부하도 없어진다.

Union All
을 제외한 집합연산(Union, Minus, Intersect)를 사용하면 Sort Unique 혹은 Hash Unique가 발생한다. Union Union All로 바꿀 수 없는지 검토해야 하고, Minus Not Exists 서브쿼리를 이용하여 Anti Join으로 바꿀 수 없는지 고려해야 한다. Intersect는 교집합이므로 조인으로 바꿀 수 있는지 검토해야 한다. 아주 가끔 Distinct를 사용한 SQL이 눈에 뛰는데 이 또한 Sort Unique 혹은 Hash Unique를 발생시킨다. 모델러나 설계자에게 문의하여 Distinct를 제거할 방법이 없는지 문의해야 한다.

 

Oracle 10g부터는 Hash Group By가 발생할 수 있는데, 이미 적절한 인덱스를 사용하는 경우라면 Hash Group By를 사용할 필요는 없다. 이런 경우 NO_USE_HASH_AGGREGATION 힌트를 사용하면 Sort Group By로 바꿀 수 있다. 이렇게 해주면 실행계획에 “SORT GROUP BY NOSORT” Operation이 발생하며, Sort Hashing 작업이 전혀 발생하지 않는다. Group By의 부하를 해결하는 또 하나의 방법은 스칼라 서브쿼리를 사용하는 것이다. 조인을 사용하면 Sum 값을 구하기 위해 Group By가 필수적이다. 하지만 스칼라 서브쿼리를 사용하면 Group By를 사용하지 않고도 sum 이나 Min/Max 값을 구할 수 있다. 또한 분석함수의 Ranking Family(rank, dens_rank, row_number)를 최적화된 인덱스와 같이 사용하면 Group By Sort를 하지 않고도 Min/Max 값을 구할 수 있다. 이때는 실행계획에 “WINDOW NOSORT” Operation이 발생한다. 관련 글을 참조하기 바란다.

 

5. 한 블록은 한번만 Scan하고 끝내라

같은 데이터를 반복적으로 Scan하는 SQL이 의외로 많다. 대표적인 경우가 Union All로 분리되었지만 실제로는 그럴 필요가 없는 경우이다. 예를 들어 Where 절에 구분코드가 1일 때 , 2일 때, 3일 때 별로 SQL이 나누어져 있는 경우이다. Where 절을 구분코드 in (1,2,3) 으로 처리하고, Select절에서 Decode Case 문을 사용하여 구분코드별로 처리해준다면 Union All은 필요 없다. Union All을 사용하는 또 한가지의 경우는 Sub Total(소계) Grand Total(총계)를 구해야 하는 경우이다. 이 경우도 Rollup/Cube Grouping Sets Group By절에 사용한다면 소계나 총계를 위한 별도의 Select문을 실행 시킬 필요는 없다. 1~4번의 과정은 SQL문의 변경이 없거나 최소화 된다. 하지만 5번의 경우는 SQL을 통합시켜야 하기 때문에 시간이 많이 소모되며, 많은 사고가 요구되는 창조적인 과정이다. 여기까지 했다면 진행되었다면 원본 SQL 자체의 튜닝은 완료 된 셈이다.

 

6. 온라인의 조회화면이라면 페이징처리는 필수이다

부분범위 처리를 해야 한다. 물론 전체 건을 처리해야 하는 경우는 있을 것이다. 하지만 조회화면이라면 몇 십만 건 혹은 몇 만 건이나 되는 결과를 모두 볼 수 없다. 따라서 볼 수 있는 단위로 끊어서 출력해야 한다. 예를 들어 결과 건수가 10만 건이라고 해도 최초의 50건을 화면에 먼저 뿌린다면 1,2,3,4 번에서 설명했던 모든 부하(Block I/O의 부하, 조인의 부하, Random Access의 부하, Sort의 부하)를 한꺼번에 감소시킬 수 있다. 따라서 가능하면 개발자를 설득하거나 책임자를 설득하여 페이징 처리를 하는 것이 바람직하다.

 

페이징 처리를 해도 효과를 볼 수 없는 몇 가지 예외가 있다. 분석함수를 사용하거나, Connect By + Start With를 사용한다면 페이징 처리의 효과는 없다. 분석함수의 경우 인라인뷰의 외부로 뺄 수 있다면 부분범위 처리가 가능하다. 이에 관해서는 해당 을 참조하기 바란다. Connect By + Start With를 사용한 경우는 부분범위처리가 불가능하다. 하지만 11g R2의 신기능인 Recursive With절을 사용한다면 페이징 처리의 효과를 볼 수 있다. 이때, Recursive With절에 Search(Order By절과 같은 기능)을 사용한다면 Connect By와 마찬가지로 페이징 처리의 효과가 없으니 주의해야 한다. 즉 인덱스의 구성을 적절히 하여 Sort를 대신해야 한다. Recursive With가 무엇인지 궁금한 사람은 관련 을 참조하기 바란다.

 

7. 답이 틀리면 안 된다. SQL을 검증하라

7번은 SQL 자체를 튜닝하는 것은 아니다. 하지만 7번을 튜닝 방법에 추가한 이유는 있다. 튜닝을 하였음에도 답이 틀린다면, 튜닝을 하지 않은 것 보다 못하다. 그러므로 튜닝 후에 답이 옳은지 항상 검증해야 한다. 1~ 7번 중에 가장 중요한 것이 7번이다.

 

방법론 정리

1.     적절한 인덱스를 사용하여 Block I/O를 최소화 하라.

2.     조인방법과 조인순서를 최적화 하라.

3.     Table Access(Random Access)를 최소화 하라

4.     Sort Hash 작업을 최소화 하라

5.     한 블록은 한번만 Scan하고 끝내라

6.     온라인의 조회화면이라면 페이징처리는 필수이다

7.     답이 틀리면 안 된다. SQL을 검증하라

 

방법론의 의미

만약 1~7번을 모두 적용할 수 있는 경우임에도 불구하고 하나라도 빠진다면 그것은 최적화된 SQL이 아니다. 물론 튜닝을 할 때 위의 1~6번을 항상 적용할 수 있는 것은 아니다. 경우에 따라서는 하나만 적용될 수도 있고, 두 개만 적용할 수 있는 SQL도 있다. 하지만 1~6번을 모두 적용할 수 있는지 꼼꼼히 살펴야 한다.

 

이 글은 튜닝 입문하여 관련 책들을 몇 권 본 사람들을 위한 기본적인 튜닝방법에 관한 것이다. 1번부터 7번까지의 방법은 기본 중에 기본이다. 이것들만 알아도 온라인 조회화면에서 사용하는 SQL을 튜닝하는데 어려움이 없을 것이다. 다시 말해 90%는 해결 할 수 있다. 그렇다면 나머지 10%? 그것들은 그때 그때 마다 다르게(On the fly 모드) 처리된다. 또한 그것들은 책이나 매뉴얼에 나와있지 않기 때문에 경험치 이거나 실험과 연구의 결과로 알아내는 것들이다.

 

일상을 낯설게 느껴보니 좋은 점이 많다. 언제 필자의 다른 일상(모델링, 시스템분석/진단)에 대한 방법론도 만들어 보려고 한다.


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

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

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
,

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

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

-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 부하를 좌우하는 두 가지 원리  (11) 2011.03.29
SQL튜닝 방법론  (20) 2011.01.27
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (7) 2010.02.11
USE_CONCAT 힌트 제대로 알기  (5) 2009.07.17
Posted by extremedb
,

-동적인 조회조건에서 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
,


부제 : 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
,

블로그가 일주일에 한번만 업데이트 되기 때문에 많은 분들이 어떤 내용이 블로그에 올라올지 궁금해 하시는것 같습니다. 그래서 시간이 허락한다면 블로그에 올라갈 내용을 미리 공지 하겠습니다.
 
제목
: Cardinality Feed Back
이 위험할 때

부제목: Cardinality Feed Back의 개념과 사용예제

문서의 목적
1. Oracle11
의 새 기능인 Cardinality Feedback의 개념을 알아보고 실행예제를 분석해본다.
2. Cardinality Feedback
이 문제가 되는 경우를 살펴보고 해결방법을 제시한다
.

목차
1.
서론
2. Cardinality Feedback의 개념:
소제목 예측, 실행, 비교, 그리고 전달 부분
3. Cardinality Feedback의 작동방법: 소제목 CF는 어떻게 실행되나? 부분
4.
Cardinality Feedback 실행예제: 소제목 CF를 발생시켜보자 부분
5.
Cardinality Feedback 문제점: 소제목 CF의 문제점은? 부분
6.
문제의 해결방법: 소제목 해결책 부분
7.
결론

분석도구
1. 10053 Trace
2. DBMS_XPLAN.display_cursor

참조문서
Closing the Query Processing Loop in Oracle 11g - Allison Lee, Mohamed Zait


예상발행일자
2010.10.25 일


주의사항: 블로그 내용은 예고없이 변경될 수 있습니다.

많이 기대해주세요.

Posted by extremedb
,

이전에 Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter) Partition Access Pattern 이라는 글에서 Bloom Filter의 개념을 설명한적 있다. 이전 글들 때문인지 모르겠으나 많은 사람들이 Parallel Query를 사용하거나 Partition을 엑세스 할때 Bloom Filter로 후행 테이블의 건수를 줄여 조인 건수를 최소화하는 것으로만 생각한다. 맞는 말이지만 그것이 전부가 아니다.
그래서 이번에는 Parallel Partition에 상관없이 Bloom Filter가 발생하는 경우를 살펴보고자 한다. 이 글을 통하여 풀고자 하는 오해는 Bloom FilterJoin 최적화를 위한 후행 테이블의 Filter 알고리즘일 뿐만 아니라 Group By를 최적화하는 도구이기도 하다는 것이다.

 

실행환경: Oracle11gR2, Windows 32bit

 

Bloom Filter를 사용하지 않는 경우

먼저 Bloom Filter가 발생하지 않게 힌트를 주고 실행한다. 뒤에서 Bloom Filter를 적용한 경우와 성능을 비교하기 위함이다.

 

SELECT /*+ LEADING(c) NO_MERGE(S) NO_PX_JOIN_FILTER(S) */

       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              | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT              |                   |     23 |00:00:06.58 |    5075 |          |

|*  1 |  HASH JOIN                    |                   |     23 |00:00:06.58 |    5075 | 1194K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID | CUSTOMERS         |    151 |00:00:00.01 |     148 |          |

|   3 |    BITMAP CONVERSION TO ROWIDS|                   |    151 |00:00:00.01 |       2 |          |

|*  4 |     BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |      1 |00:00:00.01 |       2 |          |

|   5 |   VIEW                        |                   |   7059 |00:00:06.56 |    4927 |          |

|   6 |    SORT GROUP BY              |                   |   7059 |00:00:06.54 |    4927 | 9496K (0)|

|   7 |     PARTITION RANGE ALL       |                   |    918K|00:00:02.80 |    4927 |          |

|   8 |      TABLE ACCESS FULL        | SALES             |    918K|00:00:00.95 |    4927 |          |

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

 

Predicate Information (identified by operation id):

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

   1 - access("S"."CUST_ID"="C"."CUST_ID")

   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

 

Id 기준으로 8번에서 Buffers 항목을 보면 전체건(4927 블록) Scan 하였다. 그리고 A-Rows 항목을 보면 Sales 테이블에 대해 약 92만건(918K)을 읽었다. 이제 Id 6번을 보자. 전체 건수인 92만건에 대하여 Sort Group By를 적용하는데 부하가 집중되는 것을 알 수 있다. 시간상으로도 Group By를 하는데 3.7초 정도 걸렸으며 PGA 9496K나 사용하였다. 즉 대부분의 시간을 Sort Group By Operation 에서 소비한 것이다.

 

이제 위의 SQL Bloom Filter를 적용해 보자. Sales 테이블에 파티션이 적용되어 있으나 파티션과 상관없이 Bloom Filter가 적용된다.

 

SELECT /*+ LEADING(c) NO_MERGE(S) PX_JOIN_FILTER(S) */

       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              | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT               |                   |     23 |00:00:00.15 |    5075 |          |

|*  1 |  HASH JOIN                     |                   |     23 |00:00:00.15 |    5075 | 1197K (0)|

|   2 |   JOIN FILTER CREATE           | :BF0000           |    151 |00:00:00.01 |     148 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS         |    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                         |                   |     55 |00:00:00.14 |    4927 |          |

|   7 |    SORT GROUP BY               |                   |     55 |00:00:00.14 |    4927 |88064  (0)|

|   8 |     JOIN FILTER USE            | :BF0000           |   7979 |00:00:00.12 |    4927 |          |

|   9 |      PARTITION RANGE ALL       |                   |   7979 |00:00:00.10 |    4927 |          |

|* 10 |       TABLE ACCESS FULL        | SALES             |   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를 사용해보니

위의 실행계획에서 Id 기준으로 8번을 보면 Name 항목에 Bloom Filter가 사용되었다. Bloom Filter의 위력이 얼마나 대단한지 살펴보자. 먼저 Sales 테이블을 Full Table Scan 하였으므로 Buffers 4927Bloom Filter를 사용하지 않는 경우와 똑같다. 하지만 Bloom Filter가 적용되어 92만건이 아닌 7979(A-Rows 참조)만 살아남았다. 이처럼 Bloom FilterHash Join Probe(후행) 집합에서 조인에 참여하는 건수를 줄임으로써 Join 시간을 단축시킨다. Bloom Filter의 효과는 이것이 끝이 아니다. 건수가 줄어듦으로 해서 Sort Group By 작업 또한 92만 건이 아니라 7979건만 하면 된다. Group By에 의한 PGA 사용량을 Bloom Filter가 적용된 실행계획과 비교해보면 100배 이상 차이가 나는 이유도 Bloom Filter의 효과 때문이다.

 

제약사항

이번에 test한 케이스는 Parallel Query도 아니며 Partition Pruning과도 관련이 없다. 하지만 항상 발생하지는 않는다. 이유는 세 가지 제약사항이 있기 때문이다.

첫 번째, Hash Join을 사용해야 한다. Sort Merge Join이나 Nested Loop Join에서는 발생하지 않는다.
두 번째, Build Input(Driving) 집합에 Filter 조건이 존재해야 한다. 위의 SQL에서는 cust_year_of_birth = 1987 Filter 조건으로 사용되었다. Filter가 필요한 이유는 선행집합의 Filter조건을 후행집합에서 Bloom Filter로 사용해야 하기 때문이다.
세 번째, Probe(후행) 집합에서 Group By를 사용해야 한다. 위의 SQL에서도 cust_id Group By를 하고 있다. 물론 후행집합에 Group By가 적용되려면 뷰나 인라인뷰가 필요하다.

 

 

만약 Bloom Filter가 사라져 전체 건이 조인에 참여한다면?

상상하기 싫은 경우지만 Probe(후행) 집합에 Bloom Filter가 사라지는 경우를 살펴보자. 이 경우는 Sales 테이블 전체건수( 92만건)가 모두 Hash Join에 참여하게 되므로 성능이 저하될 것이다. 아래의 SQL이 그것인데 위의 SQL에서 NO_MERGE(S) 힌트와 PX_JOIN_FILTER(S)만 뺀 것이다.

 

SELECT /*+ 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              | A-Rows |   A-Time   | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT               |                   |     23 |00:00:05.39 |    5075 |          |

|   1 |  SORT GROUP BY                 |                   |     23 |00:00:05.39 |    5075 |75776  (0)|

|*  2 |   HASH JOIN                    |                   |   3230 |00:00:05.37 |    5075 | 1185K (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS         |    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         |                   |    918K|00:00:02.70 |    4927 |          |

|   7 |     TABLE ACCESS FULL          | SALES             |    918K|00:00:00.94 |    4927 |          |

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

Predicate Information (identified by operation id):

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

 

   2 - access("S"."CUST_ID"="C"."CUST_ID")

   5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

 

악성 쿼리변환

힌트를 제거하자 View Merging(뷰 해체)이 발생하여 인라인뷰가 제거되었다. (View Merging이 발생하지 않는 독자는 MERGE(S) 힌트를 추가하기 바란다) 뷰가 없어짐에 따라 후행집합에서 Group By가 없어지고 조인이 끝난 후에 Group By가 발생한다. 후행집합의 Group By가 사라졌으므로 Bloom Filter가 적용되지 않는다. 따라서 Sales 테이블의 전체건 ( 92만건)이 조인에 참여하게 된다. Bloom Filter가 적용된 경우는 단 55건만 조인에 참여하므로 이 차이는 어마 어마한 것이다. 그 결과 전체 수행시간중에서 Hash Join에서만 절반의 시간을 소모하였다. 즉 잘못된 쿼리변환이 발생하여 Bloom Filter를 죽여버린 것이다. View Merging이 발생할 때 Bloom Filter를 적용할 수 없게되어 비효율이 발생되는지 주의깊게 관찰해야 한다.

 

 

결론

이번 Test 케이스에서 Bloom Filter의 특징을 두 가지로 압축할 수 있다. Group By 작업량을 최소화 시켜주고 Hash Join 건수를 줄여준다. 이 두 가지 효과가 맞물려 Bloom Filter를 적용한 SQL 0.15초 만에 끝날 수 있는 것이다. 후행 테이블에서 Bloom Filter로 걸러지는 건수가 많을 때 두 가지 작업(Group By, Hash Join) 모두 최대의 효율을 발휘한다. 바꿔 말하면 Bloom Filter로 제거되는 건수가 미미 하다면 사용해선 안된다.

CVM(Complex View Merging)이 발생하면 여지없이 Bloom Filter가 사라진다. CVM 때문에 성능이 저하된다면 NO_MERGE 힌트를 사용하여 뷰를 유지시켜야 한다. Bloom Filter가 사라지는 경우는 이 경우 뿐만 아니다. 11gR2에서 새로 적용된 Cardinality Feedback 때문에 Bloom Filter가 사라지는 경우가 보고되고 있다. 마지막(세번째) SQL을 최초로 실행시켰을 때와 두번째로 실행시켰을 때 DBMS_XPLAN.DISPLAY_CURSOR의 실행계획이 달라진다면 Cardinality Feedback이 Bloom Filter를 제거시킨것이다. Shared Pool을 Flush하고 두번 연달아 테스트 해보기 바란다. 이런 현상들 때문에 옵티마이져에 새로운 기능이 추가될 때마다 긴장을 늦출 수 없다. 버전이 올라갈수록 튜닝하기가 쉬워지는것인가? 아니면 그 반대인가?


 

Posted by extremedb
,

이전 글(NULL AWARE ANTI JOIN SQL을 어떻게 변경시키나?) 에서 NULL AWARE ANTI JOIN 중에서 조인방법이 NESTED LOOPS 조인을 선택한다면 NULL을 체크하는 서브쿼리가 추가된다고 설명하였다. 이번에는 NESTED LOOPS ANTI NULL AWARE가 아닌 HASH JOIN ANTI NULL AWARE에 대하여 알아보자. 들어가기 전에 이번 글을 이해하려면 이전 글의 이해가 필수적이니 먼저 빠르게 읽고 오기 바란다.

 

오해를 하다

(The Logical Optimizer) 158 페이지의 내용에 따르면 WHERE 조건이 추가되면 NULL을 체크하는 Filter가 적용되지 않는다고 하였다. 하지만 이것은 필자의 오해였다. 얼굴이 화끈거리는 오류이다. 아래의 예제를 보자.

 

SELECT /*+ QB_NAME(MAIN) */

       d.department_id, d.department_name, d.location_id

  FROM department d

 WHERE d.department_id NOT IN (SELECT /*+ QB_NAME(SUB) */

                                      e.department_id

                                 FROM employee e

                                WHERE e.job_id = 'PU_CLERK')

   AND d.location_id = 1700;

 

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

| Id  | Operation                    | Name             | Rows  | Bytes | Cost  | Time     |

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

|   0 | SELECT STATEMENT             |                  |    16 |   512 |     5 | 00:00:01 |

|*  1 |  HASH JOIN ANTI NA           |                  |    16 |   512 |     5 | 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |    21 |   420 |     2 | 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX |    21 |       |     1 | 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |     5 |    60 |     2 | 00:00:01 |

|*  5 |    INDEX RANGE SCAN          | EMP_JOB_IX       |     5 |       |     1 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

   3 - access("D"."LOCATION_ID"=1700)

   5 - access("E"."JOB_ID"='PU_CLERK')

 

위의 예제에서 필자는 “서브쿼리의 조건절에 e.JOB_ID = 'PU_CLERK' 조건을 추가하자 IS NULL FILTER가 사라졌다.” 라고 했는데 이 부분이 잘못되었다. WHERE 조건의 추가유무와는 상관없이 조인종류(JOIN METHOD)에 따라서 NULL을 체크하는 FILTER의 유무가 결정된다. 아래의 SQL로써 이 사실을 증명해보자. 아래의 SQL은 조건절을 추가하지 않고도 조인방법만 HASH로 변경하였다. USE_HASH 힌트를 빼면 NESTED LOOPS ANTI SNA 로 풀리고 NULL을 체크하는 서브쿼리가 추가된다. 


SELECT /*+ gather_plan_statistics use_hash(e@sub) */

       d.department_id, d.department_name, location_id

  FROM department d

 WHERE d.department_id NOT IN (SELECT /*+ qb_name(sub) */ 

e.department_id

                                 FROM employee e)

   AND d.location_id = 1700;

 

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

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

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

|   0 | SELECT STATEMENT             |                  |      0 |00:00:00.01 |       9 |

|*  1 |  HASH JOIN ANTI NA           |                  |      0 |00:00:00.01 |       9 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |     21 |00:00:00.01 |       2 |

|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX |     21 |00:00:00.01 |       1 |

|   4 |   TABLE ACCESS FULL          | EMPLOYEE         |     97 |00:00:00.01 |       7 |

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

 

Predicate Information (identified by operation id):

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

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

   3 - access("D"."LOCATION_ID"=1700)

 

HASH JOIN ANTI NA NULL을 체크하는 NOT EXISTS 서브쿼리를 만들지 않음을 알 수 있다. Predicate Information의 어디에도 NULL을 체크하는 FILTER는 없다. 다시 말하면 HASH JOIN ANTI NA IS NULL Filter 서브쿼리를 만들지 않고 Hash 조인을 할 때 NULL 데이터를 체크하므로 NULL 체크용 서브쿼리가 필요 없는 것이다.  

 

결론

Null을 체크하는 서브쿼리는 NESTED LOOP ANTI NA인 경우만 추가되고 HASH JOIN ANTI NA에서는 생성되지 않는다. 필자는 책을 집필할 자료를 준비할 때 데카르트의 방법을 의도적으로 사용하였지만 이렇게 간단한 원리도 놓치고 말았다. 데카르트의 방법론이 어렵고 특별할 것 같지만 사실은 아주 간단하다. 어떤 것을 연구하거나 진리를 탐구할 때 내가 아는 것이 없다고 가정하는 것이다. 즉 내가 아는 것까지 모른다고 가정하고 모든 것을 검증하라는 것이다. 궁금한 사람은 데카르트의 방법서설을 자세히 읽어보라.

 

몇 년간 데카르트의 방법을 100% 사용하기는 어려웠다. 그 약속을 지킨다는 것은 엄청난 스트레스를 수반한다. 그럼에도 안다고 생각하는 것을 모두 검증하려고 덤볐지만 결국 오류는 막을 수 없었다. 이유는 지식의 저주 때문이다. 어떠한 결과나 현상을 보았을 때 그것의 생김새나 특징이 매우 친숙하다면 내가 알고 있다고 착각 하는 것. 이것은 매우 위험한 일이었다. 이 문제는 필자를 비롯한 모든 과학자 및 연구원들의 고민일 것이다. 이 문제를 해결할 방법은 없는 걸까?


Posted by extremedb
,

Oracle 10g 까지는 NOT IN 서브쿼리를 사용할 때 NULL을 허용하는 컬럼으로 메인쿼리와 조인하면 Anti Join을 사용할 수 없었고 Filter 서브쿼리로 실행되었기 때문에 성능이 저하되었다. 마찬가지로 메인쿼리쪽의 조인컬럼이 NULL 허용이라도 Filter로 처리된다. 하지만 11g부터는 Anti Join Null Aware를 사용하여 Null인 데이터가 한 건이라도 발견되면 Scan을 중단하므로 성능이 향상된다. (The Logical Optimizer)에서도 이런 사실을 언급하고 있다. 하지만 Anti Join Null Aware로 인해 변환된 SQL의 모습은 책에서 언급되지 않았으므로 이 글을 통하여 알아보자.

 

먼저 가장 기본적인 예제를 실행해보자.

실행환경: Oracle 11.2.0.1

 

--Anti Join Null Aware를 활성화 시킨다. Default True 이므로 실행하지 않아도 됨.

ALTER SESSION SET "_optimizer_null_aware_antijoin" = TRUE;

 

SELECT d.department_id, d.department_name, location_id

  FROM department d

 WHERE d.department_id NOT IN (SELECT e.department_id

                                 FROM employee e)

   AND d.location_id = 1700;

 

NOT IN 서브쿼리는 두 가지 뜻이 있다

위의 SQL을 해석할 때 단순히 location_id = 1700인 부서 중에서 사원이 한 명도 없는 건을 출력한다고 생각하면 한가지를 놓친 것이다. 만약 이런 요건이라면 NOT IN 대신에 NOT EXISTS 서브쿼리를 사용해야 한다. 다시 말해 NOT IN 서브쿼리를 사용하면 employee 테이블의 department_id 값 중에 한 건이라도 Null이 있으면 결과집합이 출력되지 않는다. 실제로도 결과건수가 없다. 이제 위의 SQL에 해당하는 Plan을 보자.

 

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

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

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

|   0 | SELECT STATEMENT              |                   |      0 |00:00:00.01 |       7 |

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

|   2 |   NESTED LOOPS ANTI SNA       |                   |      0 |00:00:00.01 |       0 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      0 |00:00:00.01 |       0 |

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

|*  5 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      0 |00:00:00.01 |       0 |

|*  6 |   TABLE ACCESS FULL           | EMPLOYEE          |      1 |00:00:00.01 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - filter( IS NULL)

   4 - access("D"."LOCATION_ID"=1700)

   5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

   6 - filter("E"."DEPARTMENT_ID" IS NULL)

 

NULL을 발견하면 멈춘다

NESTED LOOPS ANTI NA라는 기능은 Null 데이터를 찾자마자 Scan을 멈추는 것이다. ID 기준으로 6번의 Predicate Information을 보면 NULL인 데이터를 단 한 건(A-Rows 참조)만 찾아내고 Scan을 멈추었다. 이제 NESTED LOOPS ANTI SNA가 어떻게 수행되는지 10053 Trace를 통하여 살펴보자.

 

FPD: Considering simple filter push in query block SEL$526A7031 (#1)

"D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1700 AND  NOT EXISTS (SELECT /*+ QB_NAME ("SUB") */ 0 FROM "EMPLOYEE" "E")

FPD: Considering simple filter push in query block SUB (#2)

"E"."DEPARTMENT_ID" IS NULL

try to generate transitive predicate from check constraints for query block SUB (#2)

finally: "E"."DEPARTMENT_ID" IS NULL

 

FPD(Filter Push Down) 기능으로 인하여 쿼리블럭명이 SUB Not Exists 서브쿼리가 추가 되었고 그 서브쿼리에 DEPARTMENT_ID IS NULL 조건이 추가되었다.

 

SQL 어떻게 바뀌었나?

위의 10053 Trace 결과에 따르면 Logical Optimizer SQL을 아래처럼 바꾼 것이다.

 

SELECT d.department_id, d.department_name, d.location_id

  FROM department d

 WHERE NOT EXISTS (SELECT 0           

                     FROM employee e

                    WHERE e.department_id IS NULL) –-NULL 을 체크하는 서브쿼리

   AND NOT EXISTS (SELECT 0           

                     FROM employee e

                    WHERE e.department_id  = d.department_id)                     

   AND d.location_id = 1700 ;

 

SQL을 보면 NOT IN 서브쿼리가 NOT EXIST 서브쿼리로 바뀌었고 NULL을 체크하는 서브쿼리가 추가되었다. 또한 NULL을 체크하는 서브쿼리의 결과가 한 건이라도 존재하면 SQL은 더 이상 실행되지 않는다는 것을 알 수 있다. NESTED LOOPS ANTI SNA의 비밀이 풀리는 순간이다. ORACLE 9i 10g 에서도 위와 같이 SQL을 작성하면 NESTED LOOPS ANTI SNA의 효과를 볼 수 있다. 하지만 위의 SQL처럼 수동으로 작성하는경우 NULL 한건을 체크 하는데 오래 걸리며 부하가 있다면 이렇게 사용하면 안 된다. 이제 Plan을 보자.

 

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

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

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

|   0 | SELECT STATEMENT              |                   |      0 |00:00:00.01 |       7 |

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

|   2 |   NESTED LOOPS ANTI           |                   |      0 |00:00:00.01 |       0 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      0 |00:00:00.01 |       0 |

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

|*  5 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      0 |00:00:00.01 |       0 |

|*  6 |   TABLE ACCESS FULL           | EMPLOYEE          |      1 |00:00:00.01 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - filter( IS NULL)

   4 - access("D"."LOCATION_ID"=1700)

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

   6 - filter("E"."DEPARTMENT_ID" IS NULL)

 

Operation의 순서에 유의하라

위의 Plan을 과 원본 Plan을 비교해보면 원본이 ANTI SNA라는 것만 제외하면 실행계획과 일량까지 같음을 알 수 있다. 헷갈리지 말아야 할 것은 ID 기준으로 6(NULL 체크 서브쿼리)이 가장 먼저 실행된다는 것이다. 왜냐하면 서브쿼리 내부에 메인쿼리와 조인조건이 없기 때문에 서브쿼리가 먼저 실행될 수 있기 때문이다. 반대로 Filter 서브쿼리내부에 메인쿼리와 조인 조건이 있다면 메인쿼리의 컬럼이 먼저 상수화 되기 때문에 항상 서브쿼리쪽 집합이 후행이 된다. 이런 사실을 모르고 보면 PLAN상으로만 보면 NULL 체크 서브쿼리가 가장 마지막에 실행되는 것으로 착각 할 수 있다.

 

결론

Anti Join Null Aware를 사용하여 Null인 데이터가 한 건이라도 발견되면 Scan을 중단하므로 성능이 향상된다. NULL을 체크하는 Filter 서브쿼리가 추가되기 때문이다. 하지만 그런 서브쿼리가 항상 추가되는 것은 아니다. 추가되는 기준이 따로 있는데 다음 글에서 이 부분을 다루려고 한다.

 

PS

책에 위의 SQL이 빠져있다. SQL PLAN을 출력하여 끼워 넣기 바란다.

Posted by extremedb
,

영화 <마이너리포트>의 주인공인 톰 크루즈가 사용한 Dragging Board는 이미 몇 년전에 구현되었고 아이폰과 아이패드의 탄생으로 누구나 사용하게 되었다. 영화 <메트릭스> <터미네이터>를 보면 인간보다 우월한 기계들에 의해 지배를 당하거나 고통을 받는다. 이런 일을 먼 미래의 것으로 치부해 버리기에는 기술의 발전속도가 너무 빠르다. 이미 우리는 그런 세상에 살고 있다. 근거가 뭐냐고? 현재 적지 않은 수의 개발자들이 기계(옵티마이져) 보다 SQL의 작성능력이 떨어지기 때문이다.

 

예를 들면 옵티마이져가 재작성하는 SQL은 튜닝을 모르는 개발자가 작성한 것 보다 우월하다. 즉 개발자(인간)SQL을 작성했지만 옵티마이져는 품질이 떨어진다고 판단되는 SQL을 주인의 허락 없이 변경시켜 버린다.
인간이 Software 보다 못한 것인가?

 

같은 블록을 반복해서 Scan 하면 성능이 느려진다라는 문구는 비단 개발자, DBA, 튜너만 생각하는 것이 아니다. 옵티마이져는 분석함수를 이용하여 위의 문구를 직접 실천한다. 다시 말하면 같은 테이블을 중복해서 사용하는 경우 옵티마이져는 비효율을 없애기 위해 분석함수를 이용하여 SQL을 변경시킨다. 아래의 SQL을 보자.   

 

WITH v AS  (SELECT /*+ INLINE */

                   department_id, SUM (salary) AS sal

              FROM employee

             WHERE job_id = 'ST_CLERK'

             GROUP BY department_id )

SELECT d.department_id, d.department_name, v.sal

  FROM department d, v

 WHERE d.department_id = v.department_id

   AND v.sal = (SELECT MAX (v.sal)

                  FROM v ) ;

 

 

위의 SQL 보면 인라인뷰 V 먼저 정의해놓고 아래의 Select 절에서 사용한 것을 있다. 다시 말하면 같은 테이블을 (Temp 테이블에 Loading, 메인쿼리에 한번, 서브쿼리에 한번) 사용한 것이다. 아래의 실행계획을 보고 우리의 예상이 맞는지 확인해보자.

 

------------------------------------------------------+-----------------------------------+

| Id  | Operation                         | Name      | Rows  | Bytes | Cost  | Time      |

------------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT                  |           |       |       |     6 |           |

| 1   |  MERGE JOIN                       |           |     5 |   275 |     6 |  00:00:01 |

| 2   |   TABLE ACCESS BY INDEX ROWID     | DEPARTMENT|    27 |   432 |     2 |  00:00:01 |

| 3   |    INDEX FULL SCAN                | DEPT_ID_PK|    27 |       |     1 |  00:00:01 |

| 4   |   SORT JOIN                       |           |     5 |   195 |     4 |  00:00:01 |

| 5   |    VIEW                           |           |     5 |   195 |     3 |  00:00:01 |

| 6   |     WINDOW BUFFER                 |           |     5 |    80 |     3 |  00:00:01 |

| 7   |      HASH GROUP BY                |           |     5 |    80 |     3 |  00:00:01 |

| 8   |       TABLE ACCESS BY INDEX ROWID | EMPLOYEE  |     6 |    96 |     2 |  00:00:01 |

| 9   |        INDEX RANGE SCAN           | EMP_JOB_IX|     6 |       |     1 |  00:00:01 |

------------------------------------------------------+-----------------------------------+

Predicate Information:

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

4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")

4 - filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")

5 - filter("V"."SAL"="ITEM_0")

9 - access("JOB_ID"='ST_CLERK')

 

 

우리의 예상과는 달리 Employee 테이블에 대한 액세스가 한번 나왔다. 놀랍지 않은가? URSW라는 기능으로 인하여 중복 액세스를 제거해 버린 것이다. Logical Optimizer SQL 아래와 같이 재작성 것이다.

 

SELECT d.department_id, d.department_name, v.sal sal

  FROM department d,

       (  SELECT e.department_id, SUM (e.salary) sal,

                 MAX (SUM (e.salary)) OVER () item_0

            FROM employee e

           WHERE e.job_id = 'ST_CLERK'

        GROUP BY e.department_id ) v

 WHERE d.department_id = v.department_id

   AND v.sal = v.item_0 ;

 

옵티마이져가 재작성한 SQL을 보면 employee 테이블을 단 한번 사용하고 있으므로 Plan 상에도 엑세스가 한번 나온 것이다. 이 기능은 Oracle 11gR2에서 추가되었다.  

 

위의 예제는 Uncorrelated Subquery(비상관 서브쿼리)를 사용하는 예제이다. 비상관 서브쿼리라 함은 서브쿼리 내에 메인 쿼리와의 조인절이 없다는 뜻이다. 그런데 옵티마이져는 상관 서브쿼리에서도 같은 방식을 사용한다. 아래의 SQL을 보자.

 

SELECT a.employee_id, a.first_name, a.last_name, b.department_name

  FROM employee a, department b

 WHERE a.department_id = b.department_id

   AND a.employee_id = (SELECT MAX (s.employee_id)

                          FROM employee s

                         WHERE s.department_id = b.department_id);

 

부서별로 MAX 사원번호에 해당하는 정보를 구하는 SQL. SQL Plan 아래와 같다.

----------------------------------------------------+-----------------------------------+

| Id  | Operation                       | Name      | Rows  | Bytes | Cost  | Time      |

----------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT                |           |       |       |     6 |           |

| 1   |  VIEW                           | VW_WIF_1  |   106 |  7208 |     6 |  00:00:01 |

| 2   |   WINDOW BUFFER                 |           |   106 |  6466 |     6 |  00:00:01 |

| 3   |    MERGE JOIN                   |           |   106 |  6466 |     6 |  00:00:01 |

| 4   |     TABLE ACCESS BY INDEX ROWID | DEPARTMENT|    27 |   540 |     2 |  00:00:01 |

| 5   |      INDEX FULL SCAN            | DEPT_ID_PK|    27 |       |     1 |  00:00:01 |

| 6   |     SORT JOIN                   |           |   107 |  4387 |     4 |  00:00:01 |

| 7   |      TABLE ACCESS FULL          | EMPLOYEE  |   107 |  4387 |     3 |  00:00:01 |

----------------------------------------------------+-----------------------------------+

Predicate Information:

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

1 - filter("VW_COL_5" IS NOT NULL)

6 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

6 - filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

 

Plan 보면 employee 테이블을 단 한번만 엑세스 한다. 이것 역시 사람이 작성한 SQL을 옵티마이져가 성능에 문제가 된다고 판단하여 아래처럼 변경시킨 것이다.
 

SELECT VW_WIF_1.ITEM_1 EMPLOYEE_ID, VW_WIF_1.ITEM_2 FIRST_NAME,
       VW_WIF_1.ITEM_3 LAST_NAME, VW_WIF_1.ITEM_4 DEPARTMENT_NAME
  FROM (SELECT A.EMPLOYEE_ID ITEM_1, A.FIRST_NAME ITEM_2,
               A.LAST_NAME ITEM_3, B.DEPARTMENT_NAME ITEM_4,
               CASE A.EMPLOYEE_ID
                    WHEN MAX (A.EMPLOYEE_ID) OVER (PARTITION BY A.DEPARTMENT_ID)
                    THEN A.ROWID
               END VW_COL_5
          FROM TRANSFORMER.DEPARTMENT B, TRANSFORMER.EMPLOYEE A
         WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) VW_WIF_1
 WHERE VW_WIF_1.VW_COL_5 IS NOT NULL

 


부서별로 MAX(EMPLOYEE_ID)의 값과 EMPLOYEE_ID를 비교하여 같으면 ROWID를 출력하고 있다. 따라서 ROWID 값이 NULL이 아니라면 EMPLOYEE_ID는 부서별로 MAX(EMPLOYEE_ID)와 같음을 보장한다. 그러므로 중복 엑세스가 제거될 수 있는 것이다. 이 사실은 VW_COL_5 IS NOT NULL 조건이 추가된 이유이기도 하다. 이 기능은 Oracle10g R2 에서 추가되었다.

 

SQL을 재작성하는 튜너는 옵티마이져에 포함되어 있다. 내가 작성한 SQL PLAN이 어떻게 변경되었는지 관심을 가져야 한다. 더 나아가서 훈수를 두려면 옵티마이져에 포함되어 있는 튜너보다 더 나아야 할 것이다. “지식의 대융합”(이인식 저)이라는 책을 보면 2030년을 기점으로 하여 인간이 기계보다 더 나은 점을 발견하기 힘들 것이라 한다. 이 책의 내용은 전문가들이 작성한 논문과 책을 종합한 것이므로 함부로 무시 할 수 없다.

 

사람이 기계보다 우월하려면 기계(옵티마이져)의 기능과 한계를 분석하고 이해해야 한다. 영화 <메트릭스>에서 인간과 기계 사이에 평화가 찾아온 이유는 기계의 한계(약점)를 이해하고 그것을 고쳐주었기 때문이 아닌가?

 

참조서적: The Logical Optimizer 2.18 , 2.19


 

Posted by extremedb
,

오라클 11.2 버전은 아래의 링크에서 다운받을 수 있다.
http://www.oracle.com/technology/software/products/database/index.html


실습 스크립트 다운로드
실습을 진행하기 위한 스크립트는 아래와 같다.  

1. Schema Generation Script : Oracle 11gR1 과 11gR2중 버젼을 선택해서 다운 받으면 된다.
    다운받은후 User를 생성하고 권한부여 후 Import를 하면 실습 준비가 완료된다. 실습을 진행하려면
    TLO 계정으로 접속해야 한다. TLO 계정의 비밀번호는 transformer이다.
2. Part 1 Script : SQL 파일
3. Part 2 Script : SQL 파일과 10053 Trace 파일 포함
4. Part 3 Script : SQL 파일과 10053 Trace 파일 포함
5. Part 4 Script : SQL 파일과 10053 Trace 파일 포함
6. Appendix Script : 부록의 예제 스크립트임. SQL 파일

모두 다운 받으면 아래와 같이 총 15 개의 압축 파일이 된다.

사용자 삽입 이미지

용량이 크므로 7z 를 이용하여 압축 하였지만 일반적인 압축 프로그램으로 압축을 풀수 있다. 압축을 해제하면 위와 같은 폴더의 모습이 된다.
각 폴더의 용량을 합쳐 586 MB가 나오면 정상이다.
아래의 압축 파일을 모두 Download 하기 바란다.
데이터 import 시 에러가 나는 부분은 무시해도 된다. 정상적으로 처리된 것이다.
 


invalid-file

Schema 생성 Script for Oracle 11.2.0.1

invalid-file

Schema 생성 Script for Oracle 11.1.0.6

invalid-file

Scripts for Part1

invalid-file

Scripts for Part2

invalid-file

Scripts for Part3

invalid-file

Scripts for Part4

invalid-file

Scripts for Appendix

invalid-file

서브쿼리의 From 절에 테이블이 2개 이상일때 CBQT가 발생하는 예제




PS
한가지 걱정은 블로그 구독자 정도의 수준이라면 이책을 읽을 수 있으나 초보가 띠지의 내용등에 혹 해서 사면 어쩌나 하는 것이다.  주위에 그런사람들이 있다면 말려주기 바란다. 이 책은 초보용이 아니다.



구독자분이 스키마를 exp 형태 대신에 script 형태로 제공해 달라는 요청을 받았다.
아래의 스크립트를 이용하면 된다. 단 Oracle Sample 스키마인 SH와 HR 이 존재해야 한다.
 



'The Logical Optimizer' 카테고리의 다른 글

The Logical Optimizer Part 1 - PPT  (17) 2010.07.26
The Logical Optimizer-서점  (0) 2010.04.27
The Logical Optimizer-오타와 오류등록  (27) 2010.04.20
저자와의 대화  (36) 2010.04.20
The Logical Optimizer  (62) 2010.04.05
Posted by extremedb
,

bypass_ujvc 힌트와 관련하여 필자와 의견을 달리하는 전문가도 있음을 밝혀둔다. 특정 버젼에서 특정 상황에서 힌트를 확실히 이해하고 성능문제가 큰 경우일 때만 사용한다면 된다는 것이다. 제약사항을 4가지나 붙였으므로 공감이 가는 부분이 있다. 아래의 댓글을 반드시 읽어보기 바란다. 2010-04-19 (추가)

ANSI SQL
UPDATE문은 오라클과 달리 FROM 절이 존재하며 여러 테이블 혹은 뷰와 자유로이 조인할 수 있다.
아래의 SQL을 보자.

 

UPDATE DEPT

    SET DEPT_COUNT = E.CNT

FROM DEPT D,

(SELECT DEPTNO, COUNT(*) CNT

FROM EMP

WHERE JOB = ‘CLERK’

GROUP BY DEPTNO) E

WHERE D.DEPTNO = E.DEPTNO ;

 

위의 SQL MS-SQL 서버에서 사용할 수 있는 UPDATE문이지만 오라클에서 사용할 수 없다. 위의 SQL을 오라클로 바꾼다면 조인이 불가능하므로 아래처럼 스칼라 서브쿼리와 서브쿼리를 사용해야 한다.

 

UPDATE DEPT D

SET DEPT_COUNT = (SELECT COUNT(*)  

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO

AND E.JOB = ‘CLERK’)

WHERE EXISTS (SELECT 1

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO

AND E.JOB = ‘CLERK’) ;

 

중복 조인을 피할 수 있나? 

언뜻 보기에도 비효율이 극심하게 드러난다. EMP와 조인이 두 번 발생한 것이다. 중복된 조인을 피하기 위해서 아래처럼 인라인뷰와 스칼라 서브쿼리를 혼합하여 사용할 수 있지만 이 또한 중복 조인을 피할 수 없다. 보기에는 중복조인이 없는 것처럼 보이지만 Query Transformation을 공부하였다면 중복 조인이 보일 것이다.


UPDATE (SELECT d.deptno, d.dept_count,
               (SELECT COUNT (*)
                  FROM emp e
                 WHERE e.deptno = d.deptno
                   AND e.job = 'CLERK') cnt
          FROM dept d)
   SET dept_count = cnt
 WHERE cnt > 0;


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

| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)|

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

|   0 | UPDATE STATEMENT               |                |     4 |    64 |     5   (0)|

|   1 |  UPDATE                        | DEPT           |       |       |            |

|*  2 |   FILTER                       |                |       |       |            |

|   3 |    TABLE ACCESS FULL           | DEPT           |     4 |    64 |     3   (0)|

|   4 |    SORT AGGREGATE              |                |     1 |    11 |            |

|*  5 |     TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    11 |     2   (0)|

|*  6 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - filter( (SELECT COUNT(*) FROM "SCOTT"."EMP" "E" WHERE "E"."DEPTNO"=:B1

              AND "E"."JOB"='CLERK')>0)

   5 - filter("E"."JOB"='CLERK')

   6 - access("E"."DEPTNO"=:B1)

 

Predicate Information을 보면 ID 기준으로 2번에서 서브쿼리가 FILTER로 사용되었고 6번에서 다시 스칼라 서브쿼리가 사용되었으므로 두 번 조인이 발생한 것이다. 이해가 되지 않는 독자는 스칼라 서브쿼리를 서브쿼리로 변환하라 글을 읽기 바란다.

 

또 다른 제약

조인이 안 된다는 제약을 피하기 위해 VIEW 혹은 인라인뷰를 UPDATE 하곤 한다. 하지만 아래의 새로운 예제를 본다면 또 다른 제약이 있음을 알 수 있다.

 

create or replace view v_emp

as

select  e.empno, e.ename, e.job, e.sal, d.dname, d.deptno

from   emp e, dept d

where  e.deptno = d.deptno;

 

update V_EMP

   set dname = ‘NO_DEPT’ --> DEPT 쪽을 UPDATE 하고 있으므로 에러

 where empno = ‘7369’;

 

ERROR at line 2:

ORA-01779: cannot modify a column which maps to a non key-preserved table

 

Deptemp 1:N의 관계인데 1쪽을 Update 하지 못하는 제약을 만나게 된다. 이 제약을 피하기 위해서 bypass_ujvc 힌트를 사용하는 사람이 있는데 절대 사용하면 안 되는 힌트이다. 힌트를 해석하자면 Updatable Join View Check By-Pass(무시) 하겠다는 뜻이다. 실제로 Wrong Result(답이 잘못됨)가 나오므로 사용해서는 안 된다.

 

끝없는 제약

ODS 시스템이나 데이터를 이행하기 위한 Temp성 테이블에는 Key가 없는 경우가 많다. 아래는 PK를 제거한 상태에서 인라인뷰를 UPDATE 해보았다. 단순히 사번이 들어오면 부서번호가 부서 테이블에 존재하는지 체크하여 급여를 UPDATE 하는 SQL이다. 
 

ALTER TABLE SCOTT.DEPT MODIFY CONSTRAINT PK_DEPT DISABLE; 

Update (select a.empno, a.ename, a.sal, b.dname

          from emp a, dept b

         where a.deptno = b.deptno

           and a.empno = 7369)
set sal = 5000;


ORA-01779: cannot modify a column which maps to a non key-preserved table

 

Key를 사용할 수 없으므로 뷰 혹은 인라인뷰를 update할 때 키 보존 제약이 걸리게 된다. 이때 마찬가지로 bypass_ujvc를 사용하면 에러는 피할 수 있지만 결과를 보장 하지 않는다. 이 힌트는 건널목 신호등에 빨강 불이 들어왔지만 알아서 건너가시오. 자동차에 부딪혀도 책임지지 않습니다.” 로 비유할 수 있다.

 

Bypass_ujvc 힌트를 사용하지 않고 해결해야 해

위에서 언급한 세가지 제약조건(조인이 안됨, 뷰에서 1 update 안됨, 뷰에서 키가 없으면 update 불가)과 한가지 문제(중복 조인)를 피할 수 있는 방법이 있다.  

1) 조인이 안 되는 문제와 중복 조인문제 해결

 

MERGE INTO dept d

USING (SELECT deptno, COUNT (*) cnt

         FROM emp

        WHERE job = 'CLERK'

        GROUP BY deptno) e

   ON (e.deptno = d.deptno)

 WHEN MATCHED THEN

UPDATE SET d.dept_count = e.cnt;

 

Merge successfully completed.

 

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

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

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

|   1 |  MERGE                         | DEPT    |      1 |00:00:00.01 |      18 |

|   2 |   VIEW                         |         |      3 |00:00:00.01 |      13 |

|   3 |    NESTED LOOPS                |         |      3 |00:00:00.01 |      13 |

|   4 |     NESTED LOOPS               |         |      3 |00:00:00.01 |       9 |

|   5 |      VIEW                      |         |      3 |00:00:00.01 |       7 |

|   6 |       SORT GROUP BY            |         |      3 |00:00:00.01 |       7 |

|*  7 |        TABLE ACCESS FULL       | EMP     |      4 |00:00:00.01 |       7 |

|*  8 |      INDEX UNIQUE SCAN         | PK_DEPT |      3 |00:00:00.01 |       2 |

|   9 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |00:00:00.01 |       4 |

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

Predicate Information (identified by operation id):

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

   7 - filter("JOB"='CLERK')

   8 - access("E"."DEPTNO"="D"."DEPTNO")

 

2) 1쪽이 UPDATE 안 되는 문제 해결

 

MERGE INTO dept d

USING emp e

   ON (e.deptno = d.deptno AND e.empno = '7369')

 WHEN MATCHED THEN
UPDATE SET d.dname = 'NO_DEPT' ;

 

Merge successfully completed.

 

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

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

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

|   1 |  MERGE                         | DEPT    |      1 |00:00:00.01 |       5 |

|   2 |   VIEW                         |         |      1 |00:00:00.01 |       4 |

|   3 |    NESTED LOOPS                |         |      1 |00:00:00.01 |       4 |

|   4 |     TABLE ACCESS BY INDEX ROWID| EMP     |      1 |00:00:00.01 |       2 |

|*  5 |      INDEX UNIQUE SCAN         | PK_EMP  |      1 |00:00:00.01 |       1 |

|   6 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |00:00:00.01 |       2 |

|*  7 |      INDEX UNIQUE SCAN         | PK_DEPT |      1 |00:00:00.01 |       1 |

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

Predicate Information (identified by operation id):

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

   5 - access("E"."EMPNO"=7369)

   7 - access("E"."DEPTNO"="D"."DEPTNO")

 

 

3) 키가 없으면 UPDATE가 불가한 문제 해결

 

ALTER TABLE SCOTT.DEPT MODIFY CONSTRAINT PK_DEPT DISABLE;

 

MERGE /*+ USE_HASH(D) */ INTO emp e

USING dept d

   ON (e.deptno = d.deptno AND e.empno = 7369)

 WHEN MATCHED THEN
UPDATE SET e.sal = 5000;

 

Merge successfully completed.

 

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

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

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

|   1 |  MERGE                         | EMP    |      1 |00:00:00.01 |      12 |          |

|   2 |   VIEW                         |        |      1 |00:00:00.01 |       9 |          |

|*  3 |    HASH JOIN                   |        |      1 |00:00:00.01 |       9 |  316K (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| EMP    |      1 |00:00:00.01 |       2 |          |

|*  5 |      INDEX UNIQUE SCAN         | PK_EMP |      1 |00:00:00.01 |       1 |          |

|   6 |     TABLE ACCESS FULL          | DEPT   |      4 |00:00:00.01 |       7 |          |

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

Predicate Information (identified by operation id):

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

   3 - access("E"."DEPTNO"="D"."DEPTNO")

   5 - access("E"."EMPNO"=7369)

 


결론

위의 SQL 세가지는 큰 문제 4가지를 해결한 것이므로 익혀서 적재적소에 활용하기 바란다.
MERGE
문의 문법은 매우 간단하다. 하지만 이 문법을 보고 그것을 어디에 어떻게 활용할 것인가는 전혀 다른 문제이다. DBMS의 버전이 올라가면 신기능이 탄생한다. 그때마다기능을 어디에 사용하면 가장 큰 효과가 나타날 것인지를 생각해 보라. 오늘보다 더 발전된 내일의 당신을 위해서.

 

Posted by extremedb
,

필자는 가끔 분석함수의 기능에 관해 질문을 받는다. 그때마다 대답을 하지 않고 대신에 질문에 부합하는 Analytic SQL을 보여주고 결과를 분석하게 한다. 바로 답을 주게 되면 개발자가 의존적이 되고 분석함수 각각의 기능도 금방 잊어버리기 때문이다.

 

개발자만 모르는 것이 아니다

얼마 전에 어느 DBA로 부터 요청이 왔다. 자신을 포함한 개발자들이 분석함수를 어려워하니 블로그에 분석함수의 모든 것을 정리한 문서를 올려달라는 것이었다. 물론 오라클 매뉴얼이나 Tomas Kyte Expert One on One 등의 책에 이 함수들의 기능이 있지만 페이지 수가 너무 많고 영문이라는 단점이 있다는 것이었다. 이것은 놀라운 일이다. 개발자뿐 아니라 DBA, 심지어 컨설턴트까지 Analytic Function에 관해 필자에게 질문을 던지곤 한다. Oracle8i 부터 기능이 구현되었으니 기간으로 따지자면 10년 이상 존재했던 함수인데 아직까지......

 

도대체 Analytic Function이 얼마나 어려우면 전문가 까지도 개념이 서지 않는 걸까? 고민 끝에 핵심만 정리한 문서를 올리기로 했다.

 

핵심은 3가지다

분석함수의 기능이 복잡한 것 같지만 사실은 3가지만 알면 90%를 이해한 것이다.

1) Over 절에서 사용하는 Order by의 기능

2) Over 절에서 사용하는 Partition by의 기능

3) Over 절에서 사용하는 Windowing 기능

 

이것이 90% 이다. 대부분의 개발자와 DBA들은 1)번과 2)번에 대해서 많이 알고 있지만 이상하게도 3)번에 대해서 개념이 서질 않는다고 하였다. 따라서 아래의 문서를 다운받아서 공부할 때 3)번을 집중적으로 보기 바란다.

 

그럼 나머지 10%?

나머지는 아래와 같다. 위의 3가지를 안다면 아래의 함수들은 쉽게 이해할 수 있다. 그저 종류가 많을 뿐이다.

 

      RANK, DENSE_RANK, and ROW_NUMBER --> 3가지 함수의 차이점

      FIRST/LAST

      NTILE, WIDTH_BUCKET, CUME_DIST and PERCENT_RANK

      Hypothetical Functions

      FIRST_VALUE/LAST_VALUE , LAG/LEAD

      Reporting Functions/RATIO_TO_REPORT

      Handling null

 

빨강색 부분은 개발자들이 많이 질문하는 것들이다. 참고하기 바란다.

 

Paper라고 다 같은 것은 아니다

매뉴얼이나 관련서적의 문제점은 페이지 수가 많다는 것이다. 예를 들어 분석함수 부분이 60페이지가 넘어간다면 기능을 익히는데 며칠 혹은 몇 주가 걸릴 수 있다. 필자는 페이지 수가 많은 것을 아주 싫어한다. 아래의 문서는 앞쪽의 목차와 중요성, 그리고 뒤쪽의 마무리 부분을 제외하면 9(18 페이지)으로 모든 기능과 개념을 설명하였다. 아마 한 두 시간 이내에 다 볼 수 있을 것이다.

 

invalid-file

테이블 생성 파일

invalid-file

Mastering Oracle Analytic Function ppt 파일



PS

분석함수를 문법이라고 치부해 버리는 사람들이 있다. 그렇지 않다. 많은 경우에 분석함수를 쓰는 것이 SQL 튜닝이 된다. 오죽하면 옵티마이져가 평범한 SQL을 분석함수를 사용하는 것으로 바꾸겠는가?
이제부터 필자에게 분석함수를 질문하는 개발자가 없기를 바란다. ^^


Posted by extremedb
,

"멀티 컬럼으로 Range 파티션을 할 경우 Where 절에 파티션 선두 컬럼에 해당하는 조건만 주어도 Partition Pruning이 수행된다"

위의 말이 사실일까? 어디서 흘러나온 말인지 모르겠으나 위의 경우는 Partition Pruning이 제대로 되지 않는다. 함정이 기다리고 있기 때문이다.

아래의 스크립트를 실행해서 직접 증명해보자.          

 

환경: Oracle 10.2.0.4

       4 Node RAC

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', 5),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

  PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

  PARTITION pt_2_4 VALUES LESS THAN ('2', 5),

  PARTITION pt_3_1 VALUES LESS THAN ('3', 2),

  PARTITION pt_3_2 VALUES LESS THAN ('3', 3),

  PARTITION pt_3_3 VALUES LESS THAN ('3', 4),

  PARTITION pt_3_4 VALUES LESS THAN ('3', 5),  

  PARTITION pt_4_1 VALUES LESS THAN ('4', 2),

  PARTITION pt_4_2 VALUES LESS THAN ('4', 3),

  PARTITION pt_4_3 VALUES LESS THAN ('4', 4),

  PARTITION pt_4_4 VALUES LESS THAN ('4', 5), 

  PARTITION pt_5_1 VALUES LESS THAN ('5', 2),

  PARTITION pt_5_2 VALUES LESS THAN ('5', 3),

  PARTITION pt_5_3 VALUES LESS THAN ('5', 4),

  PARTITION pt_5_4 VALUES LESS THAN ('5', 5),     

  PARTITION pt_6_1 VALUES LESS THAN ('6', 2),

  PARTITION pt_6_2 VALUES LESS THAN ('6', 3),

  PARTITION pt_6_3 VALUES LESS THAN ('6', 4),

  PARTITION pt_6_4 VALUES LESS THAN ('6', 5),   

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', 5)

);

 

Table created.

 

먼저 실습을 진행할 테이블을 생성하였다. day_num 컬럼은 1~7 로 구성되어 있다. 모든 일자 데이터를 7등분 하기 위함이다. inst_id 컬럼은 RAC에서 Instance 번호이며 1~4로 구성된다. gc buffer busy등을 줄이기 위한 용도로 자주 사용된다.

 

이제 테스트용 데이터를 insert 하자.

 

INSERT INTO t

SELECT rownum AS id,

       trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1,

       to_char(trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4),'d') AS day_num,

       1+mod(rownum,4) AS inst_id,

       dbms_random.string('p',255) AS pad

FROM dual

CONNECT BY level <= 10000

ORDER BY dbms_random.value;

 

10000 rows created.

 

commit;

 

BEGIN

  dbms_stats.gather_table_stats(

    ownname          => user,

    tabname          => 'T',

    estimate_percent => 100,

    method_opt       => 'for all columns size skewonly',

    cascade          => TRUE

  );

END;

/

 

PL/SQL procedure successfully completed.

 


각 파티션에 데이터가 골고루 Insert 되었는지 살펴보자.

SELECT partition_name, partition_position, num_rows

 FROM user_tab_partitions

 WHERE table_name = 'T'

 ORDER BY partition_position;

 

PARTITION_NAME                 PARTITION_POSITION   NUM_ROWS

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

PT_1_1                                          1        356

PT_1_2                                          2        358

PT_1_3                                          3        355

PT_1_4                                          4        355

PT_2_1                                          5        364

PT_2_2                                          6        364

PT_2_3                                          7        362

PT_2_4                                          8        362

PT_3_1                                          9        358

PT_3_2                                         10        355

PT_3_3                                         11        355

PT_3_4                                         12        356

PT_4_1                                         13        357

PT_4_2                                         14        355

PT_4_3                                         15        355

PT_4_4                                         16        358

PT_5_1                                         17        355

PT_5_2                                         18        355

PT_5_3                                         19        357

PT_5_4                                         20        358

PT_6_1                                         21        355

PT_6_2                                         22        355

PT_6_3                                         23        358

PT_6_4                                         24        356

PT_7_1                                         25        355

PT_7_2                                         26        358

PT_7_3                                         27        358

PT_7_4                                         28        355

 

28 rows selected.


모든 파티션에 건수가 골고루 분배되었다. 그럼 이제 파티션의 선두 컬럼 조건만 있을 경우 Partition Pruning이 수행되는지 알아보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';

 

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

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |     115 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    13 |   1424 |     115 |

|*  3 |    TABLE ACCESS FULL      | T    |      5 |     9 |    13 |   1424 |     115 |

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


불필요한 파티션을 액세스 한다
Pstart
Pstop 항목을 보면 9번째 파티션부터 13번째 파티션까지 Scan 되었다.

Partition Pruning이 잘된 것처럼 보이지만 자세히 보면 DAY_NUM 3인 파티션은 4개뿐인데 5개의 파티션을 액세스 하였다.

 

어떻게 된 것인가?

DAY_NUM 3인 파티션은 4개 이지만 inst_id 컬럼에 어떤 값이 있을지 알 수 없으므로 13번째 파티션을 액세스 할 수 밖에 없는 것이다. 다시 말하면 DAY_NUM 3이면서 inst_id 6인 데이터는 13번째 파티션에 들어갈 수 있으므로 불필요한 Scan이 일어나는 것이다.

 

물론 아래와 같이 DAY_NUM 조건과 INST_ID 조건을 같이 준다면 이런 현상은 발생하지 않는다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3'

   AND INST_ID = 2;

  

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

| Id  | Operation               | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

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

|   1 |  SORT AGGREGATE         |      |      1 |       |       |      1 |      23 |

|   2 |   PARTITION RANGE SINGLE|      |      1 |    10 |    10 |    355 |      23 |

|*  3 |    TABLE ACCESS FULL    | T    |      1 |    10 |    10 |    355 |      23 |

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


정확히 10번째 파티션만 액세스 하였다.

만약 INST_ID 조건을 줄 수 없는 경우라면?

모든 경우에 INST_ID 조건을 줄 수는 없을 것이다. 예를 들면 전체를 처리해야 하는 경우는 INST_ID 조건을 줄 수 없을 것이다. 이때 어떻게 하면 비효율을 없앨 수 있겠는가?


해결방법1

특정 inst_id 조건을 주지 못할 때는 inst_id 의 범위를 주어 불필요한 파티션 scan을 방지한다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3'

   AND INST_ID BETWEEN 1 AND 4;

 

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

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |      92 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    12 |   1424 |      92 |

|*  3 |    TABLE ACCESS FULL      | T    |      4 |     9 |    12 |   1424 |      92 |

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


정확히 4개의 파티션만 액세스 하여 비효율이 사라졌다..

 

해결방법2

조건을 주지 못할 때는 4번째 inst_id 파티션을 maxvalue로 바꾼다.

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', maxvalue),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

  PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

  PARTITION pt_2_4 VALUES LESS THAN ('2', maxvalue),

  ...중간생략

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', maxvalue)

);

 

위에서 실행한 insert문과 dbms_stats을 여기서 다시 실행하고 아래의 SQL을 수행하자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';


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

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

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

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |      92 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    12 |   1424 |      92 |

|*  3 |    TABLE ACCESS FULL      | T    |      4 |     9 |    12 |   1424 |      92 |

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

 

정확히 4개의 파티션만 액세스 하였다. 비효율이 없어진 것이다.

 

결론

이제 maxvalue의 진정한 의미를 알겠는가?

단순히 마지막 값이라는 의미 이외에 Partition Pruning에 영향을 끼치며 이것은 성능과 직결된다.

또한 멀티 컬럼으로 Range 파티션을 할 경우 where 조건에 따라 불필요한 파티션을 액세스 하므로 주의해야 한다.  

 

PS

위의 해결책 1,2 를 통해 문제를 해결하지 않으면 해당 select문의 성능저하뿐만 아니라 해당 테이블을 사용하는 DML문의 성능이 저하되는 끔찍한 현상이 발생할 수 있다. 다음시간에 이 문제에 대하여 논의 해보자.


Posted by extremedb
,

Dummy Table(흔히 Copy_t 라고 불림) 대신에 9i 이후부터는 Connect By level 문을 사용하곤 한다. 하지만 조심하지 않으면 해당 SQL이 종료되지 않는 장애를 만나게 된다. 오늘은 Connect By level 문을 오용하는 사례와 해결책을 제시하고자 한다.

상황
업무팀에서 새로운 SQL을 작성하고 컴파일하여 운영 시스템에 반영되었다. 문제의 SQL이 실행되자 너무 오래걸려서 Time Out이 발생하였다. 아래는 상황을 최대한 간단히 표현하여 테스트를 수행하기 위한 스크립트 이다.

--입사년도 테이블 생성
create table hire as
select '2006' hire_date from dual union all
select '2003' hire_date from dual union all
select '2002' hire_date from dual union all
select '1999' hire_date from dual union all
select '1997' hire_date from dual ;

--현재년도 에서 입사년도를 빼서 차이(gap)를 나타냄
 select hire_date,
        to_char(sysdate, 'YYYY') this_year,
        to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap
   from hire   ;


결과:
HIRE THIS        GAP
---- ---- ----------
2006 2010          4
2003 2010          7
2002 2010          8
1999 2010         11
1997 2010         13

5 rows selected.

업무요건
위의 SQL의 결과에서 나타난 GAP만큼 가상의 ROW를 생성하여야 한다. 즉 입사년도가 2006년인 사람은 ROW가 4개로 되어야 하고 2003년인 사람은 ROW가 7개가 되어야 한다. 전체적으로 43건이 나와야 한다. 아래에 원하는 답이 있다.
 
원하는 답
HIRE THIS        GAP        NUM
---- ---- ---------- ----------
2003 2010          7          7
2003 2010          7          6
2003 2010          7          5
2003 2010          7          4
2003 2010          7          3
2003 2010          7          2
2003 2010          7          1
2006 2010          4          4
2006 2010          4          3
2006 2010          4          2
2006 2010          4          1
....중간생략

문제의 SQL
아래의 SQL은 gap을 Connect By Level 절에 적용시킨 것이다. 아래처럼 SQL을 작성한다면 지옥을 경험할 수 있다.

select hire_date,
       to_char(sysdate, 'YYYY') this_year,
       to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
      level
  from hire
connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) ;

위의 SQL은 전체건을 Fetch하려면 10분이 걸려도 끝나지 않았다. 시간이 너무 오래 걸리므로 아래처럼 COUNT 로 바꿔서 실행해 보았다.

select /*+ gather_plan_statistics */ count(*)
  from  ( select hire_date,
                 to_char(sysdate, 'YYYY') this_year,
                 to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
                 level
            from hire
         connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date)
        );

       
결과:
  COUNT(*)
----------
   3773280

버그인가?
무려 370만건 이상의 건수가 나왔다. 이상하지 않은가? 건수의 예측도 할 수 없었다. 건수와 관련해서 일정한 규칙도 존재하지 않았다. 버그인지 아닌지 알 수 없지만 결론적으로 위의 SQL처럼 사용하면 안된다는 것을 알 수 있다. 아래는 Count에 대한 실행통계인데 무려 44초나 걸렸다.

-------------------------------------------------------------------------------
| Id  | Operation                      | Name | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |      |      1 |00:00:44.39 |       3 |
|   2 |   VIEW                         |      |   3773K|00:00:45.28 |       3 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |   3773K|00:00:41.51 |       3 |
|   4 |     TABLE ACCESS FULL          | HIRE |      5 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------


해결방법

 select /*+ gather_plan_statistics leading(hire) */
        hire_date,
        to_char(sysdate, 'YYYY') this_year,
        to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
        num
   from hire,
        (select level as num
           from dual
        connect by level <= 40  --> 충분한 값을 주어야 한다.
        ) b
  where num <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date);  



Connect By 절을 인라인뷰로 만들어 Dummy 테이블처럼 사용하였다. 주의 사항은 connect by level <= 40 에서 숫자값을 충분히 주어야 한다. GAP 중에 가장 큰것이 13 이므로 넉넉히 40을 주었다.    
 
결과:
HIRE THIS        GAP        NUM
---- ---- ---------- ----------
1997 2010         13         13
1997 2010         13         12
1997 2010         13         11
.....중간생략
2006 2010          4          4
2006 2010          4          3
2006 2010          4          2
2006 2010          4          1

43 rows selected.  

정상적으로 원하는 결과가 나왔고 성능도 이상적이다.

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                     |      |     43 |00:00:00.01 |       3 |          |
|   2 |   SORT JOIN                     |      |      5 |00:00:00.01 |       3 | 2048  (0)|
|   3 |    TABLE ACCESS FULL            | HIRE |      5 |00:00:00.01 |       3 |          |
|*  4 |   SORT JOIN                     |      |     43 |00:00:00.01 |       0 | 2048  (0)|
|   5 |    VIEW                         |      |     40 |00:00:00.01 |       0 |          |
|   6 |     CONNECT BY WITHOUT FILTERING|      |     40 |00:00:00.01 |       0 |          |
|   7 |      FAST DUAL                  |      |      1 |00:00:00.01 |       0 |          |
-------------------------------------------------------------------------------------------
 
주의사항
만약 위의 SQL처럼 인라인뷰를 사용한다고 해도 Nested Loop 조인으로 풀리고 Connect By문을 사용한 인라인뷰가 후행집합이 된다면 선행집합의 건수만큼 반복해서 Connect By문이 수행되므로 조심해야 한다.


결론
Dummy 테이블을 대신하는 Connect By Level을 사용할 때 주의하지 않으면 SQL이 종료되지 않는다. 이를 방지하려면 반드시 DUAL과 함께 사용해야 하며 인라인뷰를 만들어서 사용해야 된다. 만약 예전처럼 Dummy 테이블을 사용했다면 이런 성능저하는 발생하지 않을 것이다. 아무리 새롭고 좋은것도 오용한다면 결과가 어떻게 되는지 잘 보여주는 예제이다.

Posted by extremedb
,

3 부작의 마지막 편
첫 번째,
Oracle Data Access Pattern을 정복하라
두 번째, Data Access Pattern중의 파티션에 관련된
Partition Access Pattern 에 이어서 마지막 편이다.

지난 글에서 Data Access Pattern 과 Join Method 이 두 가지는 기본 중에 기본이라고 하였다.
또한 이 두 가지를 정복한다면 SQL 튜닝중의 많은 부분을 커버할 수 있다고 하였다.
튜닝에서 이것보다 중요한 것이 있을까? 이것들 중에 하나라도 빠트린다면 제대로 된 튜닝을 할 수 없다.

단순 분류 5가지
데이터의 연결방법은 단순분류해 보면 다음과 같다.

1.Nested Loop Join
2.Sort Merge Join
3.Hash Join
4.Outer Join
5.Using Subquery

세분화
하지만 이것으로는 부족하다. Join Method를 좀더 자세히 나타내면 다음과 같다.

01. Nested Loop Join
02. Sort Merge Join
03. Hash Join
04. Cartesian Join (혹은 Cross Join)
05. Sub Query-In,
06. Sub Query-Any
07. Sub Query-All
08. Sub Query-Exists
09. Subquery Factoring
10. Semi Join-Nested Loop
11. Semi Join-Sort Merge
12. Semi Join-Hash
13. Semi Join-Hash Join Right
14. Anti Join-Nested Loop
15. Anti Join-Sort Merge
16. Anti Join-Hash
17. Anti Join-Hash Join Right
18. Index Join
19. Outer Join-Full
20. Outer Join-Nested Loop
21. Outer Join-Sort Merge
22. Outer Join-Hash
23. Outer Join-Hash Join Right
24. Partition Outer Join
25. Star Query Transformation
 
극한의 세분화
물론 여기서 더 세분화 시킬 수 있다. 예를 들면 Nested Loop Join은 아래와 같이 분류할 수 있다.

Full(선행집합)-Unique (후행집합)
Full(선행집합)-Range (후행집합)
Range(선행집합)-Range (후행집합)
Unique(선행집합)-Unique (후행집합)
....중간생략

이런 방법으로 Sort Merge Join과 Hash Join까지 계속 나열한다면 아마 끝이 없을 것이다.

단 한 줄도 놓치지 마라 
아래의 첨부파일에는 Nested Loop Join도 위와 같은 방법으로 가능한 세분화 하였다. 따라서 이 파일에 담긴 Join method는 25가지가 넘는다. 오늘 이야기하는 조인방법들은 튜닝을 하려면 반드시 정복해야 할 주제이니 꼼꼼히 보기 바란다.


invalid-file

Oracle Data Join Method



PS
Star Join은 Star Query Transformation이 나온 후로 설 땅을 잃었으므로 나타내지 않았다.

Posted by extremedb
,

SQL의 길이에 따른 분석시간

언제부터인가 복잡한 업무의 배치작업에 한방 SQL이 유행하기 시작했다. 좋은 현상이다. 하지만 이제 정도가 지나친 SQL들이 가끔 눈에 뛴다. 한방 SQL을 사용하지 말아야 할 때와 사용해야 할 때를 구분할 줄 알아야 한다. SQL이 어느 정도 길어지면 PL/SQL 이나 PRO*C 등을 이용하여 절차형으로 바꾸어야 한다. 이렇게 하더라도 Bulk CollectFor all 등으로 처리하거나 배열처리를 병행한다면 만족할 만한 속도를 낼 수 있다..

 

아래는 SQL의 길이와 SQL을 전체적으로 이해하는데 걸리는 시간을 조합한 그래프이다.

사용자 삽입 이미지

SQL이 길어지면 이해하기 힘들어

이 그래프를 본다면 SQL을 길게 작성하는 것이 얼마나 위험한지 알 수 있다. SQL의 길이가 짧으면 짧을수록 그것을 이해하는 데 걸리는 시간은 얼마 되지 않음을 알 수 있다. 반대로 SQL의 길이가 길수록 이해하는 데 걸리는 시간은 무한대로 늘어난다. 누구도 위의 그래프에 예외일 수 없다.

만약 여러분이 업무 인수인계를 받는 입장인데 SQL 하나가 A4 용지 기준으로 40페이지 라면? 아마 인수인계 받는데 한달이 걸려도 전체 SQL을 이해하기 힘들 것이다. 하지만 1페이지짜리 SQL 40개 라면 웃으며 차근 차근 인수인계를 받을 수 있다. 하루에 SQL 3~4개 혹은 그 이상도 인수인계 받을 수 있다. 하루에 4개씩 인수 인계 받는다면 10일 이면 인수 인계가 끝난다. 40 페이지나 되는 한방 Query는 유지보수 하기가 대단히 어려움을 알아야 한다.

 

이제 위의 그래프에 근거하여 한방 SQL을 사용해도 되는 경우와 사용하지 말아야 할 경우를 구분해 보자.

 

한방 SQL을 사용해도 되는 경우

첫 번째, SQLA4 용지 기준으로 4페이지 이하인 경우.

4페이지라고 한 것은 꼭 정해진 것은 아니다. 하지만 유지보수의 관점에서 가독성이 좋아야 한다. 4페이지면 조금 길어서 가독성이 낮아진다고 생각할 수 있지만 필자의 경우 SQL을 출력할 때 한 면에 인쇄할 페이지 수를 2로 설정하면 2페이지만 보면 전체 SQL이 출력 되므로 4페이지 까지는 조금만 노력해도 분석이 용이했다. 하지만 한면에 인쇄할 페이지 수를 4로 하자 글자가 너무 작아져서 볼 수 없는 수준이었다. 필자의 경우 기준은 4페이지 이지만 개인에 따라 기준은 2페이지 일 수도 있고 6페이지 일 수도 있다. 하지만 아무리 SQL에 능통한 사람도 SQL의 길이가 A4 용지 기준으로 8페이지 이상이 된다면 분석시간이 급속도로 늘어날 것이다..

 

두 번째, SQL5페이지가 넘어 가더라도 Union 혹은 Minus 등으로 명확히 구분되거나 누가 보더라도 이해가 빠른 SQL인 경우.

이 경우는 5페이지가 넘어가지만 빠른 시간에 분석할 수 있으므로 5페이지가 넘어 가더라도 유지보수가 용이하다. 하지만 이 경우에도 8페이지가 넘어간다면 고민해야 한다.

 

세 번째, SQL5페이지가 넘어 가고 업무의 변경이 있더라도 SQL을 변경하는 것이 아니라 SQL을 새로 작성하기로 합의하거나 혹은 이러한 정책이 수립된 경우.

이 경우는 SQL을 수정할 일이 없으므로 길어도 상관없다. 하지만 SQL을 새로 작성하는 사람이 모델과 업무를 잘 알고 있고 튜닝을 할 줄 알아야 고품질의 SQL을 작성할 수 있다.

 

네 번째, 유지보수의 중요성 보다 성능이 더 중요한 경우.

대용량의 복잡한 업무를 처리하는데 일주일이 넘어간다면 견딜 수 없을 것이다. 예를 들면 요금청구 작업의 성능은 기업의 흥망을 좌우할 수 있다. 이런 경우는 유지보수를 희생하더라도 한방 Query를 사용할 수 있다.

 

다섯 번째, SQL5페이지가 넘어 가지만 업무의 변경이 전혀 없어 SQL을 수정 할 일이 없는 경우.

유지보수를 할 필요가 없는 경우이다. 하지만 이런 상황은 아주 예외적인 경우일 것이다.

 

위의 5가지 경우가 아니라면 한방 SQL을 작성해서는 안 된다.

한방 Query와 관련한 유명한 일화

HR(인적자원 관리) 프로젝트에서 급여를 계산하는 프로그램을 한방 SQL로 개발하였고 40페이지가 넘는다고 했다. 급여계산은 한방 Query의 성능이 빨라 Open을 성공적으로 했다고 한다. 하지만 문제는 Open2년 뒤에 찾아왔다. 업무가 변경되어 급여계산의 SQL을 수정해야 하는데 아무도 SQL을 수정할 수 있는 사람이 없었다. 조직내부에서 몇 주간 고민해 보았으나 결국 분석을 포기하고 원작자를 불렀다.  

 

핵심은 이렇다. 돈을 많이 쳐줄 테니 SQL을 고쳐달라는 것이었다. 하지만 누가 그랬던가? 사람은 망각의 동물이라고...... 결국 원작자도 2년이 지난 이상 40페이지가 넘는 SQL을 외우고 다닐 수는 없는 노릇이 아닌가? 그는 분석을 포기하였다고 한다. 아래는 원작자가 분석을 포기한 이유이다.

 

원작자: 돈을 아무리 준다고 해도 그 기간 내에는 할 수가 없습니다. 인라인 뷰가 80개가 넘는데 분석하는 데만 2~3달 걸릴 것 같습니다.

요청자:  두달 안에 변경된 업무를 반영해야 하는데 큰일 났네....

 

결국 원작자는 돌려보내고 급여 담당자가 프로그램을 절차형으로 모두 새로 작성했다고 한다. 새로 작성하는데 꼬박 한달이 걸렸다고 한다. 위의 원작자는 분석하는데만 두 달이 넘는다고 하였다. 하지만 급여담당자는 한달안에 모든 프로그램이 작성 완료되었음을 주목하라. 담당자는 한방 Query 보다 성능은 떨어졌지만 상관이 없다고 하였다. 아래는 급여 담당자의 이야기이다.

 

급여 담당자: 급여 배치가 30분 정도 결렸는데 절차형으로 바꾸니 두 시간이 걸리네요. 하지만 상관 없습니다. 오늘 저녁에 급여 배치를 돌리고 내일 급여가 지급되기 때문에 내일 오후 1시까지 배치가 끝나면 됩니다.

원작자는 유지보수의 중요성을 무시한채 Critical 하지도 않은 성능만 고려한 것이다. 아무리 좋은것 이라도 지나치면 괴로워진다. 이제는 한방 Query를 남발하지 않았으면 한다.


Posted by extremedb
,

필자는 예전에 Recursive SQL이라는 글을 통하여 IBM DB2 Microsoft SQL Serve에서 사용하는 Recursive With문을 소개한적이 있다. 시간이 흘러 이제는 Oracle11gR2에서 Recursive With문을 사용할 수 있게 되었다. 오라클에서도 DB2SQL Serve처럼 ANSI SQL을 사용할 수 있게 된 것이다.

 

DBMS에 능숙한 사람들에게는 환영할만한 일이나 오라클을 사용한 사람들은 별로 달가워하지 않는 것 같다. 왜냐하면 기존의 오라클 사용자들은 Connect By의 기능이 워낙 막강하였으므로 굳이 ANSI SQL을 사용할 필요가 없다고 생각하기 때문이다.

 

하지만 과연 그럴까? 모든 기능은 용도가 있다.

 

이 글을 보고 알 수 있는 사항은 다음과 같다.

 

1. Connect By Recursive With의 문법 비교

2. Connect By Recursive With의 기능 비교

3. Connect By Recursive With의 성능 비교

4. 1, 2, 3번을 통하여 Connect By Recursive With의 장단점 파악

 

1) 문법 관점에서 Connect By VS Recursive With

 

 SELECT employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

 

EMPLOYEE_ID FIRST_NAME           MANAGER_ID      LEVEL

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

        100 Steven                                   1

        101 Neena                       100          2

        108 Nancy                       101          3

        109 Daniel                      108          4

        110 John                        108          4

...중간생략

        202 Pat                         201          3

 

107 rows selected.

 

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE manager_id IS NULL                  --> START WITH 절에 해당함

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id --> CONNECT BY 절에 해당함

)

SELECT *

  FROM recursive;

 

 

EMPLOYEE_ID NAME                 MANAGER_ID RECURSIVE_LEVEL

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

        100 Steven                                        1

        201 Michael                     100               2

        101 Neena                       100               2

        102 Lex                         100               2

        114 Den                         100               2

...중간생략

        107 Diana                       103               4

 

107 rows selected.

 

SQL문이 길어졌다. 하지만 가독성은?

위의 예제에서 볼 수 있듯이 Recursive With 문은 Connect By에 비하여 가독성이 좋아졌다. 왜냐하면 시작조건과 찾아가는 조건이 Union All로 분기되어 있으므로 SQL이 조금 길어지긴 하였으나 해석하는데 전혀 어려운 점이 없기 때문이다. 또한 SQL이 분리되어 있으므로 각각의 성능 최적화도 쉽게 할 수 있다. 주의 사항이 있다. Recursive With 문에서는 LEVEL을 사용할 수 없지만 위의 경우처럼 숫자 초기값을 지정하고 거기에 1을 계속 더해가면 같은 기능을 사용할 수 있다. 또 하나의 주의사항은 Sort의 순서가 다르다는 것인데 Order By 기능을 설명하는 부분에서 자세히 다루어진다.

 

무한루프 발생 테스트

먼저 무한루프에 만들기 위하여 TOP LEVEL manager를 조작하고 Connect ByRecursive With문을 각각 실행시켜보자.

 

UPDATE employee

   SET manager_id = 101

 WHERE employee_id = 100;

 

commit;

 


 SELECT employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id = 100

CONNECT BY PRIOR employee_id = manager_id;

 

ORA-01436: CONNECT BY loop in user data

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE manager_id = 100

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id

)

SELECT *

  FROM recursive;

 

ORA-32044: cycle detected while executing recursive WITH query

 

위에서 보는 것처럼 에러코드가 서로 다르다. 기존의 Connect By문은 이러한 무한루프를 성공적으로 제거하는 기능이 존재했다. 아래가 그 기능을 구현한 SQL이다.

 

 

SELECT employee_id,

        manager_id,

        CONNECT_BY_ISCYCLE AS iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id ;

 

107 rows selected.

 

Connect By절에 NOCYCLE을 명시하면 무한루프를 방지할 수 있다. 뿐만 아니라 CONNECT_BY_ISCYCLE 기능을 사용하면 무한루프를 발생시키는 Row를 발견할 수 있다. 또한 Oracle 9i 10g를 거치면서 기능이 막강해져서 아래와 같이 사용할 수 있게 되었다.

 

SELECT  employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') as name_tree,

        CONNECT_BY_ROOT last_name as root_name,

        CONNECT_BY_ISLEAF as isleaf,

        CONNECT_BY_ISCYCLE as iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id ;

 

107 rows selected.

 

위의 기능 중에 하나라도 모르는 것이 있다면 Hierarchical Queries를 참조하라

 

2) 기능 관점에서 Connect By VS Recursive With

위에서 보았던 막강한 기능들을 Recursive With에서 모두 사용할 수 있는지 아닌지는 매우 중요하다. 왜냐하면 기능의 사용 가능 유무에 의해서 Recursive With의 존재가치가 판가름 날 것이기 때문이다. 위의 예제에서 사용한 모든 기능들을 Recursive With문에서 사용할 수 있으면 좋겠지만 아쉽게도 위에서 굵게 표시된 모든 예약어와 함수 등을 사용할 수 없다. 하지만 Recursive With문에서는 모든 기능을 예약어나 함수가 아닌 수동으로 재연할 수 있다. 그것도 아주 간편하게 말이다. 손 맛을 느끼게 해주는 SQL이다. 아래를 보자.

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name   --> SYS_CONNECT_BY_PATH  함수에 해당함

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX         --> ORDER SIBLINGS BY 기능에 해당함

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"   --> CONNECT_BY_ISCYCLE 기능에 해당함

SELECT employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name, --> CONNECT_BY_ROOT 기능에 해당함

       decode( regexp_count(lead(name_tree) OVER(ORDER BY IDX), name_tree), 0, 1, null, 1, 0) isleaf,

       --> CONNECT_BY_ISLEAF 기능에 해당함

       iscycle

FROM recursive;

 

102 rows selected.

 

이것 봐라?

너무나 쉽게 기능을 구현하였다. 이 정도라면 기존의 Connect By 사용자도 어라 이것 봐라?” 라고 이야기 할 것이다. 기능을 살펴보자. 먼저 SEARCH DEPTH FIRST BY 기능은 ORDER SIBLINGS BY 기능에 해당한다. 물론 employee_id 컬럼에 ASC DESC를 사용할 수 있다. SET IDX라는 기능은 Sort된 순서대로 일련번호를 생성하는 기능이다. 이것은 ROWNUM과 기능이 비슷하지만 다른 점은 정렬된 순서대로 채번된다는 것이다. 주의사항은 SEARCH DEPTH FIRST 기능을 사용하지 않고 보통의 Order By 구문을 사용하게 되면 Tree 구조가 유지되지 않는다는 것이다. 이런 관점은 Connect ByORDER SIBLINGS BY 기능과 같다.

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"
라고 선언하면 manager_id의 값이 잘못되어 무한루프가 발생될 때 식별할 수 있다. 이때 iscycle을 가상의 컬럼으로 이용하게 된다. 주의사항은 iscycle의 값은 숫자가 될 수 없으며 1 Byte 문자만 가능하다. “1” “0”을 사용한 이유도 여기에 있다. regexp_substr를 사용한 이유는 문자를 첫번째 ‘-‘ 까지 잘라내야 root_name을 구할 수 있기 때문이다. Regular Expression에 대하여 자세히 알고 싶은 독자는 오라클 Regular Expressions 완전정복을 참조하라. regexp_count는 버전 11g에 추가된 기능이다. 이 기능은 다음처럼 사용할 수 있다.

 

ex) regexp_count(text1, text2) :

text1 text2가 포함된 횟수를 Return한다. 예를 들면 regexp_count(‘yahoo.co.kr’, ‘o’) 라고 했다면 o 3번 포함되어 있으므로 3 Return 된다.  

 

 

건수가 차이 난다

하지만 문제가 있다. 결과건수가 차이가 난다. Connect By는 결과가 107건이며 Recursive With문은 102건을 Return 하였다. 5건의 차이는 무엇인가? 아래는 차이의 분석을 위하여 두 SQL의 결과값 중에서 필요한 부분만 표로 정리한 것이다.

 

Connect By 사용

Recursive With 사용

EMP_ID

NAME_TREE

IS
CYCLE

EMP_ID

NAME_TREE

IS
CYCLE

101

King-Kochhar

1

101

King-Kochhar

0

출력되지 않음

100

King-Kochhar-King

1

204

King-Kochhar-Baer

0

204

King-Kochhar-Baer

1

108

King-Kochhar-Greenberg

0

108

King-Kochhar-Greenberg

1

110

King-Kochhar-Greenberg-Chen

0

출력되지 않음

109

King-Kochhar-Greenberg-Faviet

0

113

King-Kochhar-Greenberg-Popp

0

111

King-Kochhar-Greenberg-Sciarra

0

112

King-Kochhar-Greenberg-Urman

0

205

King-Kochhar-Higgins

0

205

King-Kochhar-Higgins

1

206

King-Kochhar-Higgins-Gietz

0

출력되지 않음

203

King-Kochhar-Mavris

0

203

King-Kochhar-Mavris

1

200

King-Kochhar-Whalen

0

200

King-Kochhar-Whalen

1

 

어떤 차이가 있나?

결론을 이야기하면 Connect By NoCycle, IsCycle 기능과 Recursive WithCycle IsCycle 기능과는 차이가 있다.즉 각각의 기능이 다르므로 용도를 구분할 줄 알아야 한다는 뜻이다.

 

Connect By NoCycle 기능은 KingKochhar의 관계가 반복되는 것을 Skip하고 표시된다. 즉 직원번호 204 name_tree 항목을 보면 King-Kochhar-Baer 라고 되어 있지만 사실은 King-Kochhar- King-Kochhar - King-Kochhar ....무한반복... Baer 처럼 표시할 수 있다. 하지만 Connect By + NoCycle을 사용하면 무한반복을 Skip하고 한번만 나타낸다. 또한 IsCycle 도 반복이 시작되기 직전의 데이터를 식별해주는 기능을 한다.

 

Connect By + NoCycle을 사용하면 직원번호 101의 데이터는 Cycle 로 표시되는데 사실은 아직 Cycle이 아니다. 왜냐하면 오른쪽의 Recursive With를 사용한 경우를 보면 직원번호 100번의 데이터는 King-Kochhar-King인데 King이 두 번 반복 된 것을 볼 수 있고 이 경우가 첫 번째 반복되는 Cycle 이기 때문이다. Recursive With IsCycle 항목에는 이런 관점이 잘 반영되어 나타난다. 이 데이터는 Connect By + NoCycle을 사용하면 볼 수 없다.

 

마지막으로 Connect By를 사용하면 데이터가 출력되지만 Recursive With를 사용하면 출력되지 않는 Row가 있다. 그 이유는 Connect By는 관계의 반복만 제거하고 마지막까지 데이터를 찾아가지만 Recursive With는 반복되는 첫 번째 데이터만 찾고 거기서 멈춘다. Connect By를 사용하면 King-Kochhar-Greenberg-Chen로 데이터의 끝까지 찾아가지만 Recursive With를 사용하면 King-Kochhar-Greenberg 여기서 멈춘다. 위의 표를 유심히 비교해 보기 바란다. 지금까지 문법과 여러 가지 기능의 차이를 알아보았다.

 

3) 성능관점에서 Connect By VS Recursive With

이제 가장 중요한 성능을 비교해보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

        employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') name_tree,

        CONNECT_BY_ROOT last_name root_name,

        CONNECT_BY_ISLEAF isleaf,

        TO_CHAR(CONNECT_BY_ISCYCLE) iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

 

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

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

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

|   0 | SELECT STATEMENT              |                |    107 |      16 |          |

|*  1 |  CONNECT BY WITH FILTERING    |                |    107 |      16 | 2048  (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE       |      1 |       2 |          |

|*  3 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK  |      1 |       1 |          |

|   4 |   NESTED LOOPS                |                |    106 |      14 |          |

|   5 |    CONNECT BY PUMP            |                |    107 |       0 |          |

|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE       |    106 |      14 |          |

|*  7 |     INDEX RANGE SCAN          | EMP_MANAGER_IX |    106 |       5 |          |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

   3 - access("EMPLOYEE_ID"=100)

   7 - access("connect$_by$_pump$_002"."PRIOR employee_id "="MANAGER_ID")

 

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"

SELECT /*+ GATHER_PLAN_STATISTICS */

       employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name,

 decode(regexp_count(lead(name_tree) OVER(ORDER BY IDX), name_tree), 0, 1, null, 1, 0) isleaf,

       iscycle

  FROM recursive;

 

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

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

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

|   0 | SELECT STATEMENT                         |                |    107 |      16 |          |

|   1 |  WINDOW BUFFER                           |                |    107 |      16 | 8192  (0)|

|   2 |   VIEW                                   |                |    107 |      16 |          |

|   3 |    UNION ALL (RECURSIVE WITH) DEPTH FIRST|                |    107 |      16 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID          | EMPLOYEE       |      1 |       2 |          |

|*  5 |      INDEX UNIQUE SCAN                   | EMP_EMP_ID_PK  |      1 |       1 |          |

|   6 |     NESTED LOOPS                         |                |    106 |      14 |          |

|   7 |      NESTED LOOPS                        |                |    106 |       5 |          |

|   8 |       RECURSIVE WITH PUMP                |                |    107 |       0 |          |

|*  9 |       INDEX RANGE SCAN                   | EMP_MANAGER_IX |    106 |       5 |          |

|  10 |      TABLE ACCESS BY INDEX ROWID         | EMPLOYEE       |    106 |       9 |          |

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

Predicate Information (identified by operation id):

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

   5 - access("EMPLOYEE_ID"=100)

   9 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

IsLeaf 기능을 사용하려면 Connect By가 유리하다

Scan한 블럭수는 16으로 동일하다. 하지만 PGA 사용량이 4배나 차이 난다. 하지만 이것은 Connect ByRecursive With의 성능 차이가 아니라 분석함수 lead의 사용 유무에 의한 차이이다. IsLeaf 기능이 필요하다면 기존의 Connect By를 사용하는 것이 유리할 것이다. 하지만 Connect ByRecursive With 자체의 성능만 비교한다면 결과가 달라진다.

 

Connect ByRecursive With에서 IsLeaf 항목을 빼고 다시 실행 해보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

        employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') name_tree,

        CONNECT_BY_ROOT last_name root_name,

TO_CHAR(CONNECT_BY_ISCYCLE) iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

 

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

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

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

|   0 | SELECT STATEMENT              |                |    107 |      16 |          |

|*  1 |  CONNECT BY WITH FILTERING    |                |    107 |      16 | 2048  (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE       |      1 |       2 |          |

|*  3 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK  |      1 |       1 |          |

|   4 |   NESTED LOOPS                |                |    106 |      14 |          |

|   5 |    CONNECT BY PUMP            |                |    107 |       0 |          |

|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE       |    106 |      14 |          |

|*  7 |     INDEX RANGE SCAN          | EMP_MANAGER_IX |    106 |       5 |          |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

   3 - access("EMPLOYEE_ID"=100)

   7 - access("connect$_by$_pump$_002"."PRIOR employee_id "="MANAGER_ID")

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"

SELECT /*+ GATHER_PLAN_STATISTICS */

       employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name,

       iscycle

  FROM recursive;

 

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

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

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

|   0 | SELECT STATEMENT                        |                |    107 |      16 |

|   1 |  VIEW                                   |                |    107 |      16 |

|   2 |   UNION ALL (RECURSIVE WITH) DEPTH FIRST|                |    107 |      16 |

|   3 |    TABLE ACCESS BY INDEX ROWID          | EMPLOYEE       |      1 |       2 |

|*  4 |     INDEX UNIQUE SCAN                   | EMP_EMP_ID_PK  |      1 |       1 |

|   5 |    NESTED LOOPS                         |                |    106 |      14 |

|   6 |     NESTED LOOPS                        |                |    106 |       5 |

|   7 |      RECURSIVE WITH PUMP                |                |    107 |       0 |

|*  8 |      INDEX RANGE SCAN                   | EMP_MANAGER_IX |    106 |       5 |

|   9 |     TABLE ACCESS BY INDEX ROWID         | EMPLOYEE       |    106 |       9 |

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

Predicate Information (identified by operation id):

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

   4 - access("EMPLOYEE_ID"=100)

   8 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

일반적인 경우 Recursive With가 유리함

보았는가? 상황이 역전되어 Recursive With를 사용하면 PGA를 전혀 사용하지 않는다. 따라서 IsLeaf를 사용하지 않는 일반적인 경우 Recursive With가 유리함을 알 수 있다.

 

대용량 배치일 경우 Recursive With를 주의하라

하지만 이것이 끝이 아니다. 대용량 배치일 경우는 상황이 다시 반전된다.

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE  manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id

)

SELECT /*+ GATHER_PLAN_STATISTICS */  *

  FROM recursive;

 

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

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

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

|   0 | SELECT STATEMENT                          |                |    107 |      22 |

|   1 |  VIEW                                     |                |    107 |      22 |

|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|                |    107 |      22 |

|*  3 |    TABLE ACCESS FULL                      | EMPLOYEE       |      1 |       8 |

|   4 |    NESTED LOOPS                           |                |    106 |      14 |

|   5 |     NESTED LOOPS                          |                |    106 |       5 |

|   6 |      RECURSIVE WITH PUMP                  |                |    107 |       0 |

|*  7 |      INDEX RANGE SCAN                     | EMP_MANAGER_IX |    106 |       5 |

|   8 |     TABLE ACCESS BY INDEX ROWID           | EMPLOYEE       |    106 |       9 |

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

Predicate Information (identified by operation id):

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

   3 - filter("MANAGER_ID" IS NULL)

   7 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

대용량 배치일 경우 Recursive With는 비효율이 발견되었다. manager_id IS NULL 조건 때문에 EMPLOYEE 테이블을 Full Scan 하였지만 Union ALL 아래의 SQL에서 또다시 EMPLOYEE 테이블을 Scan하고 있다. 동일한 블록을 두 번 Scan한 셈이다. 하지만 아래를 보라.

 

SELECT /*+ GATHER_PLAN_STATISTICS */ employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

 

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

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

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

|   0 | SELECT STATEMENT                        |          |    107 |       7 |

|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|          |    107 |       7 |

|   2 |   TABLE ACCESS FULL                     | EMPLOYEE |    107 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

       filter("MANAGER_ID" IS NULL)

 

Connect By를 사용하면 Union을 사용하지 않으므로 Full Table Scan 한 번으로 끝낼 수 있다. Scan한 블럭수는 세 배 이상 차이가 난다.

 

Connect By VS Recursive With 의 결론

1) 문법적인 측면에서 거의 차이가 없다. Recursive With가 조금 길어지기는 하지만 Union All로 분리되어 있기 때문에 오히려 가독성과 성능 최적화가 쉽게 될 수 있다.

 

2) 기능면에서도 거의 차이가 없다. Connect By의 모든 기능이 구현 가능하다. 다만 Nocycle 기능과 IsCycle 기능의 용도가 서로 다르므로 구분해서 사용하면 된다.

 

3) 성능면에서는 Sort 기능을 사용해도 PGA를 전혀 사용하지 않는 Recursive With가 일반적으로 유리하다. 하지만 IsLeaf 기능을 구현하려면 분석함수를 사용해야 하기 때문에 Connect By가 유리할 수 있다. 또한 Scan할 범위가 넓은 경우 Start With 조건을 Full Table Scan으로 유도하면 같은 블럭을 반복해서 Scan하지 않는 Connect By가 유리하다.

 

결국 각 기능들의 허와 실을 제대로 파악하고 성능 이슈를 최소화 한다면 모두가 웃을 수 있을 것이다.
모두가 웃는 그날까지......  


Posted by extremedb
,

정답의 결과가 틀리다. WHERE 절에 s.sales_cust.prod_cnt > 0 조건이 추가되어야 한다.
자세한 내용은 oraking 님의 댓글을 참조하기 바란다.(2010.09.03)


좋지 않은 상황

한 고객 사에서 전화가 걸려왔는데 성능 문제였다.

특정 SQL이 성능이 좋지 않은데 더욱 상황을 악화시키는 것은 Peak Time에 수십 만 번 실행된다는 것이다. 그런데 전체 건을 조회하는 것이 아니라 고객번호 순으로 10명의 고객에 대한 통계와 실적을 보는 SQL이라고 한다. SQL은 아래와 같으며 환경은 Oracle10g R2 이다. 오라클 Install시 생성되는 SH 스키마를 이용하면 된다.

 

--인덱스 생성

CREATE INDEX ix_cust_birth ON customers (cust_year_of_birth, cust_id);

 

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) 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

   AND ROWNUM <= 10;

 

-------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                |               |     10 |    5035 |          |
|*  2 |   HASH JOIN                   |               |     10 |    5035 | 1150K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |    151 |     153 |          |
|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH |    151 |       2 |          |
|   5 |    VIEW                       |               |    523 |    4882 |          |
|   6 |     SORT GROUP BY             |               |    523 |    4882 | 8288K (0)|
|   7 |      PARTITION RANGE ALL      |               |    918K|    4882 |          |
|   8 |       TABLE ACCESS FULL       | SALES         |    918K|    4882 |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   2 - access("S"."CUST_ID"="C"."CUST_ID")
   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


CUSTOMERS 테이블은 10건을 정확히 Scan 하였지만 Salse 테이블을 Full Scan하여 비효율이 발생 하였다. 또한 위의 SQL은 Hash Join을 사용 하였으므로 정렬을 보장 하지 않는다.

좋지 않은 상황 + 악조건

먼저 위의 SQL을 보면 Rownum을 사용하므로 CVM(Complex View Merging)이 발생되지 않는다. 만약 발생된다고 해도 Group By가 전체범위로 처리되므로 TOP SQL에서는 대부분 JPPD(Join Predicate Push Down)의 성능이 우월하다. 또한 Nested Loop Join이 아닌 Hash Join이 수행 되었으므로 고객번호가 작은 것부터 나오지 않는다. Sort 문제도 해결해야 하지만 Oracle10g이므로 JPPD Extension 기능이 수행되지 않는다. JPPD Extension이란 Group By + 집계함수나 Distinct가 존재해도 JPPD가 수행되며 Semi/Anti Join시에도 JPPD가 수행되는 획기적인 기능이다. 이 기능은 Oracle11g부터 사용할 수 있다.

 

정답부터 보지말자 실력이 늘지 않는다
지금부터 여러분이 이 문제를 해결해야 한다. 여러분이 해결사 이다. 퀴즈라고 생각하고 문제를 풀어보라. 하지만 절대 답을 먼저 보아서는 안 된다. 충분히 고민한 후에 답을 풀어보고 정답을 보도록 하자.

힌트가 있다

JPPD
기능을 사용할 수 없다면 JPPD의 흉내를 내면 된다. JPPD의 효과를 만들면 된다.
아래쪽의 실행계획과 결론 부분을 보는것도 힌트가 될 수 있다.

제약사항
Sort 가 되어야 하며 Rownum 조건으로 Customers 테이블에 10건만 Scan되어야 한다. 그 10건에 해당하는 고객만 Sales 테이블에 Access 하는 것이 정답이다.
스칼라 서브쿼리를 세 번 사용하는 것은 정답이 아니다. 그것은 막노동에 가깝다. 필자의 블로그를 꾸준히 구독한 독자라면 어렵지 않게 문제를 풀 수 있다.
 

정답: 아래를 드래그 하면 된다.

 

CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT

(prod_cnt NUMBER(5),

 channel_cnt NUMBER(2),     

 tot_amt NUMBER(15,2));

/

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       s.cust_id, s.cust_first_name, s.cust_last_name,

       s.sales_cust.prod_cnt,  -- Alias 가 여기에 사용 되었다.

       s.sales_cust.channel_cnt,

       s.sales_cust.tot_amt

  FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */

               c.cust_id, c.cust_first_name, c.cust_last_name,

               (SELECT sales_cust_type -- 타입의 이름을 그대로 사용해야 한다

                          (COUNT (DISTINCT s.prod_id),

                           COUNT (DISTINCT s.channel_id),

                           SUM (s.amount_sold)

                          )

                  FROM sales s

                 WHERE s.cust_id = c.cust_id

) AS sales_cust -- 추후 메인쿼리에서 Alias 가 사용된다.

          FROM customers c

         WHERE c.cust_year_of_birth= 1987

           AND ROWNUM <= 10) s  ;

    

위의 SQL은 JPPD를 수행시킨 효과와 비슷하다. 다른말로 바꾸면 수동으로 Lateral View를 생성한 것이다.
          

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

 

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

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

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

|   1 |  VIEW                         |                    |     10 |    1104 |

|*  2 |   COUNT STOPKEY               |                    |     10 |      13 |

|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS          |     10 |      13 |

|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH_CUST |     10 |       3 |

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

 

Predicate Information (identified by operation id):

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

   2 - filter(ROWNUM<=10)

   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


읽은 블럭수가 무려 5배 정도 차이가 나며 PGA는 사용 하지도 았았다. 자주 수행될수록 성능의 차이는 급격히 벌어질 것이다.
 

결론:

JPPD 기능을 사용할 수 없으므로 JPPD의 흉내를 낸 것이다. JPPD등이 수행되지 않을 때 Logical Optimizer의 한계를 극복할 수 있는 방법은 여러분의 응용력에 달려있다. 옵티마이져가 한계를 드러낼 때 더 좋은 방법을 적용하는 것이 사람의 할 일이 되었다. 물론 그러기 위해서는 Logical Optimizer의 허와 실을 알아야 할 것이다.

 

PS:

만약 Oracle9i Oracle 8.1.7 버전을 사용하고 있다면 위의 정답이 실행되지 않으므로 http://scidb.tistory.com/69 글을 참조하여 사용하기 바란다.


Posted by extremedb
,