SQL 분석 도구

실행 계획을 확인할 수 있는 방법은 여러 가지가 있는데, 오라클이 기본으로 제공하는 쿼리 툴 SQL*Plus에서 아래 명령을 수행하면 실행계획이 PLAN_TABLE에 저장된다.

explain plan for 
select * from emp where ename = :enmae ad deptno = :deptno;

PLAN_TABLE에 저장된 정보를 읽어 분석하기 쉬운 형태로 포메팅하는 방법에 여러 가지가 있지만, 가장 쉬운 방법은 아래와 같이 dbms_xplan.display 함수를 사용하는 것이다.

select * from table( dbms_xplan.display(null, null, 'typical') );

SQL Server에서 예상 실행계획을 출력하는 방법

set showplan_text on

위 명령을 실행한 후에 SQL을 실행하면 된다.

오라클 예상 실행계획에서 기본적으로 확인할 수 있는 정보(dbms_xplan.display 함수 세 번째 인자에 ‘typical’ 입력)는 Plan Hash Value, 오브젝트 액세스 방식 (Operation), 오브젝트명, 예상 Cardinality(=Rows), 예상 데이터 크기(Bytes), 예상 Cost, 예상 CPU Time, 조건절 정보(Predicate Information) 등이다.

dbms_xplan.display 함수 세 번째 인자에 alias, outline, advanced 등을 지정하면 Query Block 및 Object Alias 정보, outline 정보, Column Projection 정보 등을 추가로 확인할 수 있다.

오라클 AutoTrace에서 확인할 수 있는 정보는 아래와 같다.

set autotrace taceonly;

예상 실행 계획, 실제 디스크에서 읽은 블록 수, 실제 기록한 Redo 크기

AUtoTrace에서 사용할 수 있는 옵션은 아래와 같다.

set autotrace on

SQL을 실행하고 결과집합과 함께 예상 실행계획 및 실행통계를 출력한다.

set autotrace on explain

SQL을 실행하고 결과 집합과 함께 예상 실행계획을 출력한다.

set autotrace on statistics

SQL을 실행하고 결과집합과 함께 실행통계를 출력한다.

set autotrace traceonly

SQL을 실행하지만 결과는 출력하지 않고, 예상 실행계획과 실행통계만 출력한다.

set autotrace traceonly explain

SQL을 실행하지 않고, 예상 실행계획만 출력한다.

set autotrace traceonly statistics

SQL을 실행하지만 결과는 출력하지 않고, 실행통계만 출력한다.

오라클에서 SQL트레이스를 확인하고자 할 때는 다음 명령어를 사용한다.

alter session set sql_trace = true

TKProf 유틸리티를 사용하면 SQL 트레이스 파일을 분석해서 리포트 파일을 생성해 준다. 유닉스 쉘이나 도스 프롬프트에서 tkprof를 실행하면 사용법을 확인할 수 있다.

아래는 TKProf 유틸리티의 가장 일반적인 사용법이다.

tkprof ora10g_ora_14370_orakiing.trc report.prf sys=no

첫 번째 인자는 트레이스 파일명이다. 두 번째 인자는 결과 파일명이며, 확장자는 자유롭게 입력할 수 있다. 마지막 sys=no 옵션은 SQL을 파싱하는 과정에서 내부적으로 수행되는 SQL문장을 제외시켜준다.

Autotrace의 ‘recursive calls’는 하드파싱 과정에 딕셔너리를 조회하거나 DB저장형 함수에 내장된 SQL을 수행할 때 발생한 Call 횟수를 표시한다.

SQL에 gather_plan_statistics 힌트를 지정하면, SQL 트레이스 정보를 서버 파일이 아닌 SGA 메모리에 기록한다. SGA 메몰이ㅔ 저장된 트레이스 정보를 V$SQL_PLAN_STATISTICS_ALL 뷰에서 직접 조회할 수도 있지만, dbms_xplan.display_curosr 함수를 이용하면 분석하기 쉬운 형태로 포매팅해 준다.