소트연산

1. 소트연산에 대한이해

• SQL 수행중 가공된 데이터 집합필요시, 오라클은 PGA와 TEMP테이블 스페이스를 활용

• 소트머지 조인, 해시 조인, 데이터 소트와 그룹핑등이 대표적

  1.1 소트 수행 과정

• 소트는 기본적으로 PGA에 할당한 Sort Area에서 수행

• 메모리공간인 Sort Area가 다 차면 디스크 TEMP테이블 스페이스 활용

• SORT AREA에서 완료할수있는지에 따라 소트를 두가지 유형으로 분류

  • 메모리 소트(In-Memory sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는것, 'Internal Sort'라고도 한다.
  • 디스크 소트(To-disk Sort) : 할당받은 Sort Area내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우, 'External Sort' 라고도 한다.

• 디스크 소트 과정

  • 소트 대상집합을 SGA버퍼캐시를 통해 READ
  • 일차적으로 Sort Area에서 정렬시도
  • 양이 많을때는 정렬된 중간집합을 TEMP테이블스페이스에 임시 세그먼트를 만들어 저장
  • Sort Area가 찰때마다 TEMP영역에 저장해 둔 중간 단계 집합을 'Sort Run'이라고 부른다.
  • 정렬된 최종 결과집합을 얻으려면 이를 다시Merge해야한다. 각Sort Run내에서는 이미 정렬된 상태이므로 Merge과정은 어렵지 않다.
  • 오름차순 정렬이라면 각각에서 가장 작은값 부터 PGA로 읽어들이다가 PGA가 찰때마다 쿼리 수행 다음단계로 전달하거나 클라이언트에게 전송하면된다.

• 소트연산은 메모리집약적(Memory-intensive)일뿐 아니라 CPU집약적(CPU-intensive)

• 처리할 데이터량이 많을때는 디스크I/O까지 발생하므로 쿼리성능을 좌우하는 매우중요한 요소

• 부분범위 처리를 불가능하게 함으로써 OLTP환경에서 어플리케이션 성능저하의 주요인이되기도한다.

• 되도록 소트가 발생하지않도록 SQL을작성해야하고, 소트가 불가피하면 메모리내에서 수행을 완료할 수 있도록한다.

  1.2 소트 오퍼레이션

  1.2.1 Sort Aggregate

  • 전체로우를 대상으로 집계수행시 나타남
  • 'Sort'라는 표현을쓰지만 실제 데이터 정렬은 하지않는다. > Sort Area를 사용한다는 의미

    SELECT SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL) FROM EMP;

  • 데이터 정렬하지않고 SUM, MAX, MIN, AVG값 구하는 절차
    • Sort Area에 SUM, MAX, MIN, COUNT값을 위한 변수를 각각 하나씩 할당
    • EMP테이블 첫번째 레코드에서 읽은 SAL값을 SUM, MAX, MIN변수에 저장하고, COUNT변수에는 1을 저장
    • EMP테이블에서 레코드를 하나씩읽어 내려가면서 SUM변수에는 값 누적, MAX변수는 기존보다 클경우 대체, MIN변수는 기존보다 작은값일경우 대체, COUNT변수에는 SAL값이 NULL이 아닌 경우 1씩 증가
    • EMP 레코드를 다읽고나면 각 변수에 담긴값을 출력하고, AVG는 SUM값을 COUNT로 나는 값을 출력

  1.2.2 Sort Order By

  • 데이터 정렬시 사용

  1.2.3 Sort Group By

  • 소팅 알고리즘을 사용해 그룹별 집계를 수행할때 나타난다.
  • 수천명의 사원(EMP)이 근무하는 회사를 가정
  • 부서는 4개뿐, 부서코드는 10,20,30,40
  • 컴퓨터없이 부서별 급여(SAL)을 집계할때 어떤방법이 좋을까?
  • 집계항목은 급여에 대한 합계, 최대값, 최소값, 평균값
  • 각 부서의 집계정보기록을위한 메모지를두고 부서번호순으로 정렬하면 준비끝
  • 각사원의 급여정보를 읽기 시작, 각사원의 부서번호에 해당하는 메모지 찾기, 정렬돼있으니 찾기쉽다.
  • 메모지 찾았으면 집계정보(SUM, MAX, MIN, COUNT)갱신
  • 1.2.1의 Sort Aggregate와 동일한 방식 사용.
  • 오라클은 이런방식을 사용하며 부서수가 많지않다면Sort Area가 전혀클필요가없다, Temp테이블스페이스를 쓰지 않는다는 뜻.
  • Hash Group By방식은 정렬을하지않고 해싱알고리즘을 사용하여 그룹별 집계항목을 갱신한다.

