1. HOME
  2. [종료]구루비 DB 스터디
  3. 2016년 상반기 - 오라클 데이터베이스 스터디
  4. 05. 소트 튜닝

05. 소트 튜닝 (by nakamori) [2016.08.08]

05. 소트튜닝

(01) 소트 수행 원리

# 소트 수행 과정

메모리 소트 (IN MEORY SORT) : 전체 데이터 정렬을 메모리 내에서 완료.
디스크 소트 (TO-DISK SORT) : 메모리에서 정렬을 완료 못해 TEMP 디스크 공간 까지 사용하는 경우. (EXTERNAL SORT)

  • SGA -> PGA -> TEMP Tablespace (Sort RUN) -> PGA

Optimal sort : 소트 포러에이션이 메모리 내에서 정렬.
Onepass sort : 정렬 대상 집합이 디스크에 한번만 쓰임.
Multipass sort : 정렬 대상 집합이 디스크에 여러번 쓰임.

# 소트 오퍼레이션 
 

 Statistics
   ----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1002  consistent gets
          0  physical reads
          0  redo size
        435  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)                    <<
          1  sorts (disk)                      << 
          1  rows processed


Trace Event 

 direct path write temp
 direct path read temp

  • Autotrace , SQL TRACE , V$SYSSTAT/V$SESSTAT/V$MYSTAT , v$sort_segment, v$sort_usage , v$temp_Extent_pool 에서도 확인이 가능하다.
#Sort Area

SORT_AREA_REAINED_SIZE : 데이터 정렬을 끝내고 나서 집합결과를 모두 Fetch 할 때 까지 Sort Area 사이즈.
workarea_size_policy = auto 인 경우. (9i이후 True default)

  • PGA : 종속적인 고유 데이터 저장하는 용도 (다른프로세스와 공유 되지 않는다.)
    • CGA : Call 이 진행되는 동안에만 필요한 정보 저장. --> Call 끝나자마자 UGA는 해지되어 PGA 반환.
      예) DML 문장 수행,
    • UGA : Call을 넘어서 다음 Call 까지 계속 참조되는 정보를 저장 (Shared Server 모드에서는 SGA 안에 위치)
#소트 튜닝 요약
  • 데이터 모델 측면에서의 검토
  • 소트가 발생하지 않도록 SQL 작성
  • 인덱스를 이용한 소트 연산 대체
  • Sort Area 를 적게 사용하도록 SQL 작성
  • Sort Area 크기 조절.

(02) 소트를 발생시키는 오퍼레이션

#Sort Aggregate
  • 전체 로우를 대상으로 집계할 때 나타나며, 'Sort'가 나오긴 하지만 실제 소트가 발생되진 않음
 
SQL> SELECT  /*+ gather_plan_statistics monitor */ SUM(SAL), MAX(SAL), MIN(SAL)
  2  FROM   SCOTT.EMP
  3  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |   828   (2)| 00:00:10 |
|   1 |  SORT AGGREGATE    |      |     1 |     2 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K|  1953K|   828   (2)| 00:00:10 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2913  consistent gets
       2906  physical reads
          0  redo size
        567  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

#Sort Order by

*정렬된 결과집합을 얻고자 할 때 나타남

 
SELECT /*+ gather_plan_statistics monitor */ *
FROM   SCOTT.EMP
ORDER  BY SAL DESC
;


-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000K|    20M|       |  7477   (1)| 00:01:30 |
|   1 |  SORT ORDER BY     |      |  1000K|    20M|    42M|  7477   (1)| 00:01:30 |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K|    20M|       |   829   (2)| 00:00:10 |
-----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2913  consistent gets
       2906  physical reads
          0  redo size
   17231057  bytes sent via SQL*Net to client
     415324  bytes received via SQL*Net from client
      37721  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     565786  rows processed

#Sort Group by
 

-- Sort Group by : Sort 알고리즘 이용하여 그룹별 집계를 수행할 때 발생. (/*+ USE_HASH_AGGREGATION */)

