next-database: 오라클 소트 작업

일반적으로 sort 작업은 메모리 sort와 디스트 sort로 구분할 수 있다. SQL문에서 sort는 성능상에 아주 나쁜 영향을 미친다.

SQL을 수행하는 도중 소트 오퍼레이션이 필요해 질때 마다 DBMS는 정해진 메모리 공간에
소트 영역(SORT AREA)을 할당하고 정렬을 수행하게 된다.
Oracle은 sort 영역을 PGA에 할당하게 되고 가급적 소트 영역 내에서 데이터 정렬 작업을 완료하는 것이 최적이지만, 대량의 데이터를 정렬할 땐 디스크 소트가 불가피 하다.
소트 영역의 메모리 공간이 부족할 경우에 Oracle은 Temp Tablespace를 이용하여 정렬을 수행한다. 간단하게 메모리 sort와 디스크 sort에 대해서 정리하면 아래와 같다.

메모리 소트 : 전체 데이터의 정렬 작업을 할당받은 소트 영역 내에서 완료하는 것을 말하며, ‘Internal Sort’ 또는 ‘Optimal Sort’라고도 한다.
디스크 소트 : 할당 받은 소트 영역 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, ‘External Sort’ 라고도 한다.
디스크에 임시 저장 했다가 다시 읽는 작업을 반복한 횟수에 따라 디스크 소트를 두 가지로 구분하기도 한다.
- Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록
- Multipass Sort : 정렬 대상 집합을 디스크에 여러 번 기록

다음은 소트를 발생시키는 오퍼레이션 들이다.

Sort Aggregate : 전체 로우를 대상으로 집계를 수행할 때 나타나며, 실제 소트가 발생하지는 않는다.

Sort Order By : 정렬된 결과집합을 얻고자 할 때 나타난다.

Sort Group By : Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다. Oracle의 경우 Hashing 알고리즘으로 그룹별 집계를 수행하기도 하는데(10g 이상) 그때는 실행계획에 ‘HASH (GROUP BY) ‘ 가 나타 난다

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    14 |   448 |     4 (25)| 00:00:01 |
|   1 | SORT GROUP BY     |       |    14 |   448 |     4 (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP |    14 |   448 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    14 |   448 |     4 (25)| 00:00:01 |
|   1 | HASH GROUP BY     |       |    14 |   448 |     4 (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP |    14 |   448 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Sort Unique : 선택된 결과집합에서 중복 레코드를 제거하고자 할 때 나타난다. Union 연산자나 Distinct 연산자를 사용할 때가 대표적이다.

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    14 |   182 |     5 (40)| 00:00:01 |
|   1 | SORT UNIQUE       |       |    14 |   182 |     4 (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    14 |   182 |     5 (40)| 00:00:01 |
|   1 | HASH UNIQUE       |       |    14 |   182 |     4 (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Sort Join : Sort Merge Join을 수행할 때 나타 난다

------------------------------------------------------------------------------
| Id | Operation           | Name   | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |    14 | 1638 |     8 (25)| 00:00:01 |
|   1 | MERGE JOIN         |        |    14 | 1638 |     8 (25)| 00:00:01 |
|   2 |   SORT JOIN         |        |    14 | 1218 |     4 (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP |    14 | 1218 |     3   (0)| 00:00:01 |
|* 4 |   SORT JOIN         |        |     4 |   120 |     4 (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Window Sort : 윈도우 함수를 수행할 때 나타난다.

소트 오퍼레이션은 메모리 집약적(Memory-intensive)일뿐만 아니라 CPU 집약적(CPU-intensive)이다.
데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 크게 떨어뜨린다.
특히, 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주요인이 되곤 한다.
가능하면 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.

next-database

2013년 7월 19일 금요일

오라클 소트 작업

댓글 없음:

댓글 쓰기