정렬된 그룹핑 결과를 얻고자 한다면, 실행계획에 설령'Sort Group By'라고 표시되어있어도 반드시 Order By를 명시해야 한다.

1.2.4 Sort Unique

• 옵티마이저가 서브쿼리를 풀어 일반 조인문으로 변환하는 것을 '서브쿼리 Unnesting'
• Unnesting된 서브쿼리가 M쪽 집합이면 메인쿼리와 조인하기전 중복레코드부터 제거해야한다.
• 이때 Sort Unique오퍼레이션이 발동
• 만약 PK/Unique 제약 또는 Unique인덱스를 통해 Unnesting된 서브쿼리의 유일성이 보장되면 Sort Unique오퍼레이션은 생략
• Union, Minus, Intersect 같은 집합(Set)연산자사용시에도 Sort Unique오퍼레이션이 나타난다.
• Distinct연산자에도 작동
• 오라클 10gR2부터 Distinct연산에도 Hash Unique방식사용, Group By와마찬가지로 Order By 생략시 작동

  1.2.5 Sort Join

• Sort Join 오퍼레이션은 소트머지 조인 수행시 나타난다

  1.2.6 Window Sort

• 윈도우 함수(=분석 함수)를 수행할 때 나타난다.

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

• SQL작성시 불필요한 소트가 발생하지않도록 주의
• Union, Minus, Distinct는 중복레코드 제거하기위한 소트 연산발생시키므로 꼭 필요한 경우에만 사용
• 성능이 느리다면 소트연산을 피할 방법이 있는지 찾아봐야 한다. 조인방식도 중요

2.1 Union vs Union All

• SQL에 Union을 사용하면 옵티마이저는 상단과 하단 두 집합간 중복을 제거하려고 소트작업을 수행
• Union All은 중복을확인하지 않고 두 집합을 단순히 결합, 될수있으면 Union All사용해야 한다.
• But, 결과집합이 달라질수있으니 주의, 두집합간 중복이없는경우는 Union All을 적극사용
• Union > Union All변경시 데이터 중복을 위하기위한 구상이 필요.

2.2 Exists활용

• 중복레코드를 제거할 목적으로 Distinct 연산자를 종종사용하는데, 조건에 해당하는 데이터를 모두 읽어 중복을 제거해야한다. 부분범위 처리는 당연히 불가하고, 모든데이터를 읽는 과정에서 많은 I/O발생
• Exists 서브쿼리는 데이터 존재여부만 확인하면되기때문에 조건절을 만족하는 데이터를 모두 읽지 않는다.
  (서브쿼리와 조인에서 ROWNUM설명 참조)
• Distinct, Minus 연산자를 사용한 쿼리는 대부분 Exists서브쿼리로 변환 가능.

2.3 조인 방식 변경

• 조인시 조인 방식도 잘선택해야함.
• 해시조인 일때 Sort Order by가 발생했지만 NL조인일때 소트연산을 생략할 수있어, 조건을 만족하는 데이터가많고, 부분범위 처리가 가능한 상황에서 큰 효과를 얻을 수 있다.
• 정렬기준이 조인키 컬럼이면 소트머지 조인도 Sort Order by를 생략할 수 있다.