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

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

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

 

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

 

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

 

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

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

 

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

 

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

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

 

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

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

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

 

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

 

더미 테이블의 단점

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

 

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

 

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

 

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

 

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

 

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

 

1. Copy

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

 

2. 데이터 체크

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

 

3. 인덱스의 효율적 사용

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

 

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

 

질문1

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

필자: 됩니다.

개발자: 어떻게요?

 

질문2

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

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

개발자: 그럴 리가요?

 

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

 

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

 

CREATE TABLE COPY_YMD AS

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

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

  FROM SALES

WHERE ROWNUM <= 14600;

 

ALTER TABLE COPY_YMD ADD CONSTRAINT PK_COPY_YMD

PRIMARY KEY (YMD_CHAR) USING INDEX; 

 

CREATE UNIQUE INDEX IDX_COPY_YMD_01 ON COPY_YMD(YMD_DT);

 

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

 

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

 

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

  FROM sales s

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

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

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

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

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

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

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

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

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

 

Predicate Information (identified by operation id):

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

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

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

       filter("CUST_ID"=53)

 

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

 

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

  FROM sales s

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

                      FROM copy_ymd c

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

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

   AND cust_id = 53;

 

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

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

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

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

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

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

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

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

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

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

 

Predicate Information (identified by operation id):

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

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

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

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

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

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

 

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

 

SELECT s.*

  FROM sales s,

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

          FROM dual

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

 WHERE s.time_id = d.time_id

   AND s.cust_id = 53; 

 

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

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

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

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

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

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

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

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

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

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

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

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

 

Predicate Information (identified by operation id):

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

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

 

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

 

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

  FROM sales s

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

                   AND TO_DATE('20011130', 'YYYYMMDD')

   AND cust_id = 53;

 

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

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

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

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

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

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

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

 

Predicate Information (identified by operation id):

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

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

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

       filter("CUST_ID"=53)

 

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

 

참고사항

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

 

결론

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

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

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

댓글을 달아 주세요

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

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

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

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

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

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

    좋은 글 항상 감사합니다.

▶적절한 인덱스가 없을 때 Sort의 부하를 줄이는 방법

Pagination에서 Sort의 부하 줄이기

주의사항

 

Order by 절에 의한 Sort의 부하는 성능에 치명적이다. Block I/O의 최소화는 분명 튜닝의 핵심이다. 하지만, Block I/O를 최소화 해도 Sort의 부하가 심하다면 결코 만족스런 성능을 내지 못한다. 특히페이징 처리용 SQL에는 Sort를 대신할 수 있는 인덱스가 있어야 성능을 확보할 수 있다고 많은 튜너들이 주장한다. 맞는 말이다. 그렇게만 된다면 Sort가 전혀 발생하지 않을 테니까. 하지만, 다음과 같은 어려움도 있다.

 

인덱스 최적화가 힘든 이유
첫 번째, 인덱스를 모든 조회화면의 기준에 맞게 만들려면 테이블마다 많은 수의 인덱스가 필요할 것이다. 두 번째, 운영중인 환경에서 인덱스를 생성 혹은 변경하기는 매우 어렵다. 따라서, 인덱스를 만들기 어렵다면, Sort의 부하를 최소화하는 다른 방법은 없는지를 고려해야 한다. 분명히 방법은 있다. 이 방법을 알지 못한다면 오직 인덱스에만 목숨을 거는 사람이 될 가능성이 높다. 오늘은 인덱스를 전혀 만들지 않은 상태에서 Sort의 부하를 최소화 하는 방법에 대해 알아볼 것이다.

 

단 한 가지 개념만 안다면, Order By에 의한 Sort의 부하를 이해한 것이다. Sort의 부하량은 면적에 비례한다는 것. 이 개념은 아래와 같이 표현할 수 있다. 참고로 는 비례한다는 의미이다.

 

Sort의 부하량(PGA 사용량) ∝ 세로(결과 건수) X 가로(컬럼 Size 합계)

 

공식의 이해가 부족하다
주위의 지인들에게 위의 식을 질문한 결과 거의 모두가 세로에 대해서는 정확히 이해하고 있었다. , Sort할 건수가 많아지면 Sort의 부하가 증가 한다는 것이다. 이에 반해서 가로에 대해서는 정확한 이해를 하는 사람이 드물었다. 대부분, Order By절에 의해 Sort의 부하가 발생하므로 Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각하는 것이다. 다시 말해, Order By절의 컬럼이 세 개라면, 세 컬럼의 Size를 합친 것이 가로라는 것이다. 과연 그럴까?


위의 주장을 검증하기 위해 테이블을 하나 만들고, 추가적으로 컬럼을 3개 만들자.

 

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

ALTER TABLE SALES_T ADD (char_100  CHAR(100)  DEFAULT 'a' NOT NULL );

ALTER TABLE SALES_T ADD (char_1000 CHAR(1000) DEFAULT 'a' NOT NULL );

ALTER TABLE SALES_T ADD (char_2000 CHAR(2000) DEFAULT 'a' NOT NULL );

 

추가된 컬럼은 모두 Char Type이며 Default 값이 ‘a’ 이다. Char Type이므로 Default값인 ‘a’가 들어오는 경우 컬럼 size는 각각 100, 1000, 2000 바이트씩 채워진다. 이제 이 컬럼들을 이용하여 SQL을 각각 실행해보자. 100 byte, 1000 byte, 2000 byte 컬럼으로 각각 Sort하여 Sort의 부하가 어떻게 달라지는지 알아보자.

 

