잡동사니 보물섬

파티션 Pruning 이란 

 - 파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 

   파티션 세그먼트를 엑세스 대상에서 제외시키는 기능이다. 파티션 테이블에 대한 쿼리나 

   DML을 수행할 때 극적인 성능 개선을 가져다주는 핵심 원리가 파티션 Pruning에 있다

기본 파티션 Pruning 

 - 기본 파티션 Pruning 기법 

    ● 정적 파티션 Pruning : 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동하며, 

                                       엑세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 

                                       특징이다. 실행계획의 Pstart 와 Pstop 컬럼에 엑세스할 

                                       파티션 번호가 출력된다. 

    ● 동적 파티션 Pruning : 파티션 키 컬럼을 바인드 변수로 조회하면 쿼리 최적화 시점에는

                                       엑세스할 파티션을 미리 결정할 수 없다. 실행 시점이 돼서야 

                                       사용자가 입력한 값에 따라 결정되며, 실행계획의 Pstart와 

                                       Pstop 컬럼에는 'KEY'라고 표시된다. NL 조인할 때도 Inner 테이블이 

                                       조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다. 

 - 파티션 컬럼에 IN-List 조건을 사용하면 상수 값이더라도 아래처럼 'KEY(I)'라고 표시 된다. 

 - NL조인에서도 inner 테이블일 때 동적 Pruning이 일어난다. 

파티션 Pruning 기능에 따른 I/O 수행량 비교 

 - 주 파티션과 서브 파티션이 있을 때 서브 파티션 키 컬럼을 함수로 가공하면 서브 파티션 

   Pruning을 사용할 수 없다. 

 - 주 파티션 키 컬럼을 가공함으로써 서브 파티션 키 컬럼에 묵시적 형변환이 일어난다. 

동적 파티션 Pruning 시 테이블 레벨 통계 사용 

 - 동적 파티션 Pruning이 일어나면 쿼리 최적화에 테이블 레벨 통계가 사용된다. 

   반면, 정적 파티션 Pruning일 때는 파티션 레벨 통계가 사용된다. 

 - 테이블 레벨 통계는 파티션 레벨 통계보다 다소 부정확하기 때문에 옵티마이저가 가끔 

   잘못된 실행계획을 수립하는 경우가 생기며, 이는 바인드 변수 때문에 생기는 대표적인 

   부작용 중 하나다. 

서브쿼리 Pruning 

 - 조인에 사용되는 고급 파티션 Pruning 기법으로는 아래 두 가지가 있다. 

    ● 서브 쿼리 Pruning (8i ~ ) 

    ● 조인 필터 Pruning (11g ~ )

 - 오라클은 Recursive 서브쿼리를 이용한 동적 파티션 Pruning을 고려한다. 

   이른바 '서브쿼리 Pruning'이라고 불리는 메커니즘으로서, 엑세스해야할 파티션 번호 목록을 

   특별한 쿼리를 통해 구해 이를 이용해 필요한 파티션만 스캔할 수 있다. 

 - KEY(SQ)에서 SQ는 SubQuery를 뜻한다. 이 방식으로 파티션 Pruning 하려면 

   드라이빙 테이블을 한 번 더 읽게 되므로 경우에 따라 총 비요이 오히려 증가할 수 있다. 

   따라서 서브쿼리 Pruning 적용 여부는 옵티마이저가 비용을 고려해 내부적으로 결정한다. 

 - 옵티마이저 결정에 영향을 미치는 Hidden 파라미터는 3가지가 있다. 

    ● _subquery_pruning_cost_factor 

    ● _subquery_pruning_reduction

    ● _subquery_pruning_enabled 

        (위 파라미터의 자세한 설명은 p.649 참조) 

 - 제거될 것으로 예상되는 파티션 개수가 상당히 (기본 값에 의하면 50%) 많고, where 조건절을 

   가진 드라이빙 테이블이 파티션 테이블에 비해 상당히 작을 때만 서브쿼리 Pruning이 작동 

 - 참고로, 아래와 같이 설정하면 옵티마이저에 의해 계산된 비용과 상관없이 항상 서브쿼리 

   Pruning을 실시한다. 

    ● _subquery_pruning_cost_factor =1 

    ● _subquery_pruning_reduction = 100

조인 필터 Pruning 

 - 서브쿼리 Pruning은 드라이빙 테이블을 한 번 더 엑세스하는 추가 비용이 발생한다. 

   그래서 11g부터 오라클은 블룸 필터 알고리즘을 기반으로 한 조인 필터 Pruning 방식을 

   도입하였다. 

 - 블룸 필터는 교집합이 작을 때 큰효과를 발휘한다. 

   * 해당 알고리즘은 p.648을 참고한다. 

 - 블룸 필터 알고리즘에서 false positive를 줄이는 방법은 더 많은 비트를 할당하거나 

   더 많은 해시 함수를 사용하면 false positive 발생 가능성은 줄어든다. 

   공간/시간의 효율성과 false positive 발생 가능성은 서로 트레이드 오프 관계이므로 

   적정한 개수의 비트와 해시 함수를 사용하는 것이 과제다. 

