잡동사니 보물섬

OR-Expansion 기본 

 - or-expansion은 기본적으로 or로 묶인 where 절에 조건의 경우 or의 사용으로 index를 

   사용할 수 없어 full table scan을 해야 한다. 그러나 or-expansion을 사용하게 되면 

   index를 사용할 수 있게 되면서 해당 결과 집합을 union all로 합쳐서 사용한다. 

 - 즉, 결론적으로 index를 사용하며 union all로 합쳐주는 작업으로 이해하면 쉽다. 

   하지만 테이블을 2번 엑세스 한다는 단점이 있어서 겹치는 부분이 적을 때 효과적이다. 

 - OR-Expansion을 제어하기 위해 사용하는 힌트로는 use_concat 과 no_expand 두가지가 있다. 

   use_concat은 OR-Expansion을 유도하고자 할 때 사용하고,

   no_expand는 이 기능을 방지하고자 할 때 사용한다. 

   ( or-expansion 기능은 _no_or_expansion 파라미터를 true로 설정하여 아예 막을 수 있다. ) 

OR-Expansion 브랜치별 조인 순서 최적화 

 - OR-Expansion에 의해 분기된 브랜치마다 각기 다른 조인 순서를 가질 수 있음은 매우 중요한 

   사실이다. 

 - where 절에서 () 안에 들어간 or 에서도 or-expansion이 발생할 수 있다. 

   * LNNVL 함수 

      - 두 쿼리의 교집합이 두번 출력되는 것을 방지하기 위해서 or-expansion 사용시 

        자동으로 사용되는 함수 

같은 컬럼에 대한 OR-Expansion 

 - 9i 까지는 같은 컬럼에 대한 or 조건이나 in-list도 or-expansion이 작동할 수 있었지만 

   10g부터는 기본적으로 아래와 같이 IN-List Iterator 방식으로만 처리된다. 

 - 만약 억지로 OR-Expansion으로 유도하려면 use_concat 힌트에 qb_name 인자를 제공하면 

   되지만 , IN-List Iterator에 비해 나은 점이 없으므로 굳이 그렇게 할 이유는 없다. 

 - 주의할 사항이 한가지 있다. 9i까지는 OR 조건이나 IN-List를 힌트로 이용해 OR-Expansion으로 

   유도하면 뒤쪽에 놓인 조건이 항상 먼저 출력됐었다. 

 - 하지만 10g CPU 비용 모델에서는 위와 같이 OR-Expansion으로 유도했을 때 통계적으로 

   카디널리티가 작은 값이 먼저 출력된다. 

 - 9i 이전 처럼 뒤쪽에 놓인 값이 먼저 출력되도록 하려면 ordered_predicate 힌트를 사용하거나 

   IO 비용 모델로 바꿔주어야 한다. 

 - 10g 이후 버전에서도 비교 연산자가 '=' 조건이 아닐 때는 일반적인 use_concat 힌트만으로도

   같은 컬럼에 대한 OR-Expansion이 잘 작동한다. 

     * 테스트에서 알아 낸 점 

       - 기본적으로 index가 있어야 하는 것 같다. or-expansion이 되려면 index가 없으면 

         full table scan이 발생하여 concatenation 실행계획이 발생하지 않았다. 

         ( 한 번 더 테스트 하고 증거 남기기  )

nvl/decode 조건식에 대한 or-expasion 

 - 조건절에 nvl 이나 decode 함수를 사용하면 or-expansion 실행계획이 발생한다. 

 - 해당 변수 값의  null 여부에 따라 위 또는 아래쪽 브랜치만 수행하는 것이다. 

 - 무엇보다 중요한 것은 변수 값 입력 여부에 따라 다른 인덱스를 사용하다는 사실이다. 

 - 이 유용하 닉능을 제어하는 파라미터는 ' _or_expand_nvl_predicate '이다. 

 - nvl 또는 decode를 여러 컬럼에 대해 사용했을 때는 그 중 변별력이 가장 좋은 컬럼 기준으로 

   한 번만 분기가 일어난다. 

 - 옵션 조건이 복잡할 때는 이 방식에만 의존하기 어려운 이유가 여기에 있고, 그럴 때는 

   여전히 수동으로 union all 분기를 해 줘야만 한다. 

조인 제거란 

 - 1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 

   참조하지 않는다면, 쿼리 수행 시 1쪽 테이블은 읽지 않아도 결과집합에 영향을 미치지 

   않기 때문에 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 

   이를 '조인 제거' 또는 '테이블 제거'라고 한다. 

 - 이러한 쿼리 변환은 오라클의 경우 10g 부터 작동하기 시작했지만 SQL Server 등에서는 

   이미 오래 전부터 적용돼 온 기능이다. 

 - 이 기능을 제어하는 파라미터는 아래와 같고, 힌트를 통해 쿼리 레벨에서 제어할 수도 있다. 

     ● eliminate_join, no_eliminate_join 

 - 조인 제거 기능이 작동하려면 아래와 같이 PK 와 FK 제약이 설정돼 있어야만 한다. 

 - FK가 설정 되어 있더라도 FK 쪽 컬럼이 null 허용 컬럼이면 결과가 틀리게 될 수 있다. 

   조인 컬럼 값이 null인 레코드는 조인에 실패해야 정상인데, 옵티마이저가 조인문을 

   함부로 제거하면 그 레코드들이 결과집합에 포함되기 때문이다. 그런 오류를 방지하기 위해

   옵티마이저가 내부적으로 e.deptno is not null 조건을 추가해 준다. 

 - 11g부터 PK와 FK 제약이 설정돼 있으면 아래와 같은 in 또는 exists 서브쿼리도 일반 조인문

   처럼 제거된다. 

   ( _optimizer_join_elimination_enabled 파라미터에 의해 영향을 받고, 

     eliminate_join, no_eliminate_join 힌트를 통한 제어도 가능 )  

조건절 이행이란 

 - 이 쿼리 변환을 한마디로 요약하면, [ (A=B) 이고 (A=C) 이다 ]는 추론을 통해 새로운 조건절을 

   내부적으로 생성해 주는 쿼리변환이다. [(A>B) 이고 (B>C)이면 (A>C)이다]와 같은 추론도 

   가능하다. 

 - 새로운 필터 조건이 추가되면서 조인 조건이 사라진다. 새로운 필터 조건으로 인하여 

   조인 조건이 필요가 없어졌다고 생각하고 옵티마이저가 중복 산정하는 것을 방지하기 위해 

   그렇게 처리하는 것이다. 

 - 9i에서는 사용자가 의도적으로 조인문을 한 버 기술하면 이 조인문이 다시 나타났는데, 

   10g부터는 아무리 여러 번 기술하더라도 그런 현상이 생기지 않는다. 

 - 만약 조건절 이행이 작용해 조인 조건이 사라지고 이로 인해 비용이 잘못 계산되는 문제가 

   생긴다면, 사용자가 명시적으로 새로운 필터조건을 추가하거나 조인문을 가공하는 방법을 

   사용해 볼 수 있다.  그러면 조건절이 사라지지 않고 그대로 남게 되며, 계산된 비용도 

   달라진다. 

      조인문 가공 방법 예시 ) d.deptno = e.deptno + 0

