제목없음

인덱스 생성
서버 프로세스(또는 인덱스가 병렬로 생성되고 있을 경우에는 여러 프로세스들)는 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) 요청은 다음을 수행해야 합니다:

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

정렬 작업 전에:
   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 파라미터의 값(바이트)보다 더 작은 영역 내에서 수행될 수 있는 경우, 오라클 서버는 메모리에서 정렬작업을 수행합니다.

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

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

정렬 영역
정렬 공간은

전용 서버로 연결될 때는 PGA의 일부가 되고,
MTS로 연결될 때는 공유 풀의 일부가 됩니다.

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

파라미터들
SORT_AREA_SIZE

정렬 작업은 init.ora 파라미터 SORT_AREA_SIZE를 사용하여 크기가 조정됩니다.
정렬 영역은 ALTER SESSION 이나 ALTER SYSTEM DEFERRED 명령을 사용하여 동적으로 설정될 수 있습니다.
디폴트 값은 운영체제에 따라 다릅니다.
디폴트는 일반적으로 대부분의 OLTP 작업에 적합합니다. DSS 작업, 배치 작업, 대규모 작업에 대해서는 정렬 영역을 조정하십시오.

SORT_AREA_RETAINED_SIZE

정렬 작업이 완료된 후에도 정렬 영역이 여전히 페치될(읽혀질) 정렬 행을 포함하고 있을 때, 정렬 영역은 SORT_AREA_RETAINED_SIZE 파라미터에 지정된 크기까지 줄어들 수 있습니다.
마지막 행이 정렬 공간으로부터 페치된 (읽혀진) 후에, 메모리는 운영체제에게로 다시 해제되는 것이 아니라 동일한 오라클 서버 프로세스가 사용하는 UGA로 다시 해제됩니다.
이 파라미터의 디폴트 값은 SORT_AREA_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;

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

S_EMP 테이블의 전체 테이블 스캔 수행
반환된 각 행에 대해 DEPT_ID 값을 사용하여, PK_ID 인덱스에 고유한 스캔 수행
인덱스 스캔으로부터 검색된 ROWID를 사용하여 대응되는 행을 S_DEPT 테이블에서 찾기
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 출력결과

V$SYSSTAT 뷰는 모든 “sort(memory)”, 모든 “sort(disk)”, 모든 “sort(rows)”의 수를 출력합니다.
- sort(disk): 임시 세그먼트에 I/O를 요구하는 정렬의 수
- sort(memory): 메모리에서 완전히 수행되는 정렬의 수
- sort(rows): 감시되는 기간에 정렬된 총 행

SQL> select * from v$systat where name like ‘%sorts%’;
STATISTIC# NAME                    CLASS      VALUE
---------- -------------------- -------- ----------
     161 sorts (memory)             64        154
       162 sorts (disk)               64          4
       163 sorts (rows)               64     571768
REPORT.TXT 출력 결과도 동일한 정보를 나타냅니다. 그러나, 이들 수치는 utlbstat와 utlestat가 실행되었을 때의 기간을 포함합니다
Statistic      Total   Per Transact  Per Logon Per Second
------------- -------- ------------- --------- -----------
sorts(disk)          4          .02        .41         .01
sorts(memory)      154          .27       5.77         .12
sorts(rows)     571768        39.62     862.59       18.19
V$SORT_SEGMENT와 V$SORT_USAGE는 사용된 임시 세그먼트와 이들 세그먼트를 사용하고 있는 사용자에 대한 정보를 출력합니다.

주의: 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보다 작을 경우에 메모리에서 수행되기 때문에, 익스텐트 크기를 설정할 때 이것을 고려해야 합니다.

SORT_AREA_SIZE의 정수 배수로서 INITIAL과 NEXT 값을 선택하여, 익스텐트 헤더에 여분의 블록을 허용하십시오.
PCTINCREASE를 0으로 설정하십시오.

