소트 튜닝

시작

• 소트 연산은 CPU부하를 높이고,
  SELECT만 수행했는데 디스크 쓰기 까지 발생시킬 수도 있음.
  OLTP환경에서 소트연산이많다면 CPU부하가중, 디스크쓰기로 시스템 전체에 영향을 미치게된다.

1. 소트 연산의 종류

1.1 Order by(Sort Order by)

• 소트하민 기본이 ORDER BY 연산, 실행계획에선 SORT ORDER BY 연산수행
• 오라클은 ORDER BY외에는 정렬을 보장하지 않는다.
  

1.2 Group by (Sort Group by, Hash Group by)

• GROUP BY 와 ORDER BY명령을 SQL문에 동시에 사용할때 SORT GROUP BY 로 수행
  정렬 알고리즘을 이용해 GROUP BY 수행
• GROUP BY 만을 수행할 때는 대부분 HASH GROUP BY연산으로 수행
• 집계연산을하는데 정렬이 꼭필요한것은 아님.
• GROUP BY 결과값으로 정렬된 데이터가 필요하다면 SQL문에 반드시 ORDER BY 절을 포함해야 함.

1.3 Distinct, IN, UNION, MINUS, INTERSECT등의 집합연산자

• IN절에서 소트 연산이 수행될 수 있음.
  INLINE VIEW에서 DEPTNO조회시 UNIQUE SORT연산을통해 중복없는 DEPTNO추출하는듯.

  SELECT *
  FROM DEPT
  WHERE DEPT IN (SELECT DEPTNO FROM EMP)

• 집합연산자 UNION, MINUS, INTERSECT사용시 SORT UNIQUE연산이 나타남.
• UNION명령사용시 중복 제거된 합집합을 만들기 위해 SORT UNIQUE연산을 사용.
• INTERSECT는 중복제거후 교집합을 결과 집합으로 만듬. SORT UNIQUE연산 사용
• DISTINCT연산은 SORT UNIQUE와 HASH UNIQUE연산을 사용. HASH UNIQUE연산은 정렬이발생하지않음.

1.4 Rank(), Dense_Rank(), Row_Number() 등 윈도우 함수(Window Sort)

• 윈도우 함수 등장으로 데이터간 순위, 집계, 순서등 행간의 관계 표현이 쉬워짐
• 윈도우 함수 사용시 WINDOW SORT연산을 수행

1.5 Sort Aggregate 연산

• SORT AGGREGATE연산은 정렬을 하는 것처럼 보이지만 실제로는 정렬을 하지않음.
• sorts(memory), sorts(disk)가 발생하는지 Statistics에서 확인해야함.

2. 인덱스를 이용한 소트 대체

2.1 Sort Order by

• 아래의 경우는 인덱스를 활용하지않고 Sort Order by연산이 발생
  실행계획을보면 인덱스를 이용해 테이블에 접근하징낳음
  힌트를 넣어도 적용되지않음. 이는 DEPTNO컬럼이 NULL을 허용해주기때문
• SORT ORDER BY 연산을 인덱스로대체하기위해선 AND DEPTNO IS NOT NULL조건이 필요.

create index emp_x01 on emp(deptno);
dept컬럼은 null가능
select *
from emp
order by deptno

2.2 Sort Group by

• 아래 sql문은 정렬은 발생하지않지만 hash group by 연산으로 수행됨
• 2.1과마찬가지로 deptno is not null조건 추가시 sort group by nosort연산이됨.
• 인덱스스캔하면서 같은값을 만나는동안 집계하는 방식으로 진행하기때문에 hash group by 연산이 아닌 sort group by 연산을 사용, 정렬을 대체 인덱스를 사용하였기에 nosort라고 표시

  select deptno, sum(sal+nvl(comm,0))
  from emp
  group by deptno

3. 불필요한 소트 제거

3.1 UNION, MINUS, INTERSECT등 집합 연산자