CREATE TABLE SORT_100 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_100

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_100 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:01.41 |    7323 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:01.41 |    7323 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:01.23 |    5915 | 4708K (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.15 |    5915 | 3471K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.21 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:00.66 |    4447 |          |

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

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:00.59 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

100 byte컬럼으로 Sort하니 PGA4.7MB 사용하였다. 이제 100 byte보다 10배나 큰 1000 byte 컬럼으로 Sort 하여 PGA 사용량을 비교해보자.

 

CREATE TABLE SORT_1000 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_1000

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_1000 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:02.17 |   13162 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:02.17 |   13162 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:01.75 |    5915 |   30M (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.56 |    5915 | 3486K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.22 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:01.05 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.02 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:00.98 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

Sort 컬럼의 size 100에서 1000 byte로 늘리자 PGA 사용량도 4.7 MB에서 30 MB로 크게 늘었다. 내친김에 Order By절을 2000 byte 컬럼으로 바꿔서 PGA 사용량이 얼마나 늘어나는지 테스트 해보자.

 

CREATE TABLE SORT_2000 NOLOGGING AS

SELECT /*+ full(p) full(c) */

       s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,

       c.cust_last_name, s.time_id, s.channel_id, s.char_2000

  FROM sales_t s, customers c, products p

 WHERE s.cust_id = c.cust_id

   AND s.prod_id = p.prod_id

   AND s.prod_id = 30

 ORDER BY s.char_2000 ;

 

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

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

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

|   0 | CREATE TABLE STATEMENT |           |      1 |      0 |00:00:03.16 |   19298 |          |

|   1 |  LOAD AS SELECT        |           |      1 |      0 |00:00:03.16 |   19298 |  521K (0)|

|   2 |   SORT ORDER BY        |           |      1 |  29282 |00:00:02.06 |    5915 |   58M (0)|

|*  3 |    HASH JOIN           |           |      1 |  29282 |00:00:01.74 |    5915 | 3515K (0)|

|   4 |     TABLE ACCESS FULL  | CUSTOMERS |      1 |  55500 |00:00:00.24 |    1468 |          |

|   5 |     NESTED LOOPS       |           |      1 |  29282 |00:00:01.19 |    4447 |          |

|*  6 |      TABLE ACCESS FULL | PRODUCTS  |      1 |      1 |00:00:00.02 |       7 |          |

|*  7 |      TABLE ACCESS FULL | SALES_T   |      1 |  29282 |00:00:01.12 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

   6 - filter("P"."PROD_ID"=30)

   7 - filter("S"."PROD_ID"=30)

 

예상대로 Sort 대상 컬럼을 1000에서 2000 byte로 바꾸자 PGA 사용량이 30 MB에서 58MB 로 약 두 배 늘었다. 위의 결과를 언뜻 보면, Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각할 수 있다. 왜냐하면, Sort 대상컬럼의 Size에 비례하여 PGA 사용량이 증가되었다고 판단하기 때문이다. 하지만 이런 생각은 절반만 옳고 나머지 절반은 틀렸다. 제대로 된 식은 다음과 같다.

 

Sort의 부하를 좌우하는 원리

Sort
의 부하량(PGA 사용량) ∝ 세로 X 가로

세로: SQL의 결과 건수

가로: Order by 절의 컬럼 size + Order by 절을 제외한 나머지 컬럼의 size

 

근거 있는 주장인가?
이 공식이 글 전체의 핵심이다. 하지만, 많은 사람들이 위와 같은 가로 세로 개념을 주장할 수 있는 근거가 무엇인지 궁금해한다. 이제 가로가 Order by 절의 컬럼 size + 나머지 컬럼의 size라는 주장에 대한 근거를 보자.

 

SELECT s.channel_id, s.char_2000

  FROM sales_t s

 WHERE s.prod_id = 30

 ORDER BY s.channel_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |  29282 |00:00:00.84 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |  29282 |00:00:00.84 |    4440 |   56M (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |      1 |  29282 |00:00:00.68 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("S"."PROD_ID"=30)

 

Order By절에는 Size가 작은 channel_id 컬럼 뿐이다. 그런데 PGA 사용량은 56 MB나 된다. 세로가 3만 건도 안 되는 집합을 Sort하는데 그 부하는 56 MB나 된다. 이상하지 않은가? 과부하의 이유는 Select절의 char_2000 컬럼 때문이다. 이 컬럼을 Select 절에서 제거하고 다시 실행해 보자.

 

SELECT s.channel_id

  FROM sales_t s

 WHERE s.prod_id = 30

 ORDER BY s.channel_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |  29282 |00:00:00.64 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |  29282 |00:00:00.64 |    4440 |  424K (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |      1 |  29282 |00:00:00.58 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

   2 - filter("S"."PROD_ID"=30)

 

Order By절 이외의 컬럼에 주목하라 
Select
절의 char_2000 컬럼을 제거하자 Sort의 부하는 424K로 급격히 줄어들었다. 왜냐하면, Sort Area에는 Order By절의 컬럼을 Sort할뿐만 아니라 나머지 컬럼 List Loading 되기 때문이다. Order By절 뿐만 아니라, Select 절에도 size가 큰 컬럼이 있다면 성능이 급격히 저하됨을 알 수 있다. 지금까지 Sort부하량 공식에 의해 가로는 Order By절 컬럼 Size + Sort 대상 이외의 컬럼 Size가 됨을 증명해 보았다.

 

이제 이 개념을 실제 SQL 프로그래밍에 적용해보자. 아래는 전형적인 Pagination SQL이다. 아래의 예제에서 처음의 약속을 지키기 위해 인덱스를 만들지도, 사용하지도 않을 것이다.
 

SELECT *

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,

c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,

                       s.char_100, s.char_1000, s.char_2000

                  FROM sales_t s, customers c, products p

                 WHERE s.cust_id = c.cust_id

                   AND s.prod_id = p.prod_id

                   AND s.channel_id = 3

                 ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a

         WHERE ROWNUM <= :v_max_row ) --> 200 대입

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

 

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

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

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

|   0 | SELECT STATEMENT         |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  1 |  VIEW                    |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  2 |   COUNT STOPKEY          |           |      1 |    200 |00:00:08.71 |    5915 |          |

|   3 |    VIEW                  |           |      1 |    200 |00:00:08.71 |    5915 |          |

|*  4 |     SORT ORDER BY STOPKEY|           |      1 |    200 |00:00:08.71 |    5915 | 3321K (0)|

|*  5 |      HASH JOIN           |           |      1 |    540K|00:00:06.30 |    5915 | 1176K (0)|

|   6 |       TABLE ACCESS FULL  | PRODUCTS  |      1 |     72 |00:00:00.01 |       7 |          |

|*  7 |       HASH JOIN          |           |      1 |    540K|00:00:03.91 |    5908 | 3568K (0)|

|   8 |        TABLE ACCESS FULL | CUSTOMERS |      1 |  55500 |00:00:00.21 |    1468 |          |

|*  9 |        TABLE ACCESS FULL | SALES_T   |      1 |    540K|00:00:01.14 |    4440 |          |

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

 

Predicate Information (identified by operation id):

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

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

   2 - filter(ROWNUM<=:V_MAX_ROW)

   4 - filter(ROWNUM<=:V_MAX_ROW)

   5 - access("S"."PROD_ID"="P"."PROD_ID")

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

   9 - filter("S"."CHANNEL_ID"=3)

 

페이징 처리된 SQL Sort 부하량은 3321K 이다. 이제 Sort의 부하를 줄이기 위해 select 절의 모든 컬럼을 제거하자.

 

SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,

c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,

        s.char_100, s.char_1000, s.char_2000

  FROM (SELECT a.*, ROWNUM rnum

          FROM (SELECT s.rowid as s_rid, p.rowid as p_rid, c.rowid as c_rid

                  FROM sales_t s, customers c, products p

                 WHERE s.cust_id = c.cust_id

                   AND s.prod_id = p.prod_id

                   AND s.channel_id = 3

                 ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a

         WHERE ROWNUM <= :v_max_row ) a,   --> 200 대입   

       sales_t s, customers c, products p

   WHERE s.rowid  = a.s_rid

   and c.rowid = a.c_rid

   and p.rowid = a.p_rid

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

 

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

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

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

|   0 | SELECT STATEMENT              |           |      1 |    200 |00:00:06.51 |    6168 |          |

|   1 |  NESTED LOOPS                 |           |      1 |    200 |00:00:06.51 |    6168 |          |

|   2 |   NESTED LOOPS                |           |      1 |    200 |00:00:06.51 |    5969 |          |

|   3 |    NESTED LOOPS               |           |      1 |    200 |00:00:06.51 |    5918 |          |

|*  4 |     VIEW                      |           |      1 |    200 |00:00:06.51 |    5915 |          |

|*  5 |      COUNT STOPKEY            |           |      1 |    200 |00:00:06.51 |    5915 |          |

|   6 |       VIEW                    |           |      1 |    200 |00:00:06.51 |    5915 |          |

|*  7 |        SORT ORDER BY STOPKEY  |           |      1 |    200 |00:00:06.51 |    5915 |96256  (0)|

|*  8 |         HASH JOIN             |           |      1 |    540K|00:00:05.81 |    5915 | 1193K (0)|

|   9 |          TABLE ACCESS FULL    | PRODUCTS  |      1 |     72 |00:00:00.01 |       7 |          |

|* 10 |          HASH JOIN            |           |      1 |    540K|00:00:03.65 |    5908 | 4514K (0)|

|  11 |           TABLE ACCESS FULL   | CUSTOMERS |      1 |  55500 |00:00:00.22 |    1468 |          |

|* 12 |           TABLE ACCESS FULL   | SALES_T   |      1 |    540K|00:00:01.06 |    4440 |          |

|  13 |     TABLE ACCESS BY USER ROWID| CUSTOMERS |    200 |    200 |00:00:00.01 |       3 |          |

|  14 |    TABLE ACCESS BY USER ROWID | PRODUCTS  |    200 |    200 |00:00:00.01 |      51 |          |

|  15 |   TABLE ACCESS BY USER ROWID  | SALES_T   |    200 |    200 |00:00:00.01 |     199 |          |

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

 

Predicate Information (identified by operation id):

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

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

   5 - filter(ROWNUM<=:V_MAX_ROW)

   7 - filter(ROWNUM<=:V_MAX_ROW)

   8 - access("S"."PROD_ID"="P"."PROD_ID")

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

  12 - filter("S"."CHANNEL_ID"=3)

 

 

Trade Off가 유리한 경우

Sort 부하량이 3321K에서 96K로 약 34.5배 줄어들었다. 이렇게 ROWID만 남기고 select 절의 모든 컬럼을 제거해도 결과는 같다. 왜냐하면, Sort된 상태로 rowid가 보관되어있기 때문이다. 페이징 처리가 모두 끝나고 200건에 대해서만 rowid로 테이블에 접근하기 때문에 테이블의 중복사용에 의한 비효율은 매우 적다. Buffers 항목을 비교해보면 5915 블록에서 6168 블록으로 비효율은 253 블록(4%) 밖에 차이가 나지 않는다. 하지만 Sort의 부하는 34.5배나 줄어들었다. 약간의 Block I/O를 손해 보더라도 Sort의 부하가 아주 큰 경우는 같은 블록을 중복해서 읽어야 함을 알 수 있다.

 

장점 + 장점

이렇게 Rowid를 제외한 Select List를 인라인뷰 외부로 빼면, Sort 부하의 최소화 이외에 또 다른 효과를 누릴 수도 있다. 인덱스만 읽고 테이블로의 접근을 하지 않을 수 있다. Where 조건에 최적화된 인덱스가 존재하고, 그 인덱스가 Order By절 컬럼을 포함 한다면 인라인뷰 내부에서는 테이블 접근을 하지 않는다. 물론 Select List의 모든 컬럼들을 가져오려면 테이블을 접근해야 한다. 하지만 위의 예제처럼 Rowid를 사용했다면 페이징 처리가 끝난 후에, 특정 페이지에 해당하는 건들만 테이블로 접근할 수 있으므로 Random Access 도 최소화 된다. Sort를 최소화 하려고 했더니 Block I/O를 최소화 하는것까지 덤으로 얻을 수 있는 것이다.    

 

주의사항

SELECT 절에 상수나 변수 심지어 NULL이 오더라도 PGA 사용량은 증가하므로 주의해야 한다.
 

SELECT s.cust_id

  FROM sales_t s

 ORDER BY s.cust_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |    918K|00:00:03.38 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |    918K|00:00:03.38 |    4440 |   13M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |      1 |    918K|00:00:01.38 |    4440 |          |

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

 

Select 절에 다른 컬럼이 없기 때문에 PGA 13MB 사용 하였다. 이번에는 Select절에 Null을 추가해보자.

 

SELECT s.cust_id, null

  FROM sales_t s

 ORDER BY s.cust_id;

 

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

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

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

|   0 | SELECT STATEMENT   |         |      1 |    918K|00:00:03.48 |    4440 |          |

|   1 |  SORT ORDER BY     |         |      1 |    918K|00:00:03.48 |    4440 |   17M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |      1 |    918K|00:00:01.37 |    4440 |          |

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

 

Select List Null을 추가하자 PGA사용량이 4MB 증가했다. 오라클은 Null이나 상수도 컬럼처럼 취급함을 알 수 있다. 따라서 Order by절이 있는 인라인뷰의 Select List에 상수나 변수 혹은 Null을 집어넣는 것은 Sort의 부하를 증가시킨다. 상수나 변수는 Order By가 있는 인라인뷰에 넣지 말고 외부로 빼서 사용하면 된다.

결론
Sort의 부하를 최소화 하려면 Order By절의 컬럼에만 집중해서는 안되며, 전체 컬럼 List를 바라보아야 한다. 또한 프로그래밍을 할 때 상수 하나, 변수 하나의 위치도 고려해야 최적의 성능을 가진 프로그램이 됨을 알 수 있다. 즉 Sort의 최적화는 튜닝의 문제일 뿐만 아니라 프로그래밍의 문제인 것이다. 이점은 Sort 부하량이 무엇으로 결정되는지 개발자도 알아야 하는 이유가 된다.

면적은 가로와 세로로 구성된다.

Sort의 부하량은 면적의 크기에 비례한다.

Sort의 부하량 ∝ 결과 건수 X 전체 컬럼 Size

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

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

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2011.03.29 17:50  댓글주소  수정/삭제  댓글쓰기

    안녕하십니까? 오동규님 ^^ 잘 지내시죠?
    그 동안 개인적으로 일이 있어서, 동규님 글을 잘 보지 못했는데, 오늘 동규님 포스팅 제목이 눈에 확 들어와 바로 보았습니다. ^^
    sort 부하를 제거하기 위한 방법으로 인덱스를 생성하는 솔루션은, 대용량 책에서부터 시작이 된 것 같은데, 그 이후에 추가 방법론에 대해서는 다루지 않은 것 같습니다. 그런데 동규님께서 이런 좋은 솔루션을 포스팅 해 주시다니, 여러 사람들에게 도움이 많이 될 것 같습니다. ^^

    제 경우는 rowid를 이용해서, 아래와 같이 튜닝을 한 적은 있었는데, Sort 부하를 줄이기 위한 rowid 사용법을 보게 되서 너무 좋았습니다.

    -- 1. 튜닝전
    SELECT DISTINCT XPRH.COL_1,
    XPRH.COL_2,
    XPRH.COL_3,
    XBR.COL_1,
    XBR.COL_2,
    XBR.COL_3
    FROM TABLE_1 XPRH,
    TABLE_2 XBR
    WHERE XPRH.REQUEST_ID = XBR.REQUEST_ID
    AND XPRH.PROMPT_LIMIT_TYPE_CODE = :3
    AND XPRH.CURRENCY_CODE = :4
    AND XBR.REQUEST_DATE >= :5
    AND XBR.REQUEST_DATE < :6
    ORDER BY XBR.COL_3 DESC
    ;

    Call Count CPU Time Elapsed Time Disk Query Current Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse 1 0.010 0.019 0 7 0 0
    Execute 1 0.040 0.038 0 12 0 0
    Fetch 12 2.010 46.904 7262 44619 0 108
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total 14 2.060 46.961 7262 44638 0 108

    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 STATEMENT
    108 VIEW (cr=44619 pr=7262 pw=0 time=46911777 us)
    108 SORT ORDER BY (cr=44619 pr=7262 pw=0 time=46911758 us)
    108 HASH UNIQUE (cr=44619 pr=7262 pw=0 time=46910961 us)
    108 FILTER (cr=42600 pr=7165 pw=0 time=22479986 us)
    108 NESTED LOOPS (cr=42600 pr=7165 pw=0 time=22479338 us)
    13624 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=161365 us)
    13624 TABLE ACCESS FULL TABLE_1 PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=147679 us)
    108 TABLE ACCESS BY INDEX ROWID TABLE_2 (cr=41969 pr=6536 pw=0 time=45016626 us)
    13620 INDEX UNIQUE SCAN TABLE_2_PK (cr=27250 pr=966 pw=0 time=6201521 us)(Object ID 480908)
    ;

    -- 2. 인덱스 생성
    CREATE INDEX USER.TABLE_2_N4 ON USER.TABLE_2 (REQUEST_DATE, REQUEST_ID)

    -- 3. 튜닝후
    SELECT DISTINCT XPRH.COL_1,
    XPRH.COL_2,
    XPRH.COL_3,
    XBR.COL_1,
    XBR.COL_2,
    XBR.COL_3
    FROM TABLE_1 XPRH,
    TABLE_2 XBR
    ------------------------------------------------
    -- TABLE_2 추가(KJS)
    TABLE_2 XBR_T
    ------------------------------------------------
    WHERE XPRH.REQUEST_ID = XBR_T.REQUEST_ID
    ------------------------------------------------
    -- 조인키 추가(KJS)
    AND XBR.ROWID = XBR_T.ROWID
    ------------------------------------------------
    AND XPRH.PROMPT_LIMIT_TYPE_CODE = :3
    AND XPRH.CURRENCY_CODE = :4
    AND XBR.REQUEST_DATE >= :5
    AND XBR.REQUEST_DATE < :6
    ORDER BY XBR.COL_3 DESC
    ;

    Call Count CPU Time Elapsed Time Disk Query Current Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse 1 0.010 0.020 0 7 0 0
    Execute 1 0.030 0.028 0 12 0 0
    Fetch 12 0.540 0.941 735 3882 0 108
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total 14 0.580 0.989 735 3901 0 108

    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 STATEMENT
    108 VIEW (cr=3882 pr=735 pw=0 time=939267 us)
    108 SORT ORDER BY (cr=3882 pr=735 pw=0 time=939252 us)
    108 HASH UNIQUE (cr=3882 pr=735 pw=0 time=938580 us)
    108 FILTER (cr=1863 pr=728 pw=0 time=592708 us)
    108 NESTED LOOPS (cr=1863 pr=728 pw=0 time=592486 us)
    108 HASH JOIN (cr=666 pr=664 pw=0 time=350058 us)
    13624 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=53849 us)
    13624 TABLE ACCESS FULL TABLE_1 PARTITION: KEY KEY (cr=631 pr=629 pw=0 time=53782 us)
    9219 INDEX RANGE SCAN TABLE_2_N4 (cr=35 pr=35 pw=0 time=100643 us)(Object ID 21787797)
    108 TABLE ACCESS BY USER ROWID TABLE_2 (cr=1197 pr=64 pw=0 time=246521 us)
    ;

    동규님 덕분에 항상 많은 도움을 많이 받고 있어서, 항상 감사하게 생각하고 있습니다. ^^
    시간 허락해 주신다면, 이번에는 제가 술을 살 수 있도록 연락 부탁드립니다. ^^

    그럼 수고하세요.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.03.29 17:44 신고  댓글주소  수정/삭제

      44619 블럭 I/O를 3882 로 만드셨네요.
      이에따라 46초 이상 걸리던 것이 1초 이내로 들어왔구요.
      대단하십니다. 이번주 중에 전화주시기 바랍니다.^^
      감사합니다.

  2. 2011.04.01 18:22  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.04.04 01:01 신고  댓글주소  수정/삭제

      오랜만이군요. TSTORY가 접속된다니 다행입니다. 아마 계속 읽으시면 적응이 되어 속도가 빨라질겁니다. 그리고 휴식은 못 하고 있습니다. 밤에 또 출근해서 블로그 관리와 독서 그리고 연구를 해야하죠. 하나라도 안하면 좀 편해질 거 같은데요.^^ 어쩔 수 없는것 같습니다.

  3. 이장미 2011.04.05 16:57  댓글주소  수정/삭제  댓글쓰기

    출근을 하루에 두 번 하시나 보네요~^^

    게다가 답글을 올리신게 새벽 1시!!

  4. 2011.04.05 21:29  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.04.05 22:20 신고  댓글주소  수정/삭제

      놔두셔도 큰문제 없습니다.^^
      블로그를 시작하시는 단계인것 같습니다.
      좋은 글 기대하겠습니다.

    • Favicon of https://1ststreet.tistory.com BlogIcon SITD 2011.04.06 13:38 신고  댓글주소  수정/삭제

      감사합니다 ^^;
      블로그는 어제 시작했습니다.
      아무래도 머릿속 정리하는데는 도움이 많이 될 것 같아서요 ㅎ
      지금은 무리지만 언젠간 저도 좋은 글 하나 남겨보고 싶습니다 ㅎ

  5. Favicon of https://aquamir.tistory.com BlogIcon 물빛미르 2019.06.25 17:12 신고  댓글주소  수정/삭제  댓글쓰기

    SQL 속도개선 검색하다가 유익하여 출처 남기고 담아가요.
    좋은 글 감사합니다.

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 부하를 좌우하는 두 가지 원리  (10) 2011.03.29
SQL튜닝 방법론  (18) 2011.01.27
Pagination과 분석함수의 위험한 조합  (26) 2010.12.23
오라클의 Update문은 적절한가?  (15) 2010.04.14
Connect By VS ANSI SQL  (6) 2010.02.11
Posted by extremedb

댓글을 달아 주세요

  1. 타락천사.. 2011.01.28 09:45  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 글 감사합니다.
    새해 복 많이 받으시길 ㅇ_ㅇ

  2. Ejql 2011.01.28 13:57  댓글주소  수정/삭제  댓글쓰기

    이런게 신입튜너한테는 아주 좋은 가이드라인 같습니다. 이것을 달고 살면서 누락된 부분이 있는지 체크포인트도 될 것이고,
    자주오면 올수록 배워가는게 있는 내용 감사합니다.

  3. 2011.01.28 14:07  댓글주소  수정/삭제  댓글쓰기

    1,2,3,4,5,6,7 을 모두 할 수 있다면 이미 초보는 아닌거같습니다 ㅎㅎ

  4. 2011.01.28 18:34  댓글주소  수정/삭제  댓글쓰기

    방법론 3.Table Access(Random Access)를 최소화 하라
    4번째 라인에
    "만약 인덱스에 없는 컬럼을 Select 해야 한다면 rowid로 테이블을 엑세스 해야 한다"라고 하셨는데,
    인덱스를 통한 테이블 엑세스가 아닌 경우에도(Full Table Scan) rowid를 이용하여 테이블 엑세스를 한다는 말씀이신가요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.01.28 23:29 신고  댓글주소  수정/삭제

      반갑습니다.
      인덱스를 사용한 경우를 나타낸 겁니다. FTS는 Random Acess를 사용하지 않습니다. 예를 들어 인덱스가 A + B 로 되어있을때 where A = '1' and B = '2' 라면 인덱스는 조회조건을 완벽하게 만족합니다. 하지만 select 절에 C 컬럼이 있다면 어쩔 수 없이 인덱스에서 획득한 rowid 로 테이블을 Random하게 엑세스 해야합니다. 이 경우를 나타낸 겁니다.
      감사합니다.

  5. 매컬리 2011.02.01 09:42  댓글주소  수정/삭제  댓글쓰기

    정말 기다리고 기다리던 글이었습니다.

    감사합니다. ^^

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.02.01 12:38 신고  댓글주소  수정/삭제

      매컬리님 블로그를 이용해 주셔서 감사합니다.
      이런 글을 기다리고 있었다니 놀랍습니다.
      매번 주관적인 입장으로 글을 쓰다보니 신입 튜너들을 위한 글은 별로 없었나 봅니다.^^ 소통이 중요하다는 것 새삼 느낍니다.

      설 연휴 잘보내시기 바랍니다.

  6. 정재열 2011.03.24 17:03  댓글주소  수정/삭제  댓글쓰기

    지난 번에 본 글인데 오늘 다시 보니 또 새롭습니다.

    저는 신입사원분의 마음에 심히 공감하게 됩니다 ㅎ

    좋은 글에 감사를 드립니다. :]

  7. 초보자 2011.05.26 15:12  댓글주소  수정/삭제  댓글쓰기

    우와.. 좋은글 감사합니다...

    많은 도움이 되네요...

  8. Favicon of http://www.perfectreplicawatch.co.uk/replica-tag-heuer-c-152.html BlogIcon replica tag heuer 2011.08.06 16:34  댓글주소  수정/삭제  댓글쓰기

    오오미 환영합니다 +_+

  9. Favicon of https://ohnu.tistory.com BlogIcon 오뉴 2015.02.01 16:45 신고  댓글주소  수정/삭제  댓글쓰기

    도움이 되는 글이라 퍼갈게요. 감사합니다. ㅎ

  10. 꽃사슴트윈스 2015.05.18 01:48  댓글주소  수정/삭제  댓글쓰기

    필요한 내용이 포함되어 있어서 공부하고 갑니다. 감사해요!^_^ 출처 밝히고 퍼가겠습니다!

  11. Favicon of https://tlstjscjswo.tistory.com BlogIcon 리틀홍콩 2015.09.16 13:51 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다!!

    출처 남기고 담아갈게요 : )

  12. Favicon of https://aquamir.tistory.com BlogIcon 물빛미르 2019.06.25 17:09 신고  댓글주소  수정/삭제  댓글쓰기

    출처 남기고 담아가겠습니다.
    감사합니다 ^^

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

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

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

