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

소트튜닝(임시) (by nakamori) [2016.08.10]

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 100302,1033 , V$SYSSTAT/V$SESSTAT/V$MYSTAT , v$sort_segment, v$sort_usage , v$temp_Extent_pool 에서도 확인이 가능하다.
    v$sql_workarea[_actvie]
#Sort Area

데이터를 정렬을 하기 위한 Sortarea 는 소트 오퍼레이션이 진행되는 동안 공간이 부족할 때 마다
Chunk 단위로 (db_cblock_size) 으로 조금씩 할당된다. -> 최소 할당하는 크기는 OENPASS 경우 72KB 이다.

PGA의 정렬크기를 지정하는 방법은 workarea_size_policy=[ Auto | Manual ] 으로 나누어진다.
자동일 경우 세션당 최대의 값은 _SMM_MAX_SIZE , _SMM_PX_MAX_SIZE (Parallel) 파라미터에 지정된다.

  • PGA : 종속적인 고유 데이터 저장하는 용도 (다른프로세스와 공유 되지 않는다.)
    • CGA : Call 이 진행되는 동안에만 필요한 정보 저장. --> Call 끝나자마자 UGA는 해지되어 PGA 반환.
      예) DML 문장 수행
    • UGA : Call을 넘어서 다음 Call 까지 계속 참조되는 정보를 저장 (Shared Server 모드에서는 SGA 안에 위치)

SORT_AREA_REAINED_SIZE : 데이터 정렬을 끝내고 나서 집합결과를 모두 Fetch 할 때 까지 Sort Area 사이즈.
메모리 소트방식으로 처리하더라도 정렬된 결과값이 이 파라미터보다 작다면 디스크 I/O 발생한다.

#소트 튜닝 요약
  • 데이터 모델 측면에서의 검토
  • 소트가 발생하지 않도록 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 은 소트머지조인을 실행할때 발생.


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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   546 |   835   (2)| 00:00:11 |
|   1 |  MERGE JOIN         |      |    14 |   546 |   835   (2)| 00:00:11 |
|   2 |   SORT JOIN         |      |    14 |   308 |   831   (2)| 00:00:10 |
|*  3 |    TABLE ACCESS FULL| EMP  |    14 |   308 |   830   (2)| 00:00:10 |
|*  4 |   SORT JOIN         |      |     6 |   102 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| DEPT |     6 |   102 |     3   (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
#Window Sort
  • 분석 함수 사용할때 발생한다. 

SELECT EMPNO,ENAME,JOB,MGR,SAL,
       ROW_NUMBER() OVER(ORDER BY HIREDATE)
FROM   SCOTT.EMP
;

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000K|    20M|       |  7476   (1)| 00:01:30 |
|   1 |  WINDOW SORT       |      |  1000K|    20M|    38M|  7476   (1)| 00:01:30 |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K|    20M|       |   828   (2)| 00:00:10 |
-----------------------------------------------------------------------------------

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

  • 불합리한 데이터 모델이 소트오퍼레이션을 유발하는 경우.
    예를 들어 가입상품이 소수일 경우 자식 테이블(가입상품)
    만약 <그림5-6>의 모델링에서 고객별 가입상품 레벨을 조회할 경우 '고객별상품라인'과 '과금'을 조인해야 하는데 M:M 관계이므로 1:M으로 만들어야 해서 항상 GROUP BY를 사용해야 함
    이 때 불필요한 소트 연산이 발생하여 성능에 불리함.

SELECT 과금.고객ID,
       과금.상품ID,
       과금.과금액,
       가입상품.가입일시
FROM   과금,
       (SELECT 고객ID,
               상품ID,
               MIN(가입일시) 가입일시
        FROM   고객별상품라인
        GROUP  BY 고객 ID,
                  상품ID
       ) 가입상품
WHERE  과금.고객 ID(+) = 가입상품.고객ID
AND    과금.상품ID(+)  = 가입상품.상품ID
AND    과금.과금연월(+) = :YYYYMM

이를 <그림5-5>의 정규화에서 구할 경우 1:M 관계이므로 GROUP BY가 필요 없으며, 소트 연산도 불필요함.


SELECT 과금.고객ID,
       과금.상품ID,
       과금.과금액,
       가입상품.가입일시
FROM   과금,
       가입상품
WHERE  과금.고객ID(+)   = 가입상품.고객ID
AND    괴금.상품ID(+)   = 가입상품.상품ID
AND    과금.과금연월(+) = :YYYYMM

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

#Union을 Union All로 대체
  • 2개의 연산에서 중복값이 없을 경우 Union 으로 연결하지 말고 Union ALL 으로 연결하여 소즈 제거. 


-- 중복값 제거 없음.

SELECT EMPNO,JOB,MGR
FROM   SCOTT.EMP
WHERE  DEPTNO = 10
UNION
SELECT EMPNO,JOB,MGR
FROM   SCOTT.EMP
WHERE  DEPTNO = 20
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |    88 |  1662   (2)| 00:00:20 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     3 |    33 |   831   (2)| 00:00:10 |
|*  3 |   TABLE ACCESS FULL| EMP  |     5 |    55 |   831   (2)| 00:00:10 |
---------------------------------------------------------------------------

-- 중복값 제거 있음.

SELECT EMPNO,JOB,MGR
FROM   SCOTT.EMP
WHERE  DEPTNO = 10
UNION ALL
SELECT EMPNO,JOB,MGR
FROM   SCOTT.EMP
WHERE  DEPTNO = 20
;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     8 |    88 |  1664   (2)| 00:00:20 |
|   1 |  SORT UNIQUE        |      |     8 |    88 |  1664   (2)| 00:00:20 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| EMP  |     3 |    33 |   831   (2)| 00:00:10 |
|*  4 |    TABLE ACCESS FULL| EMP  |     5 |    55 |   831   (2)| 00:00:10 |
----------------------------------------------------------------------------
#Union을 Union All로 대체

CASE ] 과금 테이블에서 특정 지역의 과금연월을 체크하는 쿼리있다.
       이경우 Distinct 을 사용 시 소트가 발생하게 된다.

