소트 튜닝

1. 소트와 성능

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

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

할당하고 정렬을 수행한다. 1장에서 말했다시피 Oracle은 소트 영역을 PGA(Private Global Area) 영역에 할당하고,

SQL Server 는 버퍼 캐시에 할당한다.

소트에 필요한 메모 공간이 부족하면 디스크를 사용하는데, 이 영역을 Oracle 은 Temp Tablespace 라 하고, 

SQL Server 는 tempdb 라고 한다.

가능하면 메모리의 소트영역에서 데이터 정렬 작업을 끝내는 것이 좋지만, 많은 데이터를 정렬할 때는 어쩔 수 없이

디스크 공간을 사용해야 한다. 특히 전체 대상 집합을 디스크에 기록했다가 다시 읽는 작업을 여러 번 반복하는 경우 SQL 수행 성능은 극도로 나빠진다.

구분	설명
메모리(In-Memory) 소트	전체 데이터의 정렬 작업을 할당받은 소트 영역 내에서 완료하는 것을 말하며,  'Internal Sort' 또는 'Optimal Sort' 라고도 한다.
디스크(To-Disk) 소트	할당받은 소트 영역 내에 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며 'External Sort' 라 한다. 디스크에 임시 저장한 반복한 횟수에 따라 디스크 소트를 다음 두 가지로 구분하기도 한다. Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록 Multipass Sort : 정렬 대상 집합을 디스크에 여러번만 기록

 

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

 

1) Sort Aggregate : 전체 로우를 대상으로 집계를 수행할 때 나타난다.

다음과 같이 Oracle 실행계획에 'SORT' 라는 표현이 사용됐지만 실제 소트가 발생하진 않는다. SQL Server 실행계획엔 'Stream Aggregate' 라고 표시된다.

select sum(sal), max(sal), min(sal) from emp;
---------------------------------------------
0       SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=4)
1   0     SORT(AGGREGATE) (Card=1 Bytes=4)
2   1       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=56)

 

2) Sort Order By : 정렬된 결과 집합을 얻고자 할 때 나타난다.

select * from emp by sal desc
---------------------------------------------
0       SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=518)
1    0    SORT(ORDER BY) (Cost=4 Card=14 Bytes=518)
2    1      TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)

 

3) Sort Group By : Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.

select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job;
-----------------------------------
0        SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=11 Bytes=165)
1    0     SORT (GROUP BY) (Cost=4 Card=11 Bytes=165)
2    1       TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=210)

Oracle 은 Hashing 알고리즘으로 그룹별 집계를 수행하기도 하는데, 그때는 실행계획에 다음과 같이 표시된다.

0        SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=11 Bytes=165)
1    0     HASH (GROUP BY) (Cost=4 Card=11 Bytes=165)
2    1       TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=210)

 

4) Sort Unique : 결과 집합에서 중복 레코드를 제거할 때 나타난다.

Union 연산자나 다음과 같이 Distinct 연산자를 사용할 때가 대표적이다.

select distinct deptno from emp order by deptno
-------------------------------------------
0       SELECT STATEMET Optimizer=CHOOSE (Cost=5 Card=3 Bytes=6)
1    0    SORT(UNIQUE) (Cost=4 Card=3 Bytes=6)
2    1       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=26)

5) Sort Join : 소트 머지 조인을 수행할 때 나타난다.

select /*+ordered use_merge(e)*/ * from emp e, dept d
where d.deptno=e.deptno;
---------------------------------------------
0        SELECT STATEMT Optimizer=CHOOSE (Cost=11 Card=3K Bytes=177K)
1    0     MERGE JOIN (Cost=11 Card=3K Bytes=177K)
2    1       SORT (JOIN) (Cost=4 Card=13 Bytes=442)
3    2         TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=442)
4    1       SORT (JOIN) (Cost=7 Card=654 Bytes=19K)
5    4         TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=654 Bytes=19K)

* 소트 머지 조인

NL조인을 효과적으로 수행하려면, 조인 컬럼에 인덱스가 필요하다. 만약 적절한 인덱스가 없다면, 옵티마이저는 소트머지조인이나 해시조인을 고려한다.

• 두 테이블을 각각 정렬한 다음에 두 집합을 머지(Merge)하면서 조인을 수행한다.
  • 소트단계:양쪽 집합을 조인 컬럼 기준으로 정렬한다.
  • 머지단계:정렬된 양쪽 집합을 서로 Merge한다.
