제2절_소트_튜닝

조회 수 8477 추천 수 0 2013.09.06 23:29:02
ljw *.208.140.3

제2절 소트 튜닝
가.메모리  소트와 디스크 소트

SQL수행 도중 소트 오퍼레이션이 필요할 때마다 DBMS는 정해진 메모리 공간에 소트 영역을 할당 하고 정렬을 수행

Oracle : PGA(Private Gloval Area)
SQL Server : 버퍼캐시

→메모리 공간이 부족할 경우 디스크 공간 사용

Oracle : Temp Tablespace
SQL Server : tempdb

메모리 소트와 디스크 소트
메모리(In-Memory)소트 : 전체 데이터의 정렬 작업을 할당 받은 소트 영역 내에서 완료 하는것
                                          (Optimal Sort)
디스크( To-Disk)소트 : 할당 받은 소트 영역 내에서 정렬을 완료하지 못해 디스크 공간까지
                                      사용하는 경우.(External Sort)
                                      Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록
                                      Multipass Sort : 정렬 대상 집합을 디스크에 여러번 기록

나.소트를 발생시키는 오퍼레이션
소트가 발생하는 상황(Oracle 실행계획의 오퍼레이션 기준)

1)Sort Aggregate : 전체 로우를 대상으로 집계를 수행할 때 나타나며, Oracle 실행계획에
                               ‘sort’라는 표현이 사용되지만 실제 소트가 발생하진 않음. SQL Server
                                실행계획엔 ‘Stream Aggregate’라고 표시
select count(*)
from emp;


--------------------------------------------------------------------------

| Id  | Operation             | Name  | Rows  | Cost (%CPU)|    Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |       |     1 |     1|    00:00:01 |

|   1 |  SORT AGGREGATE       |       |     1 |       |             |

|   2 |   INDEX FULL SCAN     | PK_EMP|    14 |     1 |    00:00:01 |

--------------------------------------------------------------------------



2)Sort Order By : 정렬된 결과집합을 얻고자 할 때 나타난다.
select *
from emp
order by ename;

----------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|  Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)|  00:00:01 |

|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)|  00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)|  00:00:01 |

----------------------------------------------------------------------------


3)Sort Group By : Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.
select deptno, count(*)
from emp
group by deptno
order by deptno;


------------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|    Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     3 |     9 |     4  (25)|    00:00:01 |

|   1 |  SORT GROUP BY     |      |     3 |     9 |     4  (25)|    00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)|    00:00:01 |

------------------------------------------------------------------------------



4)Sort Unique : 선택된 결과집합에서 중복 레코드를 제거하고자 할 때 나타남.
                          (Union/distinct연산)
select empno
from emp
union
select empno
from emp
where empno=10;



-------------------------------------------------------------------------------

| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)|  Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |        |    15 |    60 |     3  (67)|  00:00:01 |

|   1 |  SORT UNIQUE        |        |    15 |    60 |     3  (67)|  00:00:01 |

|   2 |   UNION-ALL         |        |       |       |            |           |

|   3 |    INDEX FULL SCAN  | PK_EMP |    14 |    56 |     1   (0)|  00:00:01 |

|*  4 |    INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)|  00:00:01 |

-------------------------------------------------------------------------------


5)Sort Join : Sort Merge Join을 수행할 때 나타남
select /*+ ordered use_merge(emp) */ *
 from  dept, emp where  dept.deptno = emp.deptno;



--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)|      Time|

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |    14 |   798 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN                   |            |    14 |   798 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID | DEPT       |    4  |    80 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN            | PK_DEPT    |    4  |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                   |            |    14 |   518 |     4  (25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL          | EMP        |    14 |   518 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------



6)Window Sort : 윈도우 함수를 수행할 때 나타난다.
select empno, max(empno) over (partition by deptno)
from emp;


-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|  Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |    98 |     4  (25)|  00:00:01 |

|   1 |  WINDOW SORT       |      |    14 |    98 |     4  (25)|  00:00:01 |

|   2 |   TABLE ACCESS FULL|  EMP |    14 |    98 |     3   (0)|  00:00:01 |

----------------------------------------------------------------------------


다.소트 튜닝요약
소트 오퍼레이션은 메모리 집약적(Memory-intesive)일뿐만 아니라 CPU 집약적(CPU-intensive)이기도 하며, 데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 크게 떨어 뜨림.
→ 부분범위처리를 할수 없게 만들어 OLTP환경에서 성능을 떨어뜨리는 주요인.
   가급적 소트가 발생하지 않도록 SQL을 작성, 소트가 불가피하다면 메모리 내에서 수행을
   완료 할 수 있도록 유도.

