실행계획
알고리즘 문제 풀때를 떠올려봅시다. 문제를 읽고 어떤 방식으로 풀어야 정확도와 효율성을 잡을 수 있을지 고민합니다. 완전탐색을 해야하는지, 이분탐으로 시간복잡도를 줄일 수 있는지, 혹은 자료구조를 어떤 것을 선택해야하는지 등 여러 고민을 하고 최적이라고 생각하는 방법을 선택합니다.
실행계획도 알고리즘 문제를 푸는 과정과 비슷합니다. 옵티마이저가 어떤 방식으로 쿼리를 실행해야 빠른속도로 결과를 반환할 수 있는지 고려하여 결과를 얻기까지의 과정을 알려줍니다. 우리는 이러한 실행계획을 확인하면서 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를 확인하는 것이 가장 강력한 방법이라는 생각이 드네요.
댓글0