부분범위 처리 혹은 페이징 처리시 탁월한 힘을 발휘하는것이 스칼라 서브쿼리이다. 여러가지 테스트 결과 스칼라 서브쿼리는 부분범위처리시 가장 좋다는 Nested Loop 조인보다 성능상 좀더 유리하다.  과다호출로 몸살을 앟고 있는 사용자 함수를 스칼라 서브쿼리로만 바꾸어도 튜닝이 된다. 또한 View Merging 이나 JPPD(Join Push Predicate Down) 등의 성공여부를 고민할 필요도 없으므로 특히 개발자 들에게 인기가 많은것 또한 사실이다.

모든 기능은 용도에 맞게 사용해야 한다.
  이렇게 많이 활용되고 있는 스칼라 서브쿼리도 한계는 있다. Multi Column, Multi Row 을 RETURN 하는 것은 만들수가 없다는 것이다. 물론 Pipilined Funtion 으로 해결이 가능하지만 모든경우에 이것을 만들기란 여간 힘든것이 아니다. 바로 이럴때 사용가능한것이 Cast + MultiSet 함수를 이용한 스칼라 서브쿼리이다. MultiSet 함수는 Oracle 8.1.7  부터 사용이 가능하다. 필자가 이기능을 소개하는 이유는 실무에서 이런기능을 꼭 써야하는 경우임에도 Pipelined 함수등을 사용하는것을 많이 보아서이다. Pipelined 등의 함수를 사용해야 할때는 자주사용하는 SQL 이 아주 길어서 Network 의 부하가 생길때 혹은 모듈로서 공유를 해야될때 이다.

그럼 한번 만들어보자.
아래의 스크립트는 customers 테이블과 sales 테이블을 이용하여 MultiSet 스칼라 서브쿼리를 구현한것이다.
Cast + MultiSet 스칼라 서브쿼리를 사용하려면 먼저 type 2개를 만들어야 한다.


CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT
(
 PROD_COUNT NUMBER(5),
 CHANNEL_COUNT NUMBER(2),       --> 스칼라 서브쿼리가 RETURN 할 TYPE 선언
 AMOUNT_TOT NUMBER
)
;
  
CREATE OR REPLACE TYPE ARRAY_SALES_CUST  --> 위에서 선언한 TYPE 을 배열로 선언
AS VARRAY(1) OF SALES_CUST_TYPE ;       --> 고객별로 여러건이 RETURN 될 경우 값을 넉넉히 줄것.
         --> 아래의 예제는 고객당 1건만 RETURN 하므로 VARRAY 에 1을 준것이다.

이렇게 2개의 type 을 선언하면 모든 준비가 끝난다. Pipilined Funtion 의 코딩량에 비하면 사용하기가 훨신 간편함을 알수 있다. 그럼 이제 SQL 을 만들어 보자.

SELECT C.CUST_ID, C.CUST_YEAR_OF_BIRTH
             ,S.PROD_COUNT, S.CHANNEL_COUNT, S.AMOUNT_TOT
  FROM (
               SELECT /*+ FULL(C) */
                            C.CUST_ID, C.CUST_YEAR_OF_BIRTH,
                            CAST( MULTISET ( SELECT count(distinct s.PROD_ID)        AS PROD_COUNT
                                                                       ,count(distinct s.CHANNEL_ID)  AS CHANNEL_COUNT
                                                                       ,sum(s.AMOUNT_SOLD)           AS AMOUNT_TOT
                                                            FROM sh.sales s
                                                           WHERE s.cust_id = c.cust_id
                                                         ) AS  ARRAY_SALES_CUST --> 위에서 선언한 배열 TYPE 이용.
                                     ) AS SALES_CUST --> MultiSet 함수의 Alias 선언
                FROM sh.customers c
            )  C,
            TABLE(SALES_CUST) S                     --> 위에서 선언한 MultiSet 함수의 Alias 를 테이블로 이용함.
 WHERE S.PROD_COUNT  <> 0 ;    --> 스칼라 서브쿼리내의 특정컬럼을 조건절에 이용한 예제임.

SQL 의 결과 :

사용자 삽입 이미지















결과는 성공적이다. 성능면에서도 스칼라서브쿼리와 동일하다. 이것은 마치 스칼라서브쿼리의 장점과 PIPELINED TABLE 함수의 장점을 취한것과 같다. 하지만 대용량 배치 SQL 에서는 Hash 조인이나 Sort Merge 조인보다 성능이 좋지 못하므로 주의 해야한다.

