[Oracle] 실행계획 확인 방법 XPLAN, AutoTrace, SQL Trace

실행계획

알고리즘 문제 풀때를 떠올려봅시다. 문제를 읽고 어떤 방식으로 풀어야 정확도와 효율성을 잡을 수 있을지 고민합니다. 완전탐색을 해야하는지, 이분탐으로 시간복잡도를 줄일 수 있는지, 혹은 자료구조를 어떤 것을 선택해야하는지 등 여러 고민을 하고 최적이라고 생각하는 방법을 선택합니다.

 

실행계획도 알고리즘 문제를 푸는 과정과 비슷합니다. 옵티마이저가 어떤 방식으로 쿼리를 실행해야 빠른속도로 결과를 반환할 수 있는지 고려하여 결과를 얻기까지의 과정을 알려줍니다. 우리는 이러한 실행계획을 확인하면서 SQL 쿼리문이 효율적으로 사용되고 있는지 확인할 수 있습니다. 데이터가 대량으로 늘어나면서 특히 속도가 중요한 요소가 되었습니다. 간단한 튜닝을 통해서도 엄청난 효율을 얻을 수 있기 때문에 튜닝의 기본인 실행계획을 확인하는 방법을 XPLAN, AutoTrace, SQL Trace 크게  3가지 방법을 알아보겠습니다.

 

 

실습 준비

OS : Red Hat Enterprise Linux Server release 6.10
DB : Oracle 11.2.0.1
SID : orcl
user : jamong

위의 환경에서 실습을 진행했고 실행계획을 확인하는 방법들에서 여러 권한 및 사전 작업이 필요하여 새롭게 jamong이라는 유저를 생성해서 진행했습니다.

 

SYS@orcl> create user jamong identified by jamong;

User created.

SYS@orcl> grant connect,resource to jamong;

Grant succeeded.

SYS@orcl> create table jamong.dept as select * from scott.dept;

Table created.

SYS@orcl> create table jamong.emp as select * from scott.emp;

Table created.

SYS@orcl> conn jamong/jamong
Connected.

JAMONG@orcl> alter table dept add constraint pk_dept primary key(deptno);

Table altered.

JAMONG@orcl> alter table emp add constraint pk_emp primary key(empno);

Table altered.

scott 계정의 dept, emp 테이블을 사용하겠습니다.

 

select
  owner,
  synonym_name,
  table_owner,
  table_name
from all_synonyms
where synonym_name = 'PLAN_TABLE';

SQL에 대한 실행계획이 plan_table에 저장됩니다. oracle 10g 부터는 설치시 기본적으로 테이블이 생성되고 이를 가르키는 plan_table synonym이 생성됩니다. 만약 해당 synonym이 없다면 오라클에서 제공하는 스크립트를 실행해서 생성할 수 있습니다. 없을 시 아래 명령으로 생성가능합니다.

SYS@orcl> @ ?/rdbms/admin/utlxplan.sql

이제 모든 준비는 끝났고 실행계획 확인방법을 하나씩 확인해보겠습니다.

 

 

실행계획 실행 순서

위와 같이 실행계획이 있다고 했을때 실행되는 순서는 어떻게 될까요? 가장 안에서부터, 위에서부터를 기억하시면 됩니다. 위의 실행계획의 순서는 3 - 2 - 5 - 4 - 1이 되겠습니다.

 

1. AutoTrace

AutoTrace는 실행계획을 확인하기 위한 도구중 하나로 가장 쉽게 확인할 수 있는 방법입니다. 쿼리 실행과 동시에 실행계획 결과를 확인할 수 있으며 수행 결과, 실행 계획, 실행 통계 3가지 부분을 옵션에따라 조합해서 출력할 수 있습니다. 수행 결과와 실행 계획은 바로 확인 가능하지만, 실행 통계 부분을 함께 출력하려면 v_$sesstat, v_$statname, v_$mystat 뷰에 대한 읽기 권한이 필요합니다. 오라클에서는 plustrce.sql 스크립트를 제공하여 실행 통계를 출력하기 위한 뷰를 롤을 생성할 수 있습니다.

 

SYS@orcl> @ ?/sqlplus/admin/plustrce.sql
SYS@orcl> grant plustrace to jamong;

Grant succeeded.

plustrace 롤을 부여하여 실행통계에 대한 부분도 출력할 수 있도록 합니다.

 

