[6주차] 소트 튜닝

5.1 소트 연산에 대한 이해

   1. 소트 수행 과정(=데이터 정렬 과정)
   2. 소트 오퍼레이션
      1) sort Aggregate
      2) Sort Order By
      3) Sort Group By
      4) Hash group by
      5) Sort Unique
      6) Sort Join
      7) Window sort

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

5.3 인덱스를 이용한 소트 연산 생략

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

 

 

0. 이해하기 위한 지식

0.1 소트와 성능

가. 메모리 소트와 디스크 소트

SQL 수행 도중 소트(Sort) 오퍼레이션이 필요할 때마다 DBMS는 정해진 메모리 공간에 소트 영역(Sort Area)을 할당하고 정렬을 수행한다. Oracle은 소트 영역을 PGA(Private Global Area) 영역에 할당하고, SQL Server는 버퍼 캐시에 할당한다고 1장에서 설명하였다.

컴퓨터에서 이루어지는 모든 작업이 그렇듯, 소트 오퍼레이션도 메모리 공간이 부족할 땐 디스크 공간을 사용한다. Oracle에선 Temp Tablespace를 이용하고, SQL Server에선 tempdb를 이용한다.

가급적 소트 영역 내에서 데이터 정렬 작업을 완료하는 것이 최적이지만, 대량의 데이터를 정렬할 땐 디스크 소트가 불가피하다. 특히, 전체 대상 집합을 디스크에 기록했다가 다시 읽는 작업을 여러 번 반복하는 경우 SQL 수행 성능은 극도로 나빠진다.

출처: https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=371 

소트 튜닝

 

0.2 정렬은 성능을 저하 시킨다

정렬이 성능 저하를 발생시킨다는 것은 모두 알고 있을 것이다. (??? 몰랐네)

하지만 정렬이 어떤 이유에서 성능 저하를 발생시키고, 또한 어떤 경우에 정렬이 발생하는지 모르는 경우가 많다.  

http://www.gurubee.net/lecture/2268

 

0.3 정렬은 언제 발생하는가?

• - ORDER BY 절
• - DISTINCT
• - 소트 머지 조인
• - 해시 조인
• - 집합 연산자(UNION ALL을 제외한 모든 집합 연산자)
• - 분석 함수
• - 그룹 함수(오라클 10g에서는 발생하지 않을 수 있음)
• - IN 절의 사용(수행 방식에 따라 발생할 수 있음)
• - 인덱스 생성

 

 

5.1 소트 연산에 대한 이해

sql 수행 도중

가공된 데이터 집합이 필요할 때

PGA, Temp 테이블 스페이스를 활용한다

 

1. 소트 수행 과정(=데이터 정렬 과정)

소트는 PGA에 할당한 sort Area에서 이뤄진다

메모리 공간 Sort Area가 다 차면 -> 디스크: temp 테이블 스페이스를 활용한다.

 

2. 소트 오퍼레이션

 

소트를 발생시키는 오퍼레이션

소트 튜닝 방안을 본격적으로 설명하기에 앞서, 어떨 때 소트가 발생하는지부터 살펴보자. Oracle 실행계획에 나타나는 오퍼레이션 형태를 기준으로 설명하며, 같은 오퍼레이션이 SQL Server 실행계획에선 어떻게 표시되는지도 함께 제시한다.

 

 

1) Sort Aggregate (유형이 비슷한 특성의 그룹) (집합의, 합치다)

전체 로우를 대상으로 집계를 수행할때

 

실제로 데이터를 정렬하지 않는다.

Sort Area를 사용한다

 

 

방법

Sort Area에 sum, max, min, count 변수를 각각 하나씩 만든다.

emp 테이블에서 레코드를 하나씩 읽어가면서 

sum, max, min, count 변수 값을 변경한다. 

2) Sort Order By

데이터를 정렬할 때 나타난다.

정렬된 결과집합을 얻고자 할 때 나타난다.** 인터넷 검색

desc를 쓰면 데이터를 정렬해야하기 때문에 pga에서 데이터를 정렬한다.

 

 

 

 

 

3) Sort Group By

소팅 알고리즘을 사용해 값을 집계한다. 결과의 정렬이 아니다.

Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.** 인터넷 검색

order by deptno를 사용하면 정렬 순서가 보장 된다.

group by deptno
order by deptno // 정렬된 그룹핑 결과를 얻고자 한다면 꼭 써라.

 

방법 1: 전체 테이블에서 sal을 읽어서 -> deptno 순으로 정렬을 한다

방법 2: 전체 테이블에서 sal을 읽어서 -> deptno 종류 4개에 각각 -> sum, max, min, count 변수에 값을 바꾼다

 

sort group by는 방법2를 사용한다.

장점:

1) sort area를 적게 쓴다.

2) temp 테이블 스페이스 안쓴다.

 

 

 

 

 

 

4) Hash group by

group by deptno
order by deptno -- 없음

 

order by deptno 없으면 Hash group by 사용함

 

5) Sort Unique

메인쿼리와 서브쿼리가 조인하기 전에 

조인 컬럼에 unique 인덱스가 없으면

중복 레코드를 제거하기 위해 sort Unique 가 실행된다.

 

선택된 결과집합에서 중복 레코드를 제거하고자 할 때 나타난다. **

Union 연산자나 아래와 같이 Distinct 연산자를 사용할 때가 대표적이다. **

 

1. sort Unique 가 실행 환경

(1) 중복 레코드 제거

(2) Union, Minus, Intersect 같은 집합 연산자 사용

 

(3) Distinct 연산자 사용

 

 

(4) Order by 생략

 

6) Sort Join

 SORT MERGE JOIN이란? 

조회의 범위가 많을 때 주로 사용하는 조인 방법론이며 양쪽 테이블을 각각 Access 하여 그 결과를 정렬하고 그 정렬한 결과를 차례로 Scan 해 나가면서 연결고리의 조건으로 Merge를 하는 방식입니다. 주로 조인 조건 칼럼에 인덱스가 없거나, 출력해야 할 결과 값이 많을 때 사용됩니다. 조회의 범위가 좁을 때 유리한 Nested Loop Join의 조인 방식과 장단점이 서로 바뀌어있다고 생각하시면 이해하기 쉽습니다.

 

SORT MERGE JOIN의 사용처

1. 연결 고리에 인덱스가 전혀 없는 경우

2. 대용량의 자료를 조인할때 유리한 경우

3. 조인 조건으로 <, >, <=, >=와 같은 범위 비교 연산자가 사용된 경우

4. 인덱스 사용에 따른 랜덤 액세스의 오버헤드가 많은 경우

 

7) Window sort

 

분석 함수는 해당 함수의 옆에 OVER 절을 설정하게 된다.

OVER 절은 무엇인가?

OVER 절은 데이터를 논리적으로 분할하는 PARTITION BY 절과

해당 논리적인 분할 영역을 정렬하는 ORDER BY 절로 구분된다.

 

이 중에서 ORDER BY 절은

각각의 논리적인 PARTITION을 정렬하게 되며

이와 같은 논리적인 PARTITION을 WINDOW라고 부르게 된다.

따라서 실행 계획에는 WINDOW(SORT)라는 실행 계획이 생성되며 이와 같은분석 함수들도 결국 정렬을 수행하게 된다.

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

5.3 인덱스를 이용한 소트 연산 생략

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