결론 :
Cast + MultiSet 스칼라 서브쿼리는 부분범위 처리시 혹은 페이징 처리시에 탁월한 성능을 발휘하며 스칼라 서브쿼리의 단점인  Multi Column, Multi Row 을 RETURN 할수 있다. 뿐만아니라 View Merging 이나 JPPD (Join Predicate Push Down) 이 불가능할 경우의 훌륭한 해결책이 될수 있다.

P.S :
위의 예제는 Multi Column을 RETURN 하지만 Multi Row 를 RETURN 하지 않는다. 독자 여러분이 위의  Cast + MultiSet 스칼라 서브쿼리를 Multi Column, Multi Row 를 RETURN 하는것으로 바꾸어 보라. 백견이 불여일행 이다.

신고

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

오라클 Regular Expressions 완전정복  (22) 2009.10.07
간단한 집합개념 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
Posted by extremedb

댓글을 달아 주세요

  1. 불량토끼.. 2009.07.20 14:53 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽고 보고...정보도 많이 얻고 있습니다...
    바쁜 와중에서도 블러그를 운영하는 열정 참 좋습니다..

    "스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기."를 읽다가...제가 아둔하여서...왜? 이렇게 복잡하게
    쿼리를 짜야하는지?....이렇게 짜면 뭤이 더 좋은지 잘 납득이 가질 않아서..이렇게 질문을 드립니다...

    뭐...워낙이 초보라서 잘 이해가 가지 않지만...이렇게 짜는 쿼리가 실무에 어떠한 도움이 되는지 좀더 이해하기
    쉽게 설명을 해 주시면 감사하겠습니다...

    참고로 이 블러그는 참 좋은 정보가 많은데...전문가에게는 많은 도움이 될것같습니다.. 좀 아쉬운 점이 있다면..
    일반적으로 프로젝트 실무에서 좀더 도움이 될 만한 보다 쉬운 지면을 할애해 주시면...불쌍한 민초(?)들이 보다 더
    많은 도움이 될 것 같습니다...감사합니다...수고하시고...늘 건강 하십시요...

    거북이를 싫어하는 불량토끼....

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

      스칼라 서브쿼리를 쓰지않으면 쿼리가 아래처럼 됩니다.

      select C.CUST_ID, C.CUST_YEAR_OF_BIRTH
      ,S.PROD_COUNT, S.CHANNEL_COUNT, S.AMOUNT_TOT
      from sh.customers c,
      (SELECT s.cust_id
      ,count(distinct s.PROD_ID) AS PROD_COUNT
      ,count(distinct s.CHANNEL_ID) AS CHANNEL_COUNT
      ,sum(s.AMOUNT_SOLD) AS AMOUNT_TOT
      FROM sh.sales s
      group by s.cust_id
      ) s
      where s.cust_id(+) = c.cust_id
      and S.PROD_COUNT(+) <> 0 ;

      건수가 많은 판매 테이블을 Full Scan 하여 Group BY 후에 조인을 하게 되므로 매우 느려질수 있습니다.
      또한 View Merging(뷰 해체) 이 발생해서 조건이 파고 든다고 해도 결국은 Group BY 를 해야하니 부분범위 처리시 매우 않좋아 집니다. 반면에 스칼라서브쿼리를 사용하면 전체 Group BY 는 피할수 있습니다. 하지만 이런 단점을 피하려고 스칼라 서브쿼리를 쓰게되면 Multi Column 을 처리할수도 없고 Multi Row 를 처리할수도 없기 때문에 대안이 MutiSet 을 이용한 스칼라 서브쿼리 인것입니다.

  2. 불량토끼.. 2009.07.22 09:33 신고  댓글주소  수정/삭제  댓글쓰기

    답변감사합니다...하지만 여전히 풀리지 않는 의문이 하나 있는데....

    스칼라 서브쿼리를 쓰게되면...일반적으로 메인 쿼리의 레코드 수만큼 서브쿼리가 실행되는 것으로 알고 있습니다..
    제가 잘못 알고 있는 것인지...

    예를 들어서...메인쿼리의 고객수가 1000건이면...스칼라서브쿼리는 1000번이 실행되나요?? 아니면 한번만 실행이
    되나요??

    또한 일반적으로 사용할 때...스칼라서브쿼리는 1000번이 실행 되는데...예제처럼 멀티 로우를 사용하는 방식으로
    쓰게되면 1번만 실행되는 건지...궁금합니다..

    또한 일반적으로 쿼리에 펑션을 사용하면...레코드 수만큼 펑션이 콜 되므로 가능하면 일반적인 쿼리에는 펑션을
    사용하지 말도록 요구하는 것이 전문가들의 의견인데..어떻게 사용하는 것이 바람직 한지 궁금합니다..

    제 생각에는 인라인 뷰를 보다 효율적으로 작성을 하면 될것 같은데..지식이 짧아서...잘 모를겠습니다..ㅎㅎㅎ

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

      스칼라서브쿼리는 메인쿼리의 레코드 수만큼 실행되지 않습니다. 그것은 subquery caching 기능이 있기 때문입니다. 즉 메인쿼리가 1000 건이지만 스칼라서브쿼리의 결과는 10종류 밖에 되지 않을경우 정확히 10번만 수행합니다. 하지만 최악의 경우 스칼라서브쿼리의 결과가 메인쿼리의 결과 row 수인 1000 종류가 나온다면 1000 번 수행됩니다.

      Function 은 무조건 스칼라 서브쿼리보다 느립니다.
      Determinstic 을 명시적으로 지정한 경우는 이론적으로 스칼라 서브쿼리의 효과를 누릴수 있지만 대개의 경우 Funtion 내에서 select 를 하고 있으므로 Determinstic으로 지정하는것은 위험합니다.

      그리고 인라인뷰를 효율적으로 작성하신 SQL 을 저에게 보내주시기 바랍니다. 어떤 것인지 궁금하네요.^^

  3. 불량토끼.. 2009.07.22 13:20 신고  댓글주소  수정/삭제  댓글쓰기

    ^^답변 감사합니다....

  4. Favicon of http://blog.naver.com/dooba202 BlogIcon 두바리 2010.05.19 08:14 신고  댓글주소  수정/삭제  댓글쓰기

    평소 방법이 없을까 했던 차에 이렇게 좋은 방법을 알게 되었네요.
    내용좀 제 블로그로 가져갑니다~
    잘활용하겠습니다.

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

      오라클 10g 등을 사용하고 계신다면 위의 방법보다 더좋은 것이 있습니다.
      아래의 링크를 참조하세요.
      http://scidb.tistory.com/100

