SQL 포기하지마라. 라는 글에서 Model 절을 언급한바 있다.
독자중에 좀더 자세히 알고 싶어 하는 분들이 있어서 개인적으로 정리한 자료를 올리게 되었다.
하지만 배포시에는 출처를 꼭 밝혀주기 바란다.
아래파일은 upload 시에 200 KB 한계가 있으므로 3개로 나누어 올리지만
3개를 모두 다운로드 받아 압축을 풀면 하나의 파일로 생성되므로 문제가 없을 것이다.

사용자 삽입 이미지











신고

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

간단한 집합개념 Test  (7) 2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.  (7) 2009.06.29
Model 절에 대하여  (10) 2009.05.28
SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
응용력 발휘하기  (2) 2009.03.20
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.05.29 09:07 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 감사드립니다 ^^

  2. 백면서생 2009.05.29 09:40 신고  댓글주소  수정/삭제  댓글쓰기

    자료 공유 감사드립니다.

  3. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.05.29 19:09 신고  댓글주소  수정/삭제  댓글쓰기

    모두들 즐공 하세요.^^

  4. 2009.06.07 08:40  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  5. Favicon of http://sleeperbus.net BlogIcon sleeperbus 2009.06.08 08:29 신고  댓글주소  수정/삭제  댓글쓰기

    Model query는 대충 훑어봤다가 쓸 일이 없어서 지나쳤는데 다시 한 번 돌아보게 된 기회가 됐네요. 자료 감사합니다. ^^

  6. 혈기린 2009.07.06 15:31 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 감사드립니다 ^^
    많은걸 배우고 가네요

  7. redcar 2009.07.07 15:50 신고  댓글주소  수정/삭제  댓글쓰기

    감사합니다.

  8. 2010.05.28 16:20 신고  댓글주소  수정/삭제  댓글쓰기

    예제에 DIMENSION BY 구절의 컬럼순서와 RELES에 정의된 순서가 동일해야 하네요
    PDF상에는 순서가 달라서 셀 참조 술어에 잘못된 차원이 있음 이라는 메세지가 나오네요
    다 아시겠지만 그냥 참고하세요 ㅎㅎ 저는 지금 처음해봐서요 ^^
    자료 감사합니다.

SQL 은 방정식을 풀기에 적합한 언어인가?
  우연히 방정식을 푸는 요건이 있었는데 상황은 아래와 같았다.
요건은 "정수 X 를 정수 Y 로 계속 나누되 N 번 나눈 결과와 N-1 번 나눈 결과의 차이가 0.25 보다 작으면
멈추고 그때까지의  나눈값과 LOOP 횟수( N )를 출력하라" 였다. 물론 X 와 Y 는 변수이다.
이해를 쉽게 하기 위하여 왼쪽의 그림을 보자.

사용자 삽입 이미지
왼쪽의 그림을 설명하면  50을 2 로 8번 나누면 0.1953 인데 이것을 7번 나눈값으로 빼면 결과는 0.25 이하이다.

따라서 원하는 출력값은 0.1953 과 8 번(LOOP 횟수) 인 것이다.

이요건을 들은 개발자들은 저마다 자바나 PL/SQL 로 개발 하면 된다고 생각했다.

하지만 이기능을 요구한 사람의 말에 따르면 SELECT 쿼리결과로 나오는 것이 가장 좋다.

하지만 불가능하면 자바나 PL/SQL 등으로 구현해도 된다는 것이었다.

여러분이 만약 이러한 요구사항을 해결해야 한다면?

퀴즈라고 생각하고 머릿속에서 해법을 생각해보라.


포기하지마라
  사실 필자는 이문제가 성능이슈가 아니었고 SQL을 제법 잘사용하는 개발자들과 DBA 가 회의에 참석하였으므로 직접 나서지 않았는데 한마디 말 때문에 흥분하기 시작했다.

개발자1 : 이것은 SQL 로는 안됩니다. Loop 도 돌려야 하고 Loop 내에서 빠져나가는 로직도 추가해야 되기 때문입니다.
필자 : SQL 로 하는것을 포기 하지마십시오.
개발자1 : 네?
개발자2 : 5분이면 PL/SQL 함수로 구현이 가능할거 같습니다.
             그함수를 SQL 에서 call 하면 됩니다. 괜히 고생하지 마시죠.
필자 : 5분이라구요?....그렇다면 두분이서 작성한 코드를 10분뒤에 저에게 보여주세요.

  이렇게 해서 회의가 잠시중단이 되었다.
사실 필자는 자주 흥분하는편이 아닌데 언제부터인지 SQL 로는 불가능하며 비효율적이라는 소리를 들을때면 이상하게 자극이 된다. 어쩔수 없는 엔지니어 출신의 비애인것 같다.

It's Time to overhaul!
시간이 되어 개발자들이 작성한 코드를 검토해 보았다.

CREATE OR REPLACE function sf_calc(v_value number, v_division number)
return varchar2 as
   v_iteration pls_integer := 0;
   v_result number;
   v_result_before number;
BEGIN
   v_result := v_value;
   FOR v_counter IN 1..999999999 LOOP
  
      v_result := v_result / v_division;
      v_iteration := v_counter;
      EXIT WHEN v_iteration > 1 and   v_result_before  - v_result  <= 0.25;
      v_result_before = v_result ;
   END LOOP;
   return '결과 : ' ||to_char(v_result) || ' , '||
            'Loop 횟수 : '|| to_char(v_iteration);
END;