AutoTrace 명령어 조합

명령	수행 결과	실행 계획	실행 통계	plustrace 권한 여부
set autotrace on	O	O	O	O
set autotrace on explain	O	O	X	X
set autotrace on statistics	O	X	O	O
set autotrace traceonly --> SQL 수행	X	O	O	O
set autotrace traceonly explain --> SQL 수행 X	X	O	X	X
set autotrace traceonly statistics --> SQL 수행	X	X	O	O

autotrace 명령은 약어 autot로 대체할 수 있습니다. 해당 명령어들을 사용하여 출력하고 싶은 내용만 선택해서 확인할 수 있습니다. 실행 통계에 대한 결과를 확인하기 위해서는 plustrace 권한이 필요하고 실행 계획만 확인할때에는 SQL문에 대한 수행없이 출력이 가능합니다.

 

실행 계획 분석

JAMONG@orcl> set autot traceonly explain
JAMONG@orcl> select * from emp where empno > 7800 and sal > 1400;

우선 실행 계획에서 각 항목이 어떤 뜻을 나타내는지를 알아보겠습니다. XPLAN을 이용한 방식에서도 위와 동일한 형태의 실행계획이 출력되는데 모두 AutoTrace를 이용했을 때와 같은 뜻을 의미합니다.

항목	의미
Id	Operation의 Id. Predicate Information에 정보가 있다면 Id와 연결된다.
Operation	수행되는 일
Name	Operation이 수행되는 테이블 혹은 인덱스 --> 오브젝트명
Rows	각 Operation이 끝났을 때 반환되는 예상치 건수
Bytes	Access 하는 byte 수 예상치
Cost(%CPU)	Operation의 비용 --> 누적치
Time	예상 수행 시간
access Predicate	block을 읽기 전에 어떤 방법으로 block을 읽을 것인지 결정. --> index ? full table scan ? 등
filter Predicate	block을 읽은 후 데이터를 필터링되어 사용되는 조건

어디까지나 실행 계획이므로 통계정보로 결과가 출력됩니다. 실제 비용과는 다를 수 있습니다.

 

실행 통계 분석

JAMONG@orcl> set autot traceonly statistics
JAMONG@orcl> select * from emp where empno > 7800 and sal > 1400;

항목	의미
recursive calls	데이터베이스 내부에서 발생한 Call의 수를 의미합니다. 재귀라는 의미를 갖는 recursive로 아래와 같이 내부적으로 수행되는 작업을 수행하는 상황에서 발생합니다. -Data Dictionary 조회 시 -사용자 정의 함수를 수행 시 -프로시저 및 트리거 내에서 SQL 수행 시
db block gets	current mode block 읽기 요청된 횟수(logical read)
consistent gets	consistent mode block 읽기 요청된 횟수(logical read)
physical reads	디스크에서 물리적으로 읽은 block의 수
redo size	redo의 크기
bytes set via SQL*NET to client	포그라운드 프로세스에서 클라이언트로 보낸 총 바이트 수
bytes received via SQL*NET from client	Oracle Net을 통해 클라이언트에서 받은 총 바이트 수
SQL*NET roundtrips to/from client	클라이언트와 주고 받은 총 Oracle Net 메시지 수
sorts (memory)	메모리 내에서의 정렬 작업 횟수
sorts (disk)	할당 받은 sort area 내에서 정렬을 완료하지 못해 디스크 공간을 사용한 정렬 작업 횟수
rows processed	최종적으로 처리되어 반환된 행의 수

실행 통계에서 출력되는 항목은 위와 같은 의미를 갖는다고 합니다.

 

 

2. SQL Trace

SQL 튜닝에 있어 가장 세부적으로 내용을 알려주는 것이 SQL Trace 입니다. AutoTrace의 결과로 문제를 찾을 수 없을 때 SQL Trace를 통해 찾을 수 있습니다. Oracle Database에는 Trace 파일이 있습니다. 파라미터의 설정을 변경하여 질의한 SQL 쿼리에 대해 대기 이벤트나 성능관련 세부 정보를 확인할 수 있습니다.

 

SQL Trace 남기기

SYS@orcl> grant alter session to jamong;

일반 사용자라면 session의 파라미터값을 변경할 수 있는 권한을 부여해줘야합니다.

 