다른 임시 테이블스페이스 사용자가 필요로 하는 정렬 공간을 정의하기 위하여, 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

진단팩 어플리케이션

CREATE INDEX NOSORT로 사용하면?
A. Data가 반드시 sort되어 있어야 한다.
B. Data에 관계없이 무조건 sort를 하지 않는다.
C. Data량을 기준으로 sort를 생략한다.
D. 기존 INDEX를 사용한다.
답

X 정답:A

SORT TUNING 시 V$SYSSTAT 의 SORT(DISK) / SORT(MEMORY) 의 적절한 RATIO는?
A. 5% 이하
B. 10% 이하
C. 15% 이하
D. 20% 이하
답

X 정답:A

SORT_AREA_SIZE라는 PARAMETER에 대한 설명 중 옳지 않은 것은?
A. SORT_AREA_SIZE 가 부족할 경우 TEMPORARY SEGMENT를 사용한다.
B. DEDICATED SERVER를 사용할 경우 SGA내에 잡힌다.
C. PARALLEL QUERY에서는 각각의 QUERY SERVER가 각각 SORT AREA를 사용한다.
D. 일반적으로 SORT_AREA_RETAINED_SIZE와 같은 크기로 잡는다.
답

X 정답:B

SORT_DIRECT_WRITES에 대한 설명 중 옳지 않은 것은?
A. 가능한 값은 true,false,auto이다.
B. direct write하기 위해 추가적인 메모리가 필요할 수 있다.
C. SGA memory를 사용한다.
D. Oracle 에서는 sort_area_size 내의 메모리를 사용한다.
답

X 정답:C

USER에게 temparary tablespace로 TEMP tablespace를 지정했다. 이 TEMP tablespace는 permanent type을 생성되었다. USER의 sort작업으로 생성된 tempoary segment는 언제 해제되는가?
A. SORT이 끝난 후
B. Database의 INSTANCE가 종료되었을 때
C. USER가 명시적으로 명령어를 수행했을 때
D. 해당사항 없음
답

X 정답:A

USER에게 temparary tablespace로 TEMP tablespace를 지정했다. 이 TEMP tablespace는 temporary type을 생성되었다. USER의 sort작업으로 생성된 tempoary segment는 언제 해제되는가?
A. SORT이 끝난 후
B. Database의 INSTANCE가 종료되었을 때
C. USER가 명시적으로 명령어를 수행했을 때
D. 해당사항 없음
답

X 정답:B

sort된 rows가 모두 몇 개인지를 알아볼 수 있는 view는?
A. v$sysstat
B. v$sesstat
C. v$sqlarea
D. v$rowcache
답

X 정답:A

temporary tablespace 구성시 스토리지 할당을 어떻게 하는 것이 좋은가. 옳지 않은 것은?
A. INITIAL 값은 SORT_AREA_SIZE의 배수로 한다.
B. NEXT는 INITIAL의 배수로 한다.
C. PCTINCREASE는 0로 한다.
답

X 정답:B

다음 중 SORT를 유발하지 않는 문장은?
A. ORDER BY
B. DISTINCT
C. UNION
D. UNION ALL
답

X 정답:D

다음과 같이 SORT PRARMETER가 설정된 경우 ORACLE은 DATABASE BUFFER CACHE를 BYPASS하는가?

SORT_AREA_SIZE = 640K
SORT_DIRECT_WRITES = AUTO
SORT_WRITE_BUFFERS = 4
SORT_WRITE_BUFFER_SIZE = 32K

A. TRUE
B. FALSE
답

X 정답:B

어떤 user에게 temp tablespace를 지정하지 않은 경우 그 user에게 sort 작업이 발생하는 tablespace는?

A. SYSTEM TABLESPACE
B. USER TABLESPACE
C. TEMP TABLESPACE
D. INDEX TABLESPACE
답

X 정답:A

문맥	참조
초기화 파라미터	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
진단팩 어플리케이션