조건절 이행이 효과를 발휘하는 사례 

 - 내부적으로 조건절 이행이 여러 곳에서 일어나고 있다. 

 - 선분 이력을 between 조건으로 조회할 때는 인덱스 구성과 검색 범위에 따라 

   인덱스 스캔 효율에 많은 차이가 생긴다. 그에 따라 아래와 같은 쿼리에서도 범위를 

   더 제한적으로 줄일 수 있다. 

    SQL 예시) 

       select * 

        from 상품이력 a. 주문 b 

      where b.거래일자 between '20090101' and '20090131'

         and a.상품번호 = b.상품번호 

         and b.거래일자 between a.시작일자 and a.종료일자 

 - 위의 쿼리에서는 아래와 같이 조건절을 명시적으로 추가해 줌으로써 SQL을 튜닝하곤 했다. 

     ● 상품이력, 시작일자 <= '20090131' 

     ● 상품이력, 종료일자 >= '20090101'

 - 하지만 9i 부터는 옵티마이저가 이들 조건을 묵시적으로 추가하고 최적화를 수행한다. 

   ( 8i 버전을 사용하고 있다면 위와 같은 조건절을 명시적으로 추가해 주는 튜닝 기법이 

     여전히 유효하다 )

튜닝사례 1

 - 조인 조건은 아니지만 커럶 간 관계 정보를 통해 추가적인 조건절이 생성되었다. 

   옵티마이저에게 많은 정보를 제공할수록 SQL 성능이 더 좋아진다. 

   ( ip 사례 ) 

튜닝사례 2 

 - 최적의 조인순서를 결정하고 그 순서에 따라 조인문을 기술해주는 것이 매우 중요 

조건절 Pushing 

 - 뷰를 엑세스하는 쿼리를 최적화할 때 옵티마이저는 1차적으로 뷰 Merging을 고려한다. 

   하지만 아래와 같은 이유로 뷰 Merging에 실해할 수 있다. 

     ●  복합 뷰 Merging 기능이 비활성화 

     ● 사용자가 no_merge 힌트 사용 

     ● Non-mergeable views : 뷰 Merging 시행하면 부정확한 결과 가능성 

     ● 비용기반 쿼리 변환이 작동해 No Merging 선택 

조건절 Pushing 종류 

  ● 조건절 : 쿼리 블록 밖에 잇는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함 

  ● 조건절 pullup : 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하며, 

                           그것을 다시 다른 쿼리 블록에 Pushdown 하는 데 사용함 

  ● 조인 조건 Pushdown : NL조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 inner 쪽 

                                    뷰 쿼리 블록 안으로 밀어 넣는 것을 말함 

관련 힌트와 파라미터 

 - 조건절 Pushdown 과 Pullup은 항상 더 나은 성능을 보장하므로 별도의 힌트를 

   제공하지 않는다. 하지만, 조인 조건 Pushdown은 NL 조인을 전체로 하기 때문에 

   성능이 더 나빠질 수도 있다. 

 - 따라서 오라클은 조인 조건 Pushdown을 제어할 수 있도록 

   push_pred 와 no_push_pred 힌트를 제공한다.

 - 조인 조건 pushdown 기능이 10g에서 비용기반 쿼리 변환으로 바뀌었고, 이 때문에 

   9i에서 빠르게 수행되던 쿼리가 10g로 이행하면서 오히려 느려지는 현상이 종종 나타나고 있다.

  이때는 문제가 되는 쿼리 레벨에서 힌트를 이용해 파라미터를 false로 변경하면 된다.   

 - 10g에서 시스템 환경에 따라 이 기능이 문제를 일으켜 쿼리 결과가 틀리는 문제도 발생하는

   것으로 보고되고 있는데, 그때는 패치를 적용하거나 시스템 레벨 변경이 불가피하다. 

Non-pushable view 

 - 뷰안에 rownum, 분석함수를 사용하면 Non-mergeable, Non-pushable view가 되므로 

    주의해야 한다.

조건절 Pushdown 

 * 나만의 요약 정리 

   조건절 push down은 조인 조건을 통해서 한쪽테이블의 조인 조건인 커럶에 조건이 따로 

   붙을 때 해당 조건이 조인 조건을 타고 들어가서 다른 테이블에도 영향을 주는 것 

   (기본적으로 바깥에서 안으로 들어간다. ) 

GROUP BY 절을 포함한 뷰에 대한 조건절 Pushdown 

 - group by 절을 포함한 복합 뷰 Merging에 실패했을 때, 쿼리 블록 밖에 있는 조건절을 

   쿼리 블록 안쪽에 밀어 넣을 수 있다면 group by 해야할 데이터량을 줄일 수 있다. 

   인덱스 상황에 따라서 효과적인 인덱스 선택이 가능해지기도 한다. 

UNION 집합 연산자를 포함한 뷰에 대한 조건덜 Pushdown 

 * 나만의 요약정리

   이것도 기본적으로 바깥에서 안으로 적용 되는데 기본적인 것은 위의 조건절 Pushdown과 

   같지만 특이한 점이 있는 것은 바깥쪽에 조건이 조인 조건이 아니더라도 union all이 있는 

   뷰에서 해당 컬럼이 있을 시 동일하게 안쪽으로 조건절이 pushdown 된다.

    ( p.504 예시를 보면 이해하기 쉽다. ) 

 - union 집합 연산자를 포함한 뷰는 Non-mergeable view에 속해 뷰 Merging에 실패한다. 

   따라서 조건걸 Pushing을 통해서만 최적화가 가능하다 

조건절 Pullup 

  * 나만의 요약정리 

   일반적으로 조건절을 밖으로 빼냏어서 다시 조건절 pushdown 하기 위해서 사용된다. 

   즉 조인하려 2개의 view에서 한쪽에 조인 조건 컬럼에 검색 조건이 있고 조인 조건이 

   바깥에 있을 때, 안쪽에 있던 조인 조건 컬럼에 대한 또 다른 조건이 바깥쪽에  

   조인 조건을 타고 들어가서 다른 vies에 조인 조건 컬럼의 검색 조건이 적용된다.

