[펌]SQL튜닝 이해

■ SQL튜닝 이해

 

1. 분포도

어떤 컬럼이 테이블에 평균적으로 분포되어 있는 정도를 말하며, 일반적으로 10 ~ 15% 정도를 인덱스와 전체테이블 액세스의 손익분기점으로 볼 수 있다.

   분포도 = ( 데이타별 평균 로우수 / 테이블의 총 로우수 ) * 100

= ( 1 / 컬럼값의 종류 ) * 100

 

2. 처리범위

데이타량과 조건절의 비교내용에 따라 결정되며 결합되는 순서에 따라 조인의 효율에 커다란 영향을 미친다.

 

3. 실행계획

SQL이 요구한 데이타를 추출하기 위해 오라클이 차례로 수행하는 작업 방법을 말하며 이를 보면 비효율이 발생한 원인을 알 수 있고 좋은 실행계획이 수립될 수 있도록 하는 방법을 찾을 수 있다.

3.1 EXPAIN PLAN 활용

   - SQL> PLAN_TABLE 생성 ( ORACLE7/RDBMS/ADMIN/UTLXPLAN.SQL )

   - PLAN_TABLE을 쿼리하는 SQL문 작성 (BINDING변수를 ID, 명칭은PLAN으로 명명)

   - SQL> EXPLAIN PLAN SET STATEMENT_ID = ‘A1’ FOR

                 SELECT ....

   - SQL> DEF ID = A1

   - SQL> @PLAN

 

3.2 TKPROF 활용

- SQL> ALTER SESSION SET SQL_TRACE=TRUE;

- SQL> SQL문 실행

- SQL> ALTER SESSION SET SQL_TRACE=FALSE;

- TKPROF ORAxxx.TRC xxx.TXT EXPLAIN=USERID/PASSWORD SYS=NO SORT=EXECPU

 

※EXPLAIN PLAN의 기능과 옵션

기  능	옵  션	설  명
AGGREGATE	GROUP BY	그룹함수(SUM, COUNT등)를 사용하여 하나의 로우가 추출되도록 하는 처리
AND-EQUAL		인덱스 머지(MERGE)를 이용하는 경우
CONNECT BY		CONNECT BY를 사용하여 트리구조로 전개
CONCATENATION		단위 액세스에서 추출한 로우들의 합집합을 생성
COUNTING		테이블의 로우수를 센다
FILTER		선택된 로우에 대하여 다른 집합에 대응되는 로우가 있다면 제거하는 작업
FIRST ROW		조회 로우 중에 첫번째 로우만 추출한다.
FOR UPDATE		선택된 로우에 LOCK을 지정
INDEX	UNIQUE RANGE SCAN RANGE SCAN  DESCENDING	UNIQUE인덱스를 사용(단 한개의 로우 추출) NON-UNIQUE한 인덱스를 사용(한 개 이상의 로우) RANGE SCAN하고 동일하지만 역순으로 로우를  추출
INTERSECTION		교집합의 로우를 추출
MERGE JOIN	OUTER	먼저 자신의 조건만으로 액세스한 후 각각을 SORT하여 머지해 가는 조인 위와 동일한 방법이지만, OUTER JOIN을 한다
MINUS		MINUS 함수를 사용
NESTED LOOPS	OUTER	먼저 어떤 드라이빙(DRIVING) 테이블의 로우를 액세스한 후 그 결과를 이용해 다른 테이블을 연결하는 조인 위와 동일한 방법이지만, OUTER JOIN을 한다
PROJECTION		내부적인 처리의 일정
REMOTE		다른 분산 데이터베이스에 있는 오브젝트를 추출하기 위해 DATABASE LINK를 사용하는 경우
SEQUENCE		SEQUENCE를 액세스
SORT	UNIQUE GROUP BY JOIN ORDER BY	같은 로우를 제거하기 위한 SORT 액세스 결과를 GROUP BY하기 위한 SORT MERGE JOIN을 하기 위한 SORT ORDER BY를 위한 SORT
TABLE ACCESS	FULL CLUSTER HASH BY ROWID	전체 테이블 스캔 CLUSTER를 액세스 키값에 대한 해쉬 알고리즘을 사용 ROWID를 이용하여 테이블을 추출
UNION		두 집합의 합집합을 구함(중복없음) 항상 전체범위처리
UNION ALL		두 집합의 합집합을 구함(중복가능) UNION과는 다르게 부분범위 처리
VIEW		어떤 처리에 의해 생성되는 가상의 집합에서 추출

 