소트튜닝 방안
-데이터 모델 측면에서의 검토
-소트가 발생하지 않도록 SQL작성
-인덱스를 이용한 소트 연산 대체
-소트 영역을 적게 사용하도록 SQL작성
-소트 영역 크기 조정



2.데이터 모델 측면에서의 검토
자주 사용되는 데이터 액세스 패턴을 고려하지 않은 채 물리 설계를 진행하거나,  M:M 관계의 테이블을 해소하지 않아 소트 오퍼레이션을 수반하고 그로인해 성능이 좋지 못한경우.

FIG_5-6.png

테이블 개수를 줄인다는 이유로 자식 테이블에 통합한 경우

FIG_5-7.png

→ 고객별 가입상품 레벨의 데이터 조회가 빈번하게 발생할 경우 아래처럼 group by가 발생

select 과금.고객id, 과금.상품id, 과금.과금액, 가입상품.가입일시
 from 과금,
         (select 고객id, 상품id, min(가입일시) 가입일시
             from 고객별상품라인
          group by 고객id, 상품id) 가입일시
where 과금.고객id(+) = 가입상품.고객id
  and 과금.상품id(+) = 가입상품.상품id
  and 과금.과금년월(+) = :yyyymm


→ 정상적인 모델인경우

select 과금.고객id, 과금.상품id, 과금.과금액, 가입상품.가입일시
 from 과금, 가입상품
where 과금.고객id(+) = 가입상품.고객id
   and 과금.상품id(+) = 가입상품.상품id
   and 과금.과금년월(+) = :yyyymm



3.소트가 발생하지 않도록 SQL 작성
가.Union을 Union All로 대체
union을 사용하면 옵티마이저는 상단과 하단의 두 집합 간 중복을 제거하려고 sort unique 연산을 수행

union all은 중복을 허용하며 두 집합을 단순히 결합하므로 소트연산 불필요

select *
from emp
union
select *
from emp2;



 --------------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time          |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    17 |   635 |     8  (63)|      00:00:01 |

|   1 |  SORT UNIQUE        |      |    17 |   635 |     8  (63)|      00:00:01 |

|   2 |   UNION-ALL         |      |       |       |            |               |

|   3 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)|      00:00:01 |

|   4 |    TABLE ACCESS FULL| EMP2 |     3 |   117 |     3   (0)|      00:00:01 |

---------------------------------------------------------------------------------



select *
from emp
union all
select *
from emp2;

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|      Time|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    17 |   635 |     6  (50)| 00:00:01 |

|   1 |  UNION-ALL         |      |       |       |            |          |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| EMP2 |     3 |   117 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------




나.Distinct를 Exists 서브쿼리로 대체
select distinct deptno
from emp;

---------------------------------------------------------------------------

| Id  | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    3 |     9 |     4  (25)| 00:00:01 |

|   1 |  HASH UNIQUE        |      |    3 |     9 |     4  (25)| 00:00:01 |

|   2 |   TABLE ACCESS FULL |  EMP |   14 |    42 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------



select deptno
 from emp a
where exists (select 'x'
                from dept
               where deptno = a.deptno);



----------------------------------------------------------------------------------

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)|     Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |    14 |    84 |     1   (0)|     00:00:01 |

|   1 |  NESTED LOOPS SEMI |         |    14 |    84 |     1   (0)|     00:00:01 |

|   2 |   INDEX FULL SCAN  | EMP_X01 |    14 |    42 |     1   (0)|     00:00:01 |

|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    12 |     0   (0)|     00:00:01 |

----------------------------------------------------------------------------------



다.불필요한 Count 연산 제거
데이터의 존재 유무를 판단할때 가장 흔하게 실수하는 예

declare
l_cnt number;
begin
select count(*) into l_cnt
 from member
where member_cls = ‘1’
   and birth_yyyy <= ‘1950’;

if l_cnt > 0 then
dbms_output.put_line(‘exists’);
else
dbms_output.put_line(‘not exists’);
end if;

end;
→ 0보다 크다는 조건을 체크 하기위해 해당 조건에 만족하는 모든 로우를 읽음.
    불필요한 I/O발생


declare
l_cnt number;
begin
select 1 into l_cnt
 from member
where memb_cls=’1’
  and birth_yyyy <= ‘1950’
  and rownum <= 1;

dbms_output.put_line(‘exists’);

exception
when no_data_found then
dbms_output.put_line(‘not exists’);
end;



4.인덱스를 이용한 소트 연산 대체
인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로 이를 이용해 소트 오퍼레이션을 생략할 수 있다.

emp테이블에서 empno(pk)로 정렬을 하는 경우

select /*+ index (emp pk_emp) */ *
from emp
order by empno

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|   Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |    14 |   518 |     2   (0)|   00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   518 |     2   (0)|   00:00:01 |