댓글을 달아 주세요

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

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

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

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

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

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

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

    비밀댓글입니다

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      아삽님 오랜만입니다.

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

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

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

      감사합니다.

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

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

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

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

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

댓글을 달아 주세요

  1. Favicon of http://blitz.egloos.com BlogIcon blitz고양이 2010.04.14 15:41  댓글주소  수정/삭제  댓글쓰기

    bypass_ujvc 힌트가 그런 문제점이 있었군요. 왜 오라클에서 권장하지 않는지 이제 알겠습니다.
    저 힌트를 이용해서 짠 업데이트 문이 굉장히 빨라서 시간이 많이 걸리는 쿼리에서는 남발하고 있었는데,
    다시 생각해 볼 문제군요.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.04.14 16:25 신고  댓글주소  수정/삭제

      그렇습니다. 이 힌트를 사용한다는 것은 굉장히 위험 하지요. merge 문을 대신 사용하시면 성능을 유지할 수 있을것 입니다. 감사합니다.

  2. Favicon of http://proud0.tistory.com BlogIcon proud 2010.04.14 17:53  댓글주소  수정/삭제  댓글쓰기

    update 중에서 컬럼으로 조인해서 해당 컬럼을 업데이트하고자 할때는 merge문으로는 안되는 것인가요?
    예를 든다면

    merge into emp e
    using (select deptno
    ,deptno_new
    from dept) d
    on (e.deptno = d.deptno)
    when matched then
    update set e.deptno = d.deptno_new
    ;

    이런 상황일 경우에는 merge를 사용할수 없나요?

  3. 나그네 2010.04.15 11:51  댓글주소  수정/삭제  댓글쓰기

    1. '또 다른 제약'과 '끝없는 제약'으로 구분하셔서 마치 Updatable Join View에 대단히 많은 제약이 있는 것처럼 언급하셨는데, 사실은 둘 다 같은 원인(ORA-01779 : 키 보존 테이블이 아닌 쪽을 Update)에 기인하므로 좀 과장되게 기술했다고 느껴지네요.

    2. "bypass_ujvc 힌트를 사용하는 사람이 있는데, 절대 사용하면 안 되는 힌트"라고 하셨는데, 아래와 같은 경우도 해당되는지 의견을 듣고 싶습니다.

    update (select d.일별매출합, m.매출
    from (select 고객번호, sum(매출) 일별매출합
    from 고객별일별매출
    group by 고객번호) d
    , 고객별월별매출 m
    where d.고객번호 = m.고객번호
    )
    set 매출 = 일별매출합 ;


    3. merge문에 update와 insert를 선택적으로 기술할 수 있게 된 건 10g부터인데, 오라클 버전이 9i일 땐 어떤 방법으로 튜닝하시는지요?


    4. "1쪽이 UPDATE 안 되는 문제를 merge문으로 해결한다"고 하셨는데, bypass_ujvc 힌트 사용에 주의가 필요한 이유가 바로 이런 경우 때문인 것으로 압니다. 만약 예시하신 아래 문장이 수행된 결과를 상상해 보시죠. SQL 작성자는 empno = '7369' 사원의 부서명(dname)을 'NO_DEPT'로 변경하고자 했지만 이 사원과 같은 부서 소속원의 부서명이 일괄적으로 변경된 결과를 낳습니다.

    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' ;

    물론 SQL 작성자가 의도한 결과일 수 있으며, 그런 경우라면 문제가 안 되겠죠. 하지만 만약 사용자가 테이블간 카디널리티를 알지 못한 상태에서 단순히 empno = '7369' 사원의 부서명만 바꾸려고 한 것이라면 merge문의 처리 결과는 문제가 될 수 있습니다. 만약 empno 컬럼이 Unique하지 않다면 어떤 결과를 낳을까요? job = 'MANAGER'와 같은 조건이었다면요? 결과를 예측할 수 없죠. 이런 위험한 상황을 피하려고 오라클이 Updatable Join View에 ORA-01779 제약을 두었고, 사용자가 bypass_ujvc 힌트를 명시한 때만 수행되도록 한 것 아닌가요?
    그럼에도 merge문을 이용해 이를 회피할 수 있다고 권고하신다면 앞뒤가 맞지 않습니다. "bypass_ujvc 힌트를 절대 사용해선 안 된다"고 하셨는데, 방금 merge문의 결과는 Updatable Join View에 bypass_ujvc 힌트를 사용한 것과 동일하기 때문이죠.
    이 문제는 어떻게 생각하시는지요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.04.15 13:13 신고  댓글주소  수정/삭제

      먼저 의견을 주셔서 감사드립니다. 저는 논리적인 비판을 환영합니다. 말씀을 안하셔도 대충 어느회사 분인지 알 것 같군요.^^

      답변을 드리겠습니다.

      1번:
      제약을 두개로 나눈것은 서로 다르기 때문입니다. key 가 있지만 1쪽을 update할 수 없다는 것과 임시성 테이블에 key가 없는 상황을 구분한 것입니다. 물론 에러는 같은 번호로 나오지만 개념이 다르지요. 이게 왜 문제가 되는지 이해할수 없군요.

      2번:
      오라클사에서 Unsupport Hint 라고 밝힌 상황에서 마치 사용해야 한다고 주장 하시는것 같습니다. 그 이유가 무었입니까? 보여주신 예제는 이유가 되지 못합니다. 약간 느리더라도 안전한 방법을 선택하시길 바랍니다.

      시스템이 업그레이드 되어 힌트가 없어진다면 큰일이 아닐수 없습니다. 그리고 그 힌트에 대해서 버그가 있더라도 버그페치가 나오지 않습니다. 아주 위험 하지요.
      물론 나그네님이 모든 책임을 진다면 할말은 없습니다.

      3번:
      말씀하신대로 9i 에서는 안됩니다. 그래서 약간 느려지는 것은 막을 수 없습니다. 3번에서도 앞뒤 문맥상으로 보면 bypass_ujvc 힌트를 사용할 수 밖에 없다는 것을 간접적으로 이야기 하시는것 같아 안타깝습니다.

      4번:
      그것은 저의 실수 입니다.
      아래처럼 하시면 됩니다. ON 절에만 조건을 추가하고 WHERE 절에 조건이 빠졌군요.

      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'
      WHERE e.empno = '7369' ;

      WHERE 절을 추가하는 것이 옳겠죠. bypass_ujvc 힌트를 사용하는것은 위험한 발상입니다.

    • 나그네 2010.04.16 23:59  댓글주소  수정/삭제

      즐거운 금요일 저녁입니다. 어제오늘 너무 바빠 이제야 침착하게 답변할 시간이 생기네요.

      저는 Undocumented 기능을 가급적 쓰지 않으려고 가장 노력하는 사람 중 한 사람입니다. 반면, 숨겨진 Undocumented 기능을 끄집어내 사용하기를 즐기는 사람들도 있습니다. 그런 사람들도 bypass_ujvc 힌트를 사용하는 것만큼은 꺼리죠. 이유는 간단합니다. 이 힌트가 DML 문장에서 사용되기 때문입니다. 요컨대, 잘못 사용하면 데이터를 망가뜨릴 수 있기 때문입니다.
      그래서 저는 이 힌트와 관련된 이슈의 핵심은 데이터 훼손 가능성에 있다고 봅니다. 아래와 같은 쿼리문에서 1쪽 집합(dept)의 조인 컬럼(deptno)에 Unique 인덱스가 없다면 저도 절대 이 힌트를 사용하지 않습니다.

      update /*+ bypass_ujvc */ (
      select e.ename, d.dname from emp e, dept d
      where e.deptno = d.deptno
      )
      set ename = dname;

      당장은 dept 테이블에 중복 레코드가 없더라도 언제고 그럴 가능성이 있기 때문입니다. 오라클 사(社)가 자신이 만들고도 이 힌트 대신 merge문을 사용하라고 권고하는 이유도 여기에 있다고 보는 것이 맞습니다. (우려하시는 것처럼 나중에 혹시 오라클이 이 힌트를 없애더라도, 그것은 이 힌트가 불완전해서가 아니라 데이터를 잘못 갱신할 위험성을 차단하기 위해서일 겁니다.)

      반면, 제가 예로 든 아래 문장의 경우엔 절대 데이터를 잘못 갱신할 가능성이 없습니다. 그래서 저는 업무적으로 성능이 아주 중요할 때, 이 힌트를 요긴하게 사용하곤 합니다. (제가 이 힌트를 적극적으로 사용하라고 권하는 것처럼 호도하셨는데, 질문 내용을 다시 잘 읽어보시면 아래와 같은 패턴의 쿼리에서만 제한적으로 사용하면 문제가 없지 않겠느냐는 의미임을 이해하실 것입니다. 물론 10g에선 가급적 merge문을 사용하는 것이 바람직합니다.)

      update /*+ bypass_ujvc */
      (select d.일별매출합, m.매출
      from (select 고객번호, sum(매출) 일별매출합
      from 고객별일별매출
      where 매출일자 like '201004%'
      group by 고객번호) d
      , 고객별월별매출 m
      where d.고객번호 = m.고객번호
      and m.매출월 = '201004'
      )
      set 매출 = 일별매출합 ;

      이 힌트를 사용하지 않았을 때 약간 느려지는 수준이라고 과소평가(2~3번 답변)하셨는데, 몇 시간 수행되는 프로그램을 1분으로 줄일 때도 많습니다. merge문을 이용해 튜닝해 보셨다면 그 효과가 얼마나 powerful한지 잘 아실 것이라 믿습니다.

      다시 말씀드리지만 문제의 핵심은 데이터에 있다고 생각합니다. Undocumented 힌트라는 이유로, 또는 나중에 이 힌트가 지원되지 않을지 모른다는 이유로 "절대 사용하면 안 된다"는 논리는 너무 확대하여 해석한 것이 아닌가 싶습니다.
      혹시 미래의 어떤 버전에서 이 힌트가 지원되지 않게 되면 어떤 문제가 발생할까요? ORA-01779 에러가 발생하겠죠. 에러 메시지를 만나게 될지언정 값이 잘못 갱신되는 일은 생기지 않습니다. 새로 업그레이드하는 시점에 프로그램을 뜯어고쳐야 하는 게 부담스럽겠지만 오라클 버전을 업그레이드한다면 항상 그 정도의 공수는 늘 수반됩니다. (참고로, 최근 유명한 모 회사는 오라클 버전을 9i에서 10g로 업그레이드하는 프로젝트를 한달 간 진행한 바 있습니다.) 미처 못 고친 프로그램이 있더라도 예외(Exception) 처리를 통해 Alerting이 발생할 뿐, 데이터를 망가뜨리지는 않습니다.

      "1쪽이 UPDATE 안 되는 문제"를 merge문으로 해결할 수 있다고 하셨는데, M쪽 집합을 읽어 1쪽 집합을 update해선 안 됩니다. 이를 막으려고 ORA-01779 제약이 생겼고, 이 제약을 피하려고 bypass_ujvc 힌트까지 생긴 것 아닙니까. 이런 상황에서 merge문이 해법이다? 다행히 M쪽 집합의 조인컬럼에 중복 값이 없다면 잘 수행되겠지만 중복 건이 존재한다면 에러가 발생합니다. 중복 값이 없더라도 제가 앞서 말씀드린 4번 항목 때문에라도 그렇게 사용하는 것은 바람직하지 않습니다. (4번 항목에 대해 실수라고 답변하셨지만, 제 설명을 잘못 이해하신 거 같습니다. WHERE절에 e.empno = '7369' 조건을 추가해도 결과는 마찬가지입니다.)

      "키가 없으면 UPDATE가 불가한 문제"도 merge문으로 해결할 수 있다고 하셨는데, 맞습니다. 그렇게 하면 1쪽 집합에 PK가 정의돼 있지 않아도 갱신이 잘 처리되며, 예상치 못하게 1쪽 집합에 중복 레코드가 입력되더라도 데이터를 잘못 갱신하는 문제만큼은 발생하지 않습니다. ORA-30926 에러가 막아주기 때문이죠. 오라클이 bypass_ujvc 힌트보다 merge문 사용을 권장하는 이유입니다.
      그런데 이것은, bypass_ujvc 힌트가 미래의 어느 버전에서 갑자기 사라졌을 때 우리가 겪게 될 위험성과 같은 정도의 위험성이군요. 데이터를 망가뜨리지는 않지만 예외(Exception) 처리를 통해 Alerting !

      혹시 deprecated 될지도 모른다고 손 놓고 있기보다 적극적으로 문제를 해결하는 것이 더 큰 가치를 주는 상황은 얼마든지 있을 수 있습니다. 그럼에도 "절대 사용하면 안 되는 힌트"라고 경고한다면, 꼭 필요로 하는 분(특히 9i 시스템을 사용하는 분)들을 위축시킬 수 있을 거 같아 주제넘게 의견을 개진해 보았습니다.
      오동규 님도 프로젝트를 수행할 때, A가 정답인 줄 알면서도 눈물을 머금고 B를 선택해야 하는 상황에 자주 맞닥뜨리실 것입니다. 오라클 Hidden 파라미터를 사용하는 것이 바람직하지 않음을 알면서도 어쩔 수 없이 값을 조정해 문제를 해결했던 사례도 있을 것입니다. 마찬가지로, Updatable Join View의 특징과 bypass_ujvc 힌트의 올바른 활용 용도를 숙지하고 적재적소에 사용한다면, 그것이 비록 차선책이어서 꺼림칙할지는 몰라도 눈물을 머금을 일은 아닌 것 같습니다.

      PS) 이 글을 읽는 모든 분께 한마디! 10g 이상 버전을 사용하고 계시다면 오동규 님의 충고를 가볍게 여기지 마시고 가급적 merge문을 사용해 주는 센스, 잊지 마시길..^^

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.04.19 11:30 신고  댓글주소  수정/삭제

      공감이 가는 부분이 많이 있습니다.
      잘 아시는 내용이겠지만 이글을 보는분들을 위하여 두가지는 짚고 넘어가야 할것 같습니다.
      1.
      먼저 Undocument 힌트라고 다 같은 것은 아닙니다.
      예를 들면 BITMAP_TREE 힌트는 Undocument 힌트라도 오라클이 내부적으로 사용하기 때문에 버그등이 있을경우 오라클사에서 지원(버그페치)이 가능합니다. 하지만 bypass_ujvc와 같은 Unsupport 힌트는 그렇지 못하지요.

      2. 1:N 문제 입니다. N쪽을 참조할 필요가 있는 경우 대부분 권장하지 않는 bypass_ujvc를 사용하므로 그렇게 하지말고 MERGE 문을 사용하자는 것입니다. 물론 말씀하신대로 의도적으로 그렇게 해야겠지요. 그런데 어떻게 보면 이것은 당연한 것입니다. 말씀하신 empno = '7369' 사원의 부서명만 update 할수 있는 방법은 세상에 없기 때문입니다.

  4. feelie 2010.04.16 14:02  댓글주소  수정/삭제  댓글쓰기

    내용도 좋고 위의 두분의 질문과 답변에도 무척 감동적 이네요..
    이해하기 힘든 내용이라 다시 책을 보고, 블러그를보니 그래도 정리가 되네요..
    그래도 궁금한점이 있어 물어봅니다.
    1.
    위의 답글 내용에서
    "말씀하신대로 9i 에서는 안됩니다. 그래서 약간 느려지는 것은 막을 수 없습니다" 라고 하셨는데
    9i에서는 insert , update를 구별해서 처리한다는 말씀인가요?

    2.
    또다른 제약에서 1쪽의 집합을 업데이트 할수 업다.
    해결방법은 힌트사용 : bypass_ujvc 사용
    힌트 사용말고 다른 방법이 있는지 궁금합니다..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.04.16 14:08 신고  댓글주소  수정/삭제

      반갑습니다.
      1번
      9i 에서는 merge 문에 update만 사용할 수 없고 무조건 insert도 있어야 한다는 뜻 입니다.
      10g 에서는 merge 에서 insert 없는 update 문만 사용할 수 있죠.

      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'
      WHERE e.empno = '7369' ;

  5. Ejql 2010.07.30 10:14  댓글주소  수정/삭제  댓글쓰기

    이제는. 무엇을 말씀하시는지 알겠습니다. 하나하나 배운다는것이 정말 힘든것도 느낍니다.
    이해가 되면 기쁘지만. 이해가 안되는 날에는.. 앞날 생각이 먼저 나는군요.

    감사합니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.07.30 17:46 신고  댓글주소  수정/삭제

      너무 급하게 하지 마시기 바랍니다.
      비관적으로 생각하지 마시고 천천히 하나씩 보신다면 내공이 쌓이면서 이해가 가실겁니다. 건승하세요.

  6. 세모변천일 2011.03.08 14:48  댓글주소  수정/삭제  댓글쓰기

    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'
    WHERE e.empno = '7369' ;
    위 쿼리에서 꼭 where e.empno = '7369' 이 조건이 들어가야 하나요 ? on절에 기술한 조건이 있는데,
    만약 안들어가면 결과가 어떻게 되나요 ?

