인덱스 생성
서버 프로세스(또는 인덱스가 병렬로 생성되고 있을 경우에는 여러 프로세스들)는 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) 요청은 다음을 수행해야 합니다:
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 파라미터의 값(바이트)보다 더 작은 영역 내에서 수행될 수 있는
경우, 오라클 서버는 메모리에서 정렬작업을 수행합니다.
정렬 작업이 이 값보다 더 많은 공간을 필요로 할 경우에는:
정렬 영역
정렬 공간은
주의: 대량의 정렬 작업을 수행하는 애플리케이션은 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;
옵티마이저는 정렬-병합 조인 대신 중첩된 루프
조인을 선택합니다. 중첩된 루프 조인은 어떠한 정렬 작업도 요구하지 않으며, 다음을 필요로 합니다:
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 %
|
|
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 |
|
동적 성능 뷰 |
V$SYSSTAT |
|
데이터 딕셔너리 뷰 |
None |
|
명령어 |
None |
|
패키지된 프로시저와 함수 |
None |
|
스크립트 |
None |
|
진단팩 어플리케이션 |