조인 조건 Pushdown 

 -  조인 조건 Pushdown은 말 그대로 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것으로서, 

    NL 조인 수행 중에 드라이빙 테이블에서 읽은 조인 컬럼 값을 Inner 쪽 뷰 쿼리 블록내에서

    참조할 수 있도록 하는 기능이다. 

 - 지금까지 보았던 조인문에서 조건절 Pushdown은 상수 조건이 조인 조건을 타고 전이된 것을 

   Pushing하는 기능이었던 반면, 조인 조건 Pushdown은 조인을 수행하는 중에 

   드라이빙 집합에서 얻은 값을 뷰 쿼리 블록 안에 실시간으로 Pushing하는 기능이다. 

   예시 ) 조인 조건은 deptno 이다. 그런데 다른 조건은 loc= 'CHICAGO'라고 한다면 

            loc가  'CHICAGO'인 deptno 값들이 들어가는 것이다. 

 - 실행계획상에서 'View Pushed Predicate' 오퍼레이션이 나타나는 것을 통해서도 

   조인 조건 Pushdown이 일어나는지 알 수 있다. 

 - 조인 조건 Pushdown을 제어하는 힌트 

     ● push_pred : 조인 조건 Pushdown을 유도한다. 

     ● no_push_pred : 조인 조건 Pushdown을 방지한다. 

 - 이를 제어하는 파라미터 

     ● _push_join_predite : 뷰 Merging에 실패한 뷰 안쪽으로 조인 조건을 Pushdown 하는 

                                    기능을 활성화한다.  

     ● _push_join_union_view : union all을 포함하는 Non-mergeable View 안쪽으로 

                                          조인 조건을 Pushdown 하는 기능을 활성한다. 

     ● _push_join_union_view2 : union을 포함하는 Non-mergeable view 안쪽으로 조인 조건을 

                                           Pushdown 하는 기능을 활성화한다.  

 - 위 항목은 10g 기준이며, 9i 에서는 _push_join_union_view2 파라미터가 없다. 

   9i에서 union all을 포함한 뷰에 대한 조인 조건 Pushdown은 작동하지만 

    union에는 작동하지 않는다는 뜻이다.  

Group by 절을 포함한 뷰에 대한 조인 조건 Pushdown 

 - group by를 포함하는 뷰에 대한 조인 조건 Pushdown 기능은 11g에 와서야 제공 시작 

   * 나의 의문증 

     여기서 궁금한 것은 10g에서 부터는 group by가 있어도 view merging이 나타난다. 

     그런데 view merging을 하지 않고 조인 조건 pushdown 하는게 더 나은 경우가 있는 건지를 

     잘 모르겠다. 

   * 스칼라 서브쿼리에서 집계 함수 

      스칼라 서브쿼리를 사용하면서 스칼라 서브쿼리의 select 절에서 집계함수를 사용하고 

      스칼라 서브쿼리에서 메인 쿼리와 조인을 하게 되면 조인 조건으로 group by 된 값들과 

      동일한 결과가 나타난다. 

      이 부분에 로직에 대해서 생각해보았는데 스칼라 서브쿼리는 캐싱 기능을 사용해야 하고 

      그렇게 되면 입력 값이 같다면 그 결고 값들도 같을 것이다. 그리고 입력된 값들만 

      뭉쳐야 하고 자동으로 group by 된 형태로 계산이 될 것이다. 

      이 부분에 대해서는 확실한 검증이 되지는 않았지만 테스트 결과로 보아서 

      스칼라 서브쿼리에서 집계함수는 메인 쿼리와 조인 조건으로 자동으로 group by 된 

      결과물을 가진다고 생각하고 나는 생각하고 있다. 

UNION 집합 연산을 포함한 뷰에 대한 조인 조건 Pushdown 

 - union 또는 union all을 포함한 뷰 쿼리 블록에 대한 조인 조건 Pushdown은 10g 이전부터 

   제공되던 기능이다. 

 - union all은 8.1.6부터, union은 10.1.0부터다. 

 - 9i에서 use_nl 힌트를 push_pred와 함께 사용하면 조인 조건 Pushdown 기능이 작동하지 않는 

   현상이 나타나므로 주의해야 한다. 

 - 이때는 push_pred 힌트만을 사용해야 하며, 조인 조건 pushdown은 NL조인을 전제로 하므로 

   굳이 use_nl 힌트를 사용할 필요는 없다. 

Outer 조인 뷰에 대한 조인 조건 Pushdown 

 - outer 조인에서 Inner 쪽 집합이 뷰 쿼리 블록일 때, 뷰 안에서 참조하는 테이블 개수에 따라 

   옵티마이져는 다음 2가지 방법 중 하나를 선택한다. 

    ( 뷰 안에서 참조하는 테이블 개수에 따라 ) 

    ● 뷰 안에서 참조하는 테이블이 단 하나일때, 뷰 Merging을 시도한다. 

    ● 뷰 내에서 참조하는 테이블이 두 개 이상일 때, 조인 조건식을 뷰 안쪽으로 Pushin하려고 

        시도한다. 

  * 좀 더 생각해 볼 점 

     p.513 에 있는 실행계획에서는 outer 조인이면 (+) 가 없는 쪽이 먼저 드라이빙 된다. 

     그런데 실행계획에서 가장 안쪽에 들어가 있는 것을 먼저 읽는다는 규칙을 생각하면 

     view 안에 테이블들을 먼저 읽은 것으로 생각된다. 

     그런데 사실 그 테이블을 먼저 읽으면 안된다. 쿼리에 대한 규칙 상이라면 그런데 

     이 부분을 규칙과도 연관되어서 해석해보자면 view안에 있는 테이블들을 먼저 읽어서 

     view를 먼저 정의 한다. 그러나 NL 조인 실행계획에서는 (+) 가 없는 쪽 테이블을 

     먼저 읽는 것으로 보아 NL 조인에서는 드라이빙 테이블이 다른 것으로 생각된다. 

     즉 view 안에 있는 테이블들을 먼저 읽어서 view를 규정하고 그 값을 기억한 후 

     NL 조인은 그에 따른 순선대로 진행된다는 것이 일단 지금 나의 생각이다.  

 - union 집합 연산자를 포함한 뷰에 대한 조인 조건 Pushdown은 10g부터 비용기반으로 

   작동하기 시작했지만 지금 설명한 Outer 조인 뷰에 대한 기능은 9i에서도 비용기반이었다. 

   'GROUP BY 절을 포함한 뷰에 대한 조인 조건 Pushdown'은 11g에 도입되면서부터 

   비용기반으로 작동한다. 

뷰 Merging 이란 

 - 뷰 Merging과 다음 절에 설명하는 조건절 Pushing이 불가능한 형태의 뷰를 사용했을 때 

   성능이 느려지기도 하므로 뷰 때문에 쿼리 성능이 더 느려진다는 말도 전혀 틀린 말은 아니다. 

   하지만 악연한 경험치를 가지고 뷰사요을 꺼리는 것보다는 옵티마아저의 쿼리 변환 원리를 

   정확히 이해함으로써 적절한 때에 뷰를 사용할 수 있어야 한다. 

단순 뷰 Merging 

 - 조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 언제든 

   Merging이 일어난다. 

 - group by 절이나 distinct 연산을 포함하는 복합 뷰는 파라미터 설정 또는 

   힌트 사용에 의해서만 뷰 Merging이 가능하다. 

 - 또한 집합 연산자, connect by, rownum등을 포함하는 복합뷰는 아예 뷰 Merging이 불가능 