4. HINT

사용자가 액세스 경로의 변경을 위해서 SQL 내에 요구사항을 기술하면 옵티마이져가 액세스 경로를 결정할 때 이를 참조하도록 하는 사용자 인터페이스 이다.

※ 종류

HINT

기   능

RULE FIRST_ROWS ALL_ROWS FULL ROWID CLUSTER HASH INDEX_ASC INDEX_DESC AND_EQUALS ORDERED USE_NL   USE_MERGE   PARALLEL CACHE NOCACHE MERGE-AJ HASH-AJ

RULE BASE 옵티마이져를 사용 첫째 레코드의 추출시간을 최소화 할 목적으로 최적화 모든 레코드를 모두 처리하는 시간의 최소화를 목적으로 최적화 지정된 테이블에 대한 전체 스캔 지정된 테이블에 대한 ROWID에 의한 테이블 스캔 지정된 테이블에 대한 클러스터 스캔 지정된 테이블에 대한 해쉬 스캔 내림차순으로 인덱스 스캔 오름차순으로 인덱스 스캔 여러개의 인덱스를 MERGE하여 사용 FROM절에 기술된 테이블순으로 조인 먼저 특정 테이블의 로우를 액세스하고 그값에 해당하는 다른 테이블의 로우를 찾는 작업을 해당범위까지 실행하는 조인 먼저 각각의 테이블의 처리범위를 스캔하여 SORT한 후 서로 MERGE하면서 조인하는 방식 병렬처리 프로세서의 개수를 지정 테이블의 로우들을 메모리내에 상주 메모리내의 상주시킨 옵션의 해제 부정형의 조인을 SORT MERGE방식으로 수행 부정형의 조인을 HASH JOIN방식으로 수행

 

5. 지침사항

5.1 인덱스를 사용할 수 없는 경우

- 인덱스 컬럼에 대한 변형(내부변형, 외부변형)

TO_CHAR, TO_NUMBER가 모두 가능한 상황이면 TO_NUMBER가 우선

- 부정형 비교

- NULL 비교

 

※   DB BLOCK의 5배 이상인 데이타량에 대해 INDEX설정 필요.

 

5.2    조건절은 상수비교 부분부터 해석되면 인덱스를 활용 할 수 없는 경우에는 FROM

                  절의 뒷 테이블부터 대상이 됨.

5.3    동일 조건에서는 FROM절의 뒷 테이블부터 해석 됨.

5.4    OR절에서는 조건의 뒷 부분부터 해석 됨.

CF. USE_CONCAT HINT 사용.

5.5    OR는 나쁜쪽의 해석 방향으로 전체를 통합

5.6    OR와 ROWNUM의 사용은 전체범위 처리됨.

5.7    IN은 OR로 풀림.

5.8    결합 인덱스는 분포도에 따라 컬럼의 순서 결정이 중요.

5.9    UNION과 UNION ALL을 사용시 똑같은 결과가 나올 때는 UNION ALL을 사용.

5.10 NULL비교는 전체 스캔을 처리.

5.11 대부분의 배타적 관계 모델링은 OUTER JOIN으로 해결.

5.12 OUTER JOIN이 되는 컬럼에는 IN, BETWEEN, LIKE, OR를 사용 못함.

5.13 OUTER JOIN시 IN의 개념이 필요하면 IN-LINE VIEW를 활용.

5.14 MAX값을 얻기 위해서는 INDEX_DESC HINT와 ROWNUM=1을 사용.

5.15 MIN 값을 얻기 위해서는 INDEX_ASC HINT와 ROWNUM=1을 사용.

5.16 동일 테이블에서 비슷한 조건절을 사용 하는 여러개의 SQL문은 DECODE를 활용.

CF. 과도한 DECODE의 사용은 금물.

출처 : Tong - 라제폰님의 데이타베이스통