잡동사니 보물섬

Sort Order by 대체 

 - where A=값 order by B 일 때 A+B 인덱스를 사용한다면 sort order by 연산을 대체할 수 있다. 

   ( A 만 있거다 A와 B 사이에 다른 컬럼이 있으면 sort order by 연산을 대체 할 수 없다. ) 

 - 물론, 소트해야 할 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 수 있는 업무에서만 

   이 방식이 유리하다. 인덱스를 스캔하면서 결과집합을 끝까지 Fetch 한다면 오히려 I/O 및 

   리소스 사용 측면에서 손해다. 대상 레코드가 소량일 때는 소트가 발생하더라도 

   부하가 크지 않아 개선 효과도 미미하다. 

Sort Group By 대체 

 - group 절에 사용 된 컬럼이 선두인 결합 인덱스나 단일 컬럼 인덱스를 사용한다면 

   sort group by 연산을 대체할 수 있다. 

   ( 'sort group by nosort' 가 실행계획으로 표시 된다. ) 

 - 이처럼 인덱스를 이용한 nosort 방식으로 수행될 때는 group by  오퍼레이션에도 불구하고 

   부분범위처리가 가능해져 OLTP환경에서 매우 극적인 성능 개선 효과를 얻을 수 있다. 

인덱스가 소트 연산을 대체하지 못하는 경우 

 - 인덱스 사용보다 full table scan이 효과적일 때 

 - 인덱스를 사용할 해당 컬럼에 not null 제약이 추가 되지 않았을 때 

    ( group by도 마찬가지다. group by nosort를 우해 사용하려는 인덱스가 단일 컬럼 

      인덱스일 때는 해당 컬럼에 not null 제약이 설정돼 있ㅇ야 제대로 작동한다. ) 

 - order by 절에 nulls first 구문을 사용하는 경우 

   ( 마찬가지 원리로 인덱스를 뒤쪽부터 읽으려고 index_desc 힌트를 쓰면 null 값들이 

    먼저 출력 될텐대, null 값들을나중에 출력하려고 nulls last 구문을 사용하면 소트가 발생한다.)

소트가 발생하지 않도록 SQL 작성 

 - 데이터 모델 측면에선 이상이 없는데, 불필요한 소트가 발생하도록 SQL을 작서하는 경우 

   예시 1) Union을 사용하면 중복제거를 하려고 sort unique 연산을 하게 되는데 union all을 

              사용해도 되는 경우 union을 사용하여 불필요한 sort unique 연산을 하게 되는 경우 

   예시 2) distinct를 사용하는 경우도 매우 흔한데, 대부분 exists 서브쿼리로 대체함으로써 

              소트 연산을 없앨 수 있다.  

데이터 모델 측면에서의 검토 

 - 튜닝 과정에서 조사된 SQL에 group by, union, distinct 같은 연산자가 불필요하게 많이 

   사용되는 패턴을 보인다면 대게 데이터 모델이 잘 정규화되지 않았음을 암시한다. 

사례 1 

 - M:M 관계를 갖도록 테이블을 설계한 경우가 대표적이다. 

 - 잘못된 데이터 모델링으로 인하여 group by를 사용하지 않아도 되는 경우에도 

   M:M으로 표시되어서 불필요한 group by를 사용하고 있었음  

사례 2

 - PK 외에 관리할 속성이 아예 없거나 소수일 때, 테이블 개수를 줄인다는 이유로 자식 테이블에

   통합시키는 경우를 종종 볼 수 있다. 

사례 3 

 - 순번 컬럼을 증가시키면서 순서대로 데이터를 적재하는 점이력 모델은 선분이력에 비해 

   DML 부하를 최소화할 수 있는 장점이 있지만, 대량 집합의 이력을 조회할 때 소트를 많이 

   발생시키는 단점이 있다.특히, 마지막 이력만 조회하는 업무가 대부분 일 때 비효율이 크다. 

 - 비효율이 있다고 해서 모델이 잘못됐다고 말할 수는 없지만 어떤 데이터 모델을 선택하느냐에 

   따라 성능에 차이가 생길 수 있음을 잘 보여준다. 

   * 나는 아직도 점 이력모델이 선분이력에 비해 DML 부하를 최소할 수 있다는 것이 

      어떠한 케이스에서 설명 될 수 있는지 모르겠다. 