• 소트머지 조인은 Outer루프와 Inner루프가 Sort Area에 미리 정렬해둔 데이터를 이용할 뿐, 실제 조인과정은 NL조인과 동일하다.
• 하지만, Sort Area가 PGA영역에 할당되므로 래치획득과정이 없기 때문에, SGA를 경유하는 것보다 훨씬 빠르다.

• 대부분 해시조인인 보다 느린 성능을 보이나, 아래와 같은 상황에서는 소트머지 조인이 유용하다.
  • First테이블에 소트연산을 대체할 인덱스가 있을 때
  • 조인할 First 집합이 이미 정렬되어 있을 때
  • 조인 조건식이 등치(=)조건이 아닐 때

6) Window Sort : 윈도우 함수를 수행할 때 나타난다.

select empno, ename, job, mgr , sal, row_number() over (order by hiredate)
from emp
---------------------------------------
0        SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=364)
1    0     WINDOW (SORT) (Cost=4 Card=13 Bytes=364)
2    1       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=364)

 

다. 소트 튜닝 요약

소트 오퍼레이션은 메모리 집약적(Memory-intensive) 할 뿐만 아니라 CPU 집약적이기도 하다.

소트할 데이터 양이 많을 때는 디스크 I/O 도 발생하므로 쿼리 성능이 나빠진다. 특히 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주요인이 되곤 한다. 될 수 있으면 소트가 발생하지 않게 SQL 을 작성하고, 소트를 피할 수 없다면 메모리에서 수행을 완료할 수 있게 해야한다.

앞으로 설명할 소트 튜닝 방안은 아래와 같다.

- 데이터 모델 측면에서의 검토

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

- 인덱스를 이용한 소트 연산 대체

- 소트 영역을 적게 사용하도록 SQL 작성

- 소트 영역 크기 조정 

---

2. 데이터 모델 측면에서의 검토

자주 사용하는 데이터 액세스 패턴을 고려하지 않고 물리 설계를 진행하거나, M:M 관계를 해소하지 않아 핵심 프로그램이 항상 소트 오퍼레이션을 수반하고 그로 인해 시스템 성능이 저하되는 경우를 흔히 접할 수 있다.

PK 외에 관리할 속성이 아예 없거나 아래 그림의 '가입상품' 처럼 소수일 때 (가입일시), 테이블 개수를 줄인다는 이유로 자식 테이블에 통합시키는 경우를 종종 볼 수 있다.

만약 첫번째 III-5-5 그림같은 테이블 구조라면 쿼리도 아래와 같이 간단해지고 시스템 전반 성능 향상에도 도움이 된다.

아래는 고객별 가입상품 레벨의 데이터 조회(고객ID,  상품ID, 과금액, 가입일시)를 하는 쿼리다.

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

 

아래는 '가입상품' 테이블을 없애고 '고객별상품라인'에 가입일시는 합한 것이다.

 

정보 누락이 없고, 가입일시는 최초 입력 후 데이터 변경이 없어 정합성에도 문제가 없겠지만

이 회사는 고객별 가입상품 레벨의 데이터 조회가 매무 빈번하게 발생한다. 그 때마다 '고객별 상품라인' 테이블을 group by 해야한다면 성능이 좋을리 없다.

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

---

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

가. Union 을 Union All 로 대체 

union 을 사용하면 옵티마이저는 상단과 하단의 두 집합 간 중복을 제거하려고 sort unique연산을 수행한다.

반면 union all 은 중복을 허용하며 두 집합을 단순히 결합하므로 sort 연산이 불필요하다.

select empno,job,mgr from emp where deptno=10
union 
select empno,job,mgr from emp where deptno=20;

위 쿼리에서 Pk컬럼인 empno 가 select-list 에 있어 두 집합 간에는 중복 가능성이 전혀 없어, union 이든 union all 이든 동일한 결과를 반환한다. 

참고로 PK 가 select-list 에 없다면 다른 결과가 나올 수 있으니 주의할 것.

 

나. Distinct 를 Exists 서브쿼리로 대체 

중복 레코드를 제거하기 위해 distinct 를 사용하는 경우가 많은데 대부분의 경우 exists 서브쿼리로 대체해 소트 연산을 제거할 수 있다. 아래는 특정 지역 reg에서 특정 월 이전에 과금이 발생한 연월을 조회한다.

