[정보처리기사 실기 - 데이터 입출력 구현]SQL 성능 개선 순서, TKPROF 수행 순서

SQL 성능 개선 순서

 

1. 문제있는 SQL 식별

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

 

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

 

 

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

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

Analyze Object_type Object_name Operation STATISTICS;

 

2) Object_name : TABLE, INDEX, CLUSTER 중 선택하여 기술한다.

 

3) Operation

• COMPUTE : 정의된 Object_name에 대하여 통계 정보를 정확하게 계산하는 방법으로, 가장 정확한 통계를 얻을 수 있지만 처리 속도는 가장 느림
• ESTIMATE : 데이터 딕셔너리의 값과 데이터 샘플링 정보를 기반으로 통계치를 예상하는 방법으로, COMPUTE보다 덜 정확하지만 처리 속도가 훨씬 빠름
• DELETE : 정의된 Object_name에 대한 모든 통계 정보를 삭제

ANALYZE emp COMPUTE STATISTICS

 

3. 실행 계획 검토

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

 

4. SQL문 재구성

• 가능한 한 where =을 많이 써서 범위가 아닌 특정 값 지정으로 인한 범위를 줄여 처리속도가 빨라지도록 함
• where 절의 컬럼에 연산자를 사용하여 컬럼 변경이 발생하면 인덱스를 활용하지 못하게 됨을 이해하여 컬럼 변경 연산자를 쓰지 않도록 함
• 부분범위 처리의 경우 Sub-Query에 Exists를 사용하여 불필요한 검색을 막음
• 옵티마이저가 비정상적인 실행계획을 수립하여 처리된다면, 힌트로서 옵티마이저의 액세스 경로 및 조인 순서를 제어할 수 있도록 함

 

5. 인덱스 재구성

• 성능에 중요한 액세스 경로를 고려하여 인덱스화
• 실행계획을 검토하여 기존 인덱스의 열 순서를 변경하거나 추가할 수 있도록 함
• 인덱스 추가 시 정상적으로 처리되고 있던 다른 SQL에 심각한 영향을 줄 수 있으므로 관련된 주요 SQL 질의결과를 함께 검토
• 한 가지 인덱스로 읽기만 하는 코드와 같은 테이블은 Index-Organized Table 고려
• 사용하지 않는 불필요한 인덱스들 제거

 

6. 실행 계획 유지 관리

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

 

TKPROF 수행 순서

 

1) 수행하고 있는 DB User로 연결하여 Trace Mode를 설정한다.

alter session set sql_trace=true;
alter session set timed_statistics=true;

2) Trace 하고자 하는 SQL 실행 후 exit한다.

 

3) USER_DUMP_DEST dir의 Trace File 확인한다. : ls ~alt

 

4) Trace File 분석 : utility(tkprof)를 활용하여 분석한다.

tkprof xxx.trc result.lst sys=no explain=uid/pwd

5) 최종 파일(result.lst)을 열어서 결과를 분석한다.