|
원본출처 : 딧세님 홈페이지 (http://informix.we.ro/)
SQL성능향상을위한방법
서론
1. Set Explain 의 출력을 해석하는 방법
2. Informix Optimizer의 역할
3. 성능향상을 위한 방법
■ Root Dbspace 에는 최소한의 시스템 정보만 포함할 것
■ UNIX File system 공간을 큰 정렬 파일로 채우면 UNIX 프로세스에서 오류가능성
■ 논리LOG를 LONG TRANSACTION으로 채우지 말것 : LOG TRX주의
OPTIMIZER
■ 주어진 질의를 실행하기 전 가장 좋은 최선의 경로를 찾는 INFORMIX 엔진의 한 부분
■ SET EXPLAIN ON : OPTIMIZER가 데이타에 접근하는데 선택한 경로를 나타냄
■ SYSTEM CATALOG의 정보를 바탕으로 결정
1. 질의에 사용되는 테이블의 행수 : systables.nrows
2. 데이타에 사용되는 페이지수와 색인에 사용되는 페이지수 : systables.npused
3. Column값의 uniqueness : sysconstraints
4. 색인존재여부 : sysindexes
5. 데이타가 색인과 같은 순서 즉, cluster index인지 여부 : sysindexes.clust
6. root node에서 leaf node 까지 색인의 레벨수
7. 각 column에서 두번째로 큰 값과 두번째로 작은 값. optimizer는 이 정보로 값의 범위를 대략적으로 알아낼 수 있슴 : syscolumns.colmin, colmax
위의 정보를 바탕으로 옵티마이저는 가능한 모든 경로를 검색하고 각각 비용(디스크 접근, 필요한 CPU자원, 네트워크 접근 등)을 추정하여 평가함.
■ 테이블 조인 순서 결정
■ Sequential scan의 수행 여부
■ 임시 테이블의 작성 여부
■ 색인 사용 여부 결정
where절에 필터 조건이 많아지고 테이블이 많이 포함되면 결정과정이 더욱 복잡해지고 정확한 통계의 중요성도 커짐
■ 통계의 정확도
. 시스템 카달로그 정보는 UPDATE STATISTICS가 실행될 때에만 갱신됨
. 동적인 테이블에 대해서는 자주 실행시킬 것
. UPDATE STATISTICS는 데이타베이스 전체나 개별 테이블, 테이블의 칼럼, 내장 프로시저에 대하여 실행 할 수 있음.
■ 엔진이 최적화를 수행하는 시기
. 표준 SQL로 사용될 때마다 질의가 최적화됨.
. SQL문이 반복 사용될 경우 PREPARE를 사용하여 한번만 최적화 시킴
. STORED PROCEDURE의 경우 PROCEDURE 가 만들어지거나
내장 PROCEDURE에 대하여 UPDATE STATISTICS가 실행될 때 SQL이
최적화 됨
■ 질의가 갑자기 느려질 때
프로그램이 SET EXPLAIN ON을 사용하여 문제 파악
개발과 생산
■ 개발하는 동안 잘 실행되는 질의가 실제적용에 들어가면 옵티마이저가 완전히 다른 경로를 선택할 수 있슴--> 실제환경의 데이타베이스와 비슷한 크기의 시험데이타베이스에서 같은 데이타로 질의를 실행
옵티마이저 제어
■ SET OPTIMIZATION HIGH : 엔진이 모든 엑세스 경로를 검사
■ SET OPTIMIZATION LOW : 초기 단계에서 가능성이 적은 옵션을 제거하여 최적화 시간을 줄임. 그러나 최적의 경로가 될 수 있는 엑세스경로가 초반에 제거되어 버릴 수 있슴
OPTCOMPIND
■ OPTCOMPIND=2 (Default) : INDEX SCAN과 FULL SCAN 비용을 비교하여 가장 효율적인 경로를 선택하도록 함
■ OPTCOMPIND=0 : INDEX사용
■ OPTCOMPIND=1 : OPTIMIZER가 2로 설정되었을 때처럼 작동.
단REPEATABLE READ가 선택되면 OPTCOMPIND가 0으로 설정되었을 때
처럼 작동 행을 모두 SCAN하면서 읽는 동안 전체 테이블을 효과적으로 공유할 수 있슴
옵티마이저를 위한 데이타분산
칼럼에서 데이타표본을 채취하여 테이블 영역에 관한 정보를 다양한 BIN에 저장하는 것으로 이루어짐 : 대형 테이블을 다룰 경우 유용한 정보가 됨. 또한 UPDATE STATISTICS명령으로 각 칼럼 분포 정보를 생성 할 수 있슴
■ UPDATE STATISTICS HIGH FOR : 테이블의 모든 행 평가. 느린 대신 정확함
■ UPDATE STATISTICS MEDIUM FOR : 데이타 표본만 추출하여 실행
■ UPDATE STATISTICS LOW FOR : 데이타 분포 정보를 얻지 않음
UPDATE STATISTICS
1. UPDATE STATISTICS문을 모든 테이블에 실행
2. 복합 색인의 첫번째 칼럼이나 질의의 한 부분으로 사용된 모든 칼럼에는 UPDATE STATISTICS HIGH문을 실행
3. 복합 색인의 첫번째 칼럼이 아닌 모든 칼럼에 대해서 UPDATE STATISTICS LOW를 실행
4. UPDATE STATISTICS단계를 완료하면 DBSCHEMA UTILITY의 다음 OPTION을 사용하여 데이타분포정보를 확인 할 수 있슴
예) dbschema -d databasename -hd tablename
5. 위의 내용은 fragmentation 전략에 유용하게 사용할 수 있슴
6. 대형 정적 테이블에 대해서는 UPDATE STATISTICS를 재실행할 필요가 없슴
SQL 질의 품질보증과 최적화
1. 큰 테이블에서는 순차적 검색을 하지 않는 것이 좋음
2. 임시 정렬FILE이 크게 생성되도록 하는 질의는 사용하지 말 것
3. Correlated Subquery를 사용
4. 서로 다른 칼럼에서 사용된 OR문은 옵티마이저의 색인 사용을 방해하므로 색인이 있고 질의 계획에서 옵티마이저가 순차스캔을 선택하면 UNION문의 사용을 고려할 것
5. 질의 초기에 가능하면 많은 행을 제거할 것
UPDATE STATISTICS HIGH 를 사용하면 옵티마이저에 데이타분포에 대한 정보가 추가로 제공되므로 알맞은 테이블이 먼저 제거됨. INDEX SCAN이 항상최선의 방법은 아님
6. 자료형을 변환하고 문자 칼럼을 비교하는 것. 가능하면 칼럼의 자료형을 숫자형으로 바꿀 것. 조인 칼럼이 문자형이면 매 행마다 한 바이트씩 비교함.
7. OR, LIKE, MATCH, 함수(MONTH, DAY, LENGTH등), 부정 표현(!=’NOUN’), 첫 문자를 제외한 하위 열 검색(POSTCODE[4,5] > 10)등은 INDEX를 사용하지 못함
8. LOGGING된 데이타베이스에서 LONG TRANSACTION을 실행하지 말것, LONG TRANSACTION은 논리로그를 채워 데이타베이스를 손상시킬 위험이 있슴
LOCK을 지나치게 많이 사용하는 것을 막으려면 테이블을 EXCLUSIVE MODE로 LOCK할 것. LOCK을 지나치게 사용하면 성능이 저하되고 또한 LOCK의 최대수에 도달하면 명령문이 실행되지 않음
9. 필요한 칼럼만 선택할 것. FRONT END와 BACK END간의 통신이 줄고 I/O도 줄어듬. 가능한 한 “SELECT *” 은 사용하지 말 것.
10. 데이타의 일정부분 집합이 WHERE 술어로 다시 선택되면 임시 테이블을 사용할 것. 검색하고자 하는 테이블이 매우 클 경우 모든 테이블을 임시 테이블로 선택하고 임시 테이블에서 재검색을 할 것.
11. 옵티마이저가 가장 좋은 경로를 선택하도록 임시 테이블을 사용함. 큰 테이블에서 임시 테이블로 행을 선택하고 임시 테이블을 나머지 테이블에 조인하면 됨
12. 임시 테이블에 색인 사용
13. 임시 테이블에 UPDATE STATISTICS사용
14. 임시 테이블을 만들 경우 WITH NO LOG를 사용. 논리 로그에 쓰는 오버헤드가 없어지므로 성능이 향상됨. 임시 테이블에 LONG TRANSACTION을 만들 가능성이 없어짐
15. 질의 시간을 잴 때에는 검사 사이에 인스턴스를 재시작하여 공유 메모리를 초기화함
■ 옵티마이저가 단편화된 분할 영역을 쉽게 결정할 수 있도록 데이타를 분할함. WHERE절이 명확해야 옵티마이저가 검색이 필요 없는 분할 영역을 제거하여 I/O를 줄이고 병행 처리를 활성화 할 수 있음
|