위의 함수는 약간 손볼곳이 있지만 그래도 결과에 충실하다. 위의 함수를 SQL 에서 사용하면 아래와 같다.

select sf_calc(50, 2) from dual;

결과 : .1953125  , Loop 횟수 : 8

  함수의 길이도 길지않고 이정도면 괜찮다고 말할수 있다. 하지만 또다른 비효율이 숨어 있었다.
결과를 comma (,) 기준으로 잘라서 2 컬럼으로 보여줘야 하는 문제가 여전히 남는다.
그렇다면 필자가 작성한 아래의 SQL 로 바꾸는 것은 어떤가?

select S as "결과", N + 1 as "Loop 횟수" From DUAL
model
dimension by (1 X)
measures ( :v_value S, 0 N)                                                    --> :v_value 에 50 대입
rules iterate (999999999) Until (PREVIOUS(S[1])-S[1]<=0.25)
( S[1] = S[1]/ :v_division,                                                      --> ::v_division 에 2 대입
  N[1] = ITERATION_NUMBER );  


결과              Loop 횟수
----------- -----------
   0.1953125                 8

Model 절 문법이 어렵다고 들었다.
  결과가 2컬럼으로 분리되어 정확히 나왔다. 코딩의 시간은 단 1분 이며 코드의 길이는 어떤가? 이정도면 충분히 짧다.
개발자들이 유지보수가 어렵다는 문제를 들고 나왔다. 1분만 투자하면 문법을 익힐수 있음에도 문법이 어렵다는 것은 미신이다.
현재 오라클이 11g 까지 나왔지만 각각의 문법들은 몇분만 투자하면 다 알수 있는 정도이다.
물론 성능면에서 신기능의 실행계획을 분석하는것은 다른 이야기 이다.
간단히 문법을 설명하면 아래와 같다.

1. rules iterate(999999999) 라는 것은 괄호 안의 작업(S[1] = ... 이후생략)을 999999999 번 반복해서 실행한다의 의미이다.
2. Until 이라는것은 Loop 를 빠져나오기 위한 조건을 지정 하는것이다.
3. PREVIOUS(N) 라는 것은 N-1 번째의 값을 의미한다. 따라서 PREVIOUS(S[1])-S[1]<=0.25 의 의미는
   "N -1 번째 :v_value 값에서 N 번째 :v_value 값을 뺀 차이가 0.25 이하일때" 인 것이다.
4. S[1] 의 값은  최초에는 :v_value 와 같지만  S[1]/ :v_division 로 계산된 값을 Loop 시마다 대입하므로 값이 계속 바뀐다.
5. N[1] 의 값은 Loop 시마다 1 증가한다. 예약어 ITERATION_NUMBER 의 기능때문이다.
    하지만 최초의 값이 0부터 시작되므로 select 시에 1을 더해서 보여줘야 한다.
6. measures ( :v_value S, 0 N) 라는 표현은 단순히 S[1] 과 N[1] 을 사용하기 위하여 단순히 alias 를 정의한것에 불과하다.


주의사항 :
  여기서 dimension by 는 아무런 뜻이 없다. 하지만 숫자 1 대신에 다른값을 사용할 경우 SQL 이 종료되지 않으므로 주의해야 한다.

Model 절로 무엇을 할수 있나?
 Model 절과 관련된 미신중의 하나는 Time series, 수요예측, 판매예측, 기상예측 등등 주로 이전실적에 여러가지 factor 를 추가하여 미래를 예측하는데 사용하거나 SQL 에서 축이동(Pivot/Unpivot) 등의 용도로만 사용된다고 알고 있다는 것이다. 하지만 위의 SQL 은 이것들중 어디에도 포함되지 않는다. 다시말하면 복잡한 연립방정식을 푸는데 제일좋은 것이 Model 절이다.  
       
결론:
  MODEL 절은 오래전(6년전)에 나온 SQL 문법이지만 아직 늦지 않았다. 필자가 확인한 결과 아직도 모르는 사람들이 많이 있으므로 지금 익힌다면 선구자가 될것이다. SQL 은 여러분이 생각하는것보다 훨씬더 강력하고 효율적임을 알수있다. 위의 SQL과 같이 간단하지만 강력한 기능들이 많이 준비되어 있다. 예를들면 X-Query, Regular Expression, Pivot/UnPivot 등이 그것이다. 무기의 종류(SQL)를 여러개 준비한 사람과 기관총 하나만 가지고 있는 사람의 차이는 어마어마 하다. 준비된 사람은 요구조건에 따라서 단검을 사용할것인지 탱크를 사용할것인지 그것도 아니라면 F16 전투기를 사용할것인지 상황에 따라서 맞는 무기를 선택할수 있다.  SQL 공부 여기서 포기할것인가?

신고

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

스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.  (7) 2009.06.29
Model 절에 대하여  (10) 2009.05.28
SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Posted by extremedb

댓글을 달아 주세요

  1. 유수익 2009.05.28 17:43 신고  댓글주소  수정/삭제  댓글쓰기

    아. 그런 기능이 있었네요.
    dimension by 는 의미가 없다고 하셨는데 왜 쓰나요?
    S[1],N[1]에서 1의 의미는 무었인가요?
    좀 더 자세히 알수 있을까요? 재미 있는 방법이네요..

    감사드립니다.

  2. Favicon of http://forwhite.tistory.com BlogIcon forwhite 2009.07.08 15:24 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽었습니다.
    모델의 활용이라..견문이 더 넓어졌네요.

    감사드립니다.

  3. 2010.05.28 15:42 신고  댓글주소  수정/삭제  댓글쓰기

    이런 기능이 있는지 조차 모르던 1인에게 공부하게 기회를 주시는군요

    감사합니다.

  4. eqon 2012.02.15 15:20 신고  댓글주소  수정/삭제  댓글쓰기

    해보니까
    6번 루프에서 값이 0.78125가 나오네요.

    혹시 오타가 아니신지 0.71825 --> 0.78125

    Model query.pdf 파일에도 똑같이 되어 있네요.

  대부분의 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 http://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 http://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 http://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 만이 완벽한 방법입니다.

