쿼리를 튜닝할 일이 생겨 몇개의 블로그에서 액기스를 좀 뽑아서 정리했습니다.
----
튜닝을 하려면 몇가지 바탕이 되는 기술이 필요합니다.
아주 간략히 얘기해보죠..
[1] PROCEDURE 의 쿼리 처리 과정과 캐쉬에 대한 이해
mssql의 쿼리처리 과정은 다음 5단계를 거친다.
1. 구문 분석 (Parsing)
2. 표준화 (Standardization)
3. 최적화 (Optimization)
4. 컴파일 (Compilation)
5. 실행 (Execute)
매번 저 단계를 거치는 것은 부하가 크므로 메모리에 캐시하게 되며
syscacheobjects 테이블을 보면 그 캐시에 저장된 쿼리 내역을 알 수 있다.
아래처럼 간단하게 호출을 한다.
...
마무리하며)
정말 정신없이 튜닝에 대해서 정리해 보았다.
아직 다루지 않은것들이 너무 많다.
l 2개이상의 JOIN 쿼리에서의 인덱스 설정 및 구성
l 트렌젝션처리 방식을 이용한 튜닝
l 커서를 사용함으로써 발생된 이슈에 대한 튜닝
l 시스템 구성을 최적화하여 성능을 높이는 시스템 레벨 튜닝
너무 방대하고 상황마다 다르지만 끝으로 몇가지 를 권장한다.
1. 인덱스와 통계 자료에 대해서 주기적으로 업데이트하라.
- 이는 매우 중요하다. 옵티마이저에게 최신의 자료를 제공해야 할 DBA의 의무가 있다.
2. AdHoc 쿼리를 지양하라. Trivial PLAN 을 갖을수 있도록 Prepared 타입의 SQL 로 작성하라.
- 사실 AdHoc 쿼리도 빈번한 수행 쿼리에 대해서는 proccache에 저장된다 하지만 where 절의 key값만 다르게 해서 다양한 Adhoc 쿼리가 수행요청이 온다면 proccache 에는 무수히 많은 complied plan이 쌓여 메모리 누수가 발생할수 있다. 매우 중요하다.
3. 적절한 인덱스를 구성하라.
- 너무 무분별하지 않게, 정확하게 필요한 인덱스를 만들어라
4. 항상 문제가 되는 SQL 쿼리가 있는지 모니터링하고 지속적으로 튜닝하라.
- 처음에는 별게 아닌 것 같지만 나중에 큰산이 되어 시스템을 망가트리게 될수 있다. 지속적인 관심과 튜닝은 필수다.
--------
[ 방법론 정리 ]
1. 적절한 인덱스를 사용하여 Block I/O를 최소화 하라.
- 조인이 있는 경우는 특히 Driving(선행) 집합에 신경을 써야 한다.
- 인덱스를 생성하거나 변경할 때는 그 테이블을 사용하는 다른 SQL의 실행계획이 변경되지 않는지 각별히 신경을 써야 한다.
2. 조인방법과 조인순서를 최적화 하라.
- 조인순서만 바꾸어 주어도 일량이 획기적으로 줄어드는 경우가 많다
- 특정 인덱스에서 Block I/O가 증가하는 경우에 조인순서의 변경을 검토하라
3. Table Access(Random Access)를 최소화 하라
- 테이블의 종류를 변경 또는 파티션을 이용
- 효율적인 인덱스를 사용하거나 조인방법과 순서를 조정
- 인덱스에 컬럼을 추가하여 Table Access를 방지
- 인덱스만 엑세스 하고 테이블로의 엑세스는 모든 조인을 끝내고 마지막에 시도
4. Sort나 Hash 작업을 최소화 하라
- order by 대신 인덱스가 sort 되어있다는 특성 이용
- Union 대신 Union All 이용
- Having 절 최소화
5. 한 블록은 한번만 Scan하고 끝내라
- Union All 로 여러번 scan 하는 구문은 한번 Scan 하는 CASE 구문으로 변경
6. 온라인의 조회화면이라면 페이징처리는 필수이다
7. 답이 틀리면 안 된다. SQL을 검증하라
--------
자세한 내용은 아래 원문글을 참고해 주세요.
출처#2: 12bme.tistory.com/204