잡동사니 보물섬

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

 - 튜닝 과정에서 조사된 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 크기 조정