어느 회식 자리에서
  오늘(5월 18일) 어느 사이트의 회식자리에서 프로젝트의 PM 옆자리에 앉게 되었다.
나중에 알고보니 그 PM 은 술자리 기피대상 1호 였는데 이유는 "공장"(?) 이야기를 너무 많이 한다는 것이었다.
필자는 운이 좋게도(?) 자리가 그사람 옆이라 공장 이야기를 많이 들을수 있었다.

PM : 요즘 배치프로그램을 보면 영화 매트릭스의 스미스(악당 요원)가 생각이 나네요.

필자 : 왜 그렇게 생각하세요?

PM:  해도 해도 너무해요. 예전에는 년배치, 분기배치, 월배치, 일배치 가 배치의 전부 였지요.
        하지만 90년대 후반부터 하루에 3~4번 도는 배치가 생기기 시작했습니다.
        이제는 시간당 한번 실행되는 배치도 성에 안차나 봅니다.
        심지어 최근에는 Near Real Time 배치(1~10분 주기)가 많아져 온라인 프로그램과 연관된 lock 문제가
        많이 발생하고 있습니다.
        Near Real Time 배치는 처리건수가 많지는 않지만 select for update를 사용하는 경우가 많은데
        바로 이것이 문제 입니다.

그게 스미스랑 무슨 상관이지?
  그 PM 은 우리가 물어보기도 전에 배치프로그램과 스미스 요원과의 연관성을 설명 하였다.
그의 주장은 영화 메트릭스 1~ 3편을 보면 스미스가 지속적으로 upgrade 되는 것을 볼수 있는데 그것은 아래의 3가지로 설명이 가능하다는 것이다..
1편에서의 돌연변이 --> 매트릭스의 아버지격인 컴퓨터 아키텍쳐의 통제에서 벗어난 행동을 한다.
                                 이것은 하루에 N 번 도는 배치에 해당한다.

2편에서의 복제기능 --> 다른사람을 스미스로 복제 해버린다.
                                 이것은 시간당 한번 실행되는 배치에 해당한다.

3편에서의 SuperMan --> 자유자재로 날아다니는 것은 물론이고 드디어 매트릭스를 벗어나 인간세계(Zion) 에 까지 영향을 미친다. 이것은 Near Real Time 배치에 해당하며 드디어 온라인 프로그램과의 충돌을 의미 한다는 것이다.

  우리는 그 PM의 이야기를 듯고 한동안 웃음바다가 되었다. 아주 멋진 비유이다.
필자가 생각해보아도 요즘은 Near Real Time 배치가 대세가 되었다.
사람의 욕심은 끝이 없어서 각종 하드웨어와 소프트웨어의 성능이 좋아질수록 거의 실시간으로 배치가 실행되고 배치의 결과물도 아주 짧은 시간에 나오기를 원하는 것이다. 그러다 보면 온라인 프로그램과의 충돌이 자주 일어날수 밖에 없다.

최대의 실수
  아래의 짧은 이야기가 오늘 최대실수가 될줄이야....
필자 : "하지만 스미스가 있으면 The One(Neo) 도 있습니다."
PM : "네? 무슨 말인지 설명해 주시죠."
허걱 술자리에서.. 칠판이나 SQL PLUS 등도 없는데 설명을 해야만 하다니....
드디어 필자가 그 PM 의 마법에 걸려들고야 말았다. 

아래는 예제는 필자의 이야기를 증명한것이다. 물론 회식 자리에서는 전부 말로 설명 하였다.

정말 배치프로그램은 무한정 기다려야 하나?
  배치프로그램에서 최초의 SQL은 Select For Update 문이고 이것은 온라인 프로그램의 Update 문과 같은 테이블을 사용한다고 가정하자. 또한 온라인 UPDATE 의 WHERE 절이 배치 SQL 의 WHERE 절에 속하거나 걸쳐 있다면 동시에 온라인과 배치가 수행될수 없는 구조이다. 
혹자는 SELECT FOR UPDATE 에 WAIT N 이나 NOWAIT 옵션을 주면 되지 않냐고 반문할지 모른다. 물론 그렇게 하면 무작정 기다리지는 않는다. 하지만 온라인 프로그램의 UPDATE 문이 3 초안에 COMMIT이나 ROLLBACK 되지 않으면 Select for Update wait 문에서 아래의 예제처럼 에러가 발생한다.

SESSION 1 :
SQL> UPDATE EMP
  2    SET SAL = SAL * 1.1
  3  WHERE EMPNO = 7566; --> 1건 UPDATE, 7566 번은 부서번호가 20번임에 유의하자.

1 row updated
...SESSION 1 에서  다른 작업(다른 SQL)이 진행중이며 아직 COMMIT 이 안된 상태임 ...

SESSION 2 : --> SESSION 1 에서 COMMIT 안된 상태에서 수행됨.
SQL> SELECT * FROM EMP
  2  WHERE DEPTNO = 20   --> 부서번호 20번 전체 SELECT
  3  FOR UPDATE WAIT 3; --> 3초간 기다린후에 에러가 발생됨.

