Oracle - 실행계획 보는 방법

실행계획

Oracle 실행 계획은 요청된 데이터에 액세스하는 방법을 보여주는 세부 단계의 집합입니다. 실행 계획의 각 단계는 행을 생성하거나 소비하는 데이터베이스 연산자 집합입니다.

실행계획 관련 파라미터는 timed_statistics 와 statistics_level 이 있고 힌트는 /*+ gather_plan_statistics */ 이 있습니다.

 

< TIMED_STATISTICS >

TIMED_STATISTICS는 시간과 관련된 통계 수집 여부를 지정합니다. 값은 true 또는 false 입니다.

• true : 통계는 trace 파일에 수집되거나 V$SESSTATS및 V$SYSSTATS 동적 뷰에 표시됩니다.
• false : 모든 시간 관련 통계 값은 0으로 설정됩니다. 이 설정을 사용하면 Oracle이 운영 체제에서 시간을 요청하는 오버헤드를 피할 수 있습니다.

11.1.0.7.0부터 STATISTICS_LEVEL의 값이 TYPICAL 또는 ALL로 설정된 경우 TIMED_STATISTICS 값을 false 로 설정할 수 없습니다. TIMED_STATISTICS를 true 로 설정하면 더 정확한 통계를 생성할 수 있습니다.

 

< STATISTICS_LEVEL >

STATISTICS_LEVEL은 데이터베이스 및 운영체제 통계정보에 대한 수집 수준을 지정합니다. 값은 basic, typical, all 중 하나입니다. 기본값은 typical 입니다.

• TYPICAL : 데이터베이스 자체 관리 기능에 필요한 모든 주요 통계 수집
• ALL : TYPICAL 설정으로 수집된 통계정보에 부가적인 통계 추가. 부가적인 통계는 시간이 지정된 운영 체제 통계 및 계획 실행 통계입니다.
• BASIC : Oracle Database 기능에 필요한 많은 중요한 통계 수집이 비활성화됨

 

/*+ gather_plan_statistics */

GATHER_PLAN_STATISTICS 힌트는 "Row source execution" 정보를 활성화합니다.

Row source execution 이란 Explain Plan에서 보는 Row수의 예측 값이 아닌 실제 실행 과정에서 추출된 Row 수를 보여줍니다.

 

오라클에서 실제 SQL문을 실행하는 동안의 실행 계획을 각 Row Source별로 수행통계를 수집하기 위해 gather_plan_statistics 힌트를 사용하거나 statistics_level 파리미터를 all로 설정합니다. 즉 statistics_level = all 과 /*+ gather_plan_statistics */ 힌트는 동일하게  추가적인 SQL 실행통계를 수집합니다.

/*+ gather_plan_statistics */ 힌트는 실행통계 데이터를 PLAN_TABLE에 저장하지 않고, V$SQL_PLAN 뷰에 저장합니다..

 

실행 계획을 보는 방법에는 EXPLAIN PLAN과 SET AUTORACE, SQL TRACE, DBMS_XPLAN 등이 있습니다.

1) EXPLAIN PLAN

EXPLAIN PLAN
SET STATEMENT_ID = 'HR_PLAN' INTO PLAN_TABLE --INTO PLAN_TABLE은 생략 가능
FOR
SELECT * 
FROM REGIONS A, COUNTRIES B 
WHERE  A.REGION_ID = B.REGION_ID;

 -- DBMS_XPLAN으로 PLAN 보기
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','HR_PLAN','ALL'));

 -- SQL로 PLAN 보기
SELECT
    lpad(' ', level - 1)||operation|| ' ('|| options||')' "Operation",
    object_name       "Name",
    optimizer         "Optimizer",
    cost              "Cost",
    cardinality       "Cardinality",
    bytes             "Bytes",
    partition_start   "Partition Start",
    partition_id      "Partition ID"
   FROM plan_table
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
  START WITH id = 0 AND statement_id = '&1'
  ORDER BY id;