• 아래쿼리는 데이터중복이 발생할수없지만, SORT UNIQUE연산발생
• 중복이 없기에 UNION ALL로 변경해주면 SORT UNIQUE연산 제거 및 정렬발생안함.

  SELECT FROM EMP WHERE MGR= 7839
  UNION
  SELECT FROM EMP WHERE MGR = 7566

3.2 IN절 안의 서브 쿼리

• IN절안에 서브쿼리 작성시 SORT UNIQUE연산이 발생함. (DEPTNO중복제거)
• IN절 부하발생시 EXISTS절로 대체 가능
• 일반적으로 IN절을 EXISTS로 변경할 경우 가장좋은 방법은 해당 컬럼으로 기존 인덱스를 사용하거나 해당컬럼으로 인덱스를 추가하는것

  SELECT *
  FROM DEPT
  WHERE DEPTNO IN (SELECT DEPTNO FROM EMP)

• 위의 쿼리에서 EMP_X01(DEPTNO)인덱스를 생성하여 쿼리작성

  SELECT /*+ LEADING(D) */
  FROM DEPT D
  WHERE EXISTS (SELECT /*+ UNNEST NL_SJ */ 'X' FROM EMP WHERE DEPTNO = D.DEPTNO)

• 위와같이 서브쿼리에서 인덱스를 사용하면 정렬연산을 인덱스로 대체하고, 액세스양도 줄일수있음.

3.3 MERGE 와 UNNEST

• 두 힌트는 FROM절의 메인쿼리와 합치는 의미는 동일
• 서브쿼리의 위치에따라 사용하는 힌트가 다름.
• MERGE는 인라인뷰에서 사용, 합치는 의미로 FROM절에 나열된 테이블과 인라인뷰를 합치는 시도
• UNNEST는 풀어낸다는 의미로 WHERE절에 위치한 중첩된 서브쿼리를 플어 FROM절에 합치는 시도, 중첩된 서브쿼리에서 사용

4. 소트영역 적게사용하기

4.1 소트영역에 대한 이해

• 소트영역은 로우의 개수와 더불어 컬럼들의 최종길이도 함께 결정 가로길이와 세로길이가 함께 결정됨
• 데이터 수와, 컬럼수가많을경우 MEMORY영역을 넘어 DISK영역까지사용 > 성능저하
• ROW수를 줄일수없다면 COLUMNS수라도 줄인다면 DISK영역을 이용한 정렬횟수를 줄일수있음.
• 컬럼의 총길이도 소트영역 정하는데 영향을 미침
• 정렬은 되도록 칼럼가공전에 수행하고, 필요한 컬럼만 정렬될 수 있도록, 필욯나만큼만 수행되도록 노력필요.

4.2 첫페이지를 빠르게 동작시키는 페이징기법

• 페이징조건을 제일바깥에다가 지정하면 더느려짐 > 소트영역이 많아지기때문
• 실행계획의 COUNT STOPKEY를 보면 ROWNUM<=10이 필터로잡히는데 이것이 소트영역을 10개로우만큼 잡으라는뜻
• 오라클은 정렬에 필요한 메모리공간으로 10개만 잡아놓고 그안에서만 데이터를 바꿔가며 정렬수행

  SELECT A.
  FROM (
           SELECT ROWNUM RN
           , A.
           FROM (
                    SELECT *
                    FROM EMP_100000
                    ORDER BY ENAME DESC
                    ) A
            WHERE ROWNUM <= 10 --이구문이 핵심
            )A
  WHERE A.RN >= 1

• COUNT STOPKEY원리
  • 오라클이 정렬을위해 5개의 메모리공간을 만들고 10개숫자를 정렬시킨다고 가정
  • 1~10까지 숫자중 필요한건 1,2,3,4,5 나머지 6~10은 순서대로 정렬할 필요가 없는경우 COUNT STOPKEY방식을 유도하면 1~5는 정렬되지만 6~10은 메모리서 밀려남
    
• 소트 튜닝은 SQL문만으로 해결되지않는 경우도많음, 중복데이터가 많이발생하도록 설계된경우 DISTINCT를 통해 제거해야하는 경우도 있고, 적절한 인덱스가 없어서 발생하는 경우도있음.