얼마전에 ORACLE DBMS ShutDown 과 관련한 회의가 있었는데 문제는 아래와 같다.

지옥이 시작되다.
  악성 SQL 이 하나 있었다. 그 SQL 은 한번수행시 평균 Elapsed Time 이 0.95 초 정도 걸리고 자주 사용되는 중요한 SQL 인데 DBA 가 조인방법을 바꾸어  Elapsed Time 을 0.8 초로 줄였다. 조인방법만 바꾸었을 뿐인데 성능이 10% 이상 향상 되었다.  그리고 그 DBA 는 튜닝된 SQL을 개발자에게 운영 시스템에 반영하라고 지시하였다. 그 SQL을 테스트 해본 개발자는 성능이 빨라진것을 확인하고 해당 힌트를 적용하여 운영시스템에 반영하였다.

 여기서 부터 지옥이 시작되었다. 잘운영되던 DBMS 가 버벅이기 시작한것이다.
결국 운영할수 없는 지경에 까지 이르러서 운영조직은 ORACLE DBMS ShutDown을 결정하였고 튜닝된 SQL 을 원복시켰다.
다시 천당이 되었다. 결국 원래의 SQL 은 문제가 없었고 DBA 가 튜닝한 SQL 이 악성 SQL 이었던 셈이다.

메모리 사용량을 계산해보자.
  원인은 튜닝된 SQL 이었다. 그 SQL 은 Peak Time 에 무려 초당 700 번이나 실행되었고 한번 수행시 사용되는 HASH AREA SIZE 를 계산해보니 5MB 정도를 소비하였다. 하나의 SQL 이 초당 3.5 Giga Byte(700 * 5MB) 를 소모한 것이다.  전체 PGA 의 1/3 이 넘는 메모리를 하나의 SQL 이 소모해버린것이다. 당연히 그 SQL 을 제외한 다른 SQL은 PGA 영역의 메모리를 사용하려고 줄을 서게 될것이고 시스템 전반적인 성능이 저하될 것이다. 그뿐인가? 원래 0.95 초 걸리던 SQL 도 수행속도가 1초가 넘어버렸다.