select distinct 과금연월
from 과금
where 과금연월 <= :yyyymm
and 지역 like :reg || '%';

=>

select 연월
from 연월테이블 a
where 연월 <= :yyyymm
and exists (
    select 'x' from 과금 
    where  과금연월 <= a.연월
    and 지역 like :reg || '%'
)

아래 변경한 쿼리는 연월테이블을 먼저 읽고 과금 테이블을 exists 서브쿼리로 필터링한다. 

exists 서브쿼리의 가장 큰 특징은, 메인 쿼리로부터 건건이 입력받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 true 를 반환하고 서브쿼리를 마친다는 점이다. 따라서 과금테이블에 [과금연월, 지역] 순으로 인덱스를 구성해주기만 하면 가장 최적으로 수행될 수 있다.

 

 

다. 불필요한 Count 연산 제거

아래는 데이터 존재 여부만 확인하면 되는데 불필요하게 전체 건수를 count 하는 경우다.

declare
	i_cnt number;
begin    
select count(*) into l_cnt
from member
where mem_cls='1' ;


=>

declare
	i_cnt number;
begin    
select count(*) into l_cnt
from member
where mem_cls='1' 
and rownum<=1; 

SQL Server 인 경우 rownum 대시 Top N 구문을 사용하면 된다.

 

---

4. 인덱스를 이용한 소트 연산 대체 

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

가. Sort Order By 대체 

아래 쿼리를 수행할 때 [region+ custid] 순으로 구성된 인덱스를 사용한다면 sort order by 연산을 대체할 수 있다.

select custid, name,resno,status, tel1 
from customer
where region='A'
order by custid;
---------------------------------------
0 SELECT STATEMENT
1   TABLE ACCESS BY INDEX ROWID | CUSTOMER
2     INDEX RANGE SCAN | CUSTOMER_X02

order by 를 사용했지만 실행계획에서는 sort order by 가 나타나지 않았다. 이 방식으로 수행되면 region='A' 조건을 만족하는 전체 로우를 읽지 않고도 정렬된 결과 집합을 얻을 수 있어 OLTP 환경에서 극적인 성능 개선 효과를 가져다 준다. 물론 소트해야할 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 수 있을 때 유용하다. 만약 인덱스를 스캔하며 끝까지 fetch 하면 오히려 I/O 및 리소스 사용 측면에서 손해다.

 

나. Sort Group By 대체 

방금 본 customer 테이블 예시에서 region 이 선두 컬럼인 인덱스를 사용하면 아래 쿼리에 필요한 sort group by 연산을 대체할 수 있다. 실행계획에서 Sort Group by NOSORT 라고 표시되는 부분을 확인하자.

select region, avg(age), count(*)
from customer
group by region 
-----------------------------------------
0    SELECT STATEMENT
1      SORT GROUP BY NOSORT
2        TABLE ACCESS BY INDEX ROWID  | CUSTOMER
3          INDEX FULL SCAN            | CUSTOMER_X01

다. 인덱스를 활용한 Min, Max 구하기 

인덱스가 항상 정렬 상태를 유지한다는 특징을 이용하면 대상 레코드 전체를 읽지 않고도 Min, Max 값을 빠르게 추출할 수 있다. 예를 들어 주문 테이블에서 일자별 주문번호를 관리한다고 하자. 그러면 PK 인덱스를 [주문일자 + 주문번호] 순으로 구성하는 것만으로 마지막 주문번호를 아주 빠르게 찾을 수 있다. 아래 실행계획에서 FIRST ROW 와 MIN/MAX 오퍼레이션이 나타난 것을 확인하기 바란다. 지금부터 이 기능을 'First Row Stopkey' 알고리즘이라고 부르기로 하자.

select nvl(max(주문번호),0)+1 
from 주문
where 주문일자 = :주문일자;
-----------------------------------------
0       SELECT STATMENT Optimizer=ALL_ROWS
1    0    SORT (AGGREGATE)
2    1     FIRST ROW
3    2       INDEX (RANGE SCAN(MIN/MAX)) OF '주문_PK' (INDEX(UNIQUE))

주의할 점은, 다음과 같이 MAX 함수 내에서 인덱스 컬럼을 가공하면 인덱스를 사용하지 못하게 될 수 있다는 사실이다.

