1. 소트 수행 원리 (by xsoft) [2010.06.02]
- 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을 작성하고, 소트가 불가피할 경우 메모리 내에서 수행을 할 수 있도록 해야 함
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2010년 06월 05일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*