1. 소트 수행 원리

조회 수 6650 추천 수 0 2011.06.16 00:04:41
balto *.49.6.17

(1) 소트 수행 과정

 

■ SQL 수행 도중 데이터 정렬이 필요할 때 오라클은 PGA 메모리에 Sort Area를 할당하며, 완료여부에 따라 두 가지 유형으로 나눔

- 메모리 소트(in-memory sort, optimal sort, internal sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것.

- 디스크 소트(to-dist sort, onepass or multipass sort, external sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것. Sort Area 내에서 데이터 정렬을 마무리하는 것이 최적이나(Optimal 소트), 양이 많을 때는 정렬된 중간 결과집합을 Temp 테이블스페이스의 Temp 세그먼트에 임시 저장. Sort Area가 찰 때마다 Temp 영역에 저장해 둘 때 이 중간 단계의 집합을 'Sort Run'이라고 함. Sort Area의 크기가 'Sort Run'에 있는 크기에 비례하여 한번에 읽어들일 수 있다면(Onepass 소트) 추가적인 Disk I/O는 발생하지않지만, 그보다 크기가 작다면 여러번 액세스해야 하므로(Multipass 소트) 성능이 나빠짐

 

fig5-0.JPG

fig5-1.jpg  

 

(2) 소트 오퍼레이션 측정

■ 실험

소트 오퍼레이션이 AutoTrace에서 어떻게 측정되는지 살펴봄(스크립트 ch05_01.txt)

(실험조건) 디스크 소트가 발생하도록 하기 위해 workarea_size_policy를 manual로 변경하고, sort_area_size는 1MB로 낮게 설정

=> ch05_01.hwp 파일참조

 

■ 실험결과

-- t_emp는 emp 테이블을 10만번 복사한 테이블

select *

from (

      select no, empno, ename, job, mgr, sal

           , avg(sal) over (partition by to_char(no), deptno) avg_sal

      from t_emp

)

where no = 1

order by sal desc ;

Execution Plan

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

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

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

| 0 | SELECT STATEMENT | | 1045K| 77M| | 46561 (2)|

| 1 | SORT ORDER BY | | 1045K| 77M| 192M| 46561 (2)|

|* 2 | VIEW | | 1045K| 77M| | 24328 (2)|

| 3 | WINDOW SORT | | 1045K| 77M| 192M| 24328 (2)|

| 4 | TABLE ACCESS FULL| T_EMP | 1045K| 77M| | 2094 (4)|

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

 

Statistics

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

230 recursive calls

78 db block gets

8022 consistent gets

28332 physical reads

0 redo size

1244 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

1 sorts (disk)

12 rows processed

- 위의 결과에서 보듯이 '1 sorts (memory)', '1 sorts (disk)' 처럼 소트를 각각 메모리와 디스크에서 한 번씩 함.

- 데이터량을 감안할 경우 데이터가 적은 쪽이 메모리를 사용할 것이므로, 분석함수는 디스크에서 하고 ORDER BY는 메모리에서 발생함.

- 일반적으로, physical reads(28332)는 consistent gets(8022)+db block gets(78)보다 클 수 없는데 여기서는 디스크 I/O와 하드파싱 I/O까지 추가되어 커짐

(SQL TRACE)

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.06 2 2 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 7.46 26.26 28976 7941 12 12

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

total 4 7.46 26.33 28978 7943 12 12

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 87

 

Rows Row Source Operation

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

12 SORT ORDER BY (cr=7941 pr=28976 pw=21295 time=26267009 us)

12 VIEW (cr=7941 pr=28976 pw=21295 time=15014498 us)

1200000 WINDOW SORT (cr=7941 pr=28976 pw=21295 time=27041975 us)

1200000 TABLE ACCESS FULL T_EMP (cr=7941 pr=7680 pw=0 time=6018266 us)

 

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

db file scattered read 528 0.08 2.01

db file sequential read 7 0.00 0.00

direct path write temp 938 0.10 0.94

direct path read temp 12051 0.15 15.63

SQL*Net message from client 2 0.02 0.04

 

- 위의 결과에서 보듯이, 테이블 액세스 시 pr 블록은 7680이나, 분석함수에 의한 'WINDOW SORT pr = 28876은 늘어났으므로 전량 소트에서 발생한 것을 알 수 있으며 Temp 세그먼트에서 발생

 

 

(3) Sort Area

- 데이터 정렬을 위해 Sort Area는 소트 오퍼레이션이 진행되는 동안 공간이 부족할 때 마다 db_block_size 설정값으로 조금씩 할당함.

- 세션마다 사용할 수 있는 최대 크기를 9i 이전에는 sort_area_size 파라미터로 설정하였으나, 9i부터는 workarea_size_policy 파라미터를 auto로 설정하여 오라클이 내부적으로 결정하도록 함.

- sort_area_retained_size는 데이터 정렬을 끝내고 나서 결과집합을 모두 Fetch할 때까지 유지할 Sort Area 크기를 지정, 이 크기를 초과한 데이터는 Temp 세그먼크에 저장했다가 Fetch 과정에서 다시 읽어들이므로, sort_area_size 크기보다 정렬된 결과 집합보다 작다면 디스크 I/O가 발생함. 참고로, 0으로 설정하면 Fetch가 완료될 때까지 Sort Area 크기를 그대로 유지하겠다는 의미.

  

PGA, UGA, CGA

1) PGA(Process Global Area)

- 각 오라클 서버 프로세스는 자신만의 PGA 메모리 영역을 할당받고, 이를 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용

- PGA는 다른 프로세스와 공유되지 않는 독립적인 메모리 공간으로, 래치 메커니즘이 필요 없어 SGA보다 훨씬 빠름

2) UGA(User Global Area)