|   2 |   INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)|   00:00:01 |

----------------------------------------------------------------------------------------

→ order by (sort) 연산이 나타나지 않는걸 확인 할수 있음



나.Sort Group By 대체
인덱스를 이용하여 sort group by 연산을 대체

select /*+ index (emp pk_emp) */ count(*)
from emp
group by empno

----------------------------------------------------------------------------------

| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)|     Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    14 |    56 |     1   (0)|     00:00:01 |

|   1 |  SORT GROUP BY NOSORT|       |    14 |    56 |     1   (0)|     00:00:01 |

|   2 |   INDEX FULL SCAN    | PK_EMP|    14 |    56 |     1   (0)|      00:00:01|

-----------------------------------------------------------------------------------



다. 인덱스를 활용한 Min, Max 구하기
인덱스는 정렬상태를 유지하므로, 해당 특성을 이용하여 Min / Max 값을 빠르게 추출할 수 있음

select max(empno)
 from emp

-----------------------------------------------------------------------------------------

| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)|    Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |         |     1 |     4 |     1   (0)|    00:00:01 |

|   1 |  SORT AGGREGATE            |         |     1 |     4 |            |             |

|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_EMP  |    14 |    56 |     1   (0)|    00:00:01 |

-----------------------------------------------------------------------------------------



참고쿼리
select /*+ index_desc (emp pk_emp) */ *
from emp
where rownum =1

-------------------------------------------------------------------------------------------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)|     Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |        |     1 |    37 |     2   (0)|     00:00:01 |

|*  1 |  COUNT STOPKEY               |        |       |       |            |              |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     2   (0)|     00:00:01 |

|   3 |    INDEX FULL SCAN DESCENDING| PK_EMP |    14 |       |     1   (0)|     00:00:01 |

-------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter(ROWNUM=1)


5.소트 영역을 적게 사용하도록 SQL 작성
소트 연산이 불가피하다면 메모리 내에서 처리되게 하도록, 소트 영역을 적게 사용하도록 쿼리를 작성해야함

가.소트 완료후 데이터 가공
1]
select lpad(상품번호, 30) || lpad(상품명, 20) || lpad(고객ID, 10) ||
          lpad(고객명, 20) || to_char(주문일시, ‘yyyymmdd hh24:mi:ss’)
 from 주문상품
where 주문일시 between :start and :end
order by 상품번호

2]
select lpad(상품번호, 30) || lpad(상품명, 20) || lpad(고객ID, 10) ||
          lpad(고객명, 20) || to_char(주문일시, ‘yyyymmdd hh24:mi:ss’)
from (
    select 상품번호, 상품명, 고객ID, 고객명, 주문일시
      from 주문상품
     where 주문일시 between :start and :end
     order by 상품번호
)

1]번 sql은 가공된 결과치를 소트역역에 담음
2]번 sql은 가공되지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공 하므로 1번 sql에 비해 소트영역을 적게 사용
→ lpad 함수 사용으로 인해 가공전 컬럼보다 데이터가 늘어나는 경우


나.TOP-N쿼리
TOP-N쿼리 사용시 소트 영역 사용량을 최소화 할수 있음
(소트 연산(=값 비교) 횟수를 최소화하고, sort area 사용량 감소)

TOP-N쿼리 사용방법
SQL Server
select top 10 거래일시, 체결건수, 체결수량, 거래대금
  from 시간별종목거래
where 종목코드 =’KR123456’
    and 거래일시 >= ‘20080304’

DB2
select 거래일시, 체결건수, 체결수량, 거래대금
  from 시간별종목거래
where 종목코드 =’KR123456’
    and 거래일시 >= ‘20080304’
order by 거래일시
fetch first 10 rows only

oracle
select *
from
(select 거래일시, 체결건수, 체결수량, 거래대금
  from 시간별종목거래
where 종목코드 =’KR123456’
    and 거래일시 >= ‘20080304’
order by 거래일시)
where rownum <= 10




select *
from
(select /*+ index (emp pk_emp) */ *
from emp
order by empno )
where rownum <=5

/* sort order by 오퍼레이션이 나타나지 않음에 주목 */

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)|     Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |        |     5 |   435 |     2   (0)|     00:00:01 |

|*  1 |  COUNT STOPKEY                |        |       |       |            |              |

|   2 |   VIEW                        |        |     5 |   435 |     2   (0)|     00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     5 |   185 |     2   (0)|     00:00:01 |

|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)|     00:00:01 |

--------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter(ROWNUM<=5)




TOP-N 쿼리의 소트 부하 경감 원리