조인 필터 (=블룸 필터) Pruning 

 - '조인 필터 Pruning' 또는 '블룸 필터 Pruning'이라고 부르는 이 기능은 11g부터 적용되기 

   시작했으며 파티션 테이블과 조인할 때 , 읽지 않아도 되는 파티션을 제거하는 것이다. 

 - 실행계획에서 part join filter create 는 블룸 필터를 생성하는 단계 , 

    partition range join-filter는 블룸 필터를 이용해 파티션 Pruning 하는 단계를 나타낸다. 

 - 블룸필터의 역활은, 교집합 아님이 확실한 원소를 찾는 데에 있다. 이 알고리즘을 사용하는 

   조인 필터 Pruning도 조인 대상 집합을 확실히 포함하는 파티션을 찾는 게 아니라, 확실히 

   포함하지 않는 파티션을 찾는다. 

 - 서브쿼리 Pruning 과 비교해 보면, 드라이빙 테이블이 클수록 조인 필터 Pruning이 유리하다.

SQL 조건절 작성 시 주의사항     

 - 파티션 Pruning을 위해서라도 like 보다는 가급적 between 연산자를 이용해 정확한 검색구산을

   지정하는 것이 바람직하다. 

테이블 파티셔닝 

 - 파티셔닝은 테이블과 인덱스 데이터를 파티션 단위로 나누어 저장하는 것을 말한다. 

   테이블을 파티셔닝하면 하나의 테이블일지라도 파티션 키에 따라 물리적으로는 별도의 

   세그먼트에 데이터가 저장되며, 인덱스도 마찬가지다. 

 - 파티셔닝이 필요한 관리적 측면과 성능적 측면 

     ● 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 

     ● 성능적 측면 : 파티션 단위 조회 및 DML 수행 

 - 파티셔닝도 클러스터, IOT와 마찬가지로 관련 있는 데이터가 흩어지지 않고 물리적으로 

   인접하도록 저장하는 클러스터링 기술에 속한다. 

   클러스터와 다른 점은 세그먼트 단위로 모아서 저장한다는 것이다. 클러스터는 블록 단위로 

   데이터를 모아 저장한다. IOT는 정렬된 순서로 데이터를 저장하는 구조인데, IOT와 파티셔닝을 

   조합함으로써 놀라운 성능 효과를 얻을 수 있다. 