SELECT DISTINCT 과금연월
FROM   과금
WHERE  과금연월 <= :YYYYMM
AND    지역 LIKE :REG 11 '%'
;

   Rows   Row Source Operation
-------   -----------------------------------------------------------------------
     35   HASH UNIQUE (cr=1586208 pr=32648 pw=O time=98704640 us)
9845517    PARTITION RANGE ITERATOR PARTITION: 1 KEY (cr=1586208 pr=32648
9845517     TABLE ACCESS FULL 과금 (cr=1586208 pr=32648 pw=O time=70155864 us)
#Distinct를 Exists 서브쿼리로 대체


CASE ] 이 경우 Exists 으로 서브쿼리 필터링으로 구현 할 경우 Sort 작업 없이 데이터 추출이 가능하다.

SELECT 연월
FROM   연월테이블 A
WHERE  연월 <= :YYYYMM
AND    EXISTS (SELECT 'x'
               FROM   과금
               WHERE  과금연월 A. 연월
               AND    지 역 LIKE :REG 11 '%');
			   

Rows   Row Source Operation
----   ------------------------------------------------------------------------------
  35   NESTED LOOPS SEMI (cr=82 pr=O pw=O time=19568 us)
  36    TABLE ACCESS FULL 연월테이블 (cr=6 pr=O pw=O t ime=557 us)
  35    PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=76 pr=O pw=O time=853 us)
  35     INDEX RANGE SCAN 과금_N1 (cr=76 pr=O pw=O time=683 us)

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

#Sort Order By 대처


SELECT *
FROM   SCOTT.EMP
WHERE  DEPTNO = 10
ORDER BY JOB
;

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     3 |    66 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |        |     3 |    66 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     3 |    66 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_N1 |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - access("DEPTNO"=10)
   
   
   
SQL> CREATE INDEX SCOTT.EMP_N2 ON SCOTT.EMP (JOB);

인덱스가 생성되었습니다.


SQL> SELECT *
  2  FROM   SCOTT.EMP
  3  WHERE  DEPTNO = 10
  4  ORDER BY JOB
  5  ;
  

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     3 |    66 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |        |     3 |    66 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     3 |    66 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_N1 |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - access("DEPTNO"=10)
#Sort Group By 대처

결합이나 단일 컬럼 인덱스에 포함되는 선두컬럼을 ORDER BY에 사용할 경우 GROUP BY 시 소트 연산 대처 가능



SQL>  SELECT /*+ NO_USE_HASH_AGGREGATION */
  2          EMPNO,
  3          SUM(SAL),
  4          COUNT(*)
  5   FROM   SCOTT.EMP E
  6   GROUP  BY EMPNO
  7   ;


  -----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000K|  6836K|       |  4264   (2)| 00:00:52 |