필자는 예전에 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

댓글을 달아 주세요

  1. feelie 2010.02.11 16:16  댓글주소  수정/삭제  댓글쓰기

    잘봤습니다.
    명절 잘보내시고요....
    책이 출판될 날이 기대됩니다...

  2. 혈기린 2010.02.12 10:09  댓글주소  수정/삭제  댓글쓰기

    오호 순환구조를 지원하는 ansi sql이 있었군요 설연휴가 끝나면 책이 나오겠네요 기대만방입니다 ^^
    설연휴 잘보내세요 ~~

  3. baind 2010.02.18 11:14  댓글주소  수정/삭제  댓글쓰기

    정말로 멋진글 잘 보았습니다. 역전의 역전이라...
    오동규님의 책 발간이 얼마 안남으셨다는 글에 실로 두근 거리는 마음에 기다립니다.
    새해 복 많이 받으시고 건강하세요~~
    두근두근~~`

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.02.18 14:46 신고  댓글주소  수정/삭제

      성원에 감사드립니다.
      안타까운 점은 이 블로그의 내용처럼 난위도가 있는 책이라 보는 사람이 별로 없을 것으로 예상됩니다.
      전통적으로 옵티마이져 책은 독자층이 매우 적습니다.
      새해 복 많이 받으세요.

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

Posted by extremedb

댓글을 달아 주세요

  1. 우연히 2009.07.16 20:06  댓글주소  수정/삭제  댓글쓰기

    힌트를 제대로 아는 것이 매우 중요하지만, 여기 예시로 든 내용이 가슴에 와 닿지는 않는군요. 오라클 매뉴얼에 이런 옵션을 굳이 설명하지 않은 이유는... 아마 필요없는 기능이기 때문일 것입니다.
    튜닝을 전담하는 사람들까지도 이런 사실을 모르고 있으니 큰일이 아닐수 없다고 하셨는데, in-list를 concatenation 방식으로 유도할 이유가 무엇이죠? inlist iterator 방식으로 놔 두었을 때보다 더 효과적인가요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.07.17 08:59 신고  댓글주소  수정/삭제

      무릇 글이란 공감대가 형성 되어야 하지만 그렇지 못했던것 같습니다. 예제가 적절하지 않았던것 같습니다.
      가슴에 와닿지 않는 글을 써서 정중히 사과드립니다.

      첫번째 질문인 메뉴얼에 없으므로 필요없는 기능이 아닌지 에 대한 답변입니다.
      그런 생각을 하실수 있습니다.
      오라클 10g R2 부터 Use_concat 의 인자가 2개로 바뀌었지만 매뉴얼은 그것을 따라오지 못하는 경우가 많이 있습니다. 또한 메뉴얼이 잘못된 점도 많지요.

      저의 직업상 메뉴얼을 교과서 처럼 탐독하기도 하지만 때로는 그것을 비판적으로 바라보아야 함을 이해해주시기 바랍니다. 교과서는 교과서일뿐 모든 경우를 커버하지 못합니다.

      이번에 국사 교과서를 보수파 입장에서 개편을 했다고 합니다. 국사 교과서가 김구선생를 마치 테러 리스트 처럼 묘사한다고 해도 그것을 그대로 믿는 사람이 있을 것이며 잘못되었다고 비판하는 사람도 있을것입니다. 참고로 제가 지금 집필하고 있는 책은 메뉴얼에 나오지 않는 것들 입니다. 조나단 루이스의 Cost Based Oracle Fundamental 처럼 말입니다. 과연 이것들이 메뉴얼에 나오지 않는다고 알 필요 없을까요? 그것은 직업에 따라 다르다고 생각합니다. 과연 초급 개발자가 이런것 까지 알아야 하나 라고 물으신다면 할말은 없습니다.


      두번째로 InList 관련 답변입니다. 우연히 님의 말씀이 맞습니다. Inlist 가 Concatnation 방식 보다 무조건 우월하거나 대등 합니다. 하지만 제가 바라보는 관점은 다른데 있습니다. 이렇게 힌트의 기능및 인자를 정확히 알면 TroubleShooting 이 가능해집니다.

      10G R2 부터는 Use_concat 힌트가 먹히지 않는 경우가 많이 있습니다. 그럴경우 USE_CONCAT(쿼리블럭명 1) 힌트를 사용하면 해결되는 경우를 숫하게 보아 왔습니다. 실전예제는 http://jonathanlewis.files.wordpress.com/2009/05/hints_on_hints.pdf 에서 파일을 다운받으시고 6~7 페이지를 보신다면 이해가 가실것입니다.

      컨설턴트는 메뉴얼이 잘못된것 혹은 메뉴얼이 빠뜨린것 까지도 파고 들수 있다는것이 저의 지론 입니다. 왜냐하면 옵티마이져가 발전함에 따라 문제가 발생되는 경우는 점점 줄어들 것 이지만 문제가 발생한다면 위의 경우(pdf 파일의 6~7 페이지)와 같이 예외 케이스인 경우가 많이 생길것이기 떄문입니다. 컨설턴트란 이럴 경우에 문제를 정확히 그리고 빨리 해결해야 하는 사람들 입니다. 이럴 경우 힌트의 사용법을 정확히 알지 못하면 튜닝이 힘들어 지는 경우가 많이 있습니다.

      물론 저와 의견을 달리 하시는 분들도 있을것입니다. 제 입장을 이해해 주시기 바랍니다. 어디까지나 저의 주관적인 생각이기 때문에 다른 생각을 한다고 해서 그것이 틀렸다고 생각하지 않습니다.

  2. ExtraOdinary 2010.11.03 15:54  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 고객사 튜닝 업무 중 use_concat 힌트를 통해 튜닝을 하였는데, 궁금한 사항이 있어 검색하던 중
    아니나 다를까 동규님 블로그가 검색되더군요..^^;;
    LNNVL 함수에 대해 좀 더 자세히 알고 싶은데...설명이 가능할까요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.11.04 09:33 신고  댓글주소  수정/삭제

      안녕하세요.
      LNNVL 함수는 중복을 제거하는 기능을 합니다.
      가령
      col1 = 'a' or col2 = 'b' 조건을 보고 Concatnation으로 풀렸다면

      select ~
      from tab
      where col1 = 'a'
      union all
      select ~
      from tab
      where col2 = 'b'
      and col1 <> 'a' -- 중복제거

      처럼 중복을 제거 할 수 있습니다.
      그런데 col1 <> 'a' 조건대신에 lnnvl함수를 쓰는 이유는 col1이 null인 경우 때문입니다. 즉 union all 기준으로 아래쪽 집합에서 lnnvl은 (col1 <> 'a' or col1 is null) 와 같은 역활을 합니다.

  3. 이재현 2011.07.26 21:52  댓글주소  수정/삭제  댓글쓰기

    안녕하세요..겸사겸사 댓글 남깁니다.

    그동안 정말 젬나게 봐온 사이트입니다. 정말 감사하고 소중한 사이트입니다.

    정말 감사합니다..

    하나 문의 사항이 있어서 댓글 남깁니다.

    A라는 테이블을 QB_NAME(MAIN) USE_CONCAT(@MAIN 1)

    B라는 테이블을 PUSH_PRED( B )

    A에서 B조인을 걸면을때 QB_NAME(MAIN) USE_CONCAT(@MAIN 1) 사용하면 전혀 UNION ALL 풀리지 않는 경우가 발생

    하더군요

    물론 QB_NAME(MAIN) USE_CONCAT(@MAIN 1) 힌트를 사용하지 않으면 IN-LIST 풀립니다.

    넓고 깊은 지식 가르침좀 주심알될까요??ㅠ

    --애독자 올림

  대부분의 TOP SQL 은 ROWNUM 을 이용하여 부분범위처리 하는 개념임을 알것이다.
하지만 RANK 등의 분석함수를 이용하여 TOP 처리를 하는경우가 많이 있다.
이런경우에 대부분의 개발자및 DBA 들이 범하는 오류가 있으므로 오늘은 그부분을 바로잡고자 한다.
환경은 Oracle 10.2.0.4 이며 모든 예제는 오라클의 SH 스키마의 SALES 테이블을 이용한다.

들어가며
  ROWNUM 을 이용한 TOP SQL과 분석함수를 이용한 것의 다른점이 무엇인가?.
분석함수를 이용하면 1등이 여러명일때도 처리가 가능 하지만 ROWNUM 을 이용할경우 단한건만 가져오므로 처리가 불가능하다는 것이다.  아래는 그러한 경우에 분석함수를 하여 간단하게 해결한 경우이다.

SELECT /*+ gather_plan_statistics */
       *
  FROM (SELECT cust_id, prod_id, time_id, amount_sold,
               RANK () OVER (ORDER BY amount_sold DESC) top_sales
          FROM sh.sales a)
 WHERE top_sales <= :V_RANK;   --> 숫자 1 대입


먼저 매출액기준으로 순위를 만든후에 인라인뷰의 밖에서 FILTER 하는 형태의 SQL 이다.   
금액기준으로 가장 많이 팔린 상품과 해당고객 그리고 해당매출일자 등을 SELECT 하고 있다.

----------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |      1 |    108 |00:00:01.09 |    1718 |          |
|*  1 |  VIEW                    |       |      1 |    108 |00:00:01.09 |    1718 |          |
|*  2 |   WINDOW SORT PUSHED RANK|       |      1 |    109 |00:00:01.09 |    1718 |   45M (0)|
|   3 |    PARTITION RANGE ALL   |       |      1 |    918K|00:00:00.01 |    1718 |          |
|   4 |     TABLE ACCESS FULL    | SALES |     28 |    918K|00:00:00.01 |    1718 |          |
----------------------------------------------------------------------------------------------
 
A-Rows 항목을 보면 1등이 108 건임을 알수 있다.
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<=:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_RANK)
 
울며 겨자먹기
  약 91만건을 full scan 하였으며 일량(Buffers)과 메모리 사용량(Used-Mem)이 엄청나다.
분명히 문제가 있다.
분석함수로 TOP SQL 을 구현할경우 이와 관련된 일반적인 3가지 미신(오류)이 있다.

첫번째 오류로는 일반적으로 금액컬럼에 인덱스를 만들지 않는다는 것이다.

두번째 오류로는 금액컬럼에  인덱스를 만들어도 분석함수를 사용하였으므로 인덱스를 타지 않는다는 것이다.
(참고로 분석함수로는 Function Based Index를 만들수 없다.)

세번째 오류가 가장 심각하다.
인덱스를 사용하지 못할 바에야 전체건을 sort 해야하므로 울며 겨자먹기로 순위컬럼을 모델에 추가하고 그컬럼에 인덱스를 생성하며 데이터가 바뀔때마다 실시간으로 순위컬럼에 UPDATE 하는방식을 많이 사용하고 있다.
이런것을 추출속성이라고 하는데 정합성을 해칠수 있으므로 물리모델링시에 최소화 시켜야 한다.  
그렇다면 이러한 문제를 어떻게 풀수 있을까?

먼저 AMOUNT_SOLD에 인덱스를 하나 만든다.

CREATE INDEX SH.SALES_IDX01 ON SH.SALES
(AMOUNT_SOLD);

SELECT /*+ gather_plan_statistics FIRST_ROWS(1) */
       *
  FROM (SELECT prod_id, amount_sold,
               RANK () OVER (ORDER BY amount_sold DESC) top_sales
          FROM sh.sales a)
 WHERE top_sales <= :V_RANK;  --> 숫자 1 대입

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      11 |
|*  1 |  VIEW                                |             |      1 |    108 |00:00:00.01 |      11 |
|*  2 |   WINDOW NOSORT STOPKEY              |             |      1 |    108 |00:00:00.01 |      11 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    109 |00:00:00.01 |      11 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    109 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<=:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_RANK)
 
 
해결책은 인덱스 생성
  금액 컬럼으로 인덱스를 하나 만들었을 뿐인데 성능과 정합성이라는 두가지 이슈를 모조리 해결 하였다.
일량이(Buffers ) 170 배정도 차이가 나며 메모리 사용은 하지도 않는다.
분석함수 사용시 인라인뷰의 바깥에서 filtering 시의 비밀은 3가지로 요약할수 있다.

1.해당컬럼 컬럼에 인덱스가 있을경우 rownum 과 같이 Stop Key Operation 이 발생 한다는 것이다.

2.Predicate Information 을 보면 뷰를 만들기 전에 filter 하는 것을 볼수 있다.(id 기준으로 2번)
  이때 인덱스를 사용하는 것이다.

3.id 기준으로 2번 Operation 을 보면 WINDOW NOSORT ~ 가 있다.
이것은 인덱스가 이미 정렬이 되어있기 때문에 Sort 가 필요없다는 뜻이다.

마치 Sort Group By 시에 해당컬럼이 인덱스로 생성되어 있을경우 NOSORT 가 나오는 원리와 같다.  
하지만 인덱스가 없으면 Full Table Scan + filter 만 일어난다. 이때 WINDOW SORT PUSHED RANK Operation 이 발생하게 된다.(가장 위의 인덱스가 없을때의 plan 을 참조 할것)


ROWNUM VS RANK

  WINDOW NOSORT STOPKEY 사용시 PLAN 을 보면 1건 더 SCAN했음을 알수 있다.
즉 1위그룹은 108 건  이지만 109 건을 scan 하였고 WINDOW FILTER 과정에서 1건이 제거되었다.
하지만 ROWNUM 을 이용한다면 어떻게 될까?
아래는 ROWNUM 으로 108 건(1위그룹의 전체건수)을 가져오는 예제이다.

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) */ *
  FROM (SELECT prod_id, amount_sold
          FROM sh.sales a
         ORDER BY amount_sold DESC )
WHERE ROWNUM <= :V_RANK --> 108 대입;        


-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      10 |
|*  1 |  COUNT STOPKEY                       |             |      1 |    108 |00:00:00.01 |      10 |
|   2 |   VIEW                               |             |      1 |    108 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    108 |00:00:00.01 |      10 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    108 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<:V_RANK)
  
정확히 108 건만 SCAN 했음을 알수 있다.
이것이 ROWNUM 과의 차이이다. ROWNUM 은 정확히 건수를 잘라내지만 WINDOW STOP KEY 는 한건을 더읽어야만 한다.
하지만 이정도(1건)의 비효율은 눈감아 줄수 있다. 하지만 아래의 경우는 비효율이 심하므로 반드시 피해야 한다.

SELECT /*+ gather_plan_statistics FIRST_ROWS(1) */
       *
  FROM (SELECT prod_id, amount_sold,
               RANK () OVER (ORDER BY amount_sold DESC) top_sales
          FROM sh.sales a)
 WHERE top_sales < :V_RANK;  --> 숫자 2 대입


-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      13 |
|*  1 |  VIEW                                |             |      1 |    108 |00:00:00.01 |      13 |
|*  2 |   WINDOW NOSORT STOPKEY              |             |      1 |    115 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    116 |00:00:00.01 |      13 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    116 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<:V_RANK)

RANK <= 1과 RANK < 2 는 결과는 같다. 하지만 성능은?
  WHERE 절에서 = 을 제거하고 < 로 바꾸었으므로 :V_RANK 변수에 2 를 대입 해야 같은결과를 넏을수 있다.
결과는 <= 를 사용한 것과 같지만 일량(Buffers)이 11 에서 13 으로 늘었다.
이것또한 분석함수를 이용한 TOP SQL 처리시 기억해야할 특징중의 하나인데 :V_RANK 변수에 2를 대입 하면 2위그룹까지 SCAN 한다.(정확히 말하면 3위그룹의 첫번째 ROW 까지 SCAN 한다.)
따라서 WINDOW STOPKEY 사용시에 = 를 생략하면 안된다.
 
결론:
여러 사이트에서 분석함수에 관한 몇가지 오해 때문에 성능이 나빠짐은 물론이고 정합성이 위배될수도 있는 일들이 벌어지고 있다.
두가지만 기억하자.
1.분석함수로 TOP SQL 처리시 반드시 인덱스를 만들것. (빠른성능을 원한다면)
2.RANKING 비교시 <= 를 사용할것.

편집후기 : 주의사항이 있으므로 반드시 댓글을 참고할것.

Posted by extremedb

댓글을 달아 주세요

  1. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2009.05.22 12:03 신고  댓글주소  수정/삭제  댓글쓰기

    좋은데요? ^^

    이 Post를 보는 다른 분들을 위해 한가지 주의점만 추가하면 위의 예제에서 amount_sold 컬럼이 반드시 NOT NULL이어야 한다는 것입니다. (그리고 sh.sales.amount_sold 컬럼은 당연히 NOT NULL로 선언되어 있구요)

    NOT NULL이 보장이 안되면 Index Scan만으로 Data를 다 가져온다는 것이 보장이 안되기 때문에 Full Scan 외에는 대안이 없기 때문입니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.22 13:35 신고  댓글주소  수정/삭제

      말씀하신데로 Full scan 을 피하려면 아래의 2가지 방법으로 하면 되겠습니다.
      1.NULL 을 허용하는 컬럼인 경우 NOT NULL 로 바꾸는 방법
      2.아래처럼 WHERE 절에 NOT NULL 을 명시하는 방법.

      SELECT /*+ gather_plan_statistics FIRST_ROWS(1) */
      *
      FROM (SELECT /*+ INDEX_DESC(A SALES_IDX01) */
      cust_id, prod_id, time_id, amount_sold,
      RANK () OVER (ORDER BY amount_sold DESC) top_sales
      FROM sh.sales a
      WHERE amount_sold IS NOT NULL)
      WHERE top_sales <= :V_RANK ;

      또한 현실적으로 볼때 null 인 경우 순위를 정할필요가 없을거 같습니다.
      좋은정보 감사합니다.

  2. feelie 2010.02.19 16:39  댓글주소  수정/삭제  댓글쓰기

    한번 읽어본 내용인데 다시 한번 보고, 실제로 테스트를 해보니 직접하는거하고 많이 다르네요.
    실제로 제가 테스트를 해봐야 겠다는 생각이 듭니다.

    내용중에 궁금한점이 있어서요.
    1. 인덱스를 만들고, first_rows(1) 힌트를 사용하셨는데요. index 힌트를 사용하는거하고 다른점이 있나요
    (first_rows, index)
    2. 9i부터로 기억되는데요. 9i 이후부터는 인덱스에 desc, asc 을 사용하지 않아도 옵티마이저가 알아서 min/max을
    사용해서 원하는 데이터만 스캔하는것으로 알고 있습니다.
    위의 테스트에서 first_rows 대신에 index 힌트를 사용했더니 인덱스를 역순으로 읽어주지 못해서, index_desc 힌트를
    사용했고, 그랬더니 정상처리됬습니다.
    분석함수에서는 min/max 을 옵티마이저가 처리를 못하는것인지 궁금합니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.02.19 17:02 신고  댓글주소  수정/삭제

      잘 아시겠지만 First_rows(n) 힌트와 INDEX 힌트는 다릅니다. 하지만 위의 예제에서 INDEX_DESC 힌트를 사용한다면 같은 효과를 볼 수 있습니다.

      그리고 분석함수에서는 min/max operation이 나타나지 않습니다. 수동으로 처리해 주어야 합니다. 물론 First_rows(N) 이 있기는 하지만 최악의 경우 FULL SCAN으로 처리될 수도 있기 때문에 INDEX_DESC 만이 완벽한 방법입니다.

  3. Favicon of http://tslee58@gmail.com BlogIcon 이태수 2019.03.27 14:14  댓글주소  수정/삭제  댓글쓰기

    늦게 늦게 인사드립니다.
    위의 샘플을 테스트 해보다 사소하지만 중요한 점을 찾았네요.
    SQL Developer에서 테스트 하는데 해당툴은 바인딩변수의 데이터 타입을 설정하는 기능이 없어 기본적으로 문자형으로
    인식하는듯 합니다. 따라서 바인딩 변수를 TO_NUMBER(:B1)과 같이 형변환을 하는데 이렇게 되었을때
    "WINDOW NOSORT STOPKEY"에서 STOPKEY가 적용이 되지 아니하여 부분범위처리를 하지 않고 전체범위처리를 하게되어
    최초보다 성능이 더 안좋게 나오네요.
    바인딩변수에 데이터형을 지정할 수 있는 다른 툴에서 숫자형으로 지정하니 위의 예제와 같이 정확히 나오구요.

    좋은 글 감사드립니다.


    -----------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
    -----------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 108 |00:00:00.01 | 20972 |
    |* 1 | VIEW | | 1 | 108 |00:00:00.01 | 20972 |
    | 2 | WINDOW NOSORT | | 1 | 918K|00:00:00.91 | 20972 |
    | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 1 | 918K|00:00:00.56 | 20972 |
    | 4 | INDEX FULL SCAN DESCENDING | SALES_IDX01 | 1 | 918K|00:00:00.13 | 2455 |
    -----------------------------------------------------------------------------------------------------

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

    1 - filter("TOP_SALES"<=TO_NUMBER(:V_RANK))

PAGE 처리 할때나 아니면 부분범위 처리를 할때 우리는 TOP SQL 을 많이 사용한다.
오늘은 rownum 을 이용한 TOP N건 처리시 몇가지 함정이 있으므로  정확한 사용법에 대해서 알아보고자 한다.
먼저  환경을 만들기 위하여 테이블및 인덱스를 생성 한다.
환경 : ORACLE 11.1.0.6

create table scott.big_emp as                --> BIG_EMP 생성   
select rownum as empno, a.ename, a.job, a.mgr, a.hiredate, a.sal + rownum as sal, a.comm, a.deptno
  from scott.emp a,
       (select level as no from dual connect by level <= 2000) b
;

create index scott.ix_emp_01 on scott.big_emp (deptno, sal) --> deptno, sal 로 구성된 결합 인덱스 생성
;

create table scott.new_dept as            --> NEW_DEPT 생성
select a.* , a.deptno as page_count    --> 추가적으로 page_count  컬럼생성
  from scott.dept a
;

alter table scott.new_dept add                                         --> PK 및 UNIQUE 인덱스 생성
( constraint pk_new_dept primary key (deptno) using index );

begin                                                                           --> 통계정보 생성
    dbms_stats.gather_table_stats('scott', 'big_emp', cascade => true);
    dbms_stats.gather_table_stats('scott', 'new_dept', cascade => true);
end;


 EMP 와 DEPT 를 이용하여 테이블및 인덱스를 생성 하였고 new_dept 테이블에는 부서별로 PAGE COUNT 라는 컬럼이 추가적으로 생성 되었다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ e.*
  from  (select /*+ index_desc(e IX_EMP_01) */
                    e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname
           from big_emp e,
                  new_dept d
         where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc) e
where rownum < :v_page_count             --> :v_page_count  에 숫자 20을 대입한다.
 ;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

20번 부서에 해당하는 사원중에서 급여가 큰순으로 20건만 보겠다는 의미이다.

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |             |      1 |     20 |00:00:00.01 |       8 |
|   2 |   VIEW                          |             |      1 |     20 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS                 |             |      1 |     20 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID | BIG_EMP     |      1 |     20 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN DESCENDING| IX_EMP_01   |      1 |     20 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------
 

위 PLAN 상의 A-Rows 에 나타나듯이 부서번호 20번에 해당하는 모든건을 엑세스및 조인한것이 아니라
 정확히 BIG_EMP 쪽을 20 건만 scan 하였다.
이것이 COUNT STOPKEY 의 위력이다.
즉 BIG_EMP 테이블에 deptno = 20 조건을 만족하는 건이 아무리 많더라도 20 건만 읽고 끝낸다는 것이다.

 하지만 rownum < :v_page_count 조건대신에 부서별로 PAGE COUNT 를 테이블에 저장 하고
그 값을 이용하여 TOP SQL 을 작성 해야 한다면 이야기가 달라진다.
아래의 SQL 을 보면 부서별로 PAGE COUNT 를 지정 하고 있다.

select deptno, page_count
  from new_dept;

<결과>
DEPTNO PAGE_COUNT
------ ----------
    10         10
    20         20
    30         30
    40         40

20번 부서의 경우 20 건만 보겠다는 의미이다.
이제 NEW_DEPT 테이블의 PAGE_COUNT 값으로 TOP SQL 을 작성해서 실행 해보자.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc) a
where rownum < a.page_count ;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  COUNT                           |             |      1 |     20 |00:00:00.01 |    6269 |
|*  2 |   FILTER                         |             |      1 |     20 |00:00:00.01 |    6269 |
|   3 |    VIEW                          |             |      1 |  10000 |00:00:00.09 |    6269 |
|   4 |     NESTED LOOPS                 |             |      1 |  10000 |00:00:00.07 |    6269 |
|   5 |      TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |      1 |00:00:00.01 |       2 |
|*  6 |       INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |      1 |00:00:00.01 |       1 |
|   7 |      TABLE ACCESS BY INDEX ROWID | BIG_EMP     |      1 |  10000 |00:00:00.04 |    6267 |
|*  8 |       INDEX RANGE SCAN DESCENDING| IX_EMP_01   |      1 |  10000 |00:00:00.01 |      29 |
-------------------------------------------------------------------------------------------------
 
 어이 없게도 20번 부서에 해당하는 전체건(10000 건)을 다 SCAN 하였다.
COUNT STOPKEY Operation 대신에 FILTER Operation 이 사용되었기 때문이다.
아래처럼 ROWNUM 조건을 인라인뷰 내로 밀어 넣어도 결과는 마찬가지 이다.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
            and rownum <= d.page_count            
          order by e.sal desc)
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   1 |  VIEW                            |             |  10071 |  7898   (1)|     20 |00:00:00.01 |    6269 |
|   2 |   COUNT                          |             |        |            |     20 |00:00:00.01 |    6269 |
|*  3 |    FILTER                        |             |        |            |     20 |00:00:00.01 |    6269 |
|   4 |     NESTED LOOPS                 |             |  10071 |  7898   (1)|  10000 |00:00:00.11 |    6269 |
|   5 |      TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  6 |       INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   7 |      TABLE ACCESS BY INDEX ROWID | BIG_EMP     |  10071 |  7897   (1)|  10000 |00:00:00.08 |    6267 |
|*  8 |       INDEX RANGE SCAN DESCENDING| IX_EMP_01   |  10071 |    27   (0)|  10000 |00:00:00.05 |      29 |
--------------------------------------------------------------------------------------------------------------

 

그렇다면  이런 경우에 어떻게 TOP 처리를 할수 있겠는가?
이 문제는 ROWNUM 에 대한 기본을 알면 해결된다.
TOP N건 처리시에 ROWNUM 과의 비교대상은 반드시 상수(혹은 변수) 이어야 한다. 
그렇지 않다면 TOP 처리는 실패한다.
아래의 SQL은 이 문제를 해결하였다.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc)
where rownum <= (select page_count from new_dept where deptno = 20)  ;

select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |             |        |            |     20 |00:00:00.01 |      10 |
|   2 |   VIEW                          |             |  10071 |  7898   (1)|     20 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS                 |             |  10071 |  7898   (1)|     20 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID | BIG_EMP     |  10071 |  7897   (1)|     20 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN DESCENDING| IX_EMP_01   |  10071 |    27   (0)|     20 |00:00:00.01 |       3 |
|   8 |   TABLE ACCESS BY INDEX ROWID   | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  9 |    INDEX UNIQUE SCAN            | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------


 PLAN 에서 보는것과 같이 서브쿼리를 사용하여 PAGE COUNT 를 상수화 하였다.
즉  rownum <= (서브쿼리) 형태가 되면 COUNT STOPKEY Operation 을 정상적으로 사용할수 있다.
"같은 테이블을 반복하여 읽지마라." 라는 튜닝의 기본적인 원칙이 깨지는 순간이다.
하지만 모든 규칙은 예외가 있음을 기억해야 한다.

결론 :
위에서 보는 바와 같이 COUNT STOPKEY 와 FILTER Operation 은 천당과 지옥의 차이이다.
몇천만건 혹은 몇억건 이상인 환경에서  FILTER  Operation 이 나온다면?
더이상 그 프로그램은 사용할수 없을것이다.
TOP SQL 처리시 주의사항은 ROWNUM 과 비교시 항상 상수(혹은변수)와 비교하거나 서브쿼리등을 이용하여 상수화 한후에 비교 하여야 한다는 것이다.

Posted by extremedb

댓글을 달아 주세요

  1. 2009.08.03 14:39  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

얼마전에 필자는 한 지인으로 부터 페이징 처리가 소용이 없을것 같은 쿼리를 봐달라는 요청을 받았다.
SQL 을 보니 WHERE 절에 대해서는 인덱스가 적절하게 잡혀 있었으나 ORDER BY 절에 대해서는
인덱스로 해결될수 있는 성격의 쿼리가 아니었다.
다시말해 ORDER BY 절 대로 인덱스를 생성할 경우 WHERE 절이 다치는 경우가 종종 있는데 그 SQL 이
그런경우 였다..
그 지인은 웹환경에서 결과건수가  1000 건 이상이 될수도 있는 쿼리 임에도 불구하고 "ORDER BY 절 때문에 부분범위 처리가 되지 않으니 페이징 처리가 필요없다"   는 주장이 었다.
얼핏보면 전체범위가 될수 밖에 없으니 맞는말 같지만 그말은 페이징처리 (Oracle 의 Rownum) 의 특성을 모르는데서 기인한다.
페이지 처리나 TOP SQL 등은 인덱스 상황이나 ORDER BY 상황 등의 여부에 따라서 하느냐 안하는냐를 결정하는것이 아니다.
ROWNUM 처리는 무조건 하는것이 이득이다.
그이유는 3가지이다.

1.전체건을 client 로 다가져온뒤에 다버리고 첫번째 페이지만 보여주는것은 비효율적이다.
  DB 입장에서도 전체건을 fetch 하는 비효율을 범했고 client 측에서도 filtering 해서 첫화면만 보여주는 Logic이
  추가되어야 하기 때문이다.

2.전체건을 다가져오게되면 DB 에서 페이지 처리되어 첫번째 화면의 데이터만 가져오는경우와 비교해보면
  네트웍의 전송량이 많아진다.

3.인덱스가 없는 ORDER BY 에 대해서 페이지 처리(ROWNUM 처리)를 하면 전체범위에 대하여 SORT 를
  수행하지 않고 해당 페이지건만 SORT 한다.

여기서는 1, 2번에 대해서는 논하지 않고  3번문제에 대해서만 논한다.
그러면 ORDER BY 절에 관련된 인덱스도 없는데 어떻게 해당건만 SORT 를 할수 있을까?
그이유는 ORDER BY + ROWNUM  작업은 ROWNUM 이 없는 ORDER BY 작업과는 구현로직이 완전히 다르다는데 있는 것이다.
아래는 ORDER BY + ROWNUM 과 ROWNUM 이 없는 ORDER BY 와의 차이점을 잘보여준다.

테이블 건수가 100만건이고 가장큰값 MAX 10 개를 찾는걸로 가정하면

select ...
from   (select * from T ORDER BY unindexed_column)
where ROWNUM <= 10;

첫번째로 위의 ORDER BY + ROWNUM <= 10 작업은 5단계로 나뉜다.

1. 맨처음 10 건을 읽어서 SORT 한후 배열에 저장한다.
2. 11건 째부터는 테이블의 값과 배열의 값을 비교한다.-->테이블의 값과 배열에서 값이 가장 작은값과
   큰지 작은지 비교한다.
3. 비교후 작으면 버린다. --> 이경우 추가작업 없음.
4. 비교후 크면 기존의 배열에서 MIN 인건을 버리고 새로 찾은건을 10 개 내에서만 SORT 하여 배열에서
   자신의  위치를 찾아서  적재한다.  
5. 2~4 번을 100 만번 반복한다.


select ...
from T
ORDER BY unindexed_column;

두번째로 ORDER BY 만 하는작업은 위의 첫번쨰 예제에서 1~ 3번에 해당하는 작업이 없다.

1. 1~3번 작업(버리는건)이 없으므로 10 건만 SORT 하는것이 아니라 배열에 있는 전체건에 대해서
   SORT 하여 자신의 위치를 찾아서 적재한다.
2. 1번을 100 만번 반복한다.

위의 가설을 증명하기위한 예제가 아래에 있다.
먼저  from 절의 테이블 T 는 어떤 테이블이라도 상관은 없으나 대용량 일수록 차이가 크다.
또한 order by 절의 컬럼은 인덱스에 없어야 한다.(있으면  sort order by 가 되지 않는다.)
그리고 테스트를 위하여 PL/SQL 이 필요하다.

1.먼저 trace 나 10046 이벤트를 활성화 한다.

2. ORDER BY + ROWNUM 조합 테스트

select ...
bulk collect into ...
from   (select * from T ORDER BY unindexed_column)
where ROWNUM <= 10;


3. ORDER BY ONLY 테스트

select ...
bulk collect into ... limit 10
from T
ORDER BY unindexed_column;

4. 2개의 Tkprof 보고서를 비교해보면 아래처럼 실행시간은 물론이고 sort order by 시 메모리 사용량 차이가 엄청난걸 알수 있다.

1) ORDER BY + ROWNUM 보고서
Rows              Row Source Operation
-------    -------------------------------------------------------
      10      COUNT STOPKEY (cr=27065 r=26550 w=0 time=9537102 us)
... 이하생략

2) ORDER BY ONLY 보고서
Rows              Row Source Operation
-------    -------------------------------------------------------
      10      SORT ORDER BY (cr=27065 r=45303 w=31780 time=29061743 us)
... 이하생략


결론 :
첫번째 경우는 건건이 100 만번 테이블을 읽으면서 최대 10건만 SORT 한다.(그나마 버리는건은 SORT 가 없다)
두번째 경우는 건건이 100 만번 테이블을 읽으면서 최대 백만건을 SORT 한다.
이 두가지의 차이는 어떤경우에서든 확연히 들어난다는걸 기억하자.

Reference : Effective Oracle by Design

Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://www.jjanglive.com/hompy/ljh1148 BlogIcon 이재현 2010.12.24 11:18  댓글주소  수정/삭제  댓글쓰기

    안녕하세요..

    한가지 궁금한점이 있습니다.

    ROWNUM 을 사용하면 해당 로우를 픽스 해야만 소트 부하를 감소 시킬수 있다는 건 잘 알겠습니다.

    물로 부분범위 처리도 가능하다는 것도요..

    그런데 ROWNUM으로 해당 로우를픽스 시킬수 없는 경우에는 어떻게 하는게 좋을까요??

    우선 ROWNUM 으로 10건을 가져오고 추가적인 테이블 스켄을 더하는게 효율적인가요??

    아님 그냥 한번 스캔을 해서.. 전체 소트 시키는게 효율적인가요??

    가져오는 양은 그때 그때 달라요.. 아직 2만건 이상은 넘지 않습니다.( 우선 전체로 소트 방식으로 해놓고 나중에 테이타가 많

    아지면 ... 고려 할 상황인것 같아서요 )

    항상 감사하는 맘으로 블로그 구독하는 구독자가...

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.12.25 00:44 신고  댓글주소  수정/삭제

      모든건을 한번에 보고자 할때는 부분범위처리가 불리합니다. 나중에 건수가 많아지면 페이징 처리를 하시는 것이 바람직 합니다.

      즐거운 연말보내세요.
      감사합니다.

업무적으로 볼때 조회화면의 검색조건들의 조합은 참으로 다양하다.
아래의 SQL 을 보면 WHERE 절의 모든 변수(:v_grade , :v_loc , :v_hiredate )에 값이 생략이 가능하다.
즉 모든 변수에 값이 들어올수도 있고 일부만 들어올수도 있고 전체가 안들어 올수도 있다.
where 절의 대부분이  그런조건이라고 가정하면 그런 SQL 들은 튜닝하기가 참 난감하다.
이럴때 당신이라면 어떻게 할것인가?
전통적인 튜닝 방법인 UNION ALL 로 모두 쪼개서 분리 할것인가?
아래의 인덱스 구조와 SQL 을 보자.

EMP 인덱스:
1) PK_EMP ( EMPNO )
2) EMP_IDX1 ( SAL )
3) EMP_IDX2 ( HIREDATE )
4) EMP_IDX3 ( DEPTNO )

DEPT 인덱스 :
1) PK_DEPT ( DEPTNO )
2) DEPT_IDX1 ( LOC )

SALGRADE 인덱스:
1) PK_SALGRADE( GRADE )
2) SALGRADE( HISAL, LOSAL)

1. 다양한 조건검색을 OR 로 처리할경우(원본 SQL)

SELECT a.empno, a.ename, a.job, b.dname, a.sal
  FROM EMP a,
       DEPT b,
       SALGRADE c
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and (c.grade = :v_grade or :v_grade is null) --> 변수에 NULL 대입
   and (b.loc = :v_loc or :v_loc is null)            -->  변수에 NULL 대입
   and (a.hiredate = :v_hiredate or :v_hiredate is null); --> 변수 :v_hiredate 에 '1980-12-17' 값을 대입한다.

물론 위의 SQL 을 아래처럼 나타태도 PLAN 상으로는 같다.

SELECT a.empno, a.ename, a.job, b.dname, a.sal
  FROM EMP a,
       DEPT b,
       SALGRADE c
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and c.grade LIKE :v_grade||'%'  --> 변수에 NULL 대입
   and b.loc LIKE :v_loc||'%'           -->  변수에 NULL 대입
   and a.hiredate LIKE :v_hiredate||'%'; --> 변수 :v_hiredate 에 '1980-12-17' 값을 대입한다.


-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                 |          |      1 |      1 |00:00:00.01 |      21 |
|   2 |   NESTED LOOPS                |          |      1 |      1 |00:00:00.01 |      19 |
|*  3 |    TABLE ACCESS FULL          | SALGRADE |      1 |      5 |00:00:00.01 |       8 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| EMP      |      5 |      1 |00:00:00.01 |      11 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX1 |      5 |     14 |00:00:00.01 |       3 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | DEPT     |      1 |      1 |00:00:00.01 |       2 |
|*  7 |    INDEX UNIQUE SCAN          | PK_DEPT  |      1 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter((:V_GRADE IS NULL OR "C"."GRADE"=TO_NUMBER(:V_GRADE)))
   4 - filter((:V_HIREDATE IS NULL OR "A"."HIREDATE"=TO_DATE(:V_HIREDATE,'YYYY-MM-DD')))
   5 - access("A"."SAL">="C"."LOSAL" AND "A"."SAL"<="C"."HISAL")
   6 - filter(("B"."LOC"=:V_LOC OR :V_LOC IS NULL))
   7 - access("A"."DEPTNO"="B"."DEPTNO")


해석 : 위 PLAN 을 보면   :v_hiredate 에 값이 들어 왔으므로 당연히 EMP_IDX2  인덱스를 먼저
         ACCESS 해야 하지만 엉뚱한 테이블 부터 ACCESS 하여서 비효율이 발생 하였다.
         즉 동적으로 변수값이 들어옴에 따라 PLAN 을 최적화 하지 못한다는 의미이다.
        물론 버젼이 11g 라면 동적으로 최적화 할수 있는 기능(Adaptive Cursor sharing)이 있지만
        항상 그렇게 되는건 아니다.
        그러면 이런 문제를 해결하기위해 어떻게 해야 할까?
        아래의 2~4 번에 해답이 있다.

2.엑세스 형태별로 UNION ALL 로 분리함

  먼저 UNION ALL 로 분리하는 기준은 똑똑한 조건에 먼저 우선순위를 주었다.
  다시말하면 :v_hiredate 는 굉장히 똑똑한 조건이므로 값이 들어오면   :v_grade 나 :v_loc 에 값이
  들어오던 들어오지 않던 대세에 지장이 없다는 의미이다.
  마찬가지 방법으로 :v_hiredate 가 들어오지 않는 상황에서는 두번째로 똑똑한 조건인 :v_grade 에
  값이 들어오면 :v_loc 이 들어오던 들어오지 않던 중요하지 않다는 의미이다.
  따라서 순서는  :v_hiredate --> :v_grade --> :v_loc 로 하였다.
  실행시에 다른변수에는 값을 넣지않고  :v_hiredate 만 '1980-12-17' 값을 대입한다.
 

SELECT ....  --> :v_hiredate 가 들어 왔을때    
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and (c.grade = :v_grade or :v_grade is null)
   and (b.loc = :v_loc or :v_loc is null)
  
and a.hiredate = :v_hiredate and :v_hiredate is not null  
UNION ALL
SELECT .... --> :v_hiredate 가 안들어 오고 :v_grade 가 들어올때
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and c.grade = :v_grade
   and (b.loc = :v_loc or :v_loc is null)
  
and :v_hiredate is null and :v_grade is not null     
UNION ALL  
SELECT .... --> :v_hiredate 가 안들어 오고 :v_grade 가 안들어오고 :v_loc 가 들어올때
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal때
   and c.grade = :v_grade
   and b.loc =:v_loc
  
and :v_hiredate is null and :v_grade is null and :v_loc is not null
UNION ALL  
SELECT .... --> 변수에 아무것도 안들어 왔을때
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and :v_hiredate is null
   and :v_grade is null
   and :v_loc is null  ;
 

---------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  UNION-ALL                       |               |      1 |      1 |00:00:00.05 |       8 |
|*  2 |   FILTER                         |               |      1 |      1 |00:00:00.05 |       8 |
|*  3 |    TABLE ACCESS BY INDEX ROWID   | SALGRADE      |      1 |      1 |00:00:00.05 |       8 |
|   4 |     NESTED LOOPS                 |               |      1 |      3 |00:00:00.08 |       7 |
|   5 |      NESTED LOOPS                |               |      1 |      1 |00:00:00.04 |       5 |
|   6 |       TABLE ACCESS BY INDEX ROWID| EMP           |      1 |      1 |00:00:00.02 |       3 |
|*  7 |        INDEX RANGE SCAN          | EMP_IDX2      |      1 |      1 |00:00:00.01 |       2 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| DEPT          |      1 |      1 |00:00:00.02 |       2 |
|*  9 |        INDEX UNIQUE SCAN         | PK_DEPT       |      1 |      1 |00:00:00.01 |       1 |
|* 10 |      INDEX RANGE SCAN            | SALGRADE_IDX1 |      1 |      1 |00:00:00.01 |       2 |
|* 11 |   FILTER                         |               |      1 |      0 |00:00:00.01 |       0 |
|  12 |    NESTED LOOPS                  |               |      0 |      0 |00:00:00.01 |       0 |
|  13 |     NESTED LOOPS                 |               |      0 |      0 |00:00:00.01 |       0 |
|  14 |      TABLE ACCESS BY INDEX ROWID | SALGRADE      |      0 |      0 |00:00:00.01 |       0 |
|* 15 |       INDEX RANGE SCAN           | PK_SALGRADE   |      0 |      0 |00:00:00.01 |       0 |
|  16 |      TABLE ACCESS BY INDEX ROWID | EMP           |      0 |      0 |00:00:00.01 |       0 |
|* 17 |       INDEX RANGE SCAN           | EMP_IDX1      |      0 |      0 |00:00:00.01 |       0 |
|* 18 |     TABLE ACCESS BY INDEX ROWID  | DEPT          |      0 |      0 |00:00:00.01 |       0 |
|* 19 |      INDEX UNIQUE SCAN           | PK_DEPT       |      0 |      0 |00:00:00.01 |       0 |
|* 20 |   FILTER                         |               |      1 |      0 |00:00:00.01 |       0 |
|* 21 |    TABLE ACCESS BY INDEX ROWID   | SALGRADE      |      0 |      0 |00:00:00.01 |       0 |
|  22 |     NESTED LOOPS                 |               |      0 |      0 |00:00:00.01 |       0 |
|  23 |      NESTED LOOPS                |               |      0 |      0 |00:00:00.01 |       0 |
|  24 |       TABLE ACCESS BY INDEX ROWID| DEPT          |      0 |      0 |00:00:00.01 |       0 |
|* 25 |        INDEX RANGE SCAN          | DEPT_IDX1     |      0 |      0 |00:00:00.01 |       0 |
|  26 |       TABLE ACCESS BY INDEX ROWID| EMP           |      0 |      0 |00:00:00.01 |       0 |
|* 27 |        INDEX RANGE SCAN          | EMP_IDX3      |      0 |      0 |00:00:00.01 |       0 |
|* 28 |      INDEX RANGE SCAN            | PK_SALGRADE   |      0 |      0 |00:00:00.01 |       0 |
|* 29 |   FILTER                         |               |      1 |      0 |00:00:00.01 |       0 |
|  30 |    MERGE JOIN                    |               |      0 |      0 |00:00:00.01 |       0 |
|  31 |     SORT JOIN                    |               |      0 |      0 |00:00:00.01 |       0 |
|* 32 |      HASH JOIN                   |               |      0 |      0 |00:00:00.01 |       0 |
|  33 |       TABLE ACCESS FULL          | DEPT          |      0 |      0 |00:00:00.01 |       0 |
|  34 |       TABLE ACCESS FULL          | EMP           |      0 |      0 |00:00:00.01 |       0 |
|* 35 |     FILTER                       |               |      0 |      0 |00:00:00.01 |       0 |
|* 36 |      SORT JOIN                   |               |      0 |      0 |00:00:00.01 |       0 |
|  37 |       INDEX FULL SCAN            | SALGRADE_IDX1 |      0 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------

Predicate Information (지면관계상 생략)

해석 : PLAN 이 개발자가 의도한대로 분리되었고 A-Rows 와 Buffers 를 보면 분리된 SQL 중에서 첫번째
       SQL 만 값이 있다.
       하지만 최적의 SQL 이 되려면 아직도 멀었다.

3.UNION ALL 로 분리된 각각의 SQL 최적화

:v_grade 에 값이 들어오지 않는다면 더이상 SALGRADE 테이블은 필요가 없다.
과감히 FROM 절에서 삭제하자.
물론 a.sal 컬럼의 값에 NULL 이 있다면 답이 달라지므로 주의해야 한다.

SELECT ....
FROM   EMP a,
       DEPT b,
       SALGRADE c
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and (c.grade = :v_grade or :v_grade is null)
   and (b.loc = :v_loc or :v_loc is null)
   and a.hiredate = :v_hiredate
   and :v_hiredate is not null
UNION ALL
SELECT ....
  FROM EMP a,
       DEPT b,
       SALGRADE c
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and c.grade = :v_grade
   and (b.loc = :v_loc or :v_loc is null)
   and :v_hiredate is null
   and :v_grade is not null
UNION ALL  
SELECT ....
 
FROM EMP a,
       DEPT b             -->
SALGRADE 테이블은 필요가 없음
 WHERE a.deptno = b.deptno
   and b.loc =:v_loc
   and :v_hiredate is null
   and :v_grade is null
   and :v_loc is not null
UNION ALL  
SELECT ....
 
FROM EMP a,
       DEPT b            -->
SALGRADE 테이블은 필요가 없음
 WHERE a.deptno = b.deptno
   and :v_hiredate is null
   and :v_grade is null
   and :v_loc is null  ;

PLAN 정보 및 Predicate Information (지면관계상 생략)


4.NVL 혹은 DECODE 함수의 활용

UNION ALL 로 분리하면 옵티마이져 입장에서는 환영할 일이지만 개발자 입장에서 보면 반복적인 코딩이 증가하고 유지보수시 일량이 늘어나는 단점이 있다.
그렇다면 코딩량을 줄일수 있는 최적의 방법은 없는것일까?

물론 방법이 있다.
아래의 SQL 을 보자.
아래의 SQL 은 UNION ALL로 분리된 SQL 중에서 마지막 2개의 SQL 을 합친 것이다.

SELECT a.empno, a.ename, a.job, b.dname, a.sal
  FROM EMP a,
       DEPT b
 WHERE a.deptno = b.deptno
   and b.loc = decode(:v_loc, null,  b.loc, :v_loc) --:V_LOC 에 'CHICAGO' 대입
   and :v_hiredate is null
   and :v_grade is null
   and :v_loc is not null

----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   1 |  CONCATENATION                  |           |      1 |      6 |00:00:00.03 |       7 |
|*  2 |   FILTER                        |           |      1 |      0 |00:00:00.01 |       0 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | EMP       |      0 |      0 |00:00:00.01 |       0 |
|   4 |     NESTED LOOPS                |           |      0 |      0 |00:00:00.01 |       0 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT      |      0 |      0 |00:00:00.01 |       0 |
|*  6 |       INDEX FULL SCAN           | DEPT_IDX1 |      0 |      0 |00:00:00.01 |       0 |
|*  7 |      INDEX RANGE SCAN           | EMP_IDX3  |      0 |      0 |00:00:00.01 |       0 |
|*  8 |   FILTER                        |           |      1 |      6 |00:00:00.03 |       7 |
|   9 |    TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      6 |00:00:00.03 |       7 |
|  10 |     NESTED LOOPS                |           |      1 |      8 |00:00:00.15 |       5 |
|  11 |      TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.0