주로 개발자 출신의 DBA 가 이와같은 실수를 많이 저지른다. 시스템 엔지니어 출신의 DBA 는 절대 이런실수가 없다. 물론 개발자 출신 DBA 의 장점은 헤아릴수 없이 많다.

메모리 증설이 해결책인가?
  결국 Hash 조인을 을 Nested Loop 조인으로 바꾸고 Access Path를 파악하여 적절한 인덱스를 생성하는것으로 사태는 진정 되었다. 운영조직은 현재 메모리를 증설할 계획이라고 한다. 하지만 메모리 증설로 해결되는것은 미시적 관점이며 거시적 관점에서 해결책은 되지 못한다. 자주 사용하는 SQL 을 튜닝 할때마다 메모리를 증설 할것인가?

위의 문제해결과정이 우리에게 주는 교훈은 무엇인가?
  수행속도의 최적화 혹은 Logical Reads의 수를 줄이는것이 항상 튜닝의 목표가 아니라는 것이다. 자원(CPU, MEMORY) 등은 한정적이다. 따라서 이자원들을 전체 시스템관점에서 적절하게 분배하는것 또한 튜닝의 목표가 되어야 한다.
자주 사용되는 SQL을 튜닝할때 Hash Join 을 남발하지 말아야 한다. Hash 조인은 조인횟수를 획기적으로 줄여주지만 반대급부로 메모리 소모가 심하다. 물론 0.95 초 걸리던 SQL 이 0.001 초만에 끝난다면 그방법이 고려될수도 있다. 자원을 독점하는 시간과 SQL 의 수행속도가 현저하게 줄어들었으므로...

수행속도의 최적화가 항상 튜닝의 목표인가?
  튜닝의 목표는 물론 Response Time 을 줄이는것 혹은 Logical Read 등을 줄이는 것에 있다.
하지만 항상 추가적으로 고려해야 할것이 전체 시스템 관점에서 자원의 효율적인 배분이다. 이것이 시스템 튜닝의 기본이다.
생각해보라. 같은시간대에 수행되는 야간배치 SQL 이 여러개 있고 그중에 하나가 Parallel 힌트를 다음과 같이 사용하였다면 얼마나 끔찍한 일이 일어날 것인가?

INSERT /*+ Parallel(B 256) */ INTO ~
SELECT /*+ Parallel(A 256) */
  FROM ~   ;

 천당과 지옥의 차이는 힌트와 같은 아주 조그만 코드에서도 좌우될수 있다.

신고
Posted by extremedb

댓글을 달아 주세요

  1. 김시연 2009.06.26 16:30 신고  댓글주소  수정/삭제  댓글쓰기

    오랜만에 들려서 좋은글 잘 보고 갑니다. ^^

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

    PARALLEL 보니까..

    예전에 제 상사가 했던 얘기가 생각나네요.

    빠르게 한다고 숫자 맘대로 쓰다간 상부에서 사색이 되서 쫓아올꺼라고 ㅎㅎ

1번부분은 오류가 있으므로 코멘트를 반드시 참조하기 바란다.(2009.06.20)

DBMS_STATS 정녕 필요악인가?
  오라클 DBMS의 모든 통계정보는 DBMS_STATS 패키지에 의해서 관리된다고 할수 있다. 오라클이 제공하는 모든 패키지가 중요하지만 가장 중요한 패키지 하나만 선정하라고 하면 주저없이 이 패키지를 꼽는다. 하지만 이패키지에는 모든사람이 알고 있는 치명적인 단점이 하나 있다. 바로 통계정보 생성 속도가 느리다는 것이다. 하지만 11g 에서 몇가지의 단점을 보완하였다.

11g 에서 성능이 빨라지다.
  오늘은 11g 에서 DBMS_STATS  패키지의 개선사항에 대하여 알아보려 한다. 11g 에서 개선된 것은 크게 3가지 이다.
1. 컬럼 단위의 MIN/MAX 통계정보 생성에 의한 SQL 성능향상.
    DBMS_STATS 패키지를 이용한 통계정보 생성시 컬럼단위의 MIN/MAX 값을 생성한다는것을 아는가?
    11g 부터 MIN/MAX aggregation 이 포함된 SQL 을 만나면 컬럼단위의 통계정보를 참조함으로서 SQL 의 성능을 향상 시킨다. 예를 들어 다음과 같은 SQL 을 수행시킬경우 10g 에 비하여 약 30% 의 성능향상을 얻을수 있다.

