• SORT AREA 할당의 두 유형 (PGA 내)
• IN-MEMORY SORT(메모리 소트; INTERNAL SORT) : 전체 데이터 정렬 작업을 메모리 내에서 완료
• TO-DISK SORT (디스크 소트;EXTERNAL SORT): 항당된 SORT AREA 내에서 정렬이 불가능하여 디스크 공간까지 사용

• 소트의 3개지 방식
• Optimal 소트 : 소트 오퍼레이션이 메모리 내에서만 이루어짐
• Onepass 소트: 정렬 대상 집합이 디스크에 한번만 쓰임
• Multipass 소트 : 정렬 대상 집합이 디스크에 여러번 쓰임

CBO ORACLE원리 13장 참조)
http://www.bysql.net/?document_srl=1107

• 디스크 소트과정

• Sort Run: Sort Area가 찰때마다 정렬된 중간 집합을 Temp Tablespace의 Temp 세그먼트에 임시저장
• Run Merge : Temp 영역에 저장해둔 중간 단계 집합을 Merge 

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

SQL> create table t_emp
as
select *
from scott.emp,(select rownum no from dual connect by level <= 100000) ;
 2    3    4

Table created.

SQL> SQL> alter session set workarea_size_policy=manual;

Session altered.

SQL> alter session set sort_area_size=104875;

Session altered.

SQL> set autot on;
SQL> select *
 2  from (
 3     select no,empno,ename, job,mgr,sal
 4             ,avg(sal) over (partition by to_char(no),deptno) avg_sal
 5     from t_emp
 6  )
 7  where no =1
 8  order by sal desc;

       NO      EMPNO ENAME      JOB              MGR        SAL    AVG_SAL
---------- ---------- ---------- --------- ---------- ---------- ----------
        1       7839 KING       PRESIDENT                  5000 2916.66667
        1       7902 FORD       ANALYST         7566       3000       2175
        1       7788 SCOTT      ANALYST         7566       3000       2175
        1       7566 JONES      MANAGER         7839       2975       2175
        1       7698 BLAKE      MANAGER         7839       2850 1566.66667
        1       7782 CLARK      MANAGER         7839       2450 2916.66667
        1       7499 ALLEN      SALESMAN        7698       1600 1566.66667
        1       7844 TURNER     SALESMAN        7698       1500 1566.66667
        1       7934 MILLER     CLERK           7782       1300 2916.66667
        1       7521 WARD       SALESMAN        7698       1250 1566.66667
        1       7654 MARTIN     SALESMAN        7698       1250 1566.66667

       NO      EMPNO ENAME      JOB              MGR        SAL    AVG_SAL
---------- ---------- ---------- --------- ---------- ---------- ----------
        1       7876 ADAMS      CLERK           7788       1100       2175
        1       7900 JAMES      CLERK           7698        950 1566.66667
        1       7369 SMITH      CLERK           7902        800       2175

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4263631893
--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  1532K|   113M|       |   366K  (1)| 00:42:24 |
|   1 |  SORT ORDER BY       |       |  1532K|   113M|   140M|   366K  (1)| 00:42:24 |
|*  2 |   VIEW               |       |  1532K|   113M|       |   184K  (1)| 00:21:24 |
|   3 |    WINDOW SORT       |       |  1532K|   113M|   140M|   184K  (1)| 00:21:24 |
|   4 |     TABLE ACCESS FULL| T_EMP |  1532K|   113M|       |  3522   (2)| 00:00:25 |
--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("NO"=1)

Note
-----
  - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        74  recursive calls
       722  db block gets
      9196  consistent gets
     59414  physical reads
         0  redo size
      1299  bytes sent via SQL*Net to client
       420  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         1  sorts (memory)
         1  sorts (disk)
        14  rows processed

• 두번의 소트 오프레이션 이 발생하여 한번은 메모리 내에서 처리 , 한번은 디스크 소트갑 발생
• 59414번의 physical reads가 발생하였으며 consistent gets 와 db block get의 값보다 큰것으로 보아
  디스크 소트 과정에서 발생한 i/o까지 포함

sqltrace

 ********************************************************************************

 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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.01       0.01          0         81          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6     23.00      33.63      81864      27588         63          42
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12     23.01      33.64      81864      27669         63          42

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
    14  SORT ORDER BY (cr=9196 pr=27292 pw=18099 time=0 us cost=67077 size=119496000 card=1532000)
    14   VIEW  (cr=9196 pr=27292 pw=18099 time=78 us cost=35299 size=119496000 card=1532000)
1400000    WINDOW SORT (cr=9196 pr=27292 pw=18099 time=6864833 us cost=35299 size=119496000 card=1532000)
1400000     TABLE ACCESS FULL T_EMP (cr=9196 pr=9192 pw=0 time=1396415 us cost=3522 size=119496000 card=1532000)


Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 ----------------------------------------   Waited  ----------  ------------
 SQL*Net message to client                       6        0.00          0.00
 direct path read                              219        0.00          0.27
 direct path write temp                       4422        0.06         11.41
 direct path read temp                       54025        0.00          0.64
 SQL*Net message from client                     6        0.00          0.00
 asynch descriptor resize                       14        0.00          0.00
********************************************************************************


