🔑 SQL 튜닝 - CH 5. 소트 튜닝

 

1️⃣ 소트 튜닝

소트는 기본적으로 PGA에 Sort Area에서 이루어진다 

PGA :  DB에 접속하는 유저에게 할당되는 각각의 서버 프로세스가 독자적으로 사용하는 오라클 메모리 영역

개인공간이라고 생각하면 편함.

http://wiki.gurubee.net/pages/viewpage.action?pageId=4949315

 

◽ Sort Area 공간이 다 차면 디스크 Temp 테이블스페이스를 활용 한다.

◽ Sort Area가 찰 때마다 Temp 영역에 저장해 둘 때 이 중간 단계의 집합을 'Sort Run'이라고 함.

◽ Sort Area의 크기가 'Sort Run'에 있는 크기에 비례하여 한번에 읽어들일 수 있다면 추가적인
    Disk I/O는 발생하지 않지만, 그보다 크기가 작다면 여러번 액세스해야 하므로 성능이 나빠짐.

---

Sort Area에서 작업을 완료할수 있는지에 따라 소트를 2가지 유형으로 나눈다

◾ 메모리 소트 (In - Memory Sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 뜻한다
                                                      'Internal Sort'  라고 한다         

◾ 디스크 소트 (To - Dist Sort) : 할당받은 Sort Area 내에서 정렬을 완료 하지 못해 디스크 공간까지 사용 하는 경우를
                                                  말하며 ' External Sort' 라고 한다

 

소트 연산은 메모리 집약적일 뿐만 아니라 CPU 집약적 이기도 한다.

1 ) 많은 서버 리소스를 사용 하도 디스크 I/O가 발생하는 것도 문제지만

2 ) 부분범위 처리를 불가능하게 함으로써  OLTP 환경에서 애플리케이션 성능늘 저하시키는 주요인 이다.

3 ) 가급적 소트를 발생시키지 않도록 SQL을 작성하고, 소트가 불가피할 경우 메모리 내에서 수행을 할 수 있도록 해야 함

---

"메모리 집약적" ❓
어떤 프로세스, 애플리케이션, 시스템 등이 많은 양의 메모리(주로 RAM)를 사용하는 경향이 있음을 나타 낸다.

"CPU 집약적" ❓
어떤 프로세스, 애플리케이션, 시스템 등이 주로 중앙 처리 장치(CPU)의 계산 능력에 크게 의존하며 많은 CPU 리소스를 사용하는 경향이 있음을 나타낸다.

계산이 많이 필요한 작업으로, CPU의 처리 능력을 주로 활용한다.
이러한 작업은 다른 시스템 리소스(예: 메모리나 디스크)보다 CPU의 속도나 능력에 더 크게 영향을 받는다.

 

---

2️⃣ 소트 오퍼레이션

소트를 발생시키는 오퍼레이션 ▶ 오퍼레이션의 뜻이 작업, 연산 인데  '정렬 작업'을 말하는건가 
데이터를 특정한 순서에 맞게 정렬 하는 ..그런거겠지 ?

 

1 ) Sort Aggregate

• 'Sort' 라는 표현을 사용 하고 있지만 , 실제로는 데이터를 정렬 하지는 않는다. Sort Atrea를 사용 한다는 의미로 
  이해하자 !

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

2 ) Sort Order By 

• Sort Order By는 데이터를 정렬 할 때 나타난다.

SELECT *
FROM   EMP
ORDER BY SAL DESC
;
                                   
                                                                           
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |  1358 |     6  (17)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |  1358 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1358 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

3 ) Sort Group By

• Sort Group By는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.

select deptno , sum (sal),max(sal),avg(sal)
from emp
group by deptno
order by deptno;
                                                                           
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |  150 |     5  (20)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |    10 |  150 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |  210 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

4 ) Sort Unique 

• 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없을 때 Unnesting 되어 조인으로 풀릴 때 Sort Unique 오퍼레이션 수행
  ( 메인쿼리와  조인하기 전 중복 레코드부터 제거 )
