인덱스 생성
서버 프로세스(또는 인덱스가 병렬로 생성되고 있을 경우에는 여러 프로세스들)는 B-Tree를 구축하기 전에 인덱스되는 값을 정렬해야 합니다.

ORDER BY 또는 GROUP BY 절
서버 프로세스는 ORDER BY 또는 GROUP BY 절의 값을 정렬해야 합니다.

DISTINCT 값
DISTINCT 키워드에 대해, 정렬 시 중복된 값을 없애야 합니다.   

UNION, INTERSECT, 또는 MINUS 연산
서버는 중복된 값을 없애기 위해 작업하고 있는 테이블을 정렬할 필요가 있습니다.

정렬-병합 조인
  SQL> select dept_id, id
    2> from s_emp, s_dept
    3> where s_emp.dept_id = s_dept.id;

사용할 수 있는 인덱스가 없을 경우, 이퀴조인(equijoin) 요청은 다음을 수행해야 합니다:

  1. S_EMP와 S_DEPT 테이블의 전체 테이블 스캔
  2. 각 행 자원을 별도로 정렬
  3. 정렬된 자원을 함께 병합하여, 한 소스의 각 행을 다른 소스에 대응되는 각 행과 결합

    정렬 작업 전에:
       SQL> select name, value
         2  from v$sysstat where name = ‘sorts (rows)’;
       NAME                                       VALUE
       -------------- ---------------------------------
       sorts (rows)                              639330

    정렬 작업 후에:
       SQL> select name, value
         2  from v$sysstat where name = ‘sorts (rows)’;
       NAME                                       VALUE
       -------------- ---------------------------------
       sorts (rows)                              655714

ANALYZE 실행
ANALYZE 명령은 테이블, 인덱스, 클러스터에 관한 통계 자료를 수집하는데 유용하기 때문에, CBO가 최상의 실행 계획을 정의하는 데 도움이 됩니다. 이 명령은 데이터를 정렬하여, 요약된 정보를 제공합니다.
        SQL> select name, value
          2  from v$sysstat where name = ‘sorts (rows)’;
        NAME            VALUE
        --------------- -----------------------
        sorts (rows)                      61751
        SQL> execute sys.dbms_utility.analyze_schema (‘SCOTT’, COMPUTE’);
        PL/SQL procedure successfully completed.
        SQL> select name, value
          2  from v$sysstat where name = ‘sorts (rows)’;
        NAME            VALUE
        --------------- -----------------------
        sorts (rows)                     622930

작업이 SORT_AREA_SIZE 파라미터의 값(바이트)보다 더 작은 영역 내에서 수행될 수 있는 경우, 오라클 서버는 메모리에서 정렬작업을 수행합니다.

정렬 작업이 이 값보다 더 많은 공간을 필요로 할 경우에는:

  1. 데이터는 ‘정렬 실행(sort run)’이라는 더 작은 부분으로 분할되고, 각 부분은 개별적으로 정렬됩니다.
  2. 서버 프로세스는 디스크의 임시 세그먼트에 분할된 데이터 부분을 기록합니다. 이들 세그먼트는 서버가 다른 정렬 실행을 처리하는 동안 중간 정렬 실행 데이터를 유지합니다.
  3. 정렬된 부분은 병합되어 최종 결과를 산출합니다.

정렬 영역
정렬 공간은

주의: 대량의 정렬 작업을 수행하는 애플리케이션은 MTS를 사용해서는 안됩니다.

파라미터들
SORT_AREA_SIZE

SORT_AREA_RETAINED_SIZE

문제 진단

튜닝 목표

NOSORT 절
SQL*Loader 사용 시 단일 CPU 기계에서 미리 정렬된 데이터에 대해 인덱스를 생성할 때 NOSORT 절을 사용하십시오. 이 절은 테이블에 삽입된 데이터에 대해서만 유효합니다.

   SQL> create index S_EMP_DEPT_ID_FK on s_emp(dept_id) NOSORT;
   ORA-01409: NOSORT option may not be used; rows are not in ascending order