JAMONG@orcl> alter session set tracefile_identifier='test_case';
JAMONG@orcl> alter session set sql_trace=true;

JAMONG@orcl> select * from dept;

JAMONG@orcl> alter session set sql_trace = false;

 

Trace를 남기기 위한 작업을 진행하겠습니다. trace_identifier를 지정하여 trace 파일을 찾기 쉽도록 설정했습니다. 이후 sql_trace파라미터를 True로 설정하고 Trace 정보를 확인하고 싶은 쿼리를 질의 후 sql_trace파라미터를 False로 변경했습니다. 

 

[oracle@601d2fce71dc app]$ cd $ORACLE_BASE/diag/rdbms/orcl/orcl/trace
[oracle@601d2fce71dc trace]$ ls *test_case*.trc
orcl_ora_3896_test_case.trc

이후 Trace파일이 생성되었는지 확인합니다.

 

tkprof 유틸리티

Trace파일을 직접 열어보면 알기 힘든 형태로 되어있습니다. 오라클에서 제공하는 tkprof 유틸리티를 사용해서 사용자가 보기 편한 형태로 분석결과를 출력해줍니다.

 

[oracle@601d2fce71dc trace]$ tkprof orcl_ora_3896_test_case.trc report.txt sys=no

report.txt라는 이름으로 파일을 생성하며 sys=no 옵션은 내부적으로 수행되는 SQL문장은 제외시켜줍니다.

 

[oracle@601d2fce71dc trace]$ vi report.txt 

파일을 내리다보면 실행한 쿼리에 대한 정보가 있는것을 확인할 수 있습니다.

 

SQL Trace 자동화

SQL Trace 작업을 위에서 간단하게 확인해봤는데 결과를 출력하기 까지 매우 번거로운 작업을 진행해야합니다. 그래서 Trace를 on,off 형태로 진행할 수 있도록 스크립트로 자동화해서 사용하면 편합니다. 자동화 스크립트에서는 오라클에서 제공하는 event Trace를 이용하여 SQL Trace를 생성 및 확인하는 작업을 하겠습니다.

 

이방식을 사용할때 레벨을 설정하는데 이를 통해 대기 이벤트와 바인드 변수에 대한 정보를 확인할 수 있습니다.

Level	기본정보	Binding 정보	Waiting 정보
1	O	X	X
4	O	O	X
8	O	X	O
12	O	O	O

 

alter session set events '10046 trace name context forever, level 12';
alter session set events '10046 trace name context off';

위와 같은 형태로 사용되는데 level만 변경하여 Trace를 출력합니다.

 

start.sql

conn jamong/jamong
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';

Trace파일은 세션단위로 갱신되기 때문에 start.sql 실행 후 입력되는 쿼리만 결과로 출력하기 위해 사용할 유저로 연결을 새롭게 해줍니다.

 

end.sql

alter session set events '10046 trace name context off';

define _editor=vi

undefine trace
column trace_file new_value trace

SELECT value AS trace_file
FROM v$diag_info
WHERE name = 'Default Trace File';

!tkprof &trace output.trc sys=no
ed output.trc

에디터를 vi로 지정해주고 trace라는 변수에 현재 Trace파일의 경로를 저장해주고 tkprof 유틸리티를 사용해서 파일을 생성합니다.

 

사용법

JAMONG@orcl> @start
JAMONG@orcl> select * from emp where empno > 7800 and sal > 1400;
JAMONG@orcl> @end

script가 저장된 경로에서 start로 시작하고 쿼리질의를 한 이후에 end로 끝내면 Trace파일을 확인할 수 있습니다.

 

 

SQL Trace 분석

항목	의미
Parse	SQL문을 파싱하고 실행 계획을 생성하는데에 대한 통계
Execute	Oracle 서버에 의해 실제 명령문이 실행되는데에 대한 통계.  INSERT,UPDATE,DELETE
Fetch	Select문에 대한 통계
count	각 단계에 대한 실행  횟수
cpu	처리에 소요되는 시간(초)
elapsed	실행에 소요되는 시간(초)
disk	물리적 블록 읽기 수
query	consistent mode 블록 읽기(논리적 읽기)
current	current mode 블록 읽기(논리적 읽기)
rows	각 단계에서 처리되는 행의 수

 