ORA-30006: 리소스 사용 중. WAIT 시간 초과로 획득이 만료됨.


위의 에러는 해당 사이트에서 자주 발생 된다고 한다.

해결책 : The One IS SKIP LOCKED Option
  이렇게 에러가 발생하게 되면 배치프로그램의 EXCEPTION 이 매우 지저분 하게 된다.
EXCEPTION 에서 특별 처리를 하거나 아니면 다음주기(1~10분)을 기다렸다가 다시 시도 해야 되는데 이때에도 온라인 프로그램이 UPDATE 하고 있지 않기를 바라는수 밖에 없다.
너무나 억울하지 않은가?
다른세션에서 EMP 테이블을 1건 UPDATE 했을뿐인데 부서번호 20번에 해당하는 전체건에 대하여
SELECT FOR UPDATE 를 할수 없는것이다.

그렇다면 이문제를 어떻게 해결할수 있겠는가?
ORACLE 11g 를 사용한다면 SKIP LOCKED 옵션을 사용할수 있다.

SESSION 2 :
SQL> SELECT * FROM EMP
  2  WHERE DEPTNO = 20
  3  FOR UPDATE SKIP LOCKED;

--> What a option! U can select for update entire row except the row which has been aready locked!

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7788 SCOTT      ANALYST    7566 1987-04-19    3000.00               20
 7876 ADAMS      CLERK      7788 1987-05-23    1100.00               20
 7902 FORD       ANALYST    7566 1981-12-03    3000.00               20

     
  환상적이지 않은가?
LOCK 이걸린 7566 을 제외한 20번 부서 전체건에 대하여 성공적으로 LOCK 을 발생시켰다.
이기능을 2003 년에 발견하였지만 undocumnted 기능으로 분류되어 있었으므로 그때는 명시적으로 이기능을 사용하지 않았다. 하지만 여러분이 10g 에서 AQ(Advanced Queuing) 기능을 사용하고 있고 dequeue 옵션으로 no_wait 을 사용했다면 여러분의 의지와는 상관없이 오라클은 SKIP LOCKED 옵션을 사용한것이다.

결론 :
11g 에서 드디어 SKIP LOCKED 이 11g 매뉴얼에 모습을 드러내었다.
이것은 대단한 Upgrade 이다. 특히 아래와 같이 천건 혹은 만건씩 처리되는 상주(Near Real Time)배치 SQL 에서 말이다.

SELECT *
  FROM TAB1
 WHERE 배치처리완료 = 'N'
   AND ROWNUM <= 1000
FOR UPDATE SKIP LOCKED;

온라인 프로그램에서 1건이 UPDATE 되었다고 해도 나머지 999 건은 처리가 될것이므로 ...

주의사항 : 9i 및 10g 에서 명시적으로 SKIP LOCKED 옵션을 사용하면 안된다.
               기능을 숨겨 놓은데는 다 이유가 있다. (NEO 가 1편부터 날라다닌것은 아니다.)
               술자리에서는 공장 이야기를 하지말자. NEO 가 있다는 것을 증명하기가 너무 힘들다...^^

신고

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

Model 절에 대하여  (10) 2009.05.28
SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.05.19 09:33 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다..

    스미스와 네오를 비유하실줄은 몰랐네요..
    아주 재미있게 보았습니다 ^^

  2. Favicon of http://blog.naver.com/hheungsu BlogIcon 돌칼 2009.09.02 15:14 신고  댓글주소  수정/삭제  댓글쓰기

    skip locked 유용한데요?

    10g 10.0.2.4에서도 되네요. 저도 개발쪽에서 문의가 들어와서 확인하다가 들렀습니다.

    select for update 재밌는 문장입니다. 위에 쓰신글도 재밌게 잘 읽었습니다.

    퍼갑니다. 감사해요~~

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.02 15:59 신고  댓글주소  수정/삭제

      반갑습니다.
      10.2.0.4 에서는 위의 옵션을 사용하는것은 위험합니다.
      공식적인 옵션이 아니므로 버그등이 있을경우 오라클에서 책임지지 않으므로 사용한 DBA 나 개발자가 책임 져야 하겠죠. 11g 에서는 옵션이 공식적인 메뉴얼에 나옵니다.
      참고하세요.

  한 선지자에 의하여 9i/10g 그리고 11g 에서 Nested Loop Join 수행시 buffer pinning 효과에 의한 성능향상이 증명된바 있다.
