성능고도화 5-1. 소트 수행 원리

소트 튜닝이 필요한 이유 

 - 소트 오퍼레이션은 수행과정에서 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 크기 조정