select  min(c2), max(c2), min(c3), max(c3), min(c6), max(c6),
           ……
           min(c32), max(c32)
  from CUSTOMERX; 

 통계정보로 부터  컬럼별로 MIN/MAX 값을 참조한후에 테이블을 scan 하면서 MIN~ MAX 값에서 벗어나는 값들만 MIN/MAX 함수를 적용하여 보여주면 되기 때문이다. 이것은 DBMS_STATS  패키지 자체의 성능향상이 아니라 DBMS_STATS  패키지에 의한 Aggregation SQL 의 성능 향상인 셈이다.
 
2. NDV(Number Of Distinct Value) 통계정보 생성시의 성능향상.
    NDV 값을 생성하기 위해서는 다음과 같은 SQL 이 수행되어야만 한다.

select  count(distinct c1), count(distinct c2), ..
  from T  

위와 같은 SQL 은 테이블 SIZE 가 클경우 심각한 SORT 부하를 발생시킨다. 그렇다고 NDV 생성시 ESTIMATE 1% 로 할경우 데이터에 NULL 이 많다거나 컬럼값이 하나로 편중되는 현상이 심할경우 부정확한 NDV 를 생성할수 있다. 이것은 SQL 의 성능에 부정적으로 작용할수 있다.
  11g  부터는 새로운 hash-based algorithm 을 추가하였다. 10g 에서 Sort Group By 대신에 Hash Group By 를 적용하면서 성능이 향상된것과 같은 개념이다. 주의 해야될사항은 새로운 hash-based algorithm 을  사용하려면 Sampling 을 하면 안된다는 것이다. Sampling을 하지 않으면 전체데이터를 scan 하지만 수행시간은 오히려 Sampling 보다 같거나 빠르다. 아래는 오라클사에서 테스트 한 결과이다.

DataBase Ver                          Elapsed Time (s)
----------------------------- ----------------
Oracle Database 10g Release 2 10%           2155
Oracle Database 10g Release 2 100%        24242
Oracle Database 11g                                 1516

10g R2 에서 Sampling 10% 로 한것보다 오히려 11g 에서  전체건에 대하여 생성하는것이 더빠르다는 결론이 나왔다. 또한 NDV 의 정확성 또한 거의 100% 에 가깝다. 새로운 algorithm을 사용할경우 획기적인 성능향상과 정확성을 기대할수 있다. 자세한 내용은 아래의 White Paper 를 참조하라.


3. 파티션 적용 테이블에서 Incremental NDV 생성기능.
    파티션 적용테이블에서 거래내역이나 이력데이터를 보관할경우 대부분이 일자로 Range 파티션을 하게된다.
Global 통계정보를 생성하려면 10g 까지는 2단계의 과정을 거쳐야 했다.
첫번째로는 파티션 레벨의 통계정보를 생성 하기위해 각파티션을 scan 하는작업수행.
두번째로는 Global 통계정보를 생성하기위해 전체 파티션을 scan 하는 작업수행.
정말 비효율적이지 않는가? 같은 파티션을 두번씩 읽은 셈이다.

10g 에서는 아래처럼 granularity를 옵션으로 사용하거나 아니면 수동으로 2번 돌려야 했다. 아래예제 에서는
estimate_percent 를 10% 로 한정 하였다.

exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', estimate_percent =>10, granularity =>'GLOBAL and PARTITION');

위의 방법대로 하면 전체 파티션을 내부적으로 2번씩 scan 해야 하므로 성능면에서 최악이다.
경험 많은 DBA 는 새로이 추가된 파티션의 통계정보만 생성하고 Global 통계정보를 생성함으로 전체 파티션에 대하여 2번씩 scan 하는 비효율을 제거한다. 아래처럼 새로 추가된 파티션을 명시하면 된다. 

exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', 'PART10', estimate_percent =>10, granularity =>'PARTITION');
exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', estimate_percent =>10, granularity =>'GLOBAL');

  하지만 2번째의 방법으로도 Global 통계정보의 생성을 위한 전체 파티션 scan은 막을수 있는 방법이 없었다. 
이러한 성능 이슈 때문에 11g 에서는 2단계의 과정이 없어지고 과정이 하나로 줄었다. 쉽게 말하면 각파티션 통계정보를 생성해놓고 Global 통계정보 생성시 파티션의 통계정보를 sum 하여 이용한다는 것이다. 또한 특정파티션을 지정하지 않아도 아래와 같이 gather_table_stats 을 수행하기전에 set_table_prefs를 수행하게 되면 바뀌거나 새로이 insert 된 파티션만 scan 을 하게된다.

