by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

1. 소트 수행 원리




  • SQL 튜닝요소 중에서 소트 튜닝 또한 중요한 파트.
  • 소트 오퍼레이션은 수행과정에서 CPU와 메모리를 많이 사용하고, 데이터량이 많을 때는 디스크 I/O까지 일으킴.
  • 많은 서버 리소스를 사용하는것도 문제지만, 부분범위처리를 불가능하게 해 OLTP에 치명적임

1. 소트 수행 과정

  • SQL 수행 도중 데이터 정렬이 필요할 때 오라클은 PGA 메모리에 Sort Area를 할당하며, 완료여부에 따라 두 가지 유형으로 나눔
    • 메모리 소트(in-memory sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것. 'internal Sort'
    • 디스크 소트(to-dist sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것. 'External Sort'

  • Sort Area 내에서 데이터 정렬을 마무리하는 것이 최적이나(Optimal 소트), 양이 많을 때는 정렬된 중간 결과집합을
    Temp 테이블스페이스의 Temp 세그먼트에 임시 저장.
  • Sort Area가 찰 때마다 Temp 영역에 저장해 둘 때 이 중간 단계의 집합을 'Sort Run'이라고 함.
  • Sort Area의 크기가 'Sort Run'에 있는 크기에 비례하여 한번에 읽어들일 수 있다면(Onepass 소트) 추가적인 Disk I/O는 발생하지
    않지만, 그보다 크기가 작다면 여러번 액세스해야 하므로(Multipass 소트) 성능이 나빠짐.
    • Optimal 소트 : 소트 오퍼리에션이 메모리 내에서만 이루어짐
    • Onepass 소트 : 정렬 대상 집합이 디스크에 한 번만 쓰임
    • Multipass 소트 : 정렬 대상 집합이 디스크에 여러 번 쓰임

2. 소트 오퍼레이션 측정

  • 소트 오퍼레이션이 AutoTrace에서 어떻게 측정되는지 살펴봄
  • 디스크 소트가 발생하도록 하기 위해 workarea_size_policy를 manual로 변경하고, sort_area_size는 1MB로 낮게 설정
SELECT * FROM v$version
;

BANNER                                                            
------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit  
PL/SQL Release 10.2.0.3.0 - Production                            

-- EMP, DEPT 테이블 생성
CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

COMMIT;
/

CREATE UNIQUE INDEX EMP_U1 ON EMP (EMPNO);

CREATE INDEX EMP_N1 ON EMP (DEPTNO);

CREATE UNIQUE INDEX DEPT_U1 ON DEPT (DEPTNO);

CREATE INDEX DEPT_N1 ON DEPT (LOC);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER,
                                'EMP',
                                CASCADE => TRUE);
END;
/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER,
                                'DEPT',
                                CASCADE => TRUE);
END;
/

-- EMP 테이블 복제한 테이블 생성
CREATE TABLE T_EMP AS
SELECT *
FROM   EMP,
       (SELECT ROWNUM NO
        FROM   DUAL
        CONNECT BY LEVEL <= 100000);

-- Hidden Parameter 현재 세팅된 값
SELECT A.KSPPINM  NAME,
       B.KSPPSTVL VALUE,
       B.KSPPSTDF DEF_YN,
       A.KSPPDESC DESCRIPTION
FROM   X$KSPPI  A,
       X$KSPPSV B
WHERE  A.INDX = B.INDX
AND    LOWER(A.KSPPINM) IN (TRIM(LOWER('workarea_size_policy ')),
                            TRIM(LOWER('sort_area_size')))
ORDER  BY 1
;

NAME                 VALUE DEF_YN DESCRIPTION                                           
-------------------- ----- ------ ------------------------------------------------------
workarea_size_policy AUTO  FALSE  policy used to size SQL working areas (MANUAL/AUTO)   
sort_area_size       65536 TRUE   size of in-memory sort work area                      
;

-- workarea_size_policy 값 세션 레벨에서 변경
ALTER SESSION SET workarea_size_policy = MANUAL;

-- sort_area_size 값 세션 레벨에서 변경
ALTER SESSION SET sort_area_size = 1048576;

-- AutoTrace 테스트
SET autotrace traceonly;

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
----------------------------------------------------------                                    
Plan hash value: 2032858773                                                                   
                                                                                              
--------------------------------------------------------------------------------------        
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |        
--------------------------------------------------------------------------------------        
|   0 | SELECT STATEMENT     |       |  1309K|    97M|       | 67057   (1)| 00:13:25 |        
|   1 |  SORT ORDER BY       |       |  1309K|    97M|   240M| 67057   (1)| 00:13:25 |        
|*  2 |   VIEW               |       |  1309K|    97M|       | 34817   (1)| 00:06:58 |        
|   3 |    WINDOW SORT       |       |  1309K|    97M|   240M| 34817   (1)| 00:06:58 |        
|   4 |     TABLE ACCESS FULL| T_EMP |  1309K|    97M|       |  2576   (2)| 00:00:31 |        
--------------------------------------------------------------------------------------        
                                                                                              