조건절에서 인덱스 컬럼을 가공하면 정상적으로 인덱스를 사용하지 못하는 것과 같은 이유다.

select nvl(max(주문번호+1),1)
from 주문
where 주문일자 = :주문일자
-------------------------------------
0       SELECT STATMENT Optimizer=ALL ROWS
1   0    SORT (AGGREGATE)
2   1      INDEX (RANGE SCAN) OF '주문_PK' (INDEX(UNIQUE))

사실 MAX 함수 내에서 상숫값을 더해서 인덱스 정렬 순서와 일치함에도 Oracle 은 First Row Stopkey 알고리즘을 사용하지 않는다. 반면에 SQL 서버는 사용한다.

---

5. 소트 영역을 적게 사용하도록 SQL 작성 

소트연산이 불가피하다면 메모리 내에서 처리하려고 노력해야한다.

 

가. 소트 완료 후 데이터 가공

특정기간에 발생한 주문상품 목록을 파일로 내리고자 한다. 아래 두 SQL 중 어느 쪽이 소트 영영을 더 적게 사용할까?

1번은 레코드당 105(=30+30+10+20+15) 바이트(헤더빼고 데이터값만) 로 가공한 결과를 소트영역에 담는다.

반면 2번은 SQL 은 가공하지 않은 상태로 정렬을 끝낸 후 최종 출력할 때 가공하므로 1번 SQL 에 비해 소트 영역을 훨씬 적게 사용한다. 실제 테스트해보면 2번이 소트 영역을 훨씬 적게 사용한다. 

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 상품번호;

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

 

나. Top N 쿼리 

Top N 쿼리 형태로 작성하면 소트연산(=값비교)횟수와 소트 영역 사용량을 최소화할 수 있다. 

-SQL server
select top 10 거래일시, 체결건수, 체결수량, 거래대금
from 시간별종목거래
where 종목코드 = 'kr123456'
and 거래일시 >= '20080304';

-Oracle
select * from (
    select 거래일시, 체결건수, 체결수량, 거래대금
    from 시간별종목거래
    where 종목코드 = 'kr123456'
    and 거래일시 >= '20080304'
    order by 거래일시
)
where rownum <= 10;

위 쿼리를 수행하는 시점에 [종목코드, 거래일시] 순으로 구성된 인덱스가 존재한다면 옵티마이저는 그 인덱스를 이용함으로써 order by 연산을 대체할 수 있다. 아래 실행계획에서 'SORT ORDER BY' 오퍼레이션이 나타나지 않은 것을 확인하길바란다. 이 기능을 'TOP N Stopkey' 알고리즘이라고 부르자.

rownum 조건을 이용해 N 건에서 멈추게 해 조건절에 부합하는 레코드가 아무리 많아도 매우 빠른 수행속도를 낼 수 있다. 실행계획에 표시된 COUNT(STOPKEY) 가 그것을 의미한다. 

0       SELECT STATEMENT Optimizer=ALL_ROWS
1    0    COUNT(STOPKEY)
2    1     VIEW
3    2       TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE)
4    3         INDEX (RANGE SCAN) OF '시간별종목거래_PK' (INDEX(UNIQUE))

* Top N 쿼리의 소트 부하 경감 원리

[종목코드 + 거래일시] 순으로 구성된 인덱스가 없을 때는 어떨까. 종목코드만을 선두로 갖는 다른 인덱스를 사용하거나 Full Table Scan 방식으로 처리할텐데, 이 때는 정렬작업이 불가피하다. 하지만 Top N Sort 알고리즘이 작동해 소트 영역을 최소한으로 사용하는 효과를 얻게 된다.

예를 들어 Top 10 (rownum <=10) 이면, 아래 그림처럼 우선 10개 레코드를 담을 배열을 할당하고 처음 읽은 10개 레코드를 정렬한 상태로 담는다,

이후 읽는 레코드에 대해서는 맨 우측에 있는 값(=가장 큰 값)과 비교해서 그보다 작은 값이 나타날 때만 배열내에서 다시 정렬을 시도한다. 물론 맨 우측에 있던 값은 버린다. 이 방식으로 처리하면 전체 레코드를 정렬하지 않고도 오름차순으로 최솟값을 가진 10개의 레코드를 정확히 찾을 수 있다. 이것이 Top N 쿼리가 소트 연산 횟수와 소트 영역 사용량을 줄여주는 원리이며, 이를 Top N Sort 알고리즘이라고 부르기로 하자.

 