복합 뷰 Merging 

 - 복합 뷰는 ' _complex_view_merging ' 파라미터를 true로 설정할 때만 Merging이 일어난다. 

     ● group by 절 

     ● select-list 에 distinct 연산자 포함 

 - 8i에서는 _complex_view_merging 파라미터의 기본 값이 false 이므로 기본적으로 복합 뷰 

   Merging이 작동하지 않는다. 복합 뷰 Merging을 원할 때는 merge 힌트를 사용해야만 한다. 

 - 9i부터는 이 파라미터가 기본적으로 true로 설정돼 있으므로 동일한 결과가 보장되는 한 

   복합 뷰 Merging이 항상 일어난다. 

 - 10g에서도 복합 뷰 Merging을 일단 시도하지만, 원본 쿼리에 대해서도 비용을 같이 계산해 

   Merging 했을 때의 비용이 더 낮을 때만 그것을 채택한다. 

 - 복합 뷰 Merging 을 아예 사용할 수 없는 경우 

   ( 파라미터 값이 변경된다고 하더라도) 

    ● 집합(set)연산자 (union, union all, intersect, minus) 

    ● connect by 절 

    ● ROWNUM pseudo 컬럼 

    ● select-list에 집계 함수 (avg, count, max, min, sum) 사용 

         ( group by 없이 전체를 집계하는 경우를 말함 ) 

    ● 분석함수 

         ( rank 함수 등의 함수와 집계 함수에 over가 붙은 형태)  

비용기반 쿼리 변환의 필요성 

 - 다른 쿼리 변환은 대게 더 나은 성능을 제공하지만, 복합 뷰 Merging은 그렇지 못할 때가 많다. 

 - 비용기반 쿼리 변환이 휴리스틱 쿼리 변환보다 고급 기능이긴 하지만 

   파싱 과정에서 더 많은 일을 수행해야만 한다. 약간의 하드 파싱 부하를 감수하더라도 더 나은 

   실행계획을 얻으려는 것이므로 이들 파라미터를 off 시키는 것은 바람직하지 않다. 

 - 실제로 10g에서 조인 조건 Pushdown 기능이 비용기반 쿼리 변환으로 바뀌면서 쿼리 성능이 

   느려지는 경우가 자주 발생한다. 이때는 문제가 되는 쿼리 레벨에서 힌트를 이용해 

   파라미터를 false로 변경하면 된다. 

Merging 되지 않은 뷰의 처리방식 

 - 어떤 이유에서건 뷰 Merging이 이루어지지 않았을 땐 2차적으로 조건절 Pushing을 시도한다. 

   하지만 이마저도 실패한다면 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 

   서브플랜을 전체 실행계획을 생성하는 데 사용한다. 실제 쿼리를 수행할 때도 뷰 쿼리의 

   수행 결과를 엑세스 쿼리에 전달하는 방식을 사용한다. 

서브쿼리의 분류 

 - 오라클메뉴얼은 3가지로 서브쿼리를 분류한다. 

    ● 인라인 뷰 : from 절에 나타나는 서브쿼리를 말한다. 

    ● 중첩된 서브쿼리 : 결과집합을 한정하기 위해 where절에 사용된 서브쿼리를 말한다. 

                               서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를

                               '상관관계 있는 서브쿼리'라고 부른다. 

    ● 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 컬럼 값만을 리터하는 것이 특징이다. 

                               주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 

                               컬럼이 올 수 있는 대부분 위치에서 사용 가능하다. 

 - 서브쿼리를 참조하는 메인 쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 블록 단위로 

   최적화를 수행한다. 즉, 쿼리 블록 단위로 최적의 엑세스 경로와 조인 순서, 조인 방식을 

   선택하는 것을 목표로 한다. 

서브쿼리 Unnesting의 의미 

 - 서브쿼리를 처리하는데 있어 옵티마이저가 선택하는 방식 2가지 

      ● 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. 이를 일컬어 

          '서브쿼리 Unnesting'이라고 한다.

         ( '서브쿼리 Flattening'이라고도 부른다. 이렇게 쿼리 변환이 이루어지고 나면 

           일반 조인문처럼 다양한 최적화 기법을 사요할 수 있게 된다. )  

      ● 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다.

          메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 

          이때 서브쿼리에 필터 오퍼레이션이 나타난다. 

서브쿼리 Unnesting의 이점 

 - 서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 엑세스 경로와 조인 메소드를 

   평가할 수 있다. 특히 옵티마이저는 많은 조인테크닉을 가지기 때문에 조인 형태로 변환했을때 

   더 나은 실행계획을 찾을 가능성이 높아진다. 

 - 10g부터는 서브쿼리 Unnesting이 비용기반 쿼리 변환 방식으로 전환되었다. 따라서 변한된 

   쿼리의 예상 비용이 더 낮을 때만 Unnesting된 버전을 사용하고, 그렇지 않을 때는 원본 

   쿼리 그대로 필터 방식으로 최적화 한다. 

 - 서브쿼리 Unnesting 과 관련한 힌트 

     ● unnest : 서브쿼리를 Unnesting 함을써 조인방식으로 최적화하도록 유도한다. 

     ● no_unnesting : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화 하도록 유도한다. 

서브쿼리 Unnesting 기본 예시 

 - 서브쿼리 Unnesting은 중첩된 서브쿼리 (where절에 사용된) 에서 발생(?) 한다. 

 - Unnesting 하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 

   넘기면서 서브쿼리를 반복 수행한다. 

 - 변환된 쿼리는 아래와 같은 조인문 형태가 된다. 

   ( 그런 경우 view merging으로 이어지는 경우가 많다. ) 

Unnesting된 쿼리의 조인 순서 조정 

 - Unnesting에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느쪽이든 드라이빙 집합으로 

   선택될 수 있다는 사실이다. 

 - 서브쿼리에서 메인 쿼리에 있는 테이블을 참조할 수는 있지만 메인 쿼리에서 서브쿼리 쪽 

   테이블을 참조하지는 못하므로 leading 힌트를 통해 서브쿼리 테이블을 먼저 읽도록 할 수는 

   없다. 

 - 대신 ordered 힌트를 사용하면 서브쿼리 쪽 테이블을 직접 참조하지 않아도 되므로 

   원하는 대로 조인 순서를 유도할 수 있다. 이것을 통해, Unnesting 된 서브쿼리가 from 절에서 

   앞쪽에 위치함을 알 수 있다.

 - 10g부터는 쿼리 블록마다 이름을 지정할 수 있는 qb_name 힌트가 제공되어 쉽고 정확하게 

   제어할 수 있다. 

    예시 ) select /*+ leading(dept@qb1) */ * from emp 

             where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept )

서브쿼리가 M쪽 집합이거나 Nonunique 인덱스 일 때 

 - M쪽 집합이 메인 쿼리 이고 1쪽 집합이 서브 쿼리 일 때는 순서가 보장이 되지만 

   이것이 바뀌었을 때는 잘못된 결과가 나온다. 

 - 만약 두 테이블 사이에 PK/Unique 제약 또는 Unique 인덱스가 없다면 옵티마이저는 두 가지 

   방식 중 하나를 선택하는데, Unnesting 후 어느 쪽 집합이 먼저 드라이빙 되느냐에 따라 

   달라진다. 

     ● 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 

         먼저 sort unique 오퍼레이션을 수행하므로써 1쪽 집합으로 만든 다음에 조인한다. 

     ● 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인 방식으로 조인한다. 이것이 세미조인이 

         탄생하게 된 배경이다. 

Sort Unique 오퍼레이션 수행 

 - 여기서는 서브쿼리에서 unique 인덱스나 pk 가 없을 시 sort Unique 가 발생한다고 한다. 

   그러나 sort unique가 발생하지 않는다면 unnest가 되면서 서브 쿼리가 먼저 실행 되도록

   하려고 한다. 

