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

사용자 삽입 이미지











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

간단한 집합개념 Test  (7) 2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.  (7) 2009.06.29
SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
응용력 발휘하기  (2) 2009.03.20
Posted by extremedb
,
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 공부 여기서 포기할것인가?

Posted by extremedb
,

  대부분의 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
,

어느 회식 자리에서
  오늘(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
응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
Posted by extremedb
,

  한 선지자에 의하여 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
,