물론 그글은 명불허전 이다.(해당글 링크:http://ukja.tistory.com/166)
   이미 밝혀진 원리에 대해서 증명이나 검증하는것은 더이상 과학이 아니다. 그리고 재미도 없다. 따라서 오늘은
buffer pinning 에 의한 성능개선이 아닌 또다른 성능개선에 대한 2가지 원리 대해서 연구해보자.

  이제부터 버젼별로 변경된 Nested Loop Join 의 실행계획과 그에 따르는 원리에 대하여 알아보려 한다. 모든 예제는 오라클 설치시 자동으로 설치되는 SH Schema의 customers 테이블과 sales 테이블을 이용한다.
먼저 인덱스를 하나 만든다.

drop index sh.sales_cust_ix;
create index sh.sales_cust_ix on sh.sales(cust_id);


 이어서 buffer cache 를 비우고 SQL 을 실행한다.
고객 테이블을 full scan 하고 sales 테이블과 Nested Loop Join 을 수행한다.

alter system flush buffer_cache;

select /*+ gather_plan_statistics */ count(*)
  from (select /*+ no_merge full(c) use_nl(c s) */
               s.cust_id,
               s.time_id,
               c.cust_year_of_birth
         from  sh.customers c,
               sh.sales s
         where c.cust_id  = s.cust_id  
           and c.cust_year_of_birth between 1960 and 1980
           and s.channel_id  between 2 and 3
           and s.prod_id < 18); 
 
select * from
table(dbms_xplan.display_cursor(null,null, 'allstats last -rows +outline -predicate' ));

Oracle 8i Plan --> just normal
------------------------------------------------
| Id  | Operation                              |
---------------------------------------------- |
|   1 |  SORT AGGREGATE                        |
|   2 |   VIEW                                 |
|   3 |     NESTED LOOPS                       |
|   4 |      TABLE ACCESS FULL                 |
|   5        TABLE ACCESS BY GLOBAL INDEX ROWID|
|   6 |        INDEX RANGE SCAN                |
------------------------------------------------

특별 할것 없는 전통적인 Nested Loop Join 이다.
이제 9i 및 10g 의 plan 을 보자.

9i & 10g Plan --> table prefetch
-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |               |      1 |00:00:05.67 |     245K|   1454 |
|   2 |   VIEW                               |               |   8269 |00:00:05.66 |     245K|   1454 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:05.62 |     245K|   1454 |
|   4 |     NESTED LOOPS                     |               |    327K|00:00:02.83 |   41304 |   1454 |
|   5 |      TABLE ACCESS FULL               | CUSTOMERS     |  20010 |00:00:00.12 |    1457 |   1454 |
|   6 |      INDEX RANGE SCAN                | SALES_CUST_IX |    307K|00:00:00.88 |   39847 |      0 |
-------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_PREFETCH(@"SEL$2" "S"@"SEL$2")
      END_OUTLINE_DATA
  */
 

Oracle 9i 에서 table prefetch 기능이 나오다.
  Inner(후행) 테이블의 위치가 Nested Loop Join 위로 올라가 버렸다. 그리고 오라클이 내부적으로
NLJ_PREFETCH 힌트를 사용하였다. 이것은 어떤 의미를 가지고 있을까?
이러한 현상에 대한 원리는 single block I/O request 에 의한 physical read 시 block 을 prefetch(미리 읽는 작업) 한다는데 있다.
여기서 physical read 란 buffer cache 에 데이터가 없어서 disk 에서 데이터를 read 하는것을 의미한다.
어차피 scan할 data 이므로 미리 엑세스할 물리적 주소를 여러개(운반단위) 모은다음 한번에 read 햐여 buffer cache 에 올리게 되는것이다. 여기서 주의할점은 multi block I/O 를 하는것이 아니라 single block I/O 여러개(운반단위만큼)가 동시에 진행된다는 것이다. 이것을 Vector IO 라고 부른다.(Batch IO 라고도 함)  바로 여기에 성능개선 효과가 있는것이다. 이기능에 의해서 rowid 에 의한 테이블 access 는 8i 에 비해서 상당한 개선 효과가 있는 것이다.(Operation ID 로는 3번이 여기 해당된다.)

이것을 증명하기 위해 v$sesstat 에서 SQL 수행전과 수행후의 value 증가분을 비교해보면 아래와 같다.

NAME                                           DIFF
---------------------------------------- ----------
undo change vector size                        2840
physical read IO requests                      3812
... 중간생략                                      
physical reads cache prefetch                  1344


위에서 보는것과 같이 table prefetch 가 발생하였다. 위의 테스트는 11g 에서 수행된것인데 9i 의 살행계획과 실행통계도 10g 와 대동소이 하다. 11g 에서 이전 버젼(9i/10g) 번젼의 plan 을 나타나게 하려면 NO_NLJ_BATCHING(테이블명) 힌트를 사용하면 된다.  9i 나 10g 에서의 후행 테이블 prefetch에 의한 성능 개선효과는 11g 에 와서야 완벽한 모습을 갖추게 된다.

11g Plan --> Index Vector I/O
------------------------------------------------------------------------------------------------------
|Id  | Operation                             | Name          | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------
|  1 |  SORT AGGREGATE                       |               |      1 |00:00:04.82 |     245K|   1454|
|  2 |   VIEW                                |               |   8269 |00:00:04.81 |     245K|   1454|
|  3 |    NESTED LOOPS                       |               |   8269 |00:00:04.79 |     245K|   1454|
|  4 |     NESTED LOOPS                      |               |    307K|00:00:01.56 |   41304 |   1454|
|  5 |      TABLE ACCESS FULL                | CUSTOMERS     |  20010 |00:00:00.08 |    1457 |   1454|
|  6 |      INDEX RANGE SCAN                 | SALES_CUST_IX |    307K|00:00:00.47 |   39847 |      0|
|  7 |     TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:01.93 |     203K|      0|
------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_BATCHING(@"SEL$2"
"S"@"SEL$2")
      END_OUTLINE_DATA
  */

궁하면 통한다.
이상하지 않은가? 테이블이 2개 인데 Nested Loop Join 이 하나가 아닌 2개가 되어버렸다. 또한 NLJ_PREFETCH 힌트가 사라지고 NLJ_BATCHING 힌트로 대체 되었다.
이러한 현상이 의미하는 바는 무엇일까?
9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
드디어 11g 에서 index scan 시 Vector IO 가 가능해졌다. 궁하면 통한다고 했던가? 오라클이 Nested Loop Join 에 대하여 지속적으로 개선해왔다는것을 알수있다.
참고로 NO_NLJ_BATCHING 힌트를 사용하면 9i/10g 의 Plan 으로 돌아가게 된다.

그러면 11g 의 버젼에서 v$sesstat 통계를 보자.

NAME                                           DIFF
---------------------------------------- ----------
Batched IO vector block count                  3758
Batched IO vector read count                     50
... 이후 생략

위에서 보듯이 Batched IO 란것이 생겼다.
Batched IO (혹은 Vector IO) 기능에 힘입어 table prefetch 에 이어서 11g 에서는 index scan 의 성능까지 향상되었다.

주의사항 : 위에서 수행한 모든 테스트는 Physical read 시에만 해당된다. 위의 예제 스크립트에 buffer cache 를 flush 한 이유도 여기에 있다.

결론:
  오라클 9i, 10g 및 11g 에서 개선된 Nested Loop Join 의 원리는 다음과 같다.
첫번째는 9I/10g 에서 후행 테이블의 TABLE ACCESS BY INDEX ROWID Operation 작업속도가 개선되었다는것과 두번째로 11g 에서 후행 테이블의 인덱스 scan 속도까지 획기적으로 개선되었다는 것이다. 이것은 table prefetch 기능과 Vector I/O 기능에 의해서 각각 구현 되었다. 이기능들과 별도로 이글의 서두에서 이야기한 buffer pinning 기능까지 덤으로 따라오게 되었다.

  앞으로 11g 를 사용시 과거처럼 Batch 용 SQL 에서 무조건 hash 조인을 남발하지 말았으면 한다.
조인건수가 많지 않고 후행 테이블에 적당한 인덱스가 있을 경우에 최소한 Nested Loop Join 과 성능비교를 해보아야 하지않을까?

<편집후기 : 위 테스트는 11g 에서 테스트 되었음을 다시한번 밝혀둡니다.>

신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.05.09 18:14 신고  댓글주소  수정/삭제  댓글쓰기

    테스트하실 때 10g의 정확한 하위 버전이 어떻게 되나요? 그 버전부터 NLJ_PREFETCH Hint와 NO_NLJ_PREFETCH Hint가 추가되었군요!

    11g에서는 NLJ_BATCHING, NO_NLJ_BATCHING Hint가 추가된 것 같습니다. Nested Loop Join과 관련된 동작 방식을 이제 거의 완벽하게 Hint로 제어할 수 있게 되었군요.

    11g에서 V$SQL_HINT view를 보면 아래와 같이 Hint가 등록되어 있네요.

    NAME INVERSE VERSION
    -------------------- -------------------- --------------------
    NLJ_BATCHING NO_NLJ_BATCHING 11.1.0.6
    NO_NLJ_BATCHING NLJ_BATCHING 11.1.0.6

    NAME INVERSE VERSION
    -------------------- -------------------- --------------------
    NLJ_PREFETCH NO_NLJ_PREFETCH 11.1.0.6
    NO_NLJ_PREFETCH NLJ_PREFETCH 11.1.0.6

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.05.11 09:59 신고  댓글주소  수정/삭제  댓글쓰기

    오타에 의해서 10g 에서 수행되었다고 적었네요.
    수정 하도록 하겟습니다.
    NLJ_BATCHING , NLJ_PREFETCH 힌트등은 11g 에서만 사용하실수 있습니다.
    역 힌트도 마찬 가지 입니다.
    또한 11g 에서도 NO_NLJ_BATCHING, NO_NLJ_PREFETCH 힌트를 같이사용하면 작동을 하지 않는것이 발견 되었습니다.
    따라서 11g 에서도 9i~11g 에서 발전된 모든 기능을 해제 하려면 파라미터를 사용하는수 밖에 없습니다.

  3. 바우 2009.05.11 18:00 신고  댓글주소  수정/삭제  댓글쓰기

    올려주신 글 잘 읽어보고 있습니다. 이번 글에서 의문점이 생겨서 문의를 드립니다.

    9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
    이 부분이 잘 이해가 안됩니다.

    실행계획에서 보면 먼저 테이블 FULL로 읽고 인덱스 RANGE SCAN할때 여러개를 묶어서 하는 것이라고 이해가 되었습니다. 그렇다면 FULL SCAN이 아니라 인덱스 SCAN하고 테이블 갈때 한다는 것인데 INDEX SCAN에 관해서는 그런 기능이 없었다라고 하시니 헷갈립니다. 이것에 대해서 자세히 설명을 해주실 수 있을런지요.
    그리고 여러개라고 하셨는데 그게 몇개인지 알 수 없나요? 그리고 만약 그 몇개를 인위적으로 늘린다면 NL조인으로 부분범위 처리를 하고자 할때 성능 차이가 생길 수 있을 것 같은데 실제로 그렇게 되는지요. 궁금합니다.

    올려주신 글 항상 고맙게 잘 보고 있습니다. 감사합니다.

  4. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.05.11 21:17 신고  댓글주소  수정/삭제  댓글쓰기

    먼저 "9i/10g 에서 table prefetch 기능이 추가되었다" 함은 TABLE ACCESS BY INDEX ROWID 부분을 두고 하는말입니다. 제가 9i/10g plan 에 파랑색으로 표시한 부분입니다. 그부분은 index scan 이 아니지요.
    index scan 에 의한 부분은 11g 에 나옵니다. 내부적인 NLJ_BATCHING 힌트나 Batched IO vector block count 등이 그증거가 되겟습니다.
    또한 table prefetch 시에 운반단위수의 수정을 말씀해주셨는데 아주 위험 합니다.
    물론 그런기능을 하는 히든 파라미터가 존재 합니다.
    하지만 그것을 수정했을경우 나타나는 버그에 대해서는 누구도 알수 없습니다.
    검증된바가 없습니다.
    따라서 오라클사의 직원들조차 그 파라미터들을 건드리지 않습니다.
    건드리는 경우는 버그에 의해서 기능을 못쓰도록 막을때나 사용합니다.
    하지만 이또한 위험 하므로 히든파라미터는 그대로 놓고 쓰시는것이 제일 좋습니다.
    http://scidb.tistory.com/entry/히든-파라미터-설정의-위험성 를 참조 하시기 바랍니다.

  5. 바우 2009.05.12 09:11 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다. ^^

  6. 야함나르 2009.05.18 16:14 신고  댓글주소  수정/삭제  댓글쓰기

    NLJ_BATCHING , NLJ_PREFETCH 힌트등은 11g 에서만 사용하실수 있습니다 라고 되어 있는데
    제가 보고 있는 DB의 경우 9.2.0.7 버전에서 8i와 같은 실행 계획이 나타나고 있습니다. 9 버전에는 힌트가 없다면 파라미터 조정을 통한 방법 말고 table prefetch를 유도하는 방법은 없을까요?.

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

      파라미터를 조정하기 전에 table prefetch 기능은
      제약사항이 있으므로 먼저 nested loop join 의 형태를 점검 하셔야 합니다.
      1.nested loop join 일때만 가능함.
      2.선행집합이 index range scan 혹은 full table scan 이어야만 가능함.
      3.후행집합이 index range scan 일때만 가능함.
      위의 3가지 조건을 만족하는지 먼저 살펴보시기 바랍니다.

    • 야함나르 2009.05.19 11:44 신고  댓글주소  수정/삭제

      네 위의 조건을 만족합니다.

      --------------------------------------------------------------------------------
      0 SELECT STATEMENT Optimizer=CHOOSE
      1 0 SORT (AGGREGATE)
      2 1 NESTED LOOPS
      3 2 TABLE ACCESS (FULL) OF 'WRITTEN'
      4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT'
      5 4 INDEX (UNIQUE SCAN) OF 'PRODUCT_PK' (UNIQUE)

      이런 플랜이 생성되어서 적용할수 있지 않을까 합니다만.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.05.19 11:48 신고  댓글주소  수정/삭제

      보여주신 plan 은 3번 째 조건을 만족 하지 못합니다.
      후행 테이블이 index unique scan 이므로 동시에 여러건을 가져오지 못합니다. index range scan 일때만 가능 합니다.

    • 야함나르 2009.05.19 13:10 신고  댓글주소  수정/삭제

      아; 그렇군요 ^^.

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

      과연 그렇군요.
      간단하지만 매우 유용한 테스트 입니다.
      좋은 정보 감사드립니다.^^

    • table prefetch 제약사항 점검.. 2009.08.28 16:23 신고  댓글주소  수정/삭제

      table prefetch 제약사항 중 "2.선행집합이 index range scan 혹은 full table scan 이어야만 가능함"에 관련하여 테스트를 해 보았습니다.
      결론은 index range scan 뿐만 아니라 index full scan, fsat full scan, skip scan에서도 발생하는 군요..
      (MIX/MAX)에서는 table prefetch가 불가한테 descending + rownum으로 하니 가능하네요...

      --스크립트 (욱짜님 스크립)
      drop table t1 purge;
      --drop table t1;

      drop table t2 purge;
      --drop table t2;

      create table t1(c1 int, c2 int);
      create table t2(c1 int, c2 int);

      --create index t1_n1 on t1(c1);
      alter table T1 add constraint T1_PK primary key(c1);
      create index t1_n2 on t1(c2,c1);
      create index t2_n1 on t2(c1);

      insert into t1
      select rownum, round(rownum,-2)
      from all_objects, all_objects
      where rownum <= 10000
      ;
      commit;

      insert into t2
      select rownum, rownum
      from all_objects, all_objects
      where rownum <= 10000
      ;
      commit;

      EXEC dbms_stats.gather_table_stats(user, 'T1', cascade => true);
      EXEC dbms_stats.gather_table_stats(user, 'T2', cascade => true);


      -- 테스트 결과.
      SELECT /*+ USE_NL(T1 T2) LEADING(T1) INDEX(T2) */
      COUNT(T2.C2)
      FROM
      --CASE 01. INDEX UNIQUE SCAN(X)
      --(SELECT C1 FROM T1 WHERE C1 = 1256) T1 , T2

      --CASE 02. INDEX RANGE SCAN(O)
      --(SELECT /*+ INDEX_RS(T1) */ C1 FROM T1 WHERE C1 <= 1256) T1 , T2

      --CASE 03 INDEX RANGE SCAN DESCENDING(O)
      --(SELECT /*+ INDEX_DESC(T1) */ C1 FROM T1 WHERE C1 <= 1000 ) T1 , T2

      --CASE 04 INDEX RANGE SCAN (MIN/MAX)(X)
      --(SELECT /*+ INDEX_RS(T1) */ MAX(C1) C1 FROM T1 WHERE C1 <= 1000 ) T1 , T2

      --CASE 05 INDEX FULL SCAN(N/A)
      --(SELECT C1 FROM T1 ) T1 , T2

      --CASE 06. INDEX FULL SCAN (MIN/MAX)(X)
      --(SELECT MAX(C1) C1 FROM T1 ) T1 , T2

      --CASE 07 INDEX FULL SCAN DESCENDING(O)
      --(SELECT /*+ INDEX_DESC(T1) */ C1 FROM T1 ) T1 , T2

      --CASE 08 INDEX FAST FULL SCAN(O)
      --(SELECT /*+ INDEX_FFS(T1) */ C1 FROM T1 ) T1 , T2

      --CASE 09 INDEX SKIP SCAN(O)
      (SELECT /*+ INDEX_SS(T1 T1_N2) */ C1 FROM T1 ) T1 , T2
      WHERE T1.C1 = T2.C1 ;

  7. 2009.07.22 11:32 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 좋은 글 항상 감사합니다.
    다시 보다보니 궁금한 점이 있어서 질문드립니다.

    1. 기존 nested loop은 outer테이블에서 출발, inner인덱스에 엑세스하고 inner테이블에 엑세스해서 해당 정보까지 가져오는데 반해 소위 advanced nested loop(기존 NL과 비교해서 이걸 뭐라고 해야할지 모르겠습니다.)는 outer테이블의 스캔이 끝난 이후 inner인덱스과 join한 중간집합의 rowid로 inner테이블에 엑세스하기 때문에 outer테이블의 크기에 의해 결과반환시간(first_rows)에 영향을 줄 것 같고 결과적으로 기존의 부분처리와는 차이가 있을 것 같습니다.
    (빠른 결과를 위해 운반단위가 다차는 순간 테이블블록에 엑세스한다는 것에 생각해봤지만 반복적인 i/o를 줄이는 효과를 감소시키는 결과라서 이건 아닐 것같은 생각이 들고요.)
    만약 제 생각이 맞다면 어떤 식으로 테스트하면 두 차이를 확연히 드러낼 수 있을까요?

    2. outer테이블의 스캔이 끝난 이후 inner인덱스과 join한 중간집합은 inner인덱스의 rowid로 정렬되어 inner테이블 엑세스 시 logical i/o와 physical i/o를 줄일 수 있다고 이해하고 있습니다.
    그런데 중간집합이 inner인덱스의 rowid로 정렬된다는 것은 어떻게 확인 할 수 있을까요?
    실행계획에는 중간집합정렬에 관한 단서는 찾을 수 없는데..제가 잘못 이해하고 있는 부분이 있나요?


    질문이 장황하지만 제가 잘못 이해하고 있는 부분이 있다면 지적해주시고
    합당한 질문이라면 명쾌한 답을 주시면 감사하겠습니 다. ^^

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

      답변이 늦어져서 죄송합니다.
      1번에 대한 답변입니다.
      prefetch 라는 개념은 후행집합이 index range scan 으로 풀리므로 어짜피 scan 해야할 block 이 정해져 있다고 볼수 있습니다. 이때 Range 범위 내에서 Single index Range Scan 을 하는것 보다는 여러개의 Process 혹은 Thread 가 Index range Scan 을 하는 것이 성능상 유리하기 때문에 생긴 개념입니다.
      하지만 이것의 특징은 Hit ratio 가 있다는 점이지요.
      Prefetch 된 Block 들의 데이터를 사용하지 않을수 있기 때문입니다. 이 Hit Ratio 가 떨어지면 Prefetch 의 효과는 감소 됩니다.
      테스트를 수행시 v$sesstat 에서 SQL 수행전과 수행후의 value 의 차이를 모니터링 하시고 이때 나온 값들중에 Hit ratio 로 판단되는 값들이 있었습니다.
      이부분을 중점적으로 테스트 해보시는 것이 좋을거 같습니다.

      두번째 답변입니다.
      inner 테이블의 데이터는 inner 테이블의 인덱스 rowid 로 절렬이 되어 있지 않습니다. 그리고 실제로 정렬이 수행되지 않습니다. 물론 인덱스의 경우 데이터가 같은 값이라면 rowid 로 정렬리 되어 있기는 합니다.

      prefetch 시에 정렬을 하는 기능은 없는것으로 생각 하시면 됩니다.

  8. 2009.07.23 19:07 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사합니다. (목이 좀 늘어났습니다 ㅎㅎ)
    저는 운반단위로 블럭에 엑세스하면 반복적인 i/o가 발생할 수 있어서 비효율적이고 그러니까 그렇게 처리하지 않을거야라고 생각했었는데 결과적으로 아닐거라고 생각한대로 동작하는군요.

    요즘 cost based oracle fundamentals(비투엔)을 읽고 있습니다. 그중 table prefetching의 동작을 설명하는 부분이 있는데 그중 "결과집합을 rowid순으로 정렬하고 나서 테이블 액세스를 시도하기 때문에 테이블 길이를 따라 블록들을 단 한 번씩만 방문하면 된다."(p386) 라는 글이 있습니다.
    이 부분때문에 여러가지로 궁리하게 되었는데 좀더 궁리해봐야겠습니다.

    답변 감사합니다. ^^

    • 브라이언홍 2009.08.27 15:44 신고  댓글주소  수정/삭제

      http://ukja.tistory.com/166
      에서 댓글 3번에 이런 글이 있네요...
      Jonathan Lewis가 [CBO Fundamentals]에서 "Table Prefetching"이라는 용어를 사용했다고 하셨는데, 제가 볼 때는 이 책에서도 새로운 NL Join 메커니즘을 잘못 설명하고 있다고 여겨집니다(번역서에서 마침 그 부분을 제가 번역했는데, 제가 알고 있는 내용과 달라 많은 고민을 했었죠.)

  9. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.08.27 16:29 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사드립니다.