세미 조인 방식으로 수행 

 - NL 세미 조인으로 수행할 때는 sort unique 오퍼레이션을 수행하지 않고도 결과집합이 M쪽 

   집합으로 확장되는 것을 방지하고 알고리즘을 사용한다. 

 - Outer 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 

   Outer 테이블의 다음 로우를 계속 처리하는 방식이다. 

 - 세미조인 방식으로 변환할 때의 장점은, NL 세미 조인뿐만 아니라 해시 세미 조인

    , 소트머지 세미 조인도 가능하다는 데에 있다. 사용자가 직접 유도할 때는 unnest 힌트와 

    함께 각각 hash_sj, merge_sj 힌트를 사용하면 된다. 

   * 여기서 중요한 것 2가지 

      ● 세미 조인은 unnesting 후에 사용한다. 

      ● 또, 세미 조인은 outer (즉 메인 테이블) 건수 만큼 random access가 발생할 것이다. 

          그런데 이 부분이 생각할 부분이 있다. 세미 조인은 일반적인 조인 보다는 더 빠르게 실행

          된다. 그러나 서브쿼리는 제대로 된 쿼리라면 1쪽 집합일 경우가 많을 것이다. 

          그렇다면 M쪽 집합에서 조인을 시도하는 것보다 random access 되는 수가 적을 텐대 

          unnest 세미 조인이 이 경우 성능이 더 좋을 지는 확신 할 수 없다. 

          대략 결과 건수가 1:1 정도가 된다면 이 방법이 더 빨라 질 수 있을 것 같다.

     ● 여기서는 좀 잘 못 생각한 부분이 있다.  
         이 부분으로 인하여 23회 SQLP 실기 문제에서도 좀 헤맸었는데 

         기본적으로 M(메인) : 1(서브) 가 옳은 쿼리라고 가정하고 있다. 
         즉 1(메인) : M(서브) 가 되면 잘 못된 값을 도출 시킬 수도 있는 것이다.

         그런데 만약 1(메인) : M(서브) 이 과정에서도 세미 조인을 실시 할 수 있다면 
         이야기는 좀 달라질 수 있다. 
         1(메인) : M(서브)  에서도 세미 조인이 가능하면 M(메인) : 1(서브) 보다 
         random 엑세스 부하를 줄 일 수 있기 때문이고
         세미조인인 이상 실제 조인이 일어나지 않기 때문에 잘못된 값을 출력할 
         가능성도 없어진다. ( 이는 물론 실제 조인이 일어나지 않아도 된다는 가정하) 
         이 부분에서 1:M 의 관계에서 세미 조인이 실시 되는지 확인해보지 못했다 

필터 오퍼레이션과 세미조인의 캐싱 효과 

 -  서브쿼리 unnesting 하지 않으면 쿼리를 최적화하는 데 있어 선택의 폭이 넓지 않아 불리 

    메인 쿼리를 수행하면서 건건이 서브쿼리를 반복 수행하는 단순한 필터 오퍼레이션을 사용할 

    수 없기 때문이이다. 

    ( 대량의 집합을 기준으로 이처럼 Random 엑세스 방식으로 서브쿼리 집합을 필터링한다면 

      결코 빠른 수행 속도를 얻을 수 없다. ) 

 - 오라클은 서브쿼리 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가 같은 값이 출력되면 

   저장된 값을 출력한다. 

    ( 스칼라 서브퀄의 캐싱효과와 같다. ) 

 - 조나단 루이스 설명에 의하며, 오라클은 8i와 9i에서 256개, 10g에서, 1024개 해시 엔트리를 

   캐싱한다고 한다. 실제 캐싱할 수 있는 엔트리 개수가 몇 개이건 간에 서브쿼리와 조인되는 

   컬럼의 Distinct Value 개수가 캐시 상한선을 초과하지 않는다면 필터 오퍼레이션은 

   매우 효과적인 수행방식일 것이다. 

 - 10g부터는 NL 세미 조인도 캐싱 효과를 갖는다. 그동안 캐싱 효과를 앞세워 명맥을 유지하던 필터 오퍼레이션이 설 자리를 잃게 되었다. 

    * 여기서 궁금한 것은 세미 조인은 outer (즉 메인 테이블)을 먼저 읽을 때 조인 하지 않고 

      빠르게 필터링 할 수 있게 해준다. 그런데 이것을 inner 테이블을 사용하면서도 semi 조인을 

      발생 시킨다면 성능적으로 더 좋지 않을까 생각한다. 단, 여기서는 결과가 메인 테이블에 있는 

      컬럼들만 출력해야 하며, 이럴경우 쿼리 자체를 조인으로 하여 세미 조인을 사용하는 것이 

      성능 향상에 도움이 될 것으로 생각된다. 

      (바로 위에서 했던 이야기와 동일하네 .. ^^;) 

Anti조인 

 - not exists, not in 서브쿼리도 Unnesting 하지 않으면 아래와 같이 필터 방식으로 처리된다. 

 - not exists 필터 : 조인에 성공하는 (서브) 레코드가 하나도 없을 때만 결과지합에 담는다. 

                          ( unnesting 하면 아래와 같이 Anti 조인 방식으로 처리된다. ) 

 - 해시 Anti 조인은 조금 다르다. 해시 Anti 조인으로 수행할 때는, 먼저 메인테이블을 

   해시 테이블로 빌드한다. 서브쿼리를 스캔하면서 해시 테이블을 탐색하고, 

   조인에 성공한 엔트리에만 표시를 한다. 마지막으로, 해시 테이블을 스캔하면서 표시가 없는 

   엔트리만 결과집합에 담는 방식이다. 

집계 서브쿼리 제거 

 - 집계 함수를 포함하는 서브쿼리를 Unnesting 하고, 이를 다시 분석 함수로 대체하는 

   쿼리 변환이 10g에서 도입되었다. 

 - 10g부터 옵티마이저가 선택할 수 있는 옵션이 한 가지 더 추가되었는데, 서브쿼리로부터 

   전환된 인라인 뷰를 제거하고 아래와 같이 메인 쿼리에 분석 함수를 사용하는 형태로 

   변환하는 것이다. 

   * 이 부분은 절차에 관한 부분은 이야기는 잘 나와있지만 실제적으로 차이

     즉, 블록의 수와 같은 부부은 나와 있지 않다 다만 테이블을 실제로 한 번 읽고 

     window buffer 가 발생한다는 것이며, 이는 자세히 보면 조인 조건을 타고 검색 조건이 

     타고 들어가서 분석 함수로 대체 되고 그로 인하여 테이블 엑세스를 줄이게 되어 있다.  

     이 부분에 대해서 이해가 위해서는 조인 조건을 타고 들어가는 검색 조건의 효과를 

     확인 후 성능의 차이를 이해하는 것이 좋을 듯 싶다. 