파티션 기본 구조 

 - 수동 파티셔닝 

   ● 파티션 테이블이 제공되기 전(7.3), 파티션 뷰를 통해 파티션 기능을 구현

        이를 '수동 파티셔닝'이라고 부른다. 

   ● 파티션 뷰의 핵심 기능은 뷰 쿼리에 사용된 조건절에 부합하는 테이블만 읽는다는 데에 

       있으며, 이를 '파티션 Pruning이라고 한다. 

   ● 10g 나 11g에서는 파티션 뷰에서 바인드 변수 사용 시 파티션 Pruning이 제대로 작동하지

       않는 것으로 확인되었다고 하는데, 이 부분은 개인적인 테스트를 해보아야 할 것 같다. 

  - 파티션 테이블 

   ● 오라클 8에서 도입된 파티션 테이블 기능을 이용하면 훨씬 간편하게 파티션을 저의할 수 

       있을 뿐 아니라 기능적으로도 더 낫다.

   ● partition by 절은 파티션 뷰의 Base 테이블에 체크 제약을 설정하는 것과 같은 역할을 한다. 

   ● 인덱스를 만들 때도 local 옵션을 지정했으므로 각 파티션별로 개별적인 인덱스가 만들어져, 

       파티션 뷰 Base 테이블에 각각 인덱스를 만든 것과 같은 모습이다. 

   ● 버전별 테이블 파티션 유형

        - Range 파티셔닝 (8 이상) 

        - 해시 파티셔닝 (8i 이상) 

        - Range-해시 파티셔닝 (8i 이상)

        - 리스트 파티셔닝 (9i 이상)          

        - Range-리스트 파티셔닝 (9i 이상) 

        - Range-Range 파티셔닝 (11g 이상) 

        - 리스트-해시 파티셔닝 (11g 이상) 

        - 리스트-리스트 파티셔닝 (11g 이상) 

        - 리스트-Range 파티셔닝 (11g 이상)  

 - Range 파티셔닝 

   ● 오라클 8 버전부터 제공된 가장 기초적인 파티셔닝 방식으로서, 

       주로 날짜 컬럼을 기준으로 한다. 

   ● 파티션 키로는 하나 이상의 컬럼을 지정할 수 있고, 최대 16개까지 허용된다. 

   ● DB 관리자의 실수로 신규 파티션 생성을 빠뜨리면 월초 또는 연초에 데이터가 입력되지 

       않는 에러가 발생하므로, maxvalue 파티션을 반드시 생성해 두는 것이 좋다.

   ● 참고로 11g 부터는 Range 파티션을 생성할 때 interval 기준으로 정의함으로써 

       정해진 간격으로 파티션이 자동 추가 되도록 할 수 있다.

 - 해시 파티셔닝 

     ● 해시 파티셔닝은 Range 파티셔닝에 이어 오라클 8i 버전부터 제공되기 시작했다. 

         파티션 키에 해시 함수를 적요한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 

         저장해 두는 방식이며, 주로 변별력이 좋고 데이터 분포가 고른 컬럼을 

         파티션 기준 컬럼으로 선정해야 효과적이다. 

     ● 검색할 때는 조건절 비교 값에 해시 함수를 적용해 읽어야 할 피타션을 결정하며, 

         해시 알고리즘 특성상 등치(=) 조건 또는 IN-List 조건으로 검색할 때만 

         파티션 Pruning이 작동한다. 

     ● 오라클은, 특정 파티션에 데이터가 몰리지 않도록 하려면 파티션 개수를 2의 제곱으로 

        설정할 것을 권고한다. 이 규칙을 따르더라도 파티션 키 컬럼의 Distinct Value 개수가 

        적다면 데이터가 고르게 분산되지 않을 가능성이 높으므로, 이때는 리스트 파티션을 

        이용해 파티션 기준을 사용자가 수동으로 결정해 주는 것이 낫다. 

     ● 데이터가 모든 파티션에 고르게 분산돼 있다면, 더구나 각 파티션이 서로 다른 디바이스에

         저장 돼 있다면 병렬 I/O 성능을 극대화할 수 있다. 반대로 말하면, 데이터가 고르게 

         분산되지 않을 때는 병렬 쿼리 효과가 반감된다. 

     ● 해시 파티셔닝의 DML 경합 분산 

         - 동시에 입력이 많은 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로도 

           해시 파티셔닝을 사용한다. 대용량 거래 테이블일수록 DML 발생량이 많아 경합 발생 

           가능성도 그만큼 크다.

         - 데이터가 입력되는 테이블 블록에도 경합이 발생할 수 있지만, 그보다는 입력할 블록을 

           할당받기 위한 Freelist 조회 때문에 세그먼트 헤더 블록에 대한 경합이 더 자주 발생한다. 

           그럴때 테이블 해시 파티셔닝하면 세그먼트 헤더 블록에 대한 경합을 줄일 수 있다. 

         - Right Growing 인덱스도 자주 경합 지점이 되곤 하는데, 

           이때도 인덱스를 해시 파티셔닝함으로써 경합 발생 가능서을 낮출 수 있다. 

         - 경합 분산이나 병렬 쿼리 성능 향상, 두 가지 모두 트랜잭션이 많이 발생하는 대용량 거래

           테이블일 대라야 효과가 있다. 단일 해시 파티셔닝보다는 Range와 해시를 조합한 

           결합 파티셔닝을 주로 사용하게 되는 이유가 여기에 있다. 

리스트 파티셔닝 

  - 오라클 9i 버전부터 제공되기 시작한 파티셔닝은, 사용자에 의해 미리 정해진 그룹핑 

    기준에 따라 데이터를 분할 저장하는 방식이다. 

  - Range 파티션에선 값의 순서에 따라 저장할 파티션이 결정되지만, 리스트 파티션에서는 

    순서와 상관없이 불연속적인 값의 목록으로써 결정된다. 

  - Range, 해시 파티셔닝과 달리 리스트 파티셔닝에는 단일 컬럼으로만 파티션 키를 지정할 수 

    있다. 그리고 Range 파티션에 maxvalue 파티션을 반드시 생성하라고 권고한 것과 같은 

    이유로, 리스트 파티션에도 default 파티션을 생성해 두어야 안전하다. 

   * range 와 구분할 때 

     range : 범위 

    리스트 : in과 같은 개념 

    으로 생각하면 편할 듯 하다. 

