실행계획(EXPLAIN PLAN)

튜닝은 항상 데이터베이스 서버에서 작업하는 것은 아닙니다. 이론적으로 대부분의 튜닝은 실행에 대한 결과가 더 쉽게 측정되는 곳에서 이루어지며 데이터베이스에 대한 근본적인 튜닝은 마술이나 마법과 같은 것도 아닙니다. 이 튜닝도구를 잘 이해하기 위해서는 다시 한번 SELECT문이 처리되는 과정에 대한 이해가 필요합니다. 다음은 SELECT문이 처리되는 과정에 대한 설명입니다.

"사용자가 실행한 SQL문이 데이터베이스에서 처음 사용된 문장인지 이미 사용된 문장인지를 공유 풀 영역을 검색하여 확인합니다. 확인하는 이유는 이미 사용된 문장이라면 구문분석(Parsing)이라는 작업을 할 필요가 없고 처음 사용되었다면 정상적으로 구문분석 작업을 해야 하기 때문입니다. 구문분석 단계에서는 SQL문 문법이 제대로 작성되었는지를 분석하고(예를 들어, SELECT가 SLECC로 잘못 작성되지는 않았는지 등) 사용된 테이블, 뷰 등이 존재하는지를('SELECT * FROM emp'에서 EMP 테이블이 데이터베이스 내에 존재하는지) 확인합니다.SQL문이 실행되었을 때 가장 빠르게 데이터를 검색해줄 수 있는 방법(Explain Plan)도 찾아줍니다."

EXPLAIN PLAN 명령어는 SQL문이 처리되는 과정의 구문분석 단계에서 서버 프로세스에 의해 해당 SQL문이 어떻게 실행되는 것이 가장 빠르게 실행될 수 있는지를 결정하게 되는데 이 결과를 실행계획(EXPLAIN PLAN)이라고 하며 이 결과를 확인할 때 사용하는 튜닝도구가 EXPLAIN PLAN 명령어입니다.

- 다음은 EXPLAN PLAN 명령어를 실행하면 참조할 수 있는 내용들입니다.

- 데이터베이스에서 SQL문이 실행될 때의 상태정보를 제공해 줍니다.

- SQL문이 테이블 전체 스캔 방법으로 검색하는지 인덱스 스캔 방법으로 검색하는지에 대한 실행경로 정보를 제공해 줍니다.

- 테이블에 인덱스를 추가하거나 삭제한 경우 어떻게 실행계획이 달라지는지 보여줍니다.

- SQL 명령문의 WHERE절과 FROM절의 미묘한 변화에 대한 정보를 제공합니다.

* 다음은 실행계획 결과의 Operation 컬럼의 항목에 대한 설명입니다.

SQL*TRACE

EXPLAIN PLAN 명령어와 함께 애플리케이션 튜닝작업 시 자주 사용되는 기능 중에 SQL*TRACE라는 기능이 있습니다. 데이터베이스 사용자가 실행한 SQL문에 대한 실행계획 만을 보여주는 EXPLAIN PLAN과는 달리 SQL*Trace 기능은 SQL문의 실행계획과 더불어 실행 소요시간과 디스크 및 메모리로부터 읽은 블록 수에 대한 정보도 포함하고 있습니다.

SQL*TRACE 기능에 의해 분석 내용은 아래와 같습니다.

- SQL문이 실행될 때 PARSE, EXECUTION, FETCH 작업을 실행한 횟수

- PARSE, EXECUTION, FETCH 시 CPU 사용시간

- 데이터베이스 내에서 SQL문이 실행되는데 소요된 시간

- SQL문이 실행되면서 발생한 DISK-I/O 블록 수

- 조건을 만족하는 전체 행 수

- SQL문이 실행되면서 사용한 SGA 영역의 크기

- SQL문의 실행계획

- 해당 세션에서 작업 시 사용했던 전체 CPU, 메모리, 블록공간의 크기

* 다음은 SQL*Trace 결과에 대한 설명입니다.

CBO의 비용분석(Cost-Based Optimizer)

비용 기반 옵티마이저는 사용자가 실행한 SQL문을 분석하여 실제로 가장 빠르게 실행되는방법의원가(Cost)를 계산하여 선택하는 방법입니다. 어떤 테이블에 인덱스를 생성하고 SELECT문을 실행하면 대부분의 사용자들은 자신이 만든 인덱스에 의해 SQL문이 실행될 것이라고 생각합니다. 하지만, 오라클의 비용기반 옵티마이저는, 만약 전체 테이블 스캔 방법이 인덱스 스캔방법보다 더 빠르게 실행될 수 있다면 인덱스를 사용하지 않습니다.