Pushing 서브쿼리 

 - Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대게 실행계획 상에서 

   맨 마지막 단계에 처리된다. 만약 서브쿼리 필터링을 먼저 처리했을 때 

   다음 수행 단계로 넘어가는 로우 수를 크게 줄일 수 있다면 성능은 그만큼 향상된다. 

   Pushing 서브쿼리는 이처럼 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 

   강제하는 것을 말하며, 이를 제어하기 위해 사용하는 옵티마이저 힌트가 push_subq이다. 

 - pushing 서브쿼리는 unnesting  되지 않는 서브쿼리에만 작동한다는 사실을 기억할 필요가 

   있다. 따라서 push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 

   사용방법이다. 

 - 9i 와 10g 사이에 push_subq 힌트를 기술하는 위치가 바뀌었다. 

    ● 9i 는 메인 쿼리에서 힌트 

    ● 10g부터 서브 쿼리에 힌트 작성

 - 서브쿼리가 조인으로 풀릴 때 서브쿼리에서 참조하는 테이블이 먼저 드라이빙되도록 

   제어할 목적으로 push_subq 힌트를 사용한다고 잘못 알고 있는 사람들이 있는데, 

   서브쿼리가 조인으로 풀린다는 것은 Unnesting 되었다는 뜻인데, Pushing 서브쿼리는 

   Unnesitng 되지 않은 서브쿼리의 처리 순서를 제어하는 기능이다. 

 * 여기서 부터 골치(?)가 아파진다. 

   - 서브쿼리 unnesting 은 비용기반 쿼리 변환이다 즉 unnesitng이 유리 하지 않는 경우도 

     발생할 수 있다는 이야기 이다. 그럼 언제 unnesting이 유리하지 않을까 ??

     여기서 메인 테이블에 from 절이 2개가 있고 두개의 테이블을 조인하고 서브쿼리 필터링 

     할 때와 서브쿼리 필터링을 하고 두 테이블이 조인하도록 하면서 조인량을 줄였다. 

     그런데 이 부분도 어찌 되었든 메인 테이블 중 1개와는 filter 작업이 발생할 것이다. 

     그렇다면 이 부분도 조인이 된다면 성능은 더 나아 질 수 있다. 

     이런 부분을 잘 생각해야 한다. 

쿼리 변환의 종류 

  ● 서브쿼리 Unnesting 

  ● 뷰 Merging 

  ● 조건절 Pushing

  ● 조건절 이행 

  ● 공통 표현식 제거 

  ● outer 조인을 Inner 조인으로 변환 

  ● 실체화 뷰 쿼리로 재작성 

  ● star 변환 

  ● outer 조인 뷰에 대한 조인 조건 Pushdown 

  ● or-expansion

 - 오라클 9i 기준 10가지 중 1~6까지는 휴리스틱 쿼리 변환에 해당, 7~10은 비용기반 쿼리 변환 

 - 10g부터는 서브쿼리 Unnesting과 뷰 Merging이 비용기반 쿼리 변환으로 전환됨 

   조건절 Pushing 중 조인 조건 pushdown도 비용기반 쿼리 변화으로 전화 

   나머지는 변환된 쿼리가 하상 더 나은 성능을 제공하므로 비용기반으로 전환이 불필요, 

   결론적으로 말해, 필요한 부분에 대해선 이미 비용기반으로 모두 개선이 이루어진 셈이다. 

쿼리 변환 두 가지 방식 

  ● 휴리스틱 쿼리 변환 : 결과만 보장된다면 무조건 쿼리 변환을 수행 

  ● 비용기반 쿼리 변환 : 변환된 쿼리의 비용이 더 낮을 때만 그것을 사요하고, 

                                   그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다. 

통계정보 수집 시 고려사항 

 - 시간 : 부하가 없는 시간대에 가능한 빠르게 수집을 완료해야 함 

 - 샘플 크기 : 가능한 적은 양의 데이터를 읽어야 함 

 - 정확성 : 전수 검사할 때의 통계치에 근접해야 함 

 - 안정성 : 데어터에 큰 변화가 없는데 매번 통계치가 바뀌지 않아야 함 

통계 수집 정책 수립은 필수 

 - 통계를 수집할 필요가 없는 오브젝트에 대해서는 lock 옵션으로 통계정보를 고정할 수 있다.

 - 통계정보에 영향을 받아선 안 되는 중요한 일부 핵심 프로그램에 대해선 옵티마이저 힌트를 

   적용해 실행계획을 고정시키는 것이 최선이다. 

 - 통계정보 변화 때문에 애플리케이션 성능에 심각한 문제가 발생했을 때를 대비해 가장 

   안정적이었던 최근 통계정보를 항상 백업해 두기 바란다. 

DBMS_STATS 

 - 통계정보 수집을 위해 오랫동안 사용돼 온 Analyze 명령어를 버리고 이제는

   dbms_stats가 더 정요하게 통계를 계산해 내기 때문이여, 특히 파티션 테이블/인덱스일 때는 

   반드시 dbms_stats를 사용해야 한다. 

 - analyze 명령어를 사용해야 하는 경우 

    ● freelist 블록 정보 ( 그러나 요즘 대부분 ASSM을 사용하므로 freelist 정보는 필요 없다. )

    ● 테이블별로 로우 체인(또는 로우 마이그레이션) 발생 현황 진단  

컬럼 히스토그램 수집 

 - 히스토그램은 이를 수집하고 관리하는 비용이 만만치 않다. 따라서 필요한 컬럼에만 

   히스토그램을 수집해야 하며, 조건절에 자주 사용되면서 편중된 데이터 분포를 갖는 컬럼이 

   주 대상이다. 

 - 인덱스 컬럼에만 히스토그램이 필요한것이 아니라 테이블을 엑세스하고 나서의 최종 선택도를 

   계산할 때는 인덱스가 없는 조건절 컬럼의 선택도도 인자로 사용되고, 그렇게 구해진 선택도에

   따라 다른 집합과의 조인 순서 및 조인 방식이 결정되기 때문에 히스토그램이 필요하다. 

 - 히스토그램이 불필요한 컬럼 

    ● 컬럼 데이터 분포가 균일 

    ● Unique하고 항상 등치조건으로만 검색되는 컬럼

    ● 항상 바인드 변수로 검색되는 컬럼 

데이터 샘플링

 - 샘플링 비율을 높일수록 통계정보의 정확도는 높아지지만 통계정보를 수집하는 데 더 많은 

   시간이 소요된다. 반대로 샘플링 비율을 낮추면 정확도는 다소 떨어지지만 더 효율적이고 

   빠르게 통계를 수집할 수 있다. 

 - 블록 단위 샘플링이 더 빠르고 효율적이긴 하지만 데이터분포가 고르지 않을 때 정확도가 

   많이 떨어진다. 기본 값은 로우 단위 샘플링이다. 

 - 샘플링 방식을 사용하면 매번 통계치가 다르게 구해질 수 있고 이는 실행계획에 영향을 미쳐 

   SQL 성능을 불안정하게 만든다. 

 - 특히 컬럼에 Null 값이 많거나 데이터 분포가 고르지 않을 때 그렇다. 

   null 값을 제외한 로우 수, Distinct value 개수는 컬럼 분포가 고르지 않을 때 샘플링 비율에 

   의해 영향을 크게 받는다. 

 - 해시 기반 알고리즘으로 NDV 계산 - 11g 

    ● 오라클 11g는 해시 기반의 새로운 알고리즘을 고안, 대용량 파티션 또는 테이블 전체를 

        스캔하더라도 기존에 샘플링 방식을 사용할 때보다 오히려 빠른 속도를 낼 수 있게 

        되었다. 소트를 수행하지 않아, 전체를 대상으로 NDV를 구하므로 정확도는 당연히 100%에 

        가깝다. 빠르고, 정확하면서도 안정적인 통계정보를 구현할 수 있게 된 것이다.