*Top N Sort 알고리즘이 작동하지 못하는 경우

앞쪽 일부 페이지만 주로 조회할 때 가장 표준적인 페이징 처리 구현 방식을 다음과 같다. 한 페이지에 10개씩 출력한다고 가정하고, 10 페이지를 출력하는 예시다. 

select * from
    ( select rownum no, 거래일시, 체결건수, 거래대금
        from (
            select 거래일시, 체결건수, 거래대금
            from 시간별종목거래
            where 종목코드='kr123456'
            and 거래일시 >= '20080304'
            order by 거래일시
        )
      where rownum <=100
    )
where no between 91 and 100;    

[종목코드 + 거래일시] 순으로 구성된 인덱스가 있으면 최적이겠지만, 없더라도 Top N Sort 알고리즘이 작동해 소트 부하만큼은 최소화할 수 있다.

쿼리를 아래처럼 작성하면 where 절 하나를 줄이고도 같은 결과를 얻을 수 있어 더 좋을 것 같다. 하지만 이렇게 하면 

Top N Stopkey, Top N Sort 알고리즘이 모두 작동하지 않는다.

select * from
    ( select rownum no, 거래일시, 체결건수, 거래대금
        from (
            select 거래일시, 체결건수, 거래대금
            from 시간별종목거래
            where 종목코드='kr123456'
            and 거래일시 >= '20080304'
            order by 거래일시
        )      
    )
where no between 91 and 100;    

* 윈도우 함수에서의 Top N 쿼리

윈도우 함수를 이용해 마지막 이력 레코드를 찾는 경우, 아래는 max() 함수를 사용하는 SQL 이다.

select 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업
from ( select 고객ID, 변경순번
        ,max(변경순번) over (partition by 고객ID) 마지막변경순번
        ,전화번호, 주소 , 자녀수, 직업
        from 고객변경이력
        )
where 변경순번 = 마지막변경순번;        

윈도우 함수를 사용할 때도 max() 함수보다 다음와 같이 rank(), row_number() 함수를 사용하는 것이 유리한데, 

이것 역시 Top N Sort 알고리즘이 작동하기 때문이다.

select 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업
from ( select 고객ID, 변경순번
        ,rank() over (partition by 고객ID order by 변경순번) rnum
        ,전화번호, 주소 , 자녀수, 직업
        from 고객변경이력
        )
where rnum=1;        

 

다. Top N 쿼리를 이용한 효과적인 이력 조회

이력 데이터 조회할 때 'First Row Stopkey' 또는 'Top N Stopkey' 알고리즘이 작동할 수 있도록 인덱스를 설계하고 

SQL 을 구현할 수 있어야 한다. 예를 들어 아래 SQL 은 상태변경이력 테이블에 PK 인덱스를 [장비번호 + 변경일자 + 변경순번] 순으로 구성하더라도 인덱스 컬럼을 가공했으므로 'First Row Stopkey' 알고리즘이 작동하지 않는다.

장비별 상태변경이력이 많을 때 성능 문제가 발생할 수 있다.

select 장비번호, 장비명, 상태코드
, substr(최종이력,1,8) 최종변경일자
, to_number((최종이력,9,4)) 최종변경순번
from (
       select 장비번호, 장비명, 상태코드
       , (
           select max( H.변경일자 || lpad(H.변경순번,4) )
           from 상태변경이력 H
           where 장비번호 = P.장비번호 
          ) 최종이력 
      from 장비 P  
      where 장비구분코드 = 'A001'
)
---------------------------------
0   SELECT STATEMENT
1     SORT AGGREGATE
2      INDEX RANGE SCAN | 상태변경이력_PK
3     TABLE ACCESS BY INDEX ROWID | 장비
4      INDEX RANGE SCAN | 장비_N1

아래 SQL 형태를 보면 인라인 뷰로 정의한 집합(모든 상태변경이력을 읽어 변경일자와 변경순번으로 정렬한 중간 집합)
을 우선 만들고 장비번호와 rownum 을 필터링할 것 처럼 보인다.
하지만 실제 수행해보면 장비번호 = p.장비번호 조건절이 인라인 뷰 안쪽으로 파고 든다.
'조건절 pushing'쿼리 변환이 작동한 결과이다. 이 방식은 혹시 인덱스 구성이 변경되어도 Top N Stopkey 가 작동하지 않아 느려질 순 있지만 쿼리 결과 집합은 보장된다.