|   1 |  SORT GROUP BY     |      |  1000K|  6836K|    15M|  4264   (2)| 00:00:52 |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K|  6836K|       |   828   (2)| 00:00:10 |
-----------------------------------------------------------------------------------


SQL> L
  1  SELECT /*+ NO_USE_HASH_AGGREGATION INDEX(E PK_EMP)*/
  2         EMPNO,
  3         SUM(SAL),
  4         COUNT(*)
  5  FROM   SCOTT.EMP E
  6  GROUP  BY EMPNO
  7*
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3177564796

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |  1000K|  6836K|  4790   (1)| 00:00:58 |
|   1 |  SORT GROUP BY NOSORT        |        |  1000K|  6836K|  4790   (1)| 00:00:58 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |  1000K|  6836K|  4790   (1)| 00:00:58 |
|   3 |    INDEX FULL SCAN           | PK_EMP |  1000K|       |  1887   (1)| 00:00:23 |
---------------------------------------------------------------------------------------
  • INDEX 을 이용해서 SORT 못하는 경우 : 복합인덱스가 Null 을 선두로 가지고 있을때, Null 허용하는 컬럼일 경우.
#인텍스를 활용한 Min, Max 구하기


SELECT MAX(SAL)
FROM   SCOTT.EMP E
WHERE  DEPTNO = 10
;


---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |     4 |            |          |
|   2 |   FIRST ROW                  |        |     1 |     4 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| EMP_N4 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

#소트 완료 후 데이터 가공
  • 데이터를 추출, 정렬하고 가공하는 것이 Sort Area 적게 사용

-- SQL_1
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 10) ||
       LPAD(고객명, 20) || TO_CHAR(주문일시, 'YYYYMMDD HH24:MI:SS')
FROM   주문상품
WHERE  주문일시 BETWEEN :START
                AND     :END
ORDER  BY 상품번호
;

-- SQL_2
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 10) ||
       LPAD(고객명, 20) || TO_CHAR(주문일시, 'YYYYMMDD HH24:MI:SS')
FROM   (SELECT 상품번호,
               상품명,
               고객ID,
               고객명,
               주문일시
        FROM   주문상품
        WHERE  주문일시 BETWEEN :START
                        AND     :END
        ORDER  BY 상품번호 )

SQL_1 : 레코드당 105(30 + 30 + 10 + 20 + 15) 바이트로 가공된 결과치를 Sortarea 에 담음.
SQL_2 : 가공되지 않은 상태로 정렬 완료 후에 가공 
;
#Top-N 쿼리
  • Top-N 쿼리로 작성하면 소트연산 횟수는 최소화, Sort Area 사용량 감소. 


-- SQL_1(SQL Server)
SELECT TOP 10 거래일시, 체결건수, 체결수량, 거래대금
FROM   시간별종목거 래
WHERE  종목코드 = 'KR123456'
AND    거래일시 = '20080304'
;

-- SQL_2(DB2)
SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM   시간별종목거래
WHERE  종목코드 = 'KR123456'
AND    거래일시 = '20080304'
ORDER  BY 거래일시
FETCH FIRST 10 ROWS ONLY
;

-- SQL_3(Oracle)
SELECT *
FROM   (SELECT 거래일시, 체결건수, 체결수량, 거래대금
        FROM   시간별종목거래
        WHERE  종목코드 = 'KR123456'
        AND    거래일시 = '20080304'
ORDER  BY 거래일시
WHERE  ROWNUM <= 10
;


Execution Plan
--------------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
o COUNT (STOPKEY)
1  VIEW
2   TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE)
3    INDEX (RANGE SCAN) OF '시간별종목거래_PK' (INDEX (UNIQUE))
#Top-N 소트 부하의 경감 원리.

전체를 모아놓고 정렬하는 것이 아니라 정렬할 공간 (Sort Area) 을 필요한 만큼 (rownum 추출개수)
사용하는데 의미가 있다. 


SQL> select * from (
  2  select
  3  *
  4  from scott.emp
  5  order by ename
  6  )
  7  where rownum <=10;
 
 
----------------------------------------------------------------------------
| Id  | Operation               | Name | E-Rows |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |        |       |       |          |
|*  1 |  COUNT STOPKEY          |      |        |       |       |          |
|   2 |   VIEW                  |      |   1000K|       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|      |   1000K|  3072 |  3072 | 2048  (0)|
|   4 |     TABLE ACCESS FULL   | EMP  |   1000K|       |       |          |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2916  consistent gets
          0  physical reads
          0  redo size
       1087  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)
         10  rows processed

		 