• 만약 서브쿼리의 컬럼이 PK 또는 Unique인 경우 Sort Unique 오퍼레이션은 생략
• Union, Minus, Intersect, Distinct 구문도 마찬가지로 Sort Unique 오퍼레이션이 나타남

5 ) Sort Unique

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

select /*+ ordered use_merge(e) */ *
from dept d, emp e
where d.deptno = e.deptno;
                                                                           
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |  770 |     8  (20)| 00:00:01 |
|   1 |  Merge JOIN        |      |    10 |  770 |     8  (20)| 00:00:01 |
|   2 |  Sort JOIN         |      |     4 |   72 |     4  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   72 |     3   (0)| 00:00:01 |
|   2 |  SORT JOIN         |      |    14 |  518 |     4  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |  518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

 6 ) Window Sort

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

select empno , ename , job , mgr , sal ,avg(sal) ,over (partition by deptno)
from emp;


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |  400 |     4  (20)| 00:00:01 |
|   1 |  Window SORT       |      |    10 |  400 |     4  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |    10 |  400 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

윈도우 함수  :  '윈도우'라고 불리는 특정 범위나 순서의 행 집합에 대해 연산을 수행하는 함수

윈도우 함수는 OVER() 절과 함께 사용되며
OVER() 절 내에서 정렬(ORDER BY)과 행의 범위(ROWS BETWEEN ... AND ...) 등을 지정

Window Sort는 윈도우 함수를 계산하기 전에 필요한 데이터 정렬을 나타낸다.

 

---

3️⃣ 소트가 발생하지 않도록 SQL 작성

✔ SQL 작성 할 때에는 불필요한 소트가 발생하지 않게 주의하자 !
Union , Minus , Distinct, 연산자는 중복 레코드를 제거하기 위한 소트 연산을 발생시키므로 필요한 경우에만 사용 

1 - 1 Union vs Union All

SQL 에서 Union을 사용 하면 옵티마이저는 상단 하단 두 집합간에 중복을 제거하고 소트작업을 한다.

그러나 Union All은 중복을 확인하지 않고 두 집합을 단순히 결합 하므로 소트 작업을 수행하지 않는다.

그러므로 Union All을 사용 해야한다.

/* 중복을 제거하기 위해 소트 수행 */
select 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 ...
from   결제
where  결제일자 = '20180316'
UNION
select 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 ...
from   결제
where  주문일자 = '20180316'

 

/* 소트가 발생하지 않도록 UNION ALL을 사용하면서 데이터 중복을 피하기 */
select 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 ...
from   결제
where  결제일자 = '20180316'
UNION ALL                                  /* UNION ALL로 변경 */
select 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 ...
from   결제
where  주문일자 = '20180316'
and    결제일자 <> '20180316'              /* 중복을 제거해주는 조건절 */

 

---

1 - 2 Exists 활용

중복 레코드를 제거할 목적으로 Distinct 연산자를 종종 사용한다

Distinct를 사용하면 조건에 해당하는 데이터를 모두 읽어서 중복을 제거해야한다.
부분범위 처리는 불가능하고 , 모든 데이터를 읽는 과정에서 많은 I/O가 발생한다.

-- DISTINCT는 소트 발생함
select DISTINCT p.상품번호 , p.상품명 , p.상품가격,...........
from 상품 p , 계약 c
where p.상품유형코드, = :pclscd
and c.상품번호 = p.상품번호
and c.계약일자  between :dt1 and :dt2
and c.계약구분코드 = :ctpcd

-- Exist 사용 , 소트x

select p.상품번호 , p.상품명 , p.상품가격,......
from 상품p
where p.상품유형코드 = :pclscd
and EXIST (select 'x' from 계약 c
			where c.상품번호 = p.상품번호
            and c.계약일자 between :dt1 and :dt2
            and c.계약구분코드 = :ctpcd )

 

NOT EXIST