* 4번이상 수행후 trace했음에도 Full table scan에  pr 있음 ㅡㅡ;;;
windows sort 단계의 pr 27292, pw=18099 개의 블록 읽고 쓰기 발생

• auto trace, sql trace외의 통계확인방법
• v$sysstat/v$sessatat / v$mystat
• v$sort_segment
• v$sort_usage
• v$temp_extent_pool

(3) Sort Area

• 데이터 정렬을 위해 사용
• 소트오프레이션이 진행되는 동안 공간이 부족해질때마다 청크 단위(db_block_size) 로 할당
• 세셜별 사용가능 최대공간
• 9i 이전 : sort_area_size 
• 9i 이후 : workarea_size_policy - auto (기본값; 오라클 자체 결정) ,7절 참조

sort_area_retained_size

• 데이터 정렬후 결과집합을 모두 Fetch 할때까지 유지할 sort area 크기 지정
• 초과 데이터는 temp 세그먼트에 저장후 fetch 과정에서 다시 읽힘
• sort_area_size가 충분히 커더라도 이 파라미터가 결과집합보다 작으면 I/O 발생
• 0 으로 설정하면 크기 유지의 의미

• PGA
• Process/Program/Private Global Area
• 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용 (1프로세스, 1PGA)
• 다른프로세스와 공유되지 않는 독립 메모리 공간
• 래치 매커니즘이 필요없어 동일개수의 블록이라도 SGA버퍼 캐시보다 빠름
• 세션과 독립적인 프로세스만의 정보 관리

• UGA
• 세션을 위한 독립적인 메모리 공간 (User Global Area), 1세션 1UGA
• Dedicated Server
• 프로세서와 세션 1:1
• PGA 에 할당
• Shared Server  or MTS
• 프로세서와 세션 1:M
• SGA에 할당
• Large Pool 이 설정됐을 경우: Large Pool
• Large Pool 이 설정되지 않을 경우 : Shared Pool 

• 프로세스와 독립적인 세션만의 정보 관리
• 하나의 데이터베이스 Call을 넘어서 다음 Call까지 계속 참조될 경우 

• CGA
• PGA 에 할당
• Call Global Area
• Call이 진행동안 필요한 데이터를 담음
• Parse Call,Execute Call, Fetch Call 마다 매번 할당
• Recursive Call발생시에도 단계별로 CGA추가 할당
• 하나의Call이 끝나면 해제되 PGA로 반환

• SQL 문 종류와 소트 수행 단계에 따라 다름
• DML 
  
• 하나의 Execute Call내에서 모든 데이터 처리 완료, call 완료순간 자동으로 커서가 닫힘( CGA에 할당)
• SELECT 문 마지막 단계: 계속 이어지는 Fetch Call에서 사용, Sort_area_retained_size제약이 없다면 
• 마지막 소트라면 UGA에 할당
• 마지막 단계보다 앞선 데이터는 첫번째 Fetch Call 내에서만 사용, CGA할당.

select /*+ ordred 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 scott.dept d, scott.emp e
where d.deptno=e.deptno
order by e.sal desc;


Execution Plan
----------------------------------------------------------
Plan hash value: 2862614994

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |    14 |   476 |     9  (34)| 00:00:01 |
|   1 |  SORT ORDER BY                 |         |    14 |   476 |     9  (34)| 00:00:01 | -> UGA
|   2 |   WINDOW SORT                  |         |    14 |   476 |     9  (34)| 00:00:01 | -> CGA
|   3 |    MERGE JOIN                  |         |    14 |   476 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |         |    14 |   294 |     5  (20)| 00:00:01 | -> CGA
|   7 |      TABLE ACCESS FULL         | EMP     |    14 |   294 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  6 - access("D"."DEPTNO"="E"."DEPTNO")
      filter("D"."DEPTNO"="E"."DEPTNO")

요약

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

2. select 문장 수행시

(1) 쿼리 중간 단계의 소트

cga에서 수행. sort_area_retained_size제약이 있다면 다음단계로 넘어가기

전에 이값을 초과하는 cga영역을 반환

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

1. sort_area_retained-size 제약이 있다면, cga에서 소트 수행

이제약만큼의 uga를 할당해 정렬된 결과를 담았다가 이후
fetch call에서 array 단위로 전송

2.sort_area_retained_size제약이 없다면, 곧바로 uga에서 소트 수행

• cga에 할당된 sort area는 하나의 call이 끝나자마자 pga에 반환
• uga에 할당된 sort area는 마지막 로우가 fetch될때 비로서 uga  heap에 반환, 대부분 부모 heap(pga,sga)에도 즉각 반환

(4) 소트 튜닝 요약

• 소트 오프레이션은 데이터량이 많을 경우 디스크 i/o를 유발시키고 이로 인해 성능의 손실과
  부분범위처리의 불가능 등의 문제가 있으므로 가능하면 소트를 발생하지 않도록 sql을 작성해야하며
  불가피할경우 메모리 내에서 완료할수 있도록 해야함

• 데이터 모델 측면에서의 검토
• 소트가 발생하지 않도록 sql작성
• 인덱스를 이용한 소트 연산 대체
• sort area를 적게 사용하도록 sql작성
• sort area크기 조정

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 테스트 (아이디)
• 최초작성일: 2011년 6월 12일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^