select  * 
from (
       select a.*, rownum rn
       from (select * from scott.emp
             order by ename)a
      ) 
where rn <=10;

 
------------------------------------------------------------------------------------
| Id  | Operation             | Name | E-Rows |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |        |       |       |          |         |
|*  1 |  VIEW                 |      |   1000K|       |       |          |         |
|   2 |   COUNT               |      |        |       |       |          |         |
|   3 |    VIEW               |      |   1000K|       |       |          |         |
|   4 |     SORT ORDER BY     |      |   1000K|    39M|  2235K|  539K (7)|   38912 |
|   5 |      TABLE ACCESS FULL| EMP  |   1000K|       |       |          |         |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         38  recursive calls
        100  db block gets
       2916  consistent gets
       8437  physical reads
          0  redo size
       1181  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)
          1  sorts (disk)
         10  rows processed
# 분석 함수에서 Top-N 쿼리.


select  id, seq, owner, object_name, object_type, created, status 
from  (select id, seq
            , max(seq) over (partition by id) last_seq
            , owner, object_name, object_type, created, status 
       from t)
where  seq = last_seq;


---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |00:00:00.68 |    7771 |  15689 |   7706 |       |       |          |         |
|*  1 |  VIEW               |      |      1 |    852K|     10 |00:00:00.68 |    7771 |  15689 |   7706 |       |       |          |         |
|   2 |   WINDOW SORT       |      |      1 |    852K|    848K|00:00:01.40 |    7771 |  15689 |   7706 |    67M|  2851K| 1057K (0)|   62464 |
|   3 |    TABLE ACCESS FULL| T    |      1 |    852K|    848K|00:00:00.16 |    7769 |      0 |      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        10         10         10  VIEW  (cr=7769 pr=15689 pw=7706 time=552443 us cost=24769 size=83521284 card=852258)
    848440     848440     848440   WINDOW SORT (cr=7769 pr=15689 pw=7706 time=1287344 us cost=24769 size=72441930 card=852258)
    848440     848440     848440    TABLE ACCESS FULL T (cr=7769 pr=0 pw=0 time=199738 us cost=2113 size=72441930 card=852258)

	
-- 분석 함수 사용하는 경우. 	

select id, seq, owner, object_name, object_type, created, status 
from  (select id, seq
            , rank() over (partition by id order by seq desc) rnum
            , owner, object_name, object_type, created, status 
       from   t)
where rnum = 1;



--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |     10 |00:00:00.88 |    7848 |     76 |  76 |          |       |          |         |
|*  1 |  VIEW                    |      |      1 |    852K|     10 |00:00:00.88 |    7848 |     76 |  76 |          |       |          |         |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |    852K|     20 |00:00:00.88 |    7848 |     76 |  76 |      666K|   481K| 1078K (3)|    1024 |
|   3 |    TABLE ACCESS FULL     | T    |      1 |    852K|    848K|00:00:00.16 |    7769 |      0 |   0 |          |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------------


	
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        10         10         10  VIEW  (cr=7769 pr=76 pw=76 time=917441 us cost=24769 size=83521284 card=852258)
        20         20         20   WINDOW SORT PUSHED RANK (cr=7769 pr=76 pw=76 time=917434 us cost=24769 size=72441930 card=852258)
    848440     848440     848440    TABLE ACCESS FULL T (cr=7769 pr=0 pw=0 time=183208 us cost=2113 size=72441930 card=852258)

(07) SORT AREA 크기 조정.

#오라클 관리 방법
  • PGA 메모리 관리 방식
    9i부터는 '자동 PGA 메모리 관리 (Automatic PGA Memory Management)' 기능이 도입되어 사용자가 수동으로 사이즈 조절이 필요없음.
    pga_aggregate_target 으로 총량을 할당하는 방식으로 처음부터 해당 공간을 점유하는 것을 아니고 OS 메모리에서 이 용량까지 사용할 수 있다는
    목표치임.

Batch 작업 처럼 작업 시간이 오래 걸리는 경우 Manual 으로 설정하여 필요한 Workarea 사이즈를 조절해서 작업 속도를 단축이 가능하다.
(WorkArea : sort_area_size, hash_area_size, bitmap_merge_area_size, create_merge_area_size )

alter session set workarea_size_policy=manual;
alter session set sort_area_size=10485760;

OLTP : (Total Physical Memory * 80% ) * 20%
DSS : (Total Physical Memory * 80% ) * 50%

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