소트 수행 원리 (by niceboyous) [2013.01.29]
소트 튜닝.
| 1. CPU,메모리 사용이 많다. 2. 데이터량이 많을때 디스크I/O까지 유발한다. 3. 부분범위 처리가 불가(어플리케이션 성능저하) |
|---|
OCM코리아 발췌 http://www.ocmkorea.com
*정렬 작업 최적화* --> 오라클 퍼포먼스 튜닝
1.자동 정렬 영역 관리
(1) PGA_AGGREGATE_TARGET
- Instance에 첨부된 모든 전용 세션의 대상 집계 PGA 메모리를 지정
- 파라미터 값 범위 10MB ~ 4000GB
- 이 파라미터를 설정할 때는 Oracle Instance에 사용할 수 있는 전체 시스템 메모리에서
SGA를 뺀 나머지 메모리를 PGA_AGGREGATE_TARGET에 지정해야 함
(2) WORKAREA_SIZE_POLICY
- Auto : PGA_AGGREGATE_TARGET이 정의되어 있는 경우에만 지정가능
- Manual : 작업 영역의 크기 조정은 수동이고 작업에 해당되는 *_AREA_SIZE 파라미터 값을 기반으로 함
MANUAL을 지정하면 성능이 준최적화되고 PGA 메모리 활용률이 낮아짐
2. PGA 관리 자원
(1) PGA_AGGREGATE_TARGET 초기화 파라미터를 관리하기 위한 통계
- v$sql_workarea_histogram
- v$pgastat
- v$sql_workarea_active
- v$sql_workarea
- v$tempseg_usage
(2) PGA 작업 영역 크기 조정을 지원하는 뷰
- v$pga_target_advice
- v$pga_target_advice_histogram
3. 자동 정렬 영역 관리 기능
- *_SORT_AREA 파라미터 보다 설정 및 크기 조정 용이
- 권고 뷰를 사용한 모니터 용이
4. 정렬 프로세스
(1) SORT_AREA_SIZE 파라미터의 값보다 작은 영역 내에서 작업을 수행할 수 있는 경우
서버는 메모리 내에서 정렬 작업을 수행
(2) 정렬 작업에 이 값보다 더 큰 공간이 필요한 경우
- 데이터는 소트 런이라고 하는 작은 조각으로 나누어지며 각 조각은 개별적으로 정렬됨
- 서버 프로세스는 이 조각을 디스크의 임시 세그먼트에 기록함. 이러한 세그먼트는 서버가
다른 소트 런 작업을 수행하는 동안 중간 소트 런의 데이터를 보유함
- 정렬된 조각은 병합되어 최종 결과로 만들어짐. SORT_AREA_SIZE가 모든 소트 런을 한번에
병합할 만큼 크지 않을 경우 소트 런의 부분 집합들은 여러 병합 패스에서 병합됨
5. 정렬 영역 및 파라미터
(1) 정렬 영역
- Dedicated server에 연결된 경우 PGA
- Oracle Shared server에 연결된 경우 Shared pool
(2) 파라미터
- SORT_AREA_SIZE
- 정렬 영역의 크기는 초기화 파라미터 SORT_AREA_SIZE로 지정
- 이 파라미터는 ALTER SESSION 또는 ALTER SYSTEM DEFERRED 사용하여 동적으로 설정
- 기본값은 운영 체제마다 다른데 대부분 OLTP 작업에 적합함. DSS 응용 프로그램, 일괄 처리 작업
또는 큰 작업의 경우에는 이 값을 높게 조정
- SORT_AREA_RETAINED_SIZE
- 정렬이 완료되었으며 패치할 정렬된 행이 정렬 영역에 남아 있는 경우 정렬 영역의 크기는
SORT_AREA_RETAINED_SIZE 파라미터에서 지정한 크기로 줄어들 수 있음
- 메모리는 마지막 행이 정렬 영역에서 패치된 후 동일한 Oracle 서버 프로세스에서 사용할 수
있도록 운영 체제가 아닌 UGA로 해제됨
- 이 파라미터의 기본값은 SORT_AREA_SIZE 파라미터의 값과 동일
- 하나의 실행 계획에 여러 정렬이 포함될 수 있음
- 정렬을 수행하는 단일 서버
- SORT_AREA_SIZE의 영역
- 조인 정렬의 경우 SORT_AREA_RETAINED_SIZE로 지정한 크기를 갖는 하나 이상의 영역
- 각 Parallel query 서버에는 SORT_AREA_SIZE가 필요
- 두 서버 집합을 한 번에 기록할 수 있음
- (SORT_AREA_SIZE * 2 * 병렬도) 계산
- (SORT_AREA_RETAINED_SIZE * 병렬도 * 두 개를 초과하는 정렬의 개수) 추가
6. 정렬 작업 튜닝
- 자동 정렬 영역 관리 사용
- 가능하면 정렬 작업 안함
- 가능하면 메모리 내에서 정렬 작업을 수행하여 스와핑 및 페이징 줄이기
- 임시 공간을 적합하게 할당하여 공간 할당 호출 줄이기
7. 임시 공간 세그먼트
- 첫번째 정렬 작업으로 생성됨
- 요구가 생기면 크기가 증가됨
- 여러 정렬 작업이 사용할 수 있는 Extent로 구성됨
8. 정렬이 필요한 작업
- 인덱스 생성
- 인덱스 유지 관리를 포함한 병렬 삽입 작업
- ORDER BY 또는 GROUP BY 절
- DISTINCT 값 선택
- UNION, INTERSECT 또는 MINUS 연산자
- Sort-merge 조인
- ANALYZE 명령 실행
9. 정렬 작업 회피
- NOSORT를 사용하여 인덱스 생성
- UNION 대신 UNION ALL 사용
- 테이블 조인에 인덱스 액세스 사용
- ORDER BY 절에서 참조되는 열에 대해 인덱스 생성
- 분석할 열 선택
- 커다란 오브젝트에 대해 COMPUTE 대신 ESTIMATE 사용
10. 진단 및 지침
- OLTP 시스템에서 메모리 정렬에 대한 디스크 정렬의 비율은 5% 미만이어야 함
- 이 비율이 5%보다 크면 SORT_AREA_SIZE / PGA_AGGREGATE_TARGET의 값을 늘림
SQL> select disk.value "DISK", mem.value "MEM",
2 (disk.value/mem.value) * 100 "RATIO" from v$sysstat mem, v$sysstat disk
3 where mem.name='sorts (memory)'
4 and disk.name='sorts (disk)';
DISK MEM RATIO
---------- ---------- ----------
0 6978 0 => 5% 초과시 튜닝(sort_area_size 값을 늘이기)
01 소트수행 원리
(1) 소트수행 과정
메모리소트 (IN-MEMORY SORT) : 전체 데이터의 정렬 작업을 메모리내에서 완료 (=Internal Sort)
디스크소트 (TO-DISK SORT) : 할당받은 Sort Area내에서 정렬을 완료 하지 못해 디스크공간까지 사용 (=External Sort)
1. Sort Area 내에서 데이터 정렬을 마무리하는 것이 최적이나(Optimal 소트)
양이 많을 때는 정렬된 중간 결과집합을 Temp 테이블스페이스의 Temp 세그먼트에 임시 저장.
2. Sort Area가 찰 때마다 Temp 영역에 저장해 둘 때 이 중간 단계의 집합을 'Sort Run'이라고 함.
3. Sort Area의 크기가 'Sort Run'에 있는 크기에 비례하여 한번에 읽어들일 수 있다면(Onepass 소트) \-> 추가적인 Disk I/O는 없음
Sort Area의 크기가 'Sort Run'에 있는 크기에 비하여 그보다 크기가 작다면 여러번 액세스해야 하므로(Multipass 소트) \-> 추가적인 Disk I/O 발생 \-> 성능이 나빠짐.
Optimal 소트 : 소트 오퍼리에션이 메모리 내에서만 이루어짐
Onepass 소트 : 정렬 대상 집합이 디스크에 한 번만 쓰임
Multipass 소트 : 정렬 대상 집합이 디스크에 여러 번 쓰임
(2) 소트 오퍼레이션 측정
SQL> conn scott/tiger
연결되었습니다.
SQL> create table t_emp
2 as
3 select *
4 from emp, (select rownum no from dual connect by level <= 100000);
테이블이 생성되었습니다.
SQL> alter session set workarea_size_policy = manual;
세션이 변경되었습니다.
SQL> alter session set sort_area_size = 1048576;
세션이 변경되었습니다.
SQL> col owner format a10
SQL> col segment_name format a20
SQL> col segment_type format a20
SQL> SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,BLOCKS,EXTENTS
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_NAME = 'T_EMP' and OWNER = 'SYS';
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS EXTENTS
---------- -------------------- -------------------- ---------- ---------- ----------
SYS T_EMP TABLE 75497472 9216 80
SQL> select count(*) from sys.t_EMP;
COUNT(*)
----------
1400000
SQL> set autotrace 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
1 7876 ADAMS CLERK 7788 1100 2175
1 7900 JAMES CLERK 7698 950 1566.66667
1 7369 SMITH CLERK 7902 800 2175
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 4263631893
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1643K| 122M| | 75352 (2)| 00:15:05 |
| 1 | SORT ORDER BY | | 1643K| 122M| 302M| 75352 (2)| 00:15:05 | -> sorts (memory)
|* 2 | VIEW | | 1643K| 122M| | 38733 (2)| 00:07:45 |
| 3 | WINDOW SORT | | 1643K| 122M| 302M| 38733 (2)| 00:07:45 | -> sorts (disk)
| 4 | TABLE ACCESS FULL| T_EMP | 1643K| 122M| | 2114 (5)| 00:00:26 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NO"=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
221 recursive calls
90 db block gets ==>
9301 consistent gets ==>
23810 physical reads ==>
0 redo size
1275 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
14 rows processed
소트가 안일어 났을경우
physical reads(디스크IO)
Logical reads = consistent gets + db block gets
| 참고 --> 디비가이드 발췌 \*consistent gets는 consistent mode에서 db block read를 수행한 숫자입니다. TKPROF 레포트에서 query에 해당하는 값입니다. consistent라는 말은 read consistency와 관련이 있는데 즉 읽기 일관성이 보장되는 상황에서 읽는 숫자라는 것입니다. 데이터를 수정하지 않고 단지 읽기만 하기때문에 lock이 발생하지 않습니다. \*db block gets는 CURRENT mode에 있는 block의 데이터를 읽은 숫자입니다. TKPROF 레포트에서 current에 해당하는 값입니다 current mode에서는 곧 수정될 Segment header나 block을 얻을 때 일어납니다. INSERT, UPDATE, DELETE에서 데이터 쿼리부분이 아니라 수정될 값을 읽을 때 나타나는 숫자입니다. select문일 경우에는 Full table scan일 경우 Segment header를 읽을 때 나타납니다 |
|---|
- consistent gets는 physical reads를 포함하고 있기 때문에 실제로 쿼리를 튜닝할 때 중요한 것은 Logical IO를 줄이는 것입니다.*
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 개의 행이 선택되었습니다.
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 개의 행이 선택되었습니다.
SQL> alter session set events '10046 trace name context forever, level 12' ;
세션이 변경되었습니다.
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 개의 행이 선택되었습니다.
SQL> alter session set events '10046 trace name context off'
2 ;
세션이 변경되었습니다.
SQL> exit
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options에서 분리되었습니다.
C:\Users\Administrator>TKPROF D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_8700.trc output1.txt
TKPROF: Release 10.2.0.3.0 - Production on 화 5월 28 11:36:42 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
C:\Users\Administrator>
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 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.60 8.05 20444 9202 11 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.60 8.05 20444 9202 11 14
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
14 SORT ORDER BY (cr=9202 pr=20444 pw=20443 time=8054450 us)
14 VIEW (cr=9202 pr=20444 pw=20443 time=4189023 us)
1400000 WINDOW SORT (cr=9202 pr=20444 pw=20443 time=9788990 us) ==> 읽기 , 쓰기(TEMP)
1400000 TABLE ACCESS FULL T_EMP (cr=9202 pr=0 pw=0 time=84 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
direct path write temp 158 0.02 0.21 ==>쓰기
direct path read temp 11839 0.04 4.24 ==>읽기
SQL*Net message from client 2 6.71 6.71
-- 엑셈 오라클백과사전 참조( http://wiki.ex-em.com/index.php/Direct_path_read )
- direct path I/O는 SGA 내의 버퍼캐쉬를 거치지 않고 세션의 PGA 로 직접 블록을 읽어 들이는 것
트레이스 파일 참고.
select * from v$sysstat;
select * from v$sesstat;
select * from v$mystat;
select * from v$sort_segment;
select * from v$sort_usage;
select * from v$temp_extend_pool;
select * from v$sysstat;
select * from v$sesstat;
select * from v$mystat ;
select * from v$sysstat where NAME like '%direct%'
select * from v$mystat where STATISTIC# in (select STATISTIC# from v$sysstat where NAME like '%direct%');
select *
from (
select no, empno, ename, job, mgr, sal
, avg(sal) over (partition by to_char(no), deptno) avg_sal
from sys.t_emp
)
where no = 1
order by sal desc
56
63
121
123
| 질문 : UGA는 쉐어드 서버 방식에서 사용하였으며 전용서버(Desicated Server)방식에서는 사용 하지 않습니다. 1. YES 2. NO |
|---|
About Dedicated and Shared Server Processes
http://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc001.htm#ADMIN11166
h2.(3)SORT AREA
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에 할당함.
C:\Users\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on 화 5월 28 16:37:08 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set autot on
SQL> select /*+ ordered use_merge(e) */
2 d.deptno, d.dname, e.ename, e.sal, e.job
3 ,rank() over (partition by d.deptno order by e.sal) sal_rank
4 from scott.dept d , scott.emp e
5 where d.deptno = e.deptno
6 order by e.sal desc;
DEPTNO DNAME ENAME SAL JOB SAL_RANK
---------- -------------- ---------- ---------- --------- ----------
10 ACCOUNTING KING 5000 PRESIDENT 3
20 RESEARCH FORD 3000 ANALYST 4
20 RESEARCH SCOTT 3000 ANALYST 4
20 RESEARCH JONES 2975 MANAGER 3
30 SALES BLAKE 2850 MANAGER 6
10 ACCOUNTING CLARK 2450 MANAGER 2
30 SALES ALLEN 1600 SALESMAN 5
30 SALES TURNER 1500 SALESMAN 4
10 ACCOUNTING MILLER 1300 CLERK 1
30 SALES MARTIN 1250 SALESMAN 2
30 SALES WARD 1250 SALESMAN 2
DEPTNO DNAME ENAME SAL JOB SAL_RANK
---------- -------------- ---------- ---------- --------- ----------
20 RESEARCH ADAMS 1100 CLERK 2
30 SALES JAMES 950 CLERK 1
20 RESEARCH SMITH 800 CLERK 1
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1594771189
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 854 | 10 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 854 | 10 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 854 | 10 (40)| 00:00:01 |
| 3 | MERGE JOIN | | 14 | 854 | 8 (25)| 00:00:01 |
| 4 | SORT JOIN | | 4 | 88 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 546 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL| EMP | 14 | 546 | 3 (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
----------------------------------------------------------
660 recursive calls
0 db block gets
142 consistent gets
15 physical reads
0 redo size
1126 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
18 sorts (memory)
0 sorts (disk)
14 rows processed
**Sort-Merge Join**
- 일반적으로 배치작업에서 주로 사용되며, 각 테이블을 Sort한 후 Merge 하는 조인을 말한다
- Hint : /*+ use_merge(테이블) */
- 동시에 각각의 테이블이 자신의 처리범위를 액세스하여 정렬해 둠
- 각 테이블은 어떠한 상수값도 서로 영향을 주지 않으며, 주어진 상수값에 의해서만 각자 범위를 줄이게됨
- 전체범위처리를하며 부분범위처리를 할수 없음
- 처리량이 많은 경우로 Random Access를 하지 않음으로 전체범위처리에 유리
- 상수값을 받아 줄여진 범위가 30%이상이면 Sort Merge가 유리
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에서 소트 수행
h2.(4) SORT 튜닝 요약
-소트 오퍼레이션은 메모리 집약적일 뿐만 아니라 CPU 집약적이고, 데이터량이 많을 경우 디스크 I/O까지 발생시키므로 쿼리 성능을 좌우하는 요인
-특히 부분범위처리를 할 수 없기 때문에 OLTP에서 성능을 떨어뜨리는 주 요인으로 작용.
-그러므로 가급적 소트를 발생시키지 않도록 SQL을 작성하고, 소트가 불가피할 경우 메모리 내에서 수행을 할 수 있도록 해야 함