1.종목코드 + 거래일시 순으로 구성된 인덱스가 존재한다면 옵티마이저는 그 인덱스를
   이용함으로써 order by 연산 대체 가능

2.rownum 조건을 사용해 N건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리
  많아도 매우 빠른 수행 속도 낼 수 있음 ==> 실행계획 상 "count stopkey"


3.TOP-N쿼리의 소트 부하 경감 원리 : 종목코드 + 거래일시 순으로 구성된 인덱스가 존재하지
   않았을 경우

3-1.rownum <= 10인 경우  0개 레코드를 담을 배열 할당 하여 처음 읽은 10개 레코드를 정렬된
      상태로 담는다.

3-2.이후 읽는 레코드는 맨 우측에 있는 값(=가장 큰 값)과 비교하여 그보다 작은 값이 나타날

     때만 배열 내에서 다시 정렬 시도하고, 맨 우측 값은 버림

3-3.  3-2과정 반복. 전체 레코드를 정렬하지 않고 오름차순(ASC)으로 최소값을 갖는 10개

        레코드를 정확히 찾아냄



Top-N 쿼리 알고리즘이 작동하지 못하는경우

/*정상*/

select *

 from 

(select rownum rn

   from 

(select /*+ index (emp pk_emp) */ *

 from emp

order by empno )

 where rownum <=50)

 where  rn between 21 and 30;


----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 182 | 2 (0)| 00:00:01 | |* 1 | VIEW | | 14 | 182 | 2 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 14 | | 2 (0)| 00:00:01 | | 4 | INDEX FULL SCAN| PK_EMP | 14 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"<=30 AND "RN">=21) 2 - filter(ROWNUM<=50)




/* top-n 쿼리가 동작하지 않은 경우*/

select *

 from 

(select rownum rn

   from 

(select /*+ index (emp pk_emp) */ *

 from emp

order by empno )

  )

 where  rn between 21 and 30;

----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 182 | 2 (0)| 00:00:01 | |* 1 | VIEW | | 14 | 182 | 2 (0)| 00:00:01 | | 2 | COUNT | | | | | | | 3 | VIEW | | 14 | | 2 (0)| 00:00:01 | | 4 | INDEX FULL SCAN| PK_EMP | 14 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN"<=30 AND "RN">=21)  







윈도우 함수에서 Top-N 쿼리
select *
from
(select /*+ index(emp pk_emp) */
             empno ,
             max(empno) over (partition by deptno) max_empno
   from  emp )
where empno = max_empno;



--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)|   Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |          |    14 |   364 |     3  (34)|   00:00:01 |

|*  1 |  VIEW                         |          |    14 |   364 |     3  (34)|   00:00:01 |

|   2 |   WINDOW SORT                 |          |    14 |    98 |     3  (34)|   00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| EMP      |    14 |    98 |     2   (0)|   00:00:01 |

|   4 |     INDEX FULL SCAN           | PK_EMP   |    14 |       |     1   (0)|   00:00:01 |

--------------------------------------------------------------------------------------------



select *
from
(select /*+ index(emp pk_emp) */
            empno ,
            rank() over (partition by deptno order by empno desc)  rank_empno
from emp )
where rank_empno = 1


-------------------------------------------------------------------------------------------

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)|       Time|

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |          |    14 |   364 |     3  (34)|  00:00:01 |

|*  1 |  VIEW                         |          |    14 |   364 |     3  (34)|  00:00:01 |

|*  2 |   WINDOW SORT PUSHED RANK     |          |    14 |    98 |     3  (34)|  00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| EMP      |    14 |    98 |     2   (0)|  00:00:01 |

|   4 |     INDEX FULL SCAN           | PK_EMP   |    14 |       |     1   (0)|  00:00:01 |

------------------------------------------------------------------------------------------- 


6.소트 영역 크기 조정
SQL Server에서는 소트 영역을 수동으로 조정하는 방법을 제공하지 않음

oracle 8i : sort_area_size 파라미터를 통해 소트 영역 크기 조절
                (alter session set sort_area_size = 1048576;)
oracle 9i : Automatic PGA Memory Management 기능 도입.
                pga_aggregate_target 파라미터를 통해 인스턴스에서 전체적으로 이용 가능한
                PGA메모리 총량을 지정하면 자동으로 각 세션에 메모리 할당.
                (workarea_size_policy=auto인경우 sort_area_size 파라미터 무시)

자동 PGA 메모리 관리 기능을 사용 하더라도 시스템/세션 레벨에서 수동 PGA 메모리 관리 방식으로 전환 가능.
(야간 배치시 리소스가 여유가 있을경우 효과를 볼수 있음- 자동PGA메모리 관리 방식에서는 프로세스당 사용할 수 있는 최대 크기가 제한됨)

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 10485760;