select 장비번호, 장비명, 상태코드
, substr(최종이력,1,8) 최종변경일자
, to_number((최종이력,9,4)) 최종변경순번
, substr(최종이력,13) 최종상태코드
from (
       select 장비번호, 장비명
       , ( select 변경일자 || lpad(H.변경순번,4) || 상태코드
           from (
              장비번호, 변경일자, 변경순번, 상태코드		
              from 상태변경이력
              where 장비번호 = P.장비번호 
              order by 변경일자 desc, 변경순번 desc
           )            
           where rownum <= 1 
          ) 최종이력 
      from 장비 P  
      where 장비구분코드 = 'A001'
)

 

11g 버전에서는 아래와 같이 바꿀 수 있다.

select 장비번호, 장비명, 상태코드
, substr(최종이력,1,8) 최종변경일자
, to_number((최종이력,9,4)) 최종변경순번
, substr(최종이력,13) 최종상태코드
from (
       select 장비번호, 장비명
       , ( select 변경일자 || lpad(H.변경순번,4) || 상태코드
           from (
              장비번호, 변경일자, 변경순번, 상태코드		
              from 상태변경이력
              order by 변경일자 desc, 변경순번 desc
           ) 
           where 장비번호 = P.장비번호 
           and rownum <= 1 
          ) 최종이력 
      from 장비 P  
      where 장비구분코드 = 'A001'
)

 

---

6. 소트 영역 크기 조정

소트가 불가피하다면 메모리 내에서 작업을 하는 것이 최적이다. 디스크 소트가 불가피할 땐, 임시 공간에 기록했다가 

다시 읽는 횟수를 최소화할 수 있어야 최적이다. 이를 위해 관리자가 시스템 레벨 또는 사용자가 세션 레벨에서 직접 소트 영역 크기를 조정하는 작업이 필요할 수 있다.

오라클 8i 까지는 데이터 정렬을 위해 사용하는 메모리 공간을 sort_area_size 파라미터를 통해 조정했었다.

기본 값은 관리자가 지정하고, 프로그램의 작업 내용에 따라 세션 레벨에서 다음과 같이 값을 조정하는 식이다.

alter session set sort_area_size=1048576;

9i 부터는 '자동 PGA메모리관리(Automatic PGA Memory Management)' 기능이 도입됐기 때문에 사용자가 일일이 그 크기를 조정하지 않아도 된다. DB 관리자가 pga_aggregate_target 파라미터를 통해 인스턴스 전체적으로 이용 가능한 PGA 메모리 총량을 지정하면, Oracle 이 시스템 부하정도에 따라 자동으로 각 세션에 메모리를 할당해준다.

자동 PGA 메모리관리 기능을 활성화하려면 workarea_size_policy 를 auto 로 설정하면 되는데, 9i 부터 기본적으로 auto 로 설정돼 있으며 sort_area_size 파라미터는 무시된다.

기본적으로 자동 PGA 메모리 관리 방식이 활성화되지만 시스템 또는 세션 레벨에서 '수동 PGA 메모리 관리' 방식으로 전환할 수 있다.

특히 트랜젝션이 거의 없는 야간에 대량 배치 job 을 수행할 때는 수동 방식으로 변경하고 직접 크기를 조정하는 것이 

효과적일 수 있다. 왜냐면 자동 PGA 메모리 관리 방식에서는 프로세스당 사용할 수 있는 소트 영역의 최대 크기를 제한하기 때문이다. 즉 소트 영역을 사용 중인 다른 프로세스가 없더라도 특정 프로세스가 모든 공간을 다 쓸 수 없다.

결국 GB 에 여유 메모리를 두고도 이를 충분히 활용 못해 작업 시간이 오래 걸릴 수 있다.

그럴 때 workarea_size_policy 파라미터를 세션 레벨에서 manual 로 변경하고, 필요한 만큼 소트 영역 크기를 늘려 전체 작업 시간을 크게 단축할 수 있다.

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

---

출처

SQL 전문가 가이드 - 한국데이터산업진흥원