11g :
exec dbms_stats.set_table_prefs('CUST', 'CUSTOMERX', 'INCREMENTAL', 'TRUE');
exec dbms_stats.gather_table_stats(‘CUST', 'CUSTOMERX', granularity =>'GLOBAL and PARTITION');

 11g 에서는 estimate_percent를 10%  로 한정 하지 않았기 때문에 성능 어떨지 궁금하지 않은가?
아래의 테스트 결과를 보자.

DataBase Ver                       Elapsed Time (s)
-------------------------------    ----------------
Oracle Database 11g Incremental                1.25
Oracle Database 10g Release 2 10%              2152     --> 전체를 2번 scan 한 경우
Oracle Database 10g Release 2 10% Manual       1058     --> 추가된 파티션만 명시한 경우


10g 에서 추가된 파티션만 명시한 경우에 비하여 무려 800 배 정도 빨라졌다. 대단하지 않은가?
이것은 3가지의 효과가 서로 상호작용한 효과이다.
1.Global 통계정보 생성시 2단계의 작업(각 파티션의 작업 + Global 작업) 이 1단계로 축소되고
2.Imcremental NDV 작업이 가능 해졌다는것
3.미리 언급한 hash-based algorithm 의 효과


결론 :
10g 까지 DBMS_STATS 패키지는 그중요성에 비하여 느린 성능이슈 때문에 Necessary Evil (필요악) 으로 인식되어 왔다.  하지만 드디어 11g 에서 몇가지의 개선사항으로 좀더 귀여운 모습을 하고 우리에게 돌아왔다. 11g 로 업그레이드 할이유가 몇가지 더 생긴것이다.

Reference : DSS Performance in Oracle 11g

편집후기 : 위에 링크된 백서는 DBMS_STATS 패키지 뿐만 아니라 11g 의 매뉴얼에 없는 여러가지 비급이 실려 있으므로 필독 하기 바란다.

백서내용 요약
1.Result Cache 신기능.
2.Composite Partitioning 신기능.
3.Full Outer Join 개선사항.
4.Nested Loop Join 개선사항.

신고
Posted by extremedb

댓글을 달아 주세요

  1. oraking 2009.06.20 20:20 신고  댓글주소  수정/삭제  댓글쓰기

    통계정보에서 얻은 MIN/MAX 값을 참조해 쿼리의 MIN/MAX 집계를 수행한다는 건 이해할 수 없군요. 통계정보는 정확한 값이 아님을 잘 아실텐데요... 예를 들어, 딕셔너리에 저장된 컬럼 통계 상으로 현재 MAX 값이 1,000인데 그 레코드가 지워져 실제 MAX 값은 999라고 합시다. 말씀하신 이론대로라면 이 상태에서 MAX 값 구하는 쿼리가 수행되면 1,000보다 크거나 같은 값만 대상으로 비교연산을 하므로 결과는 NULL이 되겠군요.
    dbms_stats 패키지 때문에 집계함수 성능이 좋아진 케이스라고 하셨는데, 참고하신 문서 내용과는 정반대의 결론이군요. 문서에 따르면 MIN/MAX 집계함수의 개선으로 dbms_stats의 성능이 좋아졌다고 설명하고 있습니다. 그리고 dbms_stats 뿐만 아니라 이들 함수를 사용한 일반 쿼리도 성능이 나아졌다는게 결론입니다.

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

      말씀하신 것처럼 1번부분은 min/max 집계함수의 성능개선으로 dbms_stats 성능이 좋아진것이 맞습니다.
      제가 이분분을 오역 하였습니다. 책발간을 축하드립니다.

  2. SOL 2015.07.21 14:58 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 감사합니다. :) 테스트 결과를 뽑는 쿼리는 어떻게 되는지 궁금합니다~ 저도 테스트를 진행해보고 싶어서요 ^-^