복수 CPU 기계에서, 데이터가 순서대로 로드되지 않더라도, 병렬로 데이터를 로드하는 것이 더 빠릅니다. 그런 다음, 병렬 인덱스 생성 기능을 사용하여 정렬 작업의 속도를 높일 수 있습니다.

UNION ALL
UNION 대신 UNION ALL을 사용하십시오. 이 절은 이중 값을 삭제하지 않습니다. 따라서 정렬할 필요가 없습니다.

중첩된 루프 조인 (Nested Loop Join)
이퀴조인(equijoin) 요청에 대해 인덱스 액세스를 사용하십시오:
   SQL> select dept_id, id
     2  from s_emp, s_dept
     3  where s_emp.dept_id = s_dept.id;

옵티마이저는 정렬-병합 조인 대신 중첩된 루프 조인을 선택합니다. 중첩된 루프 조인은 어떠한 정렬 작업도 요구하지 않으며, 다음을 필요로 합니다:

  1. S_EMP 테이블의 전체 테이블 스캔 수행
  2. 반환된 각 행에 대해 DEPT_ID 값을 사용하여, PK_ID 인덱스에 고유한 스캔 수행
  3. 인덱스 스캔으로부터 검색된 ROWID를 사용하여 대응되는 행을 S_DEPT 테이블에서  찾기
  4. S_EMP로부터 반환된 각 행을 S_DEPT로부터 반환된 대응 행과 결합

INDEXES 와 ORDER BY
ORDER BY 문과 함께 빈번하게 참조되는 열에 인덱스를 생성하십시오. 인덱스가 오름차순으로 정렬되고 이중으로 연결되어 있기 때문에, 오라클 서버는 정렬 작업 보다는 인덱스를 사용할 것입니다.

ANALYZE FOR COLUMNS
조인 조건에 관련된 열만의 통계의 양을 감소시키십시오: ANALYZE…FOR COLUMNS 또는 ANALYZE…FOR ALL INDEXED COLUMNS
주의: “ANALYZE… SIZE n” 명령은 관련 열에 대한 히스토그램을 생성합니다. 이 절을 FOR ALL INDEXED COLUMNS 절과 결합하면 기본키(primary key)와 고유한(unique) 제약조건에 대해 불필요한 히스토그램을 생성합니다.

ANALYZE ESTIMATE
옵티마이저에 대해서는 COMPUTE 절이 더 정확합니다. 그러나, 많은 양의 정렬 공간을 필요로 하기 때문에, 큰 테이블과 클러스터에 대해서는 ESTIMATE 절이 선호됩니다. 

동적인 뷰와 Report.txt 출력결과

주의: OEM 진단 팩 애플리케이션
Performance Manager -> Database_Instance -> System Statistics
Performance Manager -> Load -> Sort Rows Rate

비율
sort(disk) 대 sort(memory)의 비율은 5% 보다 작아야 합니다.

Guidelines
디스크 정렬 작업의 비율이 높을 경우, SORT_AREA_SIZE의 크기를 증가시키십시오. SORT_AREA_SIZE는 각 실행 작업의 크기를 증가시키고 실행 및 병합의 총 작업 수를 감소시킵니다.

큰 정렬 영역에 대한 성능의 득과 실
정렬 영역의 크기를 증가시킬 경우, 정렬 작업을 수행하는 각 서버 프로세스는 더 많은 메모리를 할당합니다. 이것은 운영체제 메모리 할당에 영향을 미치고 페이징과 스와핑 작업을 포함시킬 수 있을 것입니다.

정렬 영역의 크기를 증가시킬 경우, 데이터가 곧 참조될 것이 아니라면, 정렬 영역의 보유 크기를 감소시키거나 오라클이 정렬 영역을 감소시키는 크기까지 정렬 영역의 크기를 감소시킬 것을 고려하십시오. 보유된 정렬 영역이 더 작을수록 메모리 사용은 더 적지만, 디스크 상의 임시 세그먼트에 쓰기와 읽기를 하기 위한 I/O가 추가됩니다.