결합 파티셔닝 

 - 결합 파티셔닝을 구성하면 서브 파티션마다 세그먼트를 하나씩 할당하고, 

   서브 파티션 단위로 데이터를 저장한다. 즉, 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 

   서브 파티션 키에 따라 최종적으로 저장할 위치를 결정한다. 

 - 11g부터는 주 파티션이 해시 방식이 아닌 모든 조합을 지원한다. 

    ● Range + 해시 결합 파티셔닝 

        - 대용량 거래 테이블을 이와 같이 파티셔닝한다면 Range 파티셔닝과 해시 파티셔닝의 

          장점을 둘 다 누릴 수 있으며, 각각의 장점에 대해서는 이미 설명하였다. 

    ● Range + 리스트 결합 파티셔닝   

        - Rnage + 리스트 결합 파티셔닝은 논리적으로 하나의 테이블이지만 물리적으로 

          별도 세그먼트에 나눠 저장하기 때문에 성능을 떨어뜨리지 않으면서 통합의 이점을 

          누릴 수 있는 것이다.

11g에 추가된 파티션 유형

 - Reference 파티셔닝 

    ● 11g에서 부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝하는 기능이 

        도입되었는데, 이를 'Reference 파티션'이라고 부른다. 

    ● 이 기능을 사용하려면 자식 테이블의 컬럼에 not null과 FK 제약이 설정돼 있어야 한다. 

 - Interval 파티셔닝 

    ● 11g 부터는 Range 파티션을 생성할 때 아래와 같이 interval 기준을 저의함으로써 

        정해진 간격으로 파티션이 자동 추가되도록 할 수 있다. 

  - 이 외에도 시스템 파티셔닝, 가상(virtual) 컬럼 파티셔닝 등이 11g에 추가되었다. 

Sort Area 크기 조정 

 - Sort Area 크기 조정을 통한 튜닝의 핵심은, 디스크 소트가 발생하지 않도록 하는 것을 

   1차 목표로 삼고 불가피할 때는 Onepass 소트로 처리되도록 하는 데에 있다. 

PGA 메모리 관리 방식의 선택 

 - 데이터 정렬, 해시 조인, 비트맵 머지, 비트맵 생성 등을 위해 사용하는 메모리 공간을 

   'Work Area'라고 부르며, sort_area_size, hash_area_size, bitmap_merge_area_size, 

   create_bitmap_area_size 같은 파라미터를 통해 조정한다. 

자동 PGA 메모리 관리 방식 하에서 크기 결정 공식 

 - auto 모드에서 단일 프로세스가 사용할 수 있는 최대 Work Area 크기는 인스턴스 기동 시 

   오라클에 의해 내부적으로 결정되며, _smm_max_size 파라미터를 통해 확인 가능하다. 

 - SGA는 sga_max_size 파라미터로 설정된 크기만큼 공간을 미리 할당한다. 이와 대조적으로, 

   PGA는 자동 PGA 메모리 관리 기능을 사용한다고 해서 pga_aggregate_target 크기 만큼의 

   메모리를 미리 할당해 두지는 않는다. 이 파라미터는 workarea_size_policy를 auto로 설정한 

   모든 프로세스들이 할당받을 수 있는 Work Area의 총량을 제한하는 용도로 사용된다. 

수동 PGA 메모리 관리 방식으로 변경 시 주의사항 

 - manual 모드로 설정한 프로세스는 pga_aggregate_target 파라미터의 제약을 받지 않는다. 

   따라서 manual 모드로 설정한 많은 세션에서 Sort Area와 Hash Area를 아주 큰 값으로 

   설정하고 실제 매우 큰 작업을 동시에 수행한다면 가용한 물리적 메모리가 고갈돼 

   페이징이 발생하면서 시스템 전체 성능을 크게 떨어뜨릴 수 있다. 

 - 특히, workarea_size_policy 파라미터를 manual로 설정한 상태에서 병렬 쿼리를 사용하면 

   각 병렬 슬레이브 별로 sort_area_size 크기만큼 Sort Area를 사용할 수 있다는 사실을 

   바드시 기억해야 한다.  

 - sort order by나 해시 조인등을 수행할 때는 사용자가 지정한 DOP의 2배수만큼의 병렬 Slave가

   떠서 작업을 수행하므로 위 쿼리가 수행되는 동안 128개의 프로세스가 각각 최대 2GB의 

   Sort Area를 사용할 수 있다. 

 - 따라서 manual 모드에서 병렬 Degree를 크게 설정할 때는 sort_area_size와 hash_area_size를 

   반드시 확인해야 한다. 

PGA_AGGREGATE_TARGET의 적정 크기 

 - 오라클의 권고 값 

     OLTP 시스템 : ( Total Physical Memory x 80% ) x 20 %  

     DSS 시스템 : ( Total Physical Memory x 80% ) x 50 % 

 - 이것은 일반적인 권고사하일 뿐이며 애플리케이션 특성에 따라 모니터링 결괄르 바탕으로 

   세밀한 조정이 필요하다. 대부분 Optimal 소트 방식으로 수행되고 나머지 일부만 

   Onepass 소트 방식으로 수행되는 것을 목표로 삼아야 한다.