Sort Aggregate 

 - Sort aggregate는 아래처럼 전체 로우를 대상으로 집계를 수행할 때 (집계함수 사용)나타나는, 

   'sort'라는 표현을 사용하지만 실제 소트가 발생하지는 않는다. 

Sort Order by 

 - 데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다. 

Sort Group by 

 - sort group by는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다. 

   ( group by 와 order by 같이 사용 시) 

Hash Group by와 비교 

 - 10gR2에서 Hash group by 방식이 도입되면서, order by절을 함께 명시하지 않으면 

   대부분 hash group 방식으로 처리된다. 

Group by 결과의 정렬 순서 

 - 오라클은 9i부터 이미 group by 결과가 보장되지 않는다고 여러 문서를 통해 

   공식적으로 밝히고 있다. 

 - 실행계획에서 'sort group by'의 의미는 '소팅 알고리즘을 사용해 값을 집계한다'는 뜻일 뿐 

   결과의 정렬을 의미하지는 않는다. 물론 쿼리에 order by절을 명시하면 정렬 순서가 

   보장 되지만, 이때도 실행계획은 똑같이 'sort group by'로 표시된다. 

 - 10gR2에서 sort group by에서 sort group by가 나타나는 경우는 distinct , count 함수를 

   만났을 땐 항상 sort group by 방식으로 수행한다. 여기서도 결과는 정렬되지 않는다. 

Sort Unique 

 - Unnesting된 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없다면, 그리고 세미 조인으로 

   수행되지도 않느다면 메인 쿼리와 조인되기 전에 sort unique 오퍼레이션이 먼저 수행된다. 

   ( 여기서 M쪽 집합이 Unnesitng 된다고 했는데 이 튜닝을 하기 전에 고려해야 할 것은 

     M쪽 집합 서브쿼리가 Unnesting 되면 조인 형식이 되어서 경로가 바뀔 수 있다. )

 - 만약 PK/Unique 제약 또는 Unique 인덱스를 통해, Unnesting 된 서브쿼리의 Uniqueness가 

   보장된다면 sort unique 오퍼레이션은 생략된다. 

 - union, minus, intersect 같은 집합연산자를 사용할 때오 아래와 같이 sort unique 오퍼레이션이 

   나타난다. 

 - distinct 연산을 위해서도 sort unique 오퍼레이션이 사용된다. 

 - 오라클 10gR2부터는 group by처럼 distinct 연산에서도 order by를 생략하면 

   hash unique 방식으로 수행된다. 

 - 참고로, 10gR2에서 아래처럼 _convert_set_to_join 파라미터를 true로 설정하면 minus, 

   intersect 같은 집합 연산에 hash unique 오퍼레이션을 사용한다. 즉, 조인을 통해 두 집합을 

   연결하고 나서 중복을 제거하는 방식이다. 

  * 이 부분 연관하여 4장 11절 내용이 언급되는데 4장 11절은 null 허용 컬럼이기 때문에 

     그런 쿼리가 되는 건지와 distinct가 결과 출력 모양상 필요한 것인지 테스트할 필요가 있다. 

Sort Join 

 - sort join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다. 

 - outer 테이블에 pk 제약이나 unique 제약이 있을 때는 sort가 한 번만 발생한다. 

Window Sort 

 - window sort는 분석함수를 수행할 때 나타난다. 

소트 튜닝이 필요한 이유 

 - 소트 오퍼레이션은 수행과정에서 CPU와 메모리를 많이 사용하고, 데이터량이 많을 때 

   디스크 I/O까지 일으킨다. 많은 서버 리소스를 사용하는 것도 문제지만 부분범위처리를 

   불가능하게 해 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인으로 작용하기도 한다. 