select st.상황접수번호, st.관제일련번호, st.상황코드, st.관제일시
from   관제진행상황 st
where  상황코드 = '0001'   -- 신고접수
and    관제일시 between :v_timefrom || '000000' and :v_timeto || '235959'
MINUS
select st.상황접수번호, st.관제일련번호, st.상황코드, st.관제일시
from   관제진행상황 st, 구조활동 rpt
where  상황코드 = '0001'
and    관제일시 between :v_timefrom || '000000' and :v_timeto || '235959'
and    rpt.출동센터ID = :v_cntr_ID
and    st.상황접수번호 = rpt.상황접수번호
order by 상황접수번호, 관제일시


/* MINUS 연산자를 NOT EXIST 서브쿼리로 변환 */
select st.상황접수번호, st.관제일련번호, st.상황코드, st.관제일시
from   관제진행상황 st
where  상황코드 = '0001'   -- 신고접수
and    관제일시 between :v_timefrom || '000000' and :v_timeto || '235959'
and    NOT EXIST (select 'x' from 구조활동
                  where  출동센터ID = :v_cntr_ID
                  and    상황접수번호 = st.상황접수번호)
order by st.상황접수번호, st.관제일시

 

---

4️⃣ 인덱스를 이용한 소트 연산 생략

1 - 1 Top N 쿼리

Top N쿼리는 전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리다 

예를들면 TOP 10 , TOP 5 , TOP 3

전체 결과집합중 상위 N개 레코드만 선택하는 쿼리

select TOP 10 거래일시 , 체결건수 , 체결수량 , 거래대금
from 종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20180304'
order by 거래일시

 

오라클에서는 인라인 뷰로 한번 감싸야 하는 불편함이 있다고한다

select * from(
            select TOP 10 거래일시 , 체결건수 , 체결수량 , 거래대금
            from 종목거래
            where 종목코드 = 'KR123456'
            and 거래일시 >= '20180304'
            order by 거래일시
            )
    where rownum <= 10

 

---

5️⃣ Sort Area를 적게 사용하도록 SQL 작성

PGA의 범위를 넘어서면 Temp테이블 스페이스를 사용하지 않기 위해서

소트 연산이 불가피 하다면 메모리 내에서 처리를 완료할수 있도록 노력해야하는데
Sort Area 크기를 늘리는 방법도 있고 , Sort Area를 적게 사용하는 방법을 찾는게 우선순위 이다.

 

1 - 1 소트 데이터 줄이기

/* 합친다음 정렬 */
-- 전체를 다 가져옴
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객ID, 10)
    || lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
from   주문상품
where  주문일시 between :start and :end
order by 상품번호



/* 인라인뷰에서 한번 정렬 후 합침 (SortArea를 더 적게 사용) */
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객ID, 10)
    || lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
from(
  select 상품번호, 상품명, 고객ID, 고객명, 주문일시  -- 주어진것만 가져오기에
  from   주문상품				-- Sort Area를 적게씀
  where  주문일시 between : start and :end
  order by 상품번호
  )

 

1 - 2 Top N 쿼리의 소트 부하 경감 원리

만약 전교생 10000명 중에서 가장큰 학생 10명을 선발하려고 한다.

전교생을 키 순서대로 정렬한 학생부가 있다면 가장왼쪽에 있는 열명을 선발하면 되는데
"Top N Stopkey" 알고리즘을 사용하면 된다

① 전교생을 운동장으로 집합

② 맨앞줄 맨왼쪽에 있는 학생 10명을 앞으로 불러 키가 큰 순서대로 세운다

③ 나머지 990명을 한명씩 교실로 들여보내며 top10 에 위치에있는 학생과 키를 비교

④ top 10에 새로 진입한 학생 키에 맞춰 자리 재배치

➕ 삽입정렬 : 기존에 정렬된 곳에 추가 + @

 

http://www.gurubee.net/

꿈꾸는 개발자, DBA 커뮤니티 구루비