Predicate Information (identified by operation id):                                           
---------------------------------------------------                                           
                                                                                              
   2 - filter("NO"=1)                                                                         
                                                                                              
Note                                                                                          
-----                                                                                         
   - dynamic sampling used for this statement                                                 
   
Statistics
----------------------------------------------------------
        104  recursive calls                              
       3958  db block gets                                
       9399  consistent gets                              
      80275  physical reads                               
          0  redo size                                    
        893  bytes sent via SQL*Net to client             
        240  bytes received via SQL*Net from client       
          2  SQL*Net roundtrips to/from client            
          1  sorts (memory)                               
          1  sorts (disk)                                 
         14  rows processed                
;
  • 위의 결과에서 보듯이 '1 sorts (memory)', '1 sorts (disk)' 처럼 소트를 각각 메모리와 디스크에서 한 번씩 함.
  • 데이터량을 감안할 경우 데이터가 적은 쪽이 메모리를 사용할 것이므로, 분석함수는 디스크에서 하고 ORDER BY는 메모리에서 발생함.
  • 일반적으로, pr블록(physical READS - 80275)은 cr블록((db block gets - 3958) + (consistent gets - 9399))보다 클 수 없는데
    여기서는 디스크 I/O와 하드파싱 I/O가지 추가되어 커짐

-- Trace 결과
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.020        0.012          0        121          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        3   26.970      134.809      71180       9261       3958         14
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        5   26.990      134.821      71180       9382       3958         14

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     14   SORT ORDER BY (cr=9261 pr=71180 pw=71179 time=134808812 us)
     14    VIEW  (cr=9261 pr=71180 pw=71179 time=113696286 us)
1400000     WINDOW SORT (cr=9261 pr=71180 pw=71179 time=126296185 us)
1400000      TABLE ACCESS FULL T_EMP (cr=9255 pr=0 pw=0 time=106 us)

Wait Event Name                                      Count Wait(sec)  Max Wait
-------------------------------------------------- ------- ---------- --------
direct path write temp                                13778     39.047    0.003
SQL*Net message to client                                 3      0.000    0.000
SQL*Net message from client                               3      0.005    0.003
direct path read temp                                 57670     70.355    0.000
--------------------------------------------------- ------- --------- --------
Total                                                 71454    109.41
;
  • 위의 결과에서 보듯이, 테이블 액세스 시 pr 블록은 0이나, 분석함수에 의한 'WINDOW SORT'시 pr 블록이 71180으로 늘어났으므로
    전량 소트에서 발생한 것을 알 수 있으며, 추가로 pw의 지표의 결과처럼 71179 블록을 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까지 계속 참조되는 정보 저장.

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)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    14 |  1092 |    14  (29)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |    14 |  1092 |    14  (29)| 00:00:01 | --> UGA
|   2 |   WINDOW SORT         |      |    14 |  1092 |    14  (29)| 00:00:01 | --> CGA
|   3 |    MERGE JOIN         |      |    14 |  1092 |    12  (17)| 00:00:01 |  
|   4 |     SORT JOIN         |      |     4 |   116 |     6  (17)| 00:00:01 | --> CGA
|   5 |      TABLE ACCESS FULL| DEPT |     4 |   116 |     5   (0)| 00:00:01 |  
|*  6 |     SORT JOIN         |      |    14 |   686 |     6  (17)| 00:00:01 | --> CGA
|   7 |      TABLE ACCESS FULL| EMP  |    14 |   686 |     5   (0)| 00:00:01 | 
------------------------------------------------------------------------------
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   6 - access("D"."DEPTNO"="E"."DEPTNO")                                      
       filter("D"."DEPTNO"="E"."DEPTNO")                                      
                                                                              
Note                                                                          
-----                                                                         
   - dynamic sampling used for this statement                                 


Statistics
----------------------------------------------------------                    
          0  recursive calls                                                  
          0  db block gets                                                    
         30  consistent gets                                                  
          0  physical reads                                                   
          0  redo size                                                        
        925  bytes sent via SQL*Net to client                                 
        241  bytes received via SQL*Net from client                           
          2  SQL*Net roundtrips to/from client                                
          4  sorts (memory)                                                   
          0  sorts (disk)                                                     
         14  rows processed                                                                                                  

;

  • 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 제약이 없다면, 곧바로 UA에서 소트 수행
    

4. 소트 튜닝 요약

  • 소트 오퍼레이션은 메모리 집약적일 뿐만 아니라 CPU 집약적이고, 데이터량이 많을 경우 디스크 I/O까지 발생시키므로 쿼리 성능을 좌우하는 요인
  • 특히 부분범위처리를 할 수 없기 때문에 OLTP에서 성능을 떨어뜨리는 주 요인으로 작용.
  • 그러므로 가급적 소트를 발생시키지 않도록 SQL을 작성하고, 소트가 불가피할 경우 메모리 내에서 수행을 할 수 있도록 해야 함

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.