파티션 테이블 통계 수집 

 - 파티션 테이블일 때 오라클은 테이블 레벨 통계와 파티션 레벨 통계를 따로 관리한다. 

     ● 파티션 레벨 통계 : Static Partition Pruning이 작동할 때 사용된다. 결합 파티션일 때는 

                                   서브파티션 레벨로 통계를 관리할 수도 있다. 

     ●테이블 레벨 통계 : Dynamic Partition Pruning이 작동할 때 사용된다. 쿼리에 바인드 

                                   변수가 사용됐거나, 파티션 키에 대한 조건절이 없을 때도 

                                   테이블 레벨 통계가 사용된다. 

 - dbms_stats는 global 통계를 위한 쿼리를 별도로 수행하는 반면, analyze는 파티션 통계를 

   가지고 global 통계를 유추하므로 부정확하다. 

 - dbms_stats은 global 통계를 위한 쿼리를 별도로 수행하는 반면, 

   analyze는 파티션 통계를 가지고 global 통계를 유추하므로 부정확하다. 

 - dbms_stats 패키지 사용 시 granularity 옵션 

    global : 테이블 레벨 통계 수집  

    partiton : 파티션 레벨 통계 수집

    subpartition : 서브 파티션 레벨 통계 수집 

    global and partition : 테이블과 파티션 레벨 통계 수집 

    all : 테이블, 파티션, 서브 파티션 레벨 통계 수집

    auto : 파티션 유형에 따라 오라클이 결정 

 - dba/all/user_tables에서 볼 수 있는 항목 중 global_stats은 테이블 레벨 통계 수치들이 

   어떻게 구해졌는지를 알려준다. 

   (즉, 테이블 레벨 통계를 따로 수집했다면 'YES', 파티션 통계를 이용해 추정했다면 'NO'로 표시)

 - 10g 이하 버전을 사용하고 있다면, 아래처럼 최근 파티션만 통계를 수집하고 나서 테이블 

   전체 통계를 한 번 더 수행하는 방식을 사용하는 것이 효과적이다. 테이블마다 프로시저를 

   두 번 호출하도록 스크립트를 작성하는 것이 성가시더라도 어쩔 수 없다. 

 - NDV를 제외한 Incremental Global 통계 - 10.2.0.4 

    ● 'approx_global and partition' 옵션이 'global and partition'과 다른 점은, 

        테이블 통계를 위한 쿼리를 따로 수행하지 않고 파티션 레벨 통계로부터 집계한다는 데에 

        있다. 테이블 레벨 컬럼 히스토그램도 파티션 레벨로부터 집계한다. 단, 컬럼 NDV와 

        인덱스의 Distinct Key 개수는 제외된다. 

    ● unique 인덱스나 파티션 키 컬럼이 있으면 NDV도 같이 갱신된다. 

 - NDV를 포함한 호나전한 Incremental Global 통계 - 11g 

      11에선 파티션 레벨 NDV를 이용해 Global NDV를 정확히 구할 수 있는 방법까지 제공 

      ( 이 기능은 기본적으로 비활성화돼 있으며, 프로시저를 호출해 필요한 테이블별로 활성화 )

인덱스 통계 수집 

 - 대용량 테이블이어서 샘플링 비용을 지정하면 인덱스 통계까지도 같은 비율이 적용된다

   ( 인덱스는 통계 수집에 걸리는 시간은 매우 짧아 굳이 샘플링 방식을 사용할 필요가 없다 ) 

 - 그럴 때는 테이블 통계만 샘플링 방식을 사용하고, 인덱스는 전수 검사하도록 각기  

   통계를 수집해 주는 것이 좋다. 

 - 10g부터는 인덱스를 처음 생성하거나 재생성할 때는 인덱스 통계가 자동 수집되며 

   '_optimizer_compute_index_stats' 파라미터를 통해 설정을 변경할 수 있다. 

캐싱된 커서 Invalidation 

 - no_invalidation 옵션을 어떻게 지정하느냐에 따라 통계를 수집한 테이블과 관련된 SQL 커서의 

   무효과 시점이 달라진다. 

      false : 통계정보 변경 시 관련된 SQL 커서들이 즉시 무효화된다.

      true  : 통계정보 변경 시 관련된 SQL 커서들을 무료화하지 않는다. 

      dbms_stats.auto_invalidate : 통계정보 변경 시 관련된 SQL 커서들을 한꺼번에 무료화하지 

                                             않고 정해진 시간 동안 조금씩 무효화한다. 

 - 9i는 기본 값이 false 10g에서 dbms_stats.auto_invalidate로 바뀌었다. 

 - 이 기능을 제어하는 파라미터는 _optimizer_invalidation_period이고, 기본 값은 18,000초다 

   ( 즉, 늦어도 5시간 이내에는 관련 SQL 커서가 무효화된다. ) 

자동 통계 수집 

 - 오라클 10g 부터 기본적으로 매일 밤 10시부터 다음날 아침 6시까지 모든 사용자 오브젝트에 

   대한 통계를 자동 수집하도록 Job이 등록돼 있다. 이 기능은 gather_stats_job에 의해 

   자동 수행되며, 통계정보가 없거나 통계정보 수집 후 DML이 많이 발생한 모든 오브젝트를 

   대상으로 한다. 

 - 자동 통계 수집 기능을 사용하지 않으려면 스케쥴러 job을 제거하면 된다. 

자동 통계 수집 기능 활용 가이드 

 - 중대형급 이상 데이터베이스를 관리한다면 10g에서 제공하는 자동 통계 수집 기능은 

   사용하지 않는 편이 좋겠다. 특히 Maintenance 윈도우 이내에 통계 수집이 완료되지 않는 

   경우가 생기면 시스템을 불안정한 상태에 빠뜨릴 수 있으므로 주의해야 한다. 

 - 될 수 있으면 정확하고 안정적인 통계정보를 수집할 수 있도록 별도의 스크립트를 

   준비하는 것이 좋다. 

I/O 비용 모델 

 - I/O 비용 모델에서 비용은 디스크 I/O Call 횟수를 의미한다. 

 - 인덱스를 경유한 테이블 엑세스 시에는 Single Block I/O 방식이 사용된다. 

   이는 읽게 될 물리적 블록 개수가 엑세스 비용과 일치한다. 

 - cost 

     = blevel + (리프 블록 수 x 유효 인덱스 선택도 ) + (클러스터링 팩터 x 유효 테이블 선택도)

Full Scan에 의한 테이블 엑세스 비용 

 - 테이블을 Full Scan 할 때는 HWM 아래쪽 블록을 순차적으로 읽어 들이는 과정에서 발생하는 

    I/O Call 횟수로 비용을 계산한다. 

 - Multiblock I/O 단위를 증가시킬수록 I/O Call 횟수가 줄고 예상 비용도 준다 

