2. 소트를 발생시키는 오퍼레이션
2011.06.12 10:07
(1) sort aggregate
SQL> select sum(sal),max(sal),min(sal) from scott.emp;
SUM(SAL) MAX(SAL) MIN(SAL)
---------- ---------- ----------
29025 5000 800
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
554 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
- 전체 로우를 대상으로 집계를 수행할때 나타나지만 실제 소트는 발생하지 않음
(2) sort order by
SQL> select * from emp order by sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 150391907
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 448 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 448 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
164 recursive calls
0 db block gets
27 consistent gets
4 physical reads
0 redo size
1347 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)
0 sorts (disk)
14 rows processed
SQL>
- 데이터 정렬을 위해 order by 수행시 나타남
(3) sort group by
- 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타남
SQL> select deptno,job,sum(sal),max(sal),min(sal)
from emp
group by deptno,job
order by deptno,job;
2 3 4
DEPTNO JOB SUM(SAL) MAX(SAL) MIN(SAL)
---------- --------- ---------- ---------- ----------
10 CLERK 1300 1300 1300
10 MANAGER 2450 2450 2450
10 PRESIDENT 5000 5000 5000
20 ANALYST 6000 3000 3000
20 CLERK 1900 1100 800
20 MANAGER 2975 2975 2975
30 CLERK 950 950 950
30 MANAGER 2850 2850 2850
30 SALESMAN 5600 1600 1250
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 15469362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 132 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 11 | 132 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 168 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
905 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)
0 sorts (disk)
9 rows processed
SQL>
hash group by 와 비교
- 10gr2에서 도입되었으며 order by 절을 명시 않으면 대부분 hash group by 방식으로 처리
SQL> select deptno,job,sum(sal),max(sal),min(sal)
from emp
group by deptno,job 2 3 ;
DEPTNO JOB SUM(SAL) MAX(SAL) MIN(SAL)
---------- --------- ---------- ---------- ----------
20 CLERK 1900 1100 800
30 SALESMAN 5600 1600 1250
20 MANAGER 2975 2975 2975
30 CLERK 950 950 950
10 PRESIDENT 5000 5000 5000
30 MANAGER 2850 2850 2850
10 CLERK 1300 1300 1300
10 MANAGER 2450 2450 2450
20 ANALYST 6000 3000 3000
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 132 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 132 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 168 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
917 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
- 해싱 알고리즘을 사용해 데이터를 그룹핑
- 읽는 로우마다 group by 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신
- sort group by 역시 읽는 로우마다 그룹별로 집계갱신하지만 소팅알고리즘으로 group by를 수행
- 두방식 모두 집계대상 로우가 아무리 많아도 그룹개수(group by 기준 컬럼의 distinct value 개수) 가
소수일 때는 디스크 소트가 전혀 발생 하지 않음
- 관련 파라미터
- 비활성: alter system set “_gby_hash_aggregation_enabled” =false;
group by 결과의 정렬 순서
- 오라클은 group by 결과의 정렬순서를 보장하지않음
- 소팅 알고리즘을 사용해 sort gorup by 된 결과 집합은 논리적인 정렬순서를 따라 포인터로 서로 연결되
있을것이며 이는 물리적인 순서와 다를수 있다. - 실행계획상 sort group by 는 소팅 알고리즘을 사용해 값을 집계한다 는 뜻일뿐 정렬을 의미 하지 않음
SQL> create index scott.emp_idx2 on scott.emp(job,deptno);
Index created.
SQL> select /*+ index(scott.emp scott.emp_idx) */ deptno, job, avg(sal)
from scott.emp
where deptno is not null
group by deptno,job; 2 3 4
DEPTNO JOB AVG(SAL)
---------- --------- ----------
20 ANALYST 3000
10 CLERK 1300
20 CLERK 950
30 CLERK 950
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
10 PRESIDENT 5000
30 SALESMAN 1400
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2981309258
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 135 | 2 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 9 | 135 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 210 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | EMP_IDX2 | 14 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
- 컬럼 순서가 다르더라도 group by를 위해 emp_idx2 인덱스가 사용되어 기준 컬럼과 다른 정렬 순서를 보임
- 결론
- 정렬된 group by 결과를 얻고자 한다면, 실행계획에 ‘sort group by’라고 표시 되더라도 order by를 명시 해야한다.
(4) sort unique
- Sort unique 오프레이션이 먼저 수행되는 경우 (메인쿼리와 조인전)
- unnesting 된 서브 쿼리가 M쪽 집합
- Unique 인덱스가 없을 경우
- 세미 조인으로 수행되 않을 경우
- PK/unique 제약, unique인덱스를 통해 Unnesting된 서브쿼리의 uniqueness가 보장된다면 생략
SQL> select /*+ ordered use_nl(dept) */ * from scott.dept
where deptno in (select /*+unnest */ deptno
from scott.emp where job='CLERK'); 2 3
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Execution Plan
----------------------------------------------------------
Plan hash value: 397061396
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 93 | 4 (25)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3 | 93 | 4 (25)| 00:00:01 |
| 3 | SORT UNIQUE | | 3 | 33 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_IDX2 | 3 | 33 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("JOB"='CLERK')
5 - access("DEPTNO"="DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
632 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)
0 sorts (disk)
3 rows processed
SQL>
- 집합연산자 (union , minus, intersect )를 사용할때
SQL> select job,mgr from scott.emp where deptno=10
union
select job,mgr from scott.emp where deptno=20; 2 3
JOB MGR
--------- ----------
ANALYST 7566
CLERK 7782
CLERK 7788
CLERK 7902
MANAGER 7839
PRESIDENT
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3774834881
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 150 | 10 (60)| 00:00:01 |
| 1 | SORT UNIQUE | | 10 | 150 | 10 (60)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| EMP | 5 | 75 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 5 | 75 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
4 - filter("DEPTNO"=20)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
579 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)
0 sorts (disk)
6 rows processed
SQL>
SQL> select job,mgr from scott.emp where deptno=10
minus
select job,mgr from scott.emp where deptno=20; 2 3
JOB MGR
--------- ----------
CLERK 7782
PRESIDENT
Execution Plan
----------------------------------------------------------
Plan hash value: 3686975449
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 150 | 10 (60)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 5 | 75 | 5 (20)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 5 | 75 | 4 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 5 | 75 | 5 (20)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 75 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
5 - filter("DEPTNO"=20)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
- distinct 오프레이션
SQL> select distinct deptno from scott.emp order by deptno;
DEPTNO
----------
10
20
30
Execution Plan
----------------------------------------------------------
Plan hash value: 596748738
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 6 (34)| 00:00:01 |
| 1 | SORT UNIQUE | | 3 | 9 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
466 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)
0 sorts (disk)
3 rows processed
SQL>
10gr2 부터 distinct 연산에서도 order by 가 생략되면 hash unique방식으로 수행
SQL> select distinct deptno from scott.emp
2 ;
DEPTNO
----------
30
20
10
Execution Plan
----------------------------------------------------------
Plan hash value: 3709190377
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 5 (20)| 00:00:01 |
| 1 | HASH UNIQUE | | 3 | 9 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
466 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
- 참고)
- 10gr2
- _convert_set_to_join 파라미터를 true로 설정하면 minus,intersect 같은
집합(set)연산에서 hash unique 오퍼레이션 사용 - alter session set “_convert_set_to_join”=true;
(5) sort join
- 소트 머지 조인을 수행할 때 나타남
select /*+ ordered use_merge(e) */ *
from scott.dept d, scott.emp e
where d.deptno=e.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 5 (20)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
64 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
1703 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)
0 sorts (disk)
14 rows processed
SQL>
- outer 테이블인 dept의 deptno 컬럼 인덱스를 이용할 때는 한번만 발생 (2장 조인원리와 활용)
(6) window sort
SQL> select empno,ename, job,mgr ,sal, avg(sal) over (partition by deptno)
2 from scott.emp;
EMPNO ENAME JOB MGR SAL AVG(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- --------- ---------- ---------- -------------------------------
7782 CLARK MANAGER 7839 2450 2916.66667
7839 KING PRESIDENT 5000 2916.66667
7934 MILLER CLERK 7782 1300 2916.66667
7566 JONES MANAGER 7839 2975 2175
7902 FORD ANALYST 7566 3000 2175
7876 ADAMS CLERK 7788 1100 2175
7369 SMITH CLERK 7902 800 2175
7788 SCOTT ANALYST 7566 3000 2175
7521 WARD SALESMAN 7698 1250 1566.66667
7844 TURNER SALESMAN 7698 1500 1566.66667
7499 ALLEN SALESMAN 7698 1600 1566.66667
EMPNO ENAME JOB MGR SAL AVG(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- --------- ---------- ---------- -------------------------------
7900 JAMES CLERK 7698 950 1566.66667
7698 BLAKE MANAGER 7839 2850 1566.66667
7654 MARTIN SALESMAN 7698 1250 1566.66667
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 406 | 5 (20)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 406 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 406 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1210 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)
0 sorts (disk)
14 rows processed
SQL>
- 분석함수 (analytic Function) 를 수행할때 나타남
- 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
- 작성자: 남송휘 (tofriend)
- 최초작성일: 2011년 6월 12일
- 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
- 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 40 | 2. 서브쿼리 Unnesting | darkbeom | 2011.05.15 | 19841 |
| » | 2. 소트를 발생시키는 오퍼레이션 | 휘휘 | 2011.06.12 | 3508 |
| 38 | 2. 파티션 Pruning | 실천하자 | 2011.06.21 | 26102 |
| 37 |
3. 병렬 조인
 | 실천하자 | 2011.06.28 | 7220 |
| 36 |
3. 다양한 인덱스 스캔 방식
| 멋진넘 | 2011.02.18 | 33881 |
| 35 |
3. 해시 조인
| darkbeom | 2011.03.20 | 21602 |
| 34 | 3. 옵티마이저의 한계 | 멋진넘 | 2011.04.19 | 7902 |
| 33 | 3. 뷰 Merging | 실천하자 | 2011.05.15 | 23516 |
| 32 |
3. 데이터 모델 측면에서의 검토
| 멋진넘 | 2011.06.13 | 5922 |
| 31 | 3. 인덱스 파티셔닝 | darkbeom | 2011.06.19 | 54156 |
| 30 |
4. 테이블 Random 액세스 부하
[1] | darkbeom | 2011.02.23 | 14754 |
| 29 | 4. 조인 순서의 중요성 | 운영자 | 2011.03.27 | 14374 |
| 28 | 4. 통계정보 Ⅰ | darkbeom | 2011.04.25 | 18214 |
| 27 | 4. 조건절 Pushing | 실천하자 | 2011.05.31 | 7114 |
| 26 |
4. 소트가 발생하지 않도록 SQL 작성
| 멋진넘 | 2011.06.12 | 7940 |
| 25 | 5. 테이블 Random 액세스 최소화 튜닝 | suspace | 2011.02.24 | 7028 |
| 24 | 6. 스칼라 서브쿼리를 이용한 조인 [1] | 휘휘 | 2011.03.28 | 6686 |
| 23 | 5. Outer 조인 | 실천하자 | 2011.03.29 | 5084 |
| 22 | 5. 카디널리티 | suspace | 2011.04.26 | 5989 |
| 21 | 5. 조건절 이행 | 휘휘 | 2011.05.29 | 5509 |