주의: OEM 진단 팩 애플리케이션
Performance Manager -> Memory -> Memory Sort Hit % 


V$SORT_SEGMENT 뷰

이 뷰에는 인스턴스 내에 있는 TEMPORARY 테이블스페이스의 모든 정렬 세그먼트에 관한 정보가 포함되어 있습니다.
        

열

설명

CURRENT_USERS

 활동중인 사용자의 수

TOTAL_EXTENTS

 총 익스텐트 수

USED_EXTENTS

 현재 정렬 작업에 할당된 익스텐트의 수

EXTENT_HITS

 사용되지 않은 익스텐트가 풀에서 발견된 횟수

MAX_USED_BLOCKS

 사용된 블록의 최대 수

MAX_SORT_BLOCKS

 개별 정렬 작업에 의해 사용된 블록의 최대 수


임시 테이블스페이스 구성

정렬 세그먼트가 무제한으로 익스텐트를 갖는 경우를 제외하고는, 어느 MAXEXTENTS 값이 설정되든지 임시 테이블스페이스에 대한 디폴트 저장장소 파라미터는 정렬 세그먼트에 적용됩니다.

Guidelines

저장장소 파라미터  정렬 작업은 SORT_AREA_SIZE보다 작을 경우에 메모리에서 수행되기 때문에, 익스텐트 크기를 설정할 때 이것을 고려해야 합니다.

다른 임시 테이블스페이스 사용자가 필요로 하는 정렬 공간을 정의하기 위하여, V$SESSION과 V$SORT_USAGE 뷰를 결합하여 인스턴스에서 현재 활동중인 디스크 정렬 작업에 관한 정보를 입수하십시오:
  SQL>  SELCET s.username, u.”USER”, u.tablespace, u.contents,
     2         u.extents, u.blocks
     3  FROM v$session s, v$sort_usage u
     4  WHERE s.saddr=u.session_addr
     5  AND u.contents=’TEMPORARY’;
  USERNAME   USER    TABLESPACE   CONTENTS   EXTENTS     BLOCKS
  --------   -----   ----------   ---------  -------     ------
  STAT       SYSTEM  TEMP         TEMPORARY       20       1000
  SCOTT      SYSTEM  TEMP         TEMPORARY        2        100

V$SORT_USAGE 의 USER 열은 항상 이 뷰를 질의하고 있는 사용자를 나타냅니다. 정렬 작업을 수행하고 있는 사용자는 V$SESSION 뷰의 username입니다.
대용량의 디스크 공간을 요구하는 사용자 STAT는 익스텐트 크기가 더 큰 다른 임시 테이블스페이스로 배정되어야 합니다.

스트라이핑
임시 테이블스페이스는 여러 많은 디스크에 스트라이핑되어야 합니다. 임시 테이블스페이스가, 각기 초당 최대 I/O가 50회인 2개의 디스크에만 스트라이핑되어 있다면, 초당 100회의 I/O만 수행될 수 있습니다. 정렬 작업에 매우 긴 시간이 소요되기 때문에, 이러한 제한은 문제가 될 수 있습니다. 10개의 디스크에 임시 테이블스페이스를 스트라이핑할 경우, 정렬 작업의 속도를 5배나 올릴 수 있습니다. 이 경우에는, 초당 I/O가 500회는 가능할 것입니다.


 문맥 

  참조

 초기화 파라미터

  SORT_AREA_SIZE
  SORT_AREA_RETAINED_SIZE
  SORT_DIRECT_WRITES
  SORT_WRITE_BUFFERS
  SORT_WRITE_BUFFER_SIZE

 동적 성능 뷰

  V$SYSSTAT
  V$SORT_SEGMENT
  V$SORT_USAGE
  V$SESSION

 데이터 딕셔너리 뷰

  None

 명령어

  None

 패키지된 프로시저와 함수

  None

 스크립트

  None

 진단팩 어플리케이션   

X 정답:A


X 정답:A


X 정답:B


X 정답:C


X 정답:A


X 정답:B


X 정답:A


X 정답:B


X 정답:D


X 정답:B


X 정답:A