항목	의미
Rows	각 수행 단계에 출력된 행의 수
cr	consistent mode 블록 읽기 수
pr	물리적 블록 읽기 수
pw	물리적 블록 쓰기 수
time	각 수행 단계에서 소요되는 시간
cost	비용
size	데이터 크기
card	cardinality

 

 

3. XPLAN

explain plan 명령어

explain plan 명령어를 사용해서  실행계획을 확인할 수 있습니다. explain plan 명령을 사용하면 실습 준비에서 생성한 plan_table에 실행계획을 담습니다. 이후 오라클에서 제공하는 utlxpls 스크립트를 이용해서 확인하기 편안한 형태로 실행계획을 출력할 수 있습니다.

JAMONG@orcl> explain plan for select * from emp;
JAMONG@orcl> @ ?/rdbms/admin/utlxpls

 

dbms_xplan.display

utlxpls 스크립트를 확인해보면 아래와같은 명령어를 실행합니다.

select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

dbms_xplan 패키지를 이용해서 결과를 출력합니다. dbms_xplan은 오라클에서 제공하는 패키지로 실행계획을 확인할 때 사용할 수 있습니다. 첫번째 인자로 plan_table을 넣어주고 두번째 인자에는 statement_id, 세번째는 출력 포맷 옵션을 설정할 수 있습니다. 두번째 인자에 null값을 넣으면 가장 마지막에 explain plan 명령을 통해 사용된 쿼리에 대한 실행계획을 보여줍니다. 아래와 같은 형태로 실행할 수 있습니다.

 

explain plan set statement_id='test' for select * from emp;
select plan_table_output from table(dbms_xplan.display('plan_table','test','serial'));
select plan_table_output from table(dbms_xplan.display('plan_table','test','basic'));
select plan_table_output from table(dbms_xplan.display('plan_table','test','typical'));
select plan_table_output from table(dbms_xplan.display('plan_table','test','basic rows bytes cost partition parallel predicate projection alias remote note'));
select plan_table_output from table(dbms_xplan.display('plan_table','test','advanced'));

 

 

dbms_xplan.display_cursor

display_cursor 함수는 display 함수와 다르게 일반 예상 실행계획이 아닌 실제 실행계획을 확인할 수 있습니다. 즉, Shared Pool에 올라가 있는 실행계획을 읽는 것입니다. display_cursor를 사용하기 위해서는 오라클에서 실제 SQL문을 실행하는 동안의 실행 계획을 각 Row Source별루 수행 통계를 수집하기 위해 gather_plan_statistics 힌트를 사용하거나 statistics_level 파리미터를 all로 설정해야합니다.

 

그리고 일반 유저가 사용하기 위해서는 수집된 통계를 저장하는 뷰에대한 접근이 필요하여 아래 권한이 필요합니다.

grant select on v_$session to jamong;
grant select on v_$sql to jamong;
grant select on v_$sql_plan to jamong;
grant select on v_$sql_plan_statistics to jamong;
grant select on v_$sql_plan_statistics_all to jamong;

 

힌트를 사용한다면 아래처럼 사용하면 됩니다.

JAMONG@orcl> select /*+gather_plan_statistics*/ e.empno,e.sal,d.dname from emp e inner join dept d on e.deptno = d.deptno where e.sal >= 3000;
JAMONG@orcl> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

가장 마지막 쿼리에 대한 정보를 화인하기 위해 첫 두 인자 값을 null로 표시하면 됩니다.

 

display_cursor 실행계획 분석

항목	의미
Id	Operation Id
Operation	실행되는 일
Name	Operation이 수행되는 테이블 혹은 인덱스 --> 오브젝트명
Starts	각 Operation을 반복 수행하는 횟수
E-Rows	각 Operation이 끝났을 때 반환되는 행 수 (예상치)
A-Rows	각 Operation이 끝났을 때 반환되는 행 수 (실제)
A-Time	실제 실행 시간 --> 누적치
Buffers	각 Operation의 논리적 블록 읽기 수
Reads	각 Operation의 물리적 블록 읽기 수 --> 있는 경우에만 출력
Writes	각 Operation의물리적 블록 쓰기 수 --> 있는 경우에만 출력

 

정리

간단하게 실행계획을 알아보는 방법을 정리해봤습니다. 각 상황과 환경에 맞게 알맞는 방법을 사용하면 되는데 개인적으로는 SQL Trace를 확인하는 것이 가장 강력한 방법이라는 생각이 드네요.