소트 수행 과정 

 - 오라클은 데이터 정렬 필요 시 PGA 메모리에 Sort Area를 할당하는데, 그 안에서 처리를 

   완료할 수 있는지 여부에 따라 소트를 두 가지 유형으로 나눈다. 

      메모리 소트 : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며,

                          'Internal Sort'라고도 한다. 

      디스크 소트 : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 

                         경우를 말하며, 'External Sort'라고도 한다. 

 - Sort Area 내에서 데이터 정렬을 마무리하는 것이 최적이지만 (-> optimal 소트), 

   양이 많을 때 정렬된 중간 결과집합을 Temp 테이블스페이스 Temp 세그먼트에 임시 저장한다.

   Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간 단계의 집합을 'Sort Run'이라고 부른다. 

   Sort Run 생서을 마쳤으면, 이를 다시 Merge해야 정렬된 최종 결과집합을 얻게 된다. 

 - 소트의 종류 

     Optimal 소트   : 소트 오퍼레이션이 메모리 내에서만 이루어짐 

     Onepass 소트  : 정렬 대상 집합이 디스크에 한 번만 쓰임

     Multipass 소트 : 정렬 대상 집합이 디스크에 여러 번 쓰임 

소트 오퍼레이션 측정 

  * 먼저 오라클은 Consistent 모드로 읽고 Current 모드로 갱신한다는 것을 염두해두자  

  * 대규모의 소트 오퍼레이션이 발생하면 db block gets가 발생한다. 

     ( 이유는 아직 모르겠다. ) 

 - 디스크 I/O 시 버퍼캐시를 경유하므로 일반적으로 디스크 블록 I/O 개수가 메모리 블록 I/O 

   개수를 초과할 수 없다. 그런데 physical reads 값이 consistent gets와 db block gets를 합한 

   값보다 훨씬 크다면, 디스스 소트 과정에서 발생한 디스크 I/O까지 physical reads에 

   포함됐기 때문일 수 있다. 또한 SQL을 최초 수행했을 시 하드 파싱 과정에서 발생한 

   I/O도 해당 값에 포함됐을 수 있다. 

Sort Area 

 - 데이터 정렬을 위해 사용되는 Sort Area는 소트 오퍼레이션이 진행되는 동안 

   공간이 부족해질때마다 청크 단위로 조금씩 할당된다. 세션마다 사용할 수 있는 최대 크기를 

   예전에는 Sort_area_size 파라미터로 설정하였으나, 9i부터는 새로 생긴 workarea_size_policy 

   파라미터를 auto로 설정하면 오라클이 내부적으로 결정한다. 

 - sort_area_retained_size는 데이터 정렬을 끝내고 나서 결과집합을 모두 Fetch할 때까지 

   유지할 Sort Area 크기를 지정한다. 

   참고로, 0으로 설정하면 Fetch가 완료될 때까지 Sort Area크기를 그대로 유지하겠다는 의미 

   (정렬작업 후 sort area를 반환하느냐 또는 해당 SQL의 fetch 후 sort area를 반환하느냐의 차이)   

   ● PGA    

       - PGA는 다른 프로세스와 공유되지 않는 독립적인 메모리 공간으로서, 

         래치 매커니즘이 필요 없어 똑같은 개수의 블록을 읽더라도 

         SGA 버퍼 캐시에서 읽는 것보다 훨씬 빠르다. 

   ● UGA 

       - 하나의 프로레스는 하나의 PGA를 갖는다. 

       - 하나의 세션은 하나의 UGA를 갖는다. 

       - PGA에는 세션과 독립적인 프로세스만의 정보를 관리한다. 

       - UGA에는 프로세스와 독립적인 세션만의 정보를 관리한다. 

       - 거의 대부분 전용 서버 방식을 사용하므로 세션과 프로세스는 1:1 관계고, 따라서 

         UGA도 PGA 내에 할당된다고 이해하면 쉽다. 

       - 다만 공유 서버 방식으로 연결할 때는 SGA에 할당된다. 후자 방식에는 구체적으로, 

         Large Pool이 설정됐을 때는 Large Pool에, 그렇지 않을 때는 Shared Pool에 할당된다. 

   ● CGA 

       - PGA에 할당되는 메모리 공간으로는 CGA도 있다. 오라클은 하나의 데이터베이스 Call을 

         넘어서 다음 Call까지 계속 참조되어야 하는 정보는 UGA에 담고, 

         Call이 진행되는 동안에만 필요한 데이터는 CGA에 담는다. 

       - CGA에 할당된 공간은 하나의 Call이 끝나자마자 해제돼 PGA로 반환된다. 

            ● CGA : Call이 진행되는 동안만 필요한 정보 저장 

            ● UGA : Call을 넘어서 다음 Call까지 계속 참조되는 정보 저장 