Query Transformer 의 냉대
  튜닝을 하는 많은 사람들이 PM 의 개념을 모른다는 결과가 나왔다. 정기모임 술자리에서 즉석으로 설문을 하였는데 결과는 충격적이었다. 참석자 10명은 DBA, 튜너, DB 컨설턴트 등등 DB 전문가들의 모임이라고 할수 있는데 단 한명도 아는사람이 없었다.

 필자가 충격적이라고 한 이유는 그모임의 많은 사람들이 왠만한 SQL 의 COST 를 계산할수 있는 내공을 가진 사람들이 었기 때문이다. 다행히 JPPD 나 VIEW MERGING, Unnesting 과 같이 튜닝 책에 소개 되는 간단한 변환들은 알고 있었다. 하지만 Query Transformer 가 푸배접을 받고 있다는 생각은 지울수가 없었다.
 
  Query Transformer 는 그 중요성이 옵티마이져의 50% 를 차지한다. 왜그럴까? 옵티마이져의 3대 Components 는 Query Transformer, Cost Estimator,  Plan Generator  이지만  이중에서 우리가 연구할수 있는 것은  Query Transformer 와  Cost Estimator 이며  Plan Generator 의 비밀은 오라클사의 DBMS 설계자/개발자만이 알수 있는 영역이기 때문이다. 또한 SQL 이 Transformer 에 의하여 변형되고 재작성 되기 때문에 성능에 직접적인 영향을 끼친다. 대부분의 경우 Query Transformation 이 발생하면 성능에 긍정적인 영향을 끼치지만 예외적으로 부정정인 영향을 줄수가 있으므로 가능한 Query Transformer에 대하여 상세히 알아야 한다.

어찌되었건 모임에서 PM 관련 내용을 블로그에 올리겠다는 약속을 하였다.

PM ( Predicate Move Around ) 이란?
 
인라인뷰가 여러 개 있고 각각의 where 절에 공통적인 조건들이 있다고 가정하자.
이럴경우에 모든 인라인뷰의 where 절에 똑 같은 조건들을 반복해서 사용해야 할까?
물론 그렇게 해야 하는 경우가 있지만 아래의 경우에는 그렇지 않음을 알수 있다..

SELECT /*+ qb_name (v_outer) */

       v1.*

  FROM (SELECT /*+ qb_name (IV1) no_merge */

               e1.*,  d1.location_id

          FROM employee e1, department d1

         WHERE e1.department_id = d1.department_id

            AND d1.department_id = 30

        ) v1,

       (SELECT   /*+ qb_name (IV2) no_merge */

                  d2.department_id, AVG (salary) avg_sal_dept

            FROM employee e2, department d2, loc l2

           WHERE e2.department_id = d2.department_id

             AND l2.location_id = d2.location_id

        GROUP BY d2.department_id

       ) v2

 WHERE v1.department_id = v2.department_id

    AND v1.salary > v2.avg_sal_dept  ;

 

 

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

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

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

| 0  | SELECT STATEMENT                |                  |       |       |     5 |          |

| 1  |  HASH JOIN                      |                  |     1 |   176 |     5 |  00:00:01|

| 2  |   VIEW                          |                  |     1 |    28 |     2 |  00:00:01|

| 3  |    HASH GROUP BY                |                  |     1 |    21 |     2 |  00:00:01|

| 4  |     NESTED LOOPS                |                  |       |       |       |          |

| 5  |      NESTED LOOPS               |                  |     6 |   126 |     2 |  00:00:01|

| 6  |       NESTED LOOPS              |                  |     1 |    14 |     1 |  00:00:01|

| 7  |        INDEX RANGE SCAN         | DEPT_IX_01       |     1 |    11 |     1 |  00:00:01|

| 8  |        INDEX UNIQUE SCAN        | LOC_ID_PK        |    23 |    69 |     0 |          |

| 9  |       INDEX RANGE SCAN          | EMP_DEPARTMENT_IX|     6 |       |     0 |          |

| 10 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     6 |    42 |     1 |  00:00:01|

| 11 |   VIEW                          |                  |     6 |   888 |     2 |  00:00:01|

| 12 |    NESTED LOOPS                 |                  |       |       |       |          |

| 13 |     NESTED LOOPS                |                  |     6 |   474 |     2 |  00:00:01|

| 14 |      INDEX RANGE SCAN           | DEPT_IX_01       |     1 |    11 |     1 |  00:00:01|

| 15 |      INDEX RANGE SCAN           | EMP_DEPARTMENT_IX|     6 |       |     0 |          |

| 16 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES        |     6 |   408 |     1 |  00:00:01|

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

Predicate Information:

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

1 - access("V1"."DEPARTMENT_ID"="V2_DEPT"."DEPARTMENT_ID")