- 전용서버 방식으로 연결할 때는 프로세스와 세션이 1:1이지만, 공유서버 방식은 1:M 관계이므로, 공유서버에서 여러 세션을 관리하기 위해 UGA 공간을 사용함

- 하나의 프로세스는 하나의 PGA를 갖는다.

- 하나의 세션은 하나의 UGA를 갖는다.

- PGA에는 세션과 독립적인 프로세스만의 정보를 관리한다.

- UGA에는 프로세스와 독립적인 세션만의 정보를 관리한다.

- 거의 대부분 전용 서버 방식을 사용하므로 세션과 프로세스는 1:1 관계고, 따라서 UGA도 PGA 내에 할당된다고 보면 된다.

3) CGA(Call Global Area)

- 오라클은 하나의 데이터베이스 Call을 넘어서 다음 Call까지 계속 참조되어야 하는 정보는 UGA에 담고, Call이 진행되는 동안에 필요한 데이터는 CGA에 담음.

- CGA는 Parse, Execute, Fetch Call 마다 매번 할당받는데, Recursive Call이 발생하면 그 안에서도 단계별로 CGA가 추가로 할당

- CGA : Call이 진행되는 동안만 필요한 정보 저장.

- UGA : Call을 넘어서 다음 Call까지 계속 참조되는 정보 저장.

 

fig5-2.jpg

 

fig5-3.jpg

 

 

 

 

 

Sort Area 할당 위치

- Sort Area 할당 위치는 SQL문 종류와 소트 수행 단계에 따라 다름

- DML 일 경우, 하나의 Execute Call 내에서 모든 데이터 처리를 완료하며, Execute Call이 끝나는 순간 자동으로 커서가 닫히므로, Sort Area를 CGA에 할당함.

- SELECT일 경우, Execute Call 이후 Fetch Call을 해야 하므로 Sort Area는 UGA에 할당함. 반면, 마지막보다 앞선 단계에서 정렬된 데이터는 첫 번째 Fetch Call 내에서만 사용되므로 CGA에 할당함.

 

■ 실험

set autotrace traceonly;

 

SELECT /*+ ORDERED USE_MERGE(E) */

D.DEPTNO, D.DNAME, E.ENAME, E.SAL, E.JOB,

RANK() OVER(PARTITION BY D.DEPTNO ORDER BY E.SAL) SAL_RANK

FROM DEPT D, EMP E

WHERE D.DEPTNO = E.DEPTNO

ORDER BY E.SAL DESC;

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

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

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

| 0 | SELECT STATEMENT | | 12 | 408 | 8 (38)|

| 1 | SORT ORDER BY | | 12 | 408 | 8 (38)|

| 2 | WINDOW SORT | | 12 | 408 | 8 (38)|

| 3 | MERGE JOIN | | 12 | 408 | 6 (17)|

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)|

| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|

|* 6 | SORT JOIN | | 12 | 252 | 4 (25)|

| 7 | TABLE ACCESS FULL | EMP | 12 | 252 | 3 (0)|

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

- SELECT문 마지막 단계라도 sort_area_retained_size 제약이 있을 경우, 소트 작업은 CGA에서 수행하고 제약만큼의 공간을 UGA에 할당해 소트된 결과를 옮김

■ 요약 내용

1. DML 문장 수행 시 발생하는 소트는 CGA에서 수행

2. SELECT 문장 수행 시

1) 쿼리 중간 단계의 소트

    ① CGA에서 수행. sort_area_retained_size 제약이 있다면 다음 단계로 넘어가기 전에 이 값을 초과하는 CGA 영역을 반환

2) 결과집합을 출력하기 직전 단계에서 수행하는 소트

    ① sort_area_retained_size 제약이 있다면, CGA에서 소트 수행.

         이 제약만큼의 UGA를 할당해 정렬된 결과를 담았다가 이후 Fetch Call에서 Array 단위로 전송

    ② sort_area_retained_size 제약이 없다면, 곧바로 UGA에서 소트 수행

 

(4) 소트 튜닝 요약

- 소트 오퍼레이션은 메모리 집약적일 뿐만 아니라 CPU 집약적이고, 데이터량이 많을 경우 디스크 I/O까지 발생시키므로 쿼리 성능을 좌우하는 요인

- 특히 부분범위처리를 할 수 없기 때문에 OLTP에서 성능을 떨어뜨리는 주 요인으로 작용.

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

 

■ 소트 튜닝 방안 - 소트 발생을 줄이며, 인덱스를 이용한 소트, Sort Area 절약 등으로 튜닝