Sort Area 할당 위치 

 1. DML 문장 수행 시 발생하는 소트는 CGA에서 수행 

 2. SELECT 문장 수행 시 

    (1) 쿼리 중간 단계의 소트   

          CGA에서 수행, sort_area_retained_size 제약이 있다면 다음 단계로 넘어가기 전에 

          이 값을 초과하는 CGA 영역을 반환 

    (2) 결과 집합을 출력하기 직전 단계에서 수행하느 소트 

          ① sort_area_retained_size 제약이 있다면, CGA에서 소트 수행

              이 제약만큼의 UGA를 할당해 정렬된 결과를 담았다가 이후 Fetch Call에서 

              Array 단위로 전송 

          ① sort_area_retained_size 제약이 없다면, 곧바로 UGA에서 소트 수행 

 - CGA에 할당된 Sort Area는 하나의 Call이 끝나자마자 PGA에 반환된다. UGA에 할당된 

   Sort Area는 마지막 로우가 Fetch 될 때 비로소 UGA Heap에 반환되고, 거의 대부분 

   그 부모 Heap에도 즉각 반한된다. 

소트 튜닝 요약 

 - 소트 오퍼레이션은 메모리 집약적일뿐만 아니라 CPU 집약적이기도 하며, 

   데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 좌우하는 가장 중요한 요소다.

   특히, 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주요인으로 

   작용한다. 따라서 될 수 있으면 소트가 발생하지 않도록 SQL을 작성해야 하고, 

   소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다. 

 - 소트 튜닝 방안의 요약 

     ● 데이터 모델 측면에서의 검토 

     ● 소트가 발생하지 않도록 SQL 작성 

     ● 인덱스를 이용한 소트 연산 대체 

     ● Sort Area를 적게 사용하도록 SQL 작성 

     ● Sort Area 크기 조정 

조인 컬럼에 IS NOT NULL 조건 추가 

 - 조인 컬럼에 is not null 조건을 추가하면 필요시 불필요한 테이블 엑게스 조건 및 

   조인 시도를 줄일 수 있다. 

 - 조인시 is not null 조건이 없더라도 인덱스를 읽을 때는 null인 컬럼은 조인하지 않는다. 

   다만 full table scan일 시에는 모두 조인한다. 

   ( 그러나 is not null 조건을 명시적으로 추가해 준다면 염려할 필요가 없다.

     다만 full table scan 시 is not null 조건을 필터링 하기 위한 테이블 방문은 일어난다. )  

 - 다행히, 컬럼 통계를 수집하고 나면 옵티마이저가 그런 조건절을 자동으로 추가해 준다. 

   단, 조인 컬럼의 null 값 비중이 5% 이상일 때만 이 기능이 작동한다. 

 - 조인 컬럼에 is not null 조건을 추가해 주면 NL 조인뿐만 아니라 해시 조인, 소트 머지 조인 

   시에도 효과를 발휘한다. 

 - 우선, 해시 조인을 위해 Build Input을 읽어 해시 맵을 만들 때 더 적은 메모리를 사용한다.    

   Probe Input을 읽을 때도 Null 값인 레코드를 제외함으로써 해시 맵 탐색 횟수를 줄일 수 있다. 

   양쪽 모두 null 값 비중이 클수록 효과도 커진다. 

 - 소트 머지 조인할 때도 양쪽 테이블에서 조인 컬럼이 null인 레코드를 제외한다면 소트 및 

   비교 연산 횟수를 줄일 수 있다. 

 - 조인 컬럼에 대한 is not null 조건을 추가한다고 손해 볼 일은 전혀 없다. 

 - 그리고 조인 컬럼에 null 값 비중이 많을 때 임의의 default 값으로 채우는 방식으로 설계하면 

   조인 성능을 떨어뜨릴 수 있다는 사실도 기억하기 바란다. 