1 - filter("V1"."SALARY">"V2_DEPT"."AVG_SAL_DEPT")

7 - access("D2"."DEPARTMENT_ID"=30)

8 - access("L2"."LOCATION_ID"="D2"."LOCATION_ID")

9 - access("E2"."DEPARTMENT_ID"=30)

14 - access("D1"."DEPARTMENT_ID"=30)

15 - access("E1"."DEPARTMENT_ID"=30)


다른뷰에 조건이 추가되었다.

Predicate Information을 보면 두번째 뷰(v2 ) "D2"."DEPARTMENT_ID"=30 조건과 "E2"."DEPARTMENT_ID"=30 조건이 파고들어 간 것을 알수 있다. 이 현상 때문에 D1 E1 에서 인덱스를 사용하였는데 결과는 성능면에서 아주 성공적이다. 그렇다면  오라클은 어떤 과정을 거쳐서 이작업을 진행하였을까?

10053 trace 정보를 보면 PM의 진행과정이 매우 상세하게 나와 있다.


PM: Considering predicate move-around in query block V_OUTER (#1)

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

Predicate Move-Around (PM)

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

PM:   Passed validity checks.

PM:   Pulled up predicate "V1"."DEPARTMENT_ID"=30

 from query block IV1 (#2) to query block V_OUTER (#1)

PM:   Pushed down predicate "D2"."DEPARTMENT_ID"=30

 from query block V_OUTER (#1) to query block IV2 (#3)

 

PM 은 순서가 중여하다.
10053 trace
내용을 분석하여 수행과정을 살펴보자.

1.       먼저 PM 이 수행될수 있는지 검사한다.

2.       V1 에서 WHERE 조건 d1.department_id = 30 를 바깥쪽 메인쿼리로 이동시킨다.

      이것을 Predicate pull up 이라고 한다.

3.       메인쿼리로 옮겨진 where 조건을 v2 에 복사한다.

이것을 Predicate Push down 이라고 한다.

4.  최종 결과에서 중복된 조건절이 존재하면 삭제한다..

 

V1, V2 가 메인쿼리(V_OUTER)에서 department_id 기준으로 조인되고 있기 때문에 조건절을 V_OUTER 로 빼낸 다음에 V2 에 조건절을 밀어 넣고 있다. 이것은 JPPD 기능과 유사한 면이 있지만 Predicate pull up 이 반드시 먼저 일어나야 한다는 점과 Hash 조인등에서도 PM 이 발생한다는 점에서 엄연히 다르다. 오라클은 여러분이 모르는 사이에 조건절을 이리 저리 옮겨 다니면서 SQL 의 최적화를 시도하고 있다.

 

PM 아무때나 발생하나?
그럼 PM 이 어떤 조건일 경우 발생하는지 짐작할수 있겠는가?

다음과 같은 조건일 경우 PM 이 발생된다.

1.       (혹은 인라인뷰) 2개 혹은 그이상이 되어야 한다.(예제에서 V1, V2 가 있음)

2.       특정 인라인뷰내의 조건이 존재하고 뷰의 바깥쪽에서 조건을 사용한 컬럼으로 조인이 발생할 경우에 발생된다. 예제에서 는 d1.department_id = 30 으로  V1 내부에 조건이 존재하고 V_OUTER 에서 department_id V2 와 조인을 하고 있다.

3.       VIEW MERGE 가 발생하지 않아야 한다. Merging 이 발생되면 PM 대신에 Transitive Predicates 가 발생된다. 

       4.   파라미터 _PRED_MOVE_AROUND true 로 지정이 되어 있어야 한다.


결론 :
  제목에서 보듯이 PM 의 개념은 매우 간단하다. Where 조건을 다른뷰에 이동시키는 기능이며 Heuristic Transformatin 의 대표적인 예제이다.
  오늘 올린 글은 현재 집필중인 책의 내용인데 일부를 먼저 공개하기로 결정 하였다.

편집후기 :  JPPD 와 PM 이 헷갈린다는 보고가 들어왔다. 둘다 WHERE 조건이 PUSH 되는것이지만 가장 결정적이 차이점은 JPPD 는 Predicate pull up 기능이 없다는 것이다. 아주 명확하게 구분할수 있다.

 

신고
Posted by extremedb

댓글을 달아 주세요

  1. 유수익 2009.11.17 00:21 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용 감사합니다.
    책을 집필중이라고 하셨는데 언제 출간 예정이신가요?