SQL 성능 튜닝

 

👍 SQL 성능 튜닝 순서

 

👊 문제있는 SQL 식별

애플리케이션의 성능을 관리하거나 모니터링하기 위한 툴인 APM(Application Performance Management) 등을 활용

Oracle의 경우 TKPROF 또는 SQL_Trace 등 유틸리티 사용

---

👊 옵티마이저 통계 확인

옵티마이저 : 개발자가 작성한 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 데이터베이스 핵심 모듈

Oracle은 CBO(Cost Based Optimizer)와 RBO(Rule Based Optimizer) 모드를 지원하고, 이주중에서 비용기반인 CBO 모드를 기본으로 지원

비용기반 옵티마이저 모드에서 최적의 처리경로를 생성하기 위해서는 옵티마이저가 활용하는 통계정보를 주기적으로 현행화해야 함

---

👊 실행계획 검토

실행계획 : DBMS의 옵티마이저가 수립한 SQL 코드의 실행절차와 방법

실행계획에는 요구사항들을 처리하기 위한 연산 순서가 적혀 있으며, 연산에는 조인, 테이블, 검색, 필터, 정렬 등이 있음

Driving 테이블이 최상의 필터를 가지고 있는지를 중심으로 검토

처리량이 작은 Table을 Driving 테이블로 지정되었는지 확인

 

*Driving 테이블

JOIN이 발생할 때 첫번째로 액세스 되는 테이블

드라이빙 테이블 순서에 따라 데이터를 액세스하는 양이 대폭 늘어나거나 줄어들 수 있기 때문에, 어떤 테이블을 먼저 드라이빙 하는지가 매우 중요함

가장 적은 데이터를 추출할 것으로 예상되는 테이블을 먼저 드라이빙 해야함

---

👊 SQL문 재구성

WHERE절 활용 : 범위를 줄여 처리속도를 빠르게 구성

컬럼 변경 연산자 (없도록) 확인 : WHERE절의 컬럼에 연산자를 사용하여 컬럼 변경이 발생하면 Index를 활용하지 못하게 되므로 컬럼 변경 연산자를 쓰지 않음

범위 한정 사용 : 서브쿼리에 특정 데이터가 존재하는지 확인할 때는, 서브 쿼리의 모든 데이터를 확인하는 IN보다 데이터의 존재여부가 확인되면 검색이 종료되는 EXISTS를 사용하여 불필요한 검색을 하지 않도록 함

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

---

👊 인덱스 재구성

인덱스 구성 : 성능에 중요한 액세스 경로를 고려하여 인덱스화

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

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

코드 테이블 확인 : 단일 인덱스로 읽기만 하는 코드와 같은 테이블은 인덱스 안에 테이블 데이터를 직접 삽입하여 저장하는 IOT(Index-Organized Table) 고려

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

---

👊 실행계획 유지관리

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