필터 조건 추가 

 - 바인드 변수를 사용한 between 쿼리에서 두 값을 비교해서 최소 값이 최대값보다 클 때 

   쿼리를 읽을 필요가 없다. 그때는 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터 

   조건식을 추가한다. 

 - 바인드 변수 대신 상수 값으로 조회할 때도 filter 조건이 추가되는데, 아래와 같은 9i 와 10g 

   에서 조금 다르게 처리하고 있다. 

       9i : filter (5000<=100) 

       10g 이상 : filter(NULL IS NOT NULL) 

 - 9i에서 오브젝트 통계가 없으면 RBO 모드로 작동해 위와 같은 쿼리 변환이 일어나지 않는다. 

   10g는 통계정보가 없어도 항상 CBO 모드로 작동하므로 쿼리 변환이 잘 일어난다. 

  * 테스트 시 확인 사항 

     테스트 시 확인한 것은 위와 같은 상황에서 블록 읽기가 발생하지 않게 하려면 

     해당 테이블들이 buffer cache에 있어야 한다는 점이다. 

     만약 해당 테이블들이 buffer cache에 없다면 consistent gets의 값이 full table scan을 

     할 때와 동일하게 발생하였다. 물론 이는 정확한 값을 입력했다고 해도 마찬가지였다. 

     이 부분은 매우 중요한 부분임으로 몇번 더 테스트를 해보아야 할 것이다.

조건절 비교 순서 

 - 옵티마이저는, 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 Filter 조건식을 

   평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다. 

 - 이런 쿼리 변환이 작동하려면 9i, 10g를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써

   CPU Costing 모델을 활성화해야 한다. I/O Costing 모델에서는 where절에 기술된 순서대로 

   조건식 비교가 일어난다. RBO 모드에서는 where절에 기술된 반대 순서로 조건식 비교가 

   일어난다. 

       RBO                           : where절에 기술된 반대 순서

       CBO(I/O Costing모드)   : where절에 기술된 순서

       CBO(CPU Costing모드) : 비교 연산해야 할 일량을 고려해 옵티마이저가 결정, 

                                          선택도가 낮은 조건식부터 평가 

 - ordered_predicates 힌트를 사용하면 CPU Costing 모드에서도 조건절 비교 순서를 

   제어할 수 있다. 이 힌트를 사용하면 where절에 기술도니 순서대로 비교가 일어난다. 

ordered_predicates 힌트의 또 다른 용도 

 - 10g에서 OR 또는 IN-List 조건에 대한 OR-Expansion이 일어날 때 실행 순서를 제어할 

   목적으로 ordered_predicates 힌트를 사용할 수도 있다. 

   * 나의 생각 

      - 난 아직 이걸 어떨때 유용하게 사용할 수 있을지 모르겠다. 어차피 or 조건에 있는 

        2번 다 실행해야 할텐대 처리 순서가 왜 중요한 건지 모르겠다.      

집합 연산을 조인으로 변환이란 

 - Intersect나 Minus 같은 집합 연산을 조인 형태로 변환하는 것을 말한다. 

 - sys_op_map_nonnull 함수는 비공식적인 함수지만 가끔 유용하게 사용할 수 있다. 

   Null 값끼리 '=' 비교하면 true가 되도록 처리해야 하는 경우에 사용하면 된다. 

   * 이 부분은 예시 쿼리가 좀 이상했다. 그냥 결과를 만들어내기 위한 쿼리여서 

      실제 어떠한 상황에서 도움이 될지는 좀 더 생각해보아야 한다. 