I/O 비용 모델의 비현실적인 가정 

 - Single Block I/O 와 Multiblock I/O는 비용이 같다. 

 - 캐싱 효과를 전혀 고려하지 않는다. 

 - 이를 보정하기 위한 8버전 부터 제공된 파라미터 

     ● optimizer_index_cost_adj 

          - 이 파라미터는 인덱스 탐색 비용을 조정하고자 할 때 사용하며, 설정할 수 있는 값의 

            범위는 1 ~ 10000 이다. 기본 값으로 설정된 100이란 수치는, 한 번의 I/O Call을 통해 

            single block read 방식으로 한 블록을 읽는 비용과 multiblock read 방식으로 

            여러 블록을 읽는 비용을 같게 평가하라는 뜻이다. 

          - 이 값을 낮게 설정할수록 옵티마이저는 테이블 스캔보다 인덱스를 이용한 

            테이블 엑세스를 선호하게 된다. 

     ● optimizer_index_caching 

          - NL 조인에서 inner 쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 

            파라미터이며, IN-List Iterator 방식으로 인덱스를 탐색할 때 읽게 되는 인덱스 블록 

            엑세스 비용에도 영향을 미친다. 

          - 설정할 수 있는 값의 범위는 0 ~ 100이며, 이 값을 높게 설정할수록 옵티마이저는 

            인덱스를 이용한 NL 조인을 선호하게 된다. 

CPU 비용 모델 

 - 모든 데이터베이스 오퍼레이션은 CPU를 사용하며, 경우에 따라서는 

   I/O 보다 성능에 더 큰 영향을 끼치기도 한다. 

 - 블록 I/O가 소량인데도 쿼리 수행 시간이 상당히 오래 걸리는 경우 

      ● 해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을스캔하는 부하가 심할때 

      ● 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 

          스캔할때 

      ● 버퍼를 Pin한 상태에서 같은 블록을 반복 엑세스할 때 

      ● 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때 

      ● 메모리 소트를 반복할 때

 - 다소 CPU 사용량이 증가하는 경우 

      ● 조건절 개수가 아주 많을 때 

      ● 조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때

 - CPU 비용 모델에서 비용계산식 

     cost = ( #SRds * sreadtim + #MRds *mreadtim + #CPUCycles / cpuspeed ) / sreadtim 

CPU 비용 

 - CPU 비용 모델에서 말하는 비용은, 쿼리의 예상 총 수행 시간을 single Block I/O 시간 단위로 

   표현한 것이라고 할 수 있다.   

히스토그램 유형

 - 오라클이 사용하는 히스토그램 

     ● 높이균형 히스토그램 

     ● 도수분포 히스토그램

 - 히스토그램을 생성하려면 컬럼 통계 수집 시 버킷 개수를 2 이상으로 지정하면 된다. 

 - 히스토그램 정보는 dba_histograms 또는 dba_tab_histograms 뷰를 통해 확인할 수 있다. 

    특히, 10g에서는 dba_tab_columns 뷰에 histogram 컬럼이 추가되면서 히스토그램 유형을 

    쉽게 파악할 수 있게 되었다. 

     ● FREQUENCY : 도수 분포 히스토그램 

     ● HEIGHT-BALANCED : 높이균형 히스토그램 

     ● NON : 히스토그램 생성하지 않음  

도수분포 히스토그램 

 - 도수분포 히스토그램은 컬럼 값마다 하나의 버킷을 할당한다. 사용자가 요청한 버킷 개수가 

   컬럼이 가진 값의 수보다 많거나 같을 때 사용되며, 최대 254개의 버킷만 허용하므로 

   값의 수가 254를 넘는 컬럼에는 이 히스토그램을 사용할 수 없다. 

 - 254개를 요청하더라도 값의 수만큼만 버킷을 할당하므로 정확한 히스토그램을 위해서라면 

   항상 254개를 요청하는 것이 좋다. 

 - 히스토그램 정보를 조회할 수 있는 뷰에는 아래 두 컬럼이 있다. 

     ● endpoint_value  : 버킷에 할당된 컬럼 값 

     ● endpoint_number : endpoint_value로 정렬했을 때, 최소 값부터 현재 값까지의 누적 수량

높이균형 히스토그램 

 - 높이균형 히스토그램에서는 버킷 개수보다 값의 수가 많기 때문에 하나의 버킷이 여러 개 

   값을 담당한다. 

 - 요청할 수 있는 최대 버킷 개수는 254개이므로 값의 수가 254개를 넘으면 무조건 히스토그램이 

   만들어진다.  (컬럼수보다 버킷을 적게 적어주어도 높이균형 히스토그램이 생성된다.)

 - 높이균형 히스토그램에서는 말 그대로 각 버킷의 높이가 같다. 각 버킷은 {1/(버킷 개수) x100}%

   의 데이터 분포를 갖는다. 

 - 빈도 수가 많은 값을 포함할 때는 두 개 이상의 버킷이 할당된다.  

 - 오라클은 popular value에 대한 카디널리티를 구할 때만 버킷에 의한 계산식을 사용하고 

   나머지는 미리 구해 놓은 density 값을 이용한다. 

 - popular value에 대한 선택도/카디널리티 계산 

     ● 조건절 값이 두 개 이상 버킷을 가진 popular value이면 아래 공식을 따른다. 

         선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)

 - non-popular value에 대한 선택도/카디널리티 계산 

     ● 카디널리티 = 총 로우 수 x 선택도 = 총 로우 수 x density

 - 바인드 변수 사용 시 카디널리티 계산 

     ● 바인드 변수 사용시 변수 값 입력이 나중에 되니 실행계획에서 카디널리티를 계산할 수가 

         없다. 그래서 컬럼 분포가 균일할 때는 문제될 것이 없지만 그렇지 않을 때는 실행 시점에 

         바인딩되는 값에 따라 최적이 아닌 실행계획일 수 있어 문제다. 

     ● 바인드 변수를 사용하면 정확한 컬럼 히스토그램에 근건하지 않고 카디널리티를 구하는 

         정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할 가능성이 

         커진다.

     ● 좋은 실행계획을 위해서라면 DW, OLAP, 배치 프로그램에서 수행되는 쿼리는 

         바인드 변수보다 상수를 사용하는 것이 좋고, 날짜 컬럼처럼 부등호, between 같은 

         범위 조건으로 자주 검색되는 컬럼일 때 특히 그렇다.

     ● OLTP성 쿼리이더라도 값의 종류가 적고 분포가 균일하지 않을 때는 상수 조건을 쓰는 것이 

         유용할 수 있다.    

결합 선택도 

 - 동적 샘플링은 소량의 데이터 샘플링을 통해 where 조건절에 사용된 두개 이상 컬럼의 결합 

   분포를 구하는 기능으로서, 동적 샘플링 레벨을 4 이상으로 설정할 때만 작동한다. 

    ( 힌트 사용 시 /*+ dynamic_sampling(4) */ 이렇게 사용 

 - 다중 컬럼 통계 11g에서는 다중 컬럼에 대한 히스토그램도 생성할 수 있게 되었다.