SQL 성능개선 순서를 이해한다. - 문제 있는 SQL 식별, 옵티마이저(Optimizer) 통계 확인, 실행계획 검토, SQL 문 재구성, 인덱스 재구성, 실행계획 유지관리

 

 

SQL 성능개선 순서를 이해한다.

·       문제 있는 SQL 식별

·       옵티마이저(Optimizer) 통계 확인

·       실행계획 검토

·       SQL 문 재구성

·       인덱스 재구성

·       실행계획 유지관리

 

 

 

 

문제 있는 SQL 식별

·       문제 있는 SQL을 식별하기 위해서는, 애플리케이션의 성능을 관리하거나 모니터링하기 위한 툴인 APM(Application Performance Management) 등을 활용한다.

·       Oracle의 경우, TKPROF 또는 SQL_Trace와 같은 유틸리티를 사용하여 성능에 문제가 있는 SQL을 확인한다.

 

 

 

 

옵티마이저(Optimizer) 통계 확인

·       옵티마이저(Optimizer)는 개발자가 작성한 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해 주는 데이터베이스 핵심모듈로서, Oracle은 CBO(Cost Based Optimizer)와 RBO(Rule Based Optimizer) 모드를 지원하고, 이중 비용기반인 CBO 모드를 기본으로 지원하고 있다.

·       비용기반 옵티마이저 모드에서 최적의 처리경로를 생성하기 위해서는 옵티마이저가 활용하는 통계정보를 주기적으로 현행화하여야 하는데 이를 위한 문장은 다음과 같다.

 

Analyze Object_type Object_name Operation STATISTICS;

 

Object_name	TABLE, INDEX, CLUSTER 중 선택하여 기술한다.
Operation	COMPUTE 정의된 Object_name에 대하여 통계정보를 정확하게 계산하는 방법으로, 가장 정확한 통계를 얻을 수 있지만 처리 속도는 가장 느리다. ESTIMATE 데이터 딕셔너리의 값과 데이터 샘플링 정보를 기반으로 통계치를 예상하는 방법으로, COMPUTE보다 덜 정확하지만 처리속도가 훨씬 빠르다. DELETE 정의된 Object_name에 대한 모든 통계 정보를 삭제 한다. 예시 ANALYZE TABLE emp COMPUTE STATISTICS ;

 

 

 

 

실행계획 검토

Driving 테이블이 최상의 필터를 가지고 있는지를 중심으로 검토한다. 즉 처리량이 작은 Table을 Driving 테이블로 지정되었는지 확인하다.

 

 

 

 

 

SQL 문 재구성

·       가능한 한 where = 을 많이 써서 범위가 아닌 특정 값 지정으로 인한 범위를 줄여 처리속도가 빠르도록 한다.

·       where 절의 칼럼에 연산자를 사용하여 칼럼변경이 발생하면 인덱스를 활용하지 못하게 됨을 이해하여 칼럼 변경 연산자를 쓰지 않도록 한다.

·       부분범위 처리의 경우 Sub-Query에 Exists 사용하여 불필요한 검색을 하지 않도록 한다.

·       옵티마이저가 비정상적인 실행계획을 수립하여 처리된다면, 힌트로서 옵티마이저의 액세스 경로 및 조인 순서를 제어할 수 있도록 한다.

 

 

 

 

인덱스 재구성

·       성능에 중요한 액세스 경로를 고려하여 인덱스화한다.

·       실행계획을 검토하여 기존 인덱스의 열 순서를 변경하거나 추가 할 수가 있도록 한다.

·       인덱스 추가 시 정상적으로 처리되고 있던 다른 SQL에 심각한 영향을 줄 수 있으므로 관련된 주요 SQL 질의결과를 함께 검토하여야 한다.

·       한가지 인덱스로 읽기만 하는 코드와 같은 테이블은 Index-Organized Table을 고려한다.

·       사용하지 않는 불필요한 인덱스들은 제거한다.

 

 

 

 

 

실행계획 유지관리

데이터베이스 버전 업그레이드나, 데이터의 시스템 이동 등 시스템 환경의 변경 사항 발생시에도 실행계획이 유지되고 있는지 모니터링하고 관리한다.