실체화 뷰란 

 - 뷰는 쿼리만 저장하고 있을 뿐 자체적으로 데이터를 갖지는 않는다. 반면, 실체화 뷰는 

   'Materialize'가 의미하는 것처럼 물리적으로 실제 데이터를 갖는다. 

 - MV를 활용하는 이유는 기준 테이블이 그만큼 대용량이기 때문인데 Join View는 같은 데이터를 

   중복 저장하는 비효율이 있어 활용도가 낮고, 주로 Aggregate View 형태로 활용되는 편이다. 

 - 아래는 MV를 사용하게 만드는 가장 중요한 두 가지 특징이다. 

     ● Refresh 옵션을 이용해 오라클이 집계 테이블을 자동 관리하도록 할 수 있다. 

     ● 옵티마이저에 의한 Query Rewrite가 지원된다. 

         ( Query Rewrite 일반 적인 쿼리를 작성 시 필요한 실체화된 뷰가 있으면 대신 해서 

           쿼리가 변경 되어 MV를 사용하는 것 ) 

 - 쿼리 재작성 기능이 작동하려면 MV를 정의할 때 enable query rewrite 옵셥을 지정해주어야 

   하고, 세션 또는 시스템 레벨에서 아래와 같이 파라미터도 변경해 주어야 한다. 9i 까지는 

   기본 설정이 false였지만 10g부터는 true로 바뀌었다. 

     예시)  alter sessson set query_rewrite_enabled =true ;

Outer 조인을 Inner 조인으로 변환 

 - Outer 조인문을 작성하면서 일부 조건절에 Outer 기호 (+)를 빠드리면 Inner 조인할 때와 

   같은 결과가 나온다. 이럴 때 옵티마이저는 Outer 조인을 Inner 조인문으로 바꾸는 쿼리 

   변환을 시행한다. 

 - 옵티마이저가 굳이 이런 쿼리 변환을 시행하는 이유는 조인 순서를 자유롭게 결정하기 

   위해서다. 

 - Outer 조인에서 Inner쪽 테이블에 대한 필터 조건을 아래처럼 where절에 기술한다면 

   inner 조인할 때와 같은 결과집합을 얻게 된다. 따라서 옵티마이저가 Outer 조인을 아예 

   Inner 조인으로 변환해 버린다. 

 - 제대로된 Outer 조인 결과집합을 얻으려면 조건을 on절에 기술해주어야 한다. 

   (이는 즉 조인 후 filter가 되느냐 조인 되기 전에 filter가 되느냐를 결정한다. ) 

 - ANSI Outer 조인문에서 where 절에 기술한 Inner쪽 필터 조건이 의미 있게 사용되는 경우는 

   is null 조건을 체크하는 경우뿐이며, 조인에 실패하는 레코드를 찾고자 할 때 흔히 사용되는 

   SQL이다. 

 - Outer 쪽 필터조건은 on절에 기술하든 where절에 기술하든 결과집합이나 성능에 하등 

   차이가 없다. 

공통 표현식 제거란 

 - 같은 조건식이 여러 곳에서 반복 사용될 경우, 오라클은 해당 조건식이 각 로우당 

   한 번씩만 평가되도록 쿼리를 변환하는데, 이를 '공통 표현식 제거'라고 한다. 

 - ' _eliminate_common_subexpr ' 파라미터를 통해 제어한다. 

 - 이 부분은 단순히 쿼리를 좀 더 보기 쉽고 짧게 바꾼다고 생각할 수 있는데 

 - 위의 ' _eliminate_common_subexpr ' 파라미터를 false로 했을 시 조인 자체가 2번 발생한다. 

   * 이는 내가 했던 테스트 실행계획과 조금 다른 점이 있는데 이 부분은 다시 확인 해보는 것이 

     좋을 것 같다.