EXPLAIN PLAN의 사용법은 SELECT문 앞에 실행 계획을 저장하라는 EXPLAIN PLAN 명령어를 사용하고 실행할 SQL에 statement ID를 부여한 다음 FOR를 쓰면 됩니다. 이렇게 쿼리문의 실행계획을 PLAN_TABLE에 저장한 후 직접 조회하거나 DBMS_XPLAN 패키지를 사용하여 조회하면 됩니다. EXPLAIN PLAN은 SQL의 실행 계획만을 수립하고 SQL문은 수행하지 않습니다. DBMS_XPLAN 패키지를 사용하는 것이 편리하고 효율적이지만, 추가적인 정보(Partition 정보 등)를 보고자 할 때 SQL 문을 수행할 수 있습니다. 

 

다만 EXPLAIN PLAN은 한번에 하나의 쿼리문의 실행계획만을 확인할 수 있고 그 실행계획을 또 확인하기 위해 별도의 SELECT문을 작성해야하는 번거로움과 데이터를 읽지 않기 떄문에 소요시간을 추정한다거나 데이터 관련 I/O정보를 확인할 수 없다는 점이 단점입니다.

 

2) AUTOTRACE 

SET AUTOTRACE ON;

SELECT * 
FROM REGIONS A, COUNTRIES B 
WHERE  A.REGION_ID = B.REGION_ID;

SET AUTOTRACE 명령어를 사용하면 쿼리문을 실행시킬 때마다 실행계획을 바로 확인할 수 있습니다. AUTOTRACE의 옵션을 활용하여 통계정보, 실행계획, 실제 데이터 조회 여부 등을 조정할 수 있습니다.

 

AUTOTRACE가 안될 경우

AUTOTRACE도 마찬가지로 PLAN_TABLE 테이블이 생성되어 있어야 하고 plustrace 라는 롤이 부여되어 있어야 합니다. plustrace 롤은 기본적으로 생성되어 있지 않으므로 sysdba로 아래 sql을 수행하여 role을 생성해 줍니다.

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

PLUSTRACE ROLE을 사용자에게 부여합니다.

$ sqlplus "/as sysdba"

SQL> grant plustrace to <user>;

이제 set autotrace on 명령어를 사용할 수 있습니다.

 

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	X	O	O	O
set autotrace traceonly explain	X	O	X	X
set autotrace traceonly statistics	X	X	O	O

 

 

3) SQL TRACE 

SQL TRACE는 실행 계획뿐만이 아니라 여러 세션에서 수행한 SQL의 통계 정보, 수행시간, 결과 등을 TRACE 파일로 기록하여 이를 파일 형태로 저장하는 방법을 말합니다. SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일을 만듭니다. 이때 만들어지는 파일은 .trc의 확장자를 갖습니다. 이 .trc 파일들은 바이너리 형태로 저장되기에 읽기 편한 형태로 파일을 변환하는 과정이 필요한데 TKPROF 유틸리티를 이용하여 변환합니다.

또한 SQL Trace는 세션 레벨과 인스턴스 레벨에서 SQL 문장들을 분석 할 수 있는데 인스턴스 레벨로 SQL Trace를 수행시키면 DBMS에 성능에 영향이 있으므로 인스턴스스 레벨의 SQL Trace는 설정하지 않도록 합니다.

 

sql trace 설정 및 해제

SQL> alter session set sql_trace = true;
OR
SQL> execute dbms_system.set_sql_trace_in_session(true);

-- 특정 세션 sql trace 설정
SQL> execute dbms_system.set_sql_trace_in_session(session_id, serial_id, true);

-- session level sql trace 종료
SQL> alter session set sql_trace = false;
-- 특정 세션 sql trace 설정 해제
SQL> execute dbms_system.set_sql_trace_in_session(session_id, serial_id, false);

DBMS_SYSTEM 패키지 는 SYS 계정만 사용할 수 있습니다. 일반 계정이 dbms_system 패키지를 사용하려면 아래와  같이 synonym 생성 및 실행권한을 부여합니다. 일반계정에 dbms_system 실행권한을 부여할 경우에는 영향도 검토를 충분히 한 다음에 부여합니다.

SQL> CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;
SQL> grant execute on dbms_system to <user>

-- To grant privileges to all users:
SQL> grant execute on dbms_system to public

 

trace 파일

sql_trace를 true로 설정한 session에서 수행한 sql 문에 대한 상세 정보는 diag 디렉토리에 SID_ora_spid.trc 라는 파일 이름으로 저장됩니다. 이 디렉토리에는 많은 trc 파일이 존재하므로, 특정 세션의 spid를 알아야 그 세션에서 생성한 파일임을 쉽게 알 수 있습니다.