SQL> SELECT /*+ OPT_PARAM('_GBY_HASH_AGGREGATION_ENABLED', 'FALSE') */
  2         DEPTNO,
  3         JOB,
  4         SUM(SAL),
  5         MAX(SAL),
  6         MIN(SAL)
  7  FROM   SCOTT.EMP
  8  GROUP  BY DEPTNO, JOB;

Execution Plan
----------------------------------------------------------
Plan hash value: 15469362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    11 |    66 |   863   (6)| 00:00:11 |
|   1 |  SORT GROUP BY     |      |    11 |    66 |   863   (6)| 00:00:11 |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K|  5859K|   829   (2)| 00:00:10 |
---------------------------------------------------------------------------

-- HASH Group by : Hash 알고리즘 이용하여 그룹별 집계를 수행할 때 발생
SQL> SELECT /*+ gather_plan_statistics monitor */
  2         DEPTNO,
  3         JOB,
  4         SUM(SAL),
  5         MAX(SAL),
  6         MIN(SAL)
  7  FROM   SCOTT.EMP
  8  GROUP  BY DEPTNO, JOB;

Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    11 |    66 |   863   (6)| 00:00:11 |
|   1 |  HASH GROUP BY     |      |    11 |    66 |   863   (6)| 00:00:11 |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K|  5859K|   829   (2)| 00:00:10 |
---------------------------------------------------------------------------
#Sort Unique
  • Unnesting 된 서브쿼리가 M 쪽 집합 또는 Uniuqe 인덱스가 없다면, 세미조인으로 수행되지 않는다면 메인쿼리와 조인하기 전에 Sort unique 가 발생.
  • Union , minus, intersect 집합 연산자를 사용할 때도 발생.
  • distinct , group by 시 발생 (해당 컬럼에 order by 사용 할 경우, 아니면 hash unique 으로 발생.)

SQL> select /*+ ordered use_nl(depts) */ * from scott.emp
    where deptno in (select /*+ unnest  */ deptno from scott.depts);

~
14 개의 행이 선택되었습니다.

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    14 |   490 |   834   (2)| 00:00:11 |
|*  1 |  HASH JOIN          |       |    14 |   490 |   834   (2)| 00:00:11 |
|   2 |   SORT UNIQUE       |       |     6 |    78 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPTS |     6 |    78 |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL | EMP   |    14 |   308 |   830   (2)| 00:00:10 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"="DEPTNO")
   4 - filter("DEPTNO" IS NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2917  consistent gets
       2906  physical reads
          0  redo size
       1414  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

#Sort Join
  • Sort Join 은 소트머지조인을 실행할떄 발생.
    {code :xml}

SQL> SELECT /*+ ORDERED USE_MERGE(E D) */ *
2 FROM scott.EMP E, scott.DEPT D
3 WHERE E.DEPTNO = D.DEPTNO
4 ;

14 개의 행이 선택되었습니다.

Execution Plan

--
Plan hash value: 3406566467

IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT14546835 (2)00:00:11
1MERGE JOIN14546835 (2)00:00:11
2SORT JOIN14308831 (2)00:00:10
  • 3
TABLE ACCESS FULLEMP14308830 (2)00:00:10
  • 4
SORT JOIN61024 (25)00:00:01
5TABLE ACCESS FULLDEPT61023 (0)00:00:01

Predicate Information (identified by operation id):

---

3 - filter("E"."DEPTNO" IS NOT NULL)
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")

Statistics

--
1 recursive calls
0 db block gets
2919 consistent gets
2906 physical reads
0 redo size
1716 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed



h6. #Window Sort 
{code :xml}

(03) 데이터 모델 측면에서의 검토

(04) 소트가 발생하지 않도록 SQL 작성

(05) 인덱스를 이용한 소트 연산 대체

(06) Sort Area 를 적게 사용하도록 SQL 작성

(07) SORT AREA 크기 조정.

  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2016년 상반기 - 오라클 데이터베이스 스터디
  4. 05. 소트 튜닝