trace 파일은 diagnostic_dest 로 정의된 디렉토리 하위에 diag/rdbms/orcl/ORCL/trace 경로에 존재합니다.

예시에서는 diagnostic_dest가 /data 이므로 /data/diag/rdbms/orcl/ORCL/trace 경로에 trace 파일이 생성됩니다. 경로는 각자의 SID에 따라 상이합니다.

$ sqlplus hr/<password>
SQL> select sys_context('USERENV', 'SID') from dual;

-- 위에서 확인한 SID 를 아래 &SID에 입력
select b.spid
  from v$session a, v$process b
 where a.sid = &sid
   and b.addr = a.paddr;

22978

## diagnostic 경로 확인(trace 파일이 저장되는 경로)
$ sqlplus "/as sysdba"
SQL> show parameter diag;
diagnostic_dest                      string      /data

SQL> exit;

$ cd /data/diag/rdbms/orcl/ORCL/trace

## 위에서 찾은 SPID로 trace 파일을 찾는다. trace 파일명은 SID_ora_spid.trc 형태임
## 예로 SID가 ORCL이고 spid가 22987인 경우 trace 파일은 ORCL_ora_22987.trc 

$ ls ORCL_ora_22987.trc

 

tkprof - trace 파일 분석

tkprof 명령어는 binary trace 파일을 text 파일 형태로 읽기 쉽게 변환해 줍니다.

$ tkprof trace_file.trc output_file.txt sys=no

## 위의 trace 파일을 tkprof 로 분석
$ tkprof ORCL_ora_22987.trc output_file.txt sys=no
## tkprof 로 분석한 내용을 확인(vi or vim)
$ vi output_file.txt

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute     14      0.00       0.00          0          0          0           0
Fetch       14      0.00       0.00          0         28          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       32      0.00       0.00          0         28          0           2

Misses in library cache during parse: 3
Misses in library cache during execute: 3

sys=no 옵션은 시스템 내부적으로 수행되는 SQL 문을 분석 대상에서 제외합니다.

 

4) DBMS_XPLAN 패키지

DBMX_XPLAN 패키지에는 아래와 같이 여러 기능의 function 들이 존재합니다. 이 중 가장 많이 사용하는 display 와 display_cursor function에 대해 설명합니다.

Subprogram	Description
DIFF_PLAN	플랜 비교
DISPLAY	plan_table 내용을 출력
DISPLAY_AWR	AWR에 저정되어 있는 실행계획을 표시
DISPLAY_CURSOR	cursor cache에 있는 cursor의 실행계획을 표시
DISPLAY_PLAN	다양한 형식으로 plan_table의 내용을 표시 - format : BASIC, TYPICAL, SERIAL, ALL, ADPTIVE  - type : 'TEXT', 'ACTIVE', 'HTML', 'XML'
DISPLAY_SQL_PLAN_BASELINE	SQL plan baseline의 지정된 SQL 핸들에 대해 하나 이상의 실행 계획을 표시
DISPLAY_SQLSET	SQL tuning set 에 저장된 실행 계획을 표시합니다.

 

DBMS_XPLAN.DISPLAY : 예상 실행계획 출력

Syntax

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2 DEFAULT NULL);

• table_name : 실행계획이 저장된 테이블 이름을 지정합니다. 기본값은 PLAN_TABLE입니다. NULL이 지정되면 기본값도 PLAN_TABLE입니다.
• statement_id :  표시할 statement_id를 지정합니다. 이 매개변수의 기본값은 NULL이며, 이는 set statement_id 절 없이 EXPLAIN PLAN 명령이 실행될 때의 기본값입니다. statement_id가 지정되지 않으면 함수는 가장 최근에 설명된 명령문의 실행계획을 표시합니다.
• format : 실행계획에 대한 세부 정보 수준을 제어합니다. BASIC, TYPICAL, SERIAL, ALL 4가지가 있습니다.  여러가지 옵션을 추가할 수 있습니다.
• filter_preds : 실행계획이 저장된 테이블에서 선택된 행 세트를 제한하는 SQL 필터 redicate. 값이 NULL(기본값)이면 마지막으로 실행된 explain plan을 표시합니다. 예) filter_preds=>'plan_id = 10' 

Examples

Plan 테이블에 저장된 마지막 EXPLAIN PLAN 명령의 결과를 표시합니다.

SELECT * FROM table (DBMS_XPLAN.DISPLAY);

기본 plan 테이블이 아닌 다른 테이블("my_plan_table")의 데이터를 표시합니다.

SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));

실행계획의 최소 정보만을 표시합니다.

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));

To display the plan for a statement identified by 'foo', such as statement_id='foo':

statement_id='foo'와 같이 'foo'로 식별되는 SQL문장에 대한 실행계획을 표시합니다.

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'foo'));

 

 DBMS_XPLAN.DISPLAY_CURSOR : shared pool 커서의 실제 실행계획 출력

Syntax

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id            IN  VARCHAR2  DEFAULT  NULL,
   cursor_child_no   IN  NUMBER    DEFAULT  0, 
   format            IN  VARCHAR2  DEFAULT  'TYPICAL');

• sql_id :  커서 캐시에 있는 SQL 문의 SQL_ID를 지정합니다. V$SQL 또는 V$SQLAREA에서 SQL_ID 열을 쿼리하여 검색할 수 있습니다. 또는 V$SESSION에서 특정 세션에 대해 PREV_SQL_ID 열을 선택할 수 있습니다. 이 변수는 default가 NULL이며 이 경우 세션에서 실행한 마지막 커서의 실행계획이 표시됩니다.
• cursor_child_no : 표시할 커서의 자식번호입니다. 자식번호가 제공하지 않으면 제공된 sql_id 와 일치하는 모든 커서의 실행 계획이 표시됩니다.
• format : 실행계획에 대한 세부 정보 수준을 제어합니다. BASIC, TYPICAL, SERIAL, ALL, ADPTIVE 5가지 외에 문서화되지 않은 advanced가 있습니다. advanced는 all 에 Peeked Binds, Outline Data, note 등을 더보여줍니다. format 뒤에 여러가지 옵션을 추가할 수 있습니다. 예) 'advanced allstats last'

 

예제

현재 세션에서 실행된 마지막 SQL 문의 실행 계획을 표시하려면 다음을 수행합니다.

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

SQL ID 'atfwcg8anrykp'와 연관된 실행 계획을 표시하려면 다음을 수행합니다.

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));

런타임 통계를 표시하려면 다음을 수행합니다.

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS LAST');

DBMS_XPLAN에서 제공하는 모든 통계 정보를 표시하려면 아래와 같이 수행합니다.

 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', '0', 'advanced allstats last'))

 

마무리

위와 같이 다양한 방법으로 SQL문의 실행 계획과 그에 따른 통계정보를 확인할 수 있습니다.

사전 예측정보는 실제 수행 통계와 상이할 수 있으므로 간단한 SQL 실행계획 확인용으로 사용하고,

SQL튜닝을 위한 상세 정보를 위해서는 DBMS_XPLAN.DISPLAY_CURSOR를 활용합니다.

parsing, fetch, query block, current block 등의 추가 정보가 필요한 경우에는 tkprof 를 활용합니다. 

 

 

 

SQL 튜닝 절차

 

1. 튜닝대상 SQL 식별 :

     AWR report, APM 툴, DB 모니터링 툴, 개발자 요청, 전수조사, 샘플조사 등

 

2. 튜닝대상 SQL의 sql_id 추출 :

     SQL_ID가 제공된 경우에는 제공된 sql_id를 사용하고 그렇지 않은 경우에는V$SQLAREA, V$SQL,           

     DBA_HIST_SQLTEXT, DBA_HIST_SQLSTAT 등의 뷰에서 추출합니다.

 

3. DBMS_XPLAN.DISPLAY_CURSOR 수행 :

    추출한 sql_id 를 가지고  DBMS_XPLAN.DISPLAY_CURSOR 패키지로 실행계획, 통계 정보 등을 조회합니다.

 

4. SQL 튜닝

    인덱스 생성, SQL 재작성, SQL분리, 힌트 사용, 데이터모델 변경, 반정규화, 요약테이블 생성  등으로 SQL 문장을 튜닝합니다.