[Oracle] 오라클 실행 계획 확인하기 (EXPLAIN PLAN, SET AUTORACE, SQL TRACE)

실행 계획 확인하기 

실행 계획이란 SQL이 실행되어 데이터를 처리하고자 할 때 옵티마이저에서 수립되는 SQL 실행 방법론입니다. 이 실행 계획을 구성하는 내용의 분석을 통해 SQL의 비 효율적인 부분을 확인할 수 있고 쿼리의 성능 향상을 위한 튜닝 포인트도 도출해낼 수 있습니다. 실행 계획을 알아내는 방법은 크게 EXPLAIN PLAN과 SET AUTORACE, SQL TRACE 이렇게 세 가지 방법이 있습니다.

 

[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여

[DB] 데이터베이스 실행 계획에 대하여

 

 EXPLAIN PLAN 

사용 예시

EXPLAIN PLAN -- EXPLANIN  PLAN 선언부
SET STATEMENT_ID = 'PLAN1' INTO PLAN_TABLE -- SQL에 PLAN1이라는 ID 부여
FOR
SELECT * FROM REGIONS A --SQL 입력부
LEFT OUTER JOIN COUNTRIES B ON A.REGION_ID = B.REGION_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY -- PLAN_TABLE에 저장된 실행계획 조회
('PLAN_TABLE','PLAN1','ALL'));

EXPLAIN PLAN의 사용법은 SELECT문 앞에 실행 계획을 저장하라는 명시를 해주고 실행할 SQL에 임의의 ID를 부여한 다음 FOR를 쓰면 됩니다. 이렇게 쿼리문의 실행계획을 PLAN_TABLE에 저장한 후 직접 조회하시거나 오라클에서 제공하는 DBMS_XPLAN 패키지를 사용하여 조회하시면 됩니다. EXPLAIN PLAN은 SQL의 실행 계획만을 확인할 수 있고 명령을 사용할 때 데이터를 처리하지 않습니다. 그렇기 때문에 데이터베이스에 그 어떤 부하도 걸리지는 않습니다.

 

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

 

@$ORACLE_HOME/rdbms/admin/utlxplan

만약 PLAN_TABLE SYNONYM 이 없다면 위 명령어를 통해 오라클에서 제공하는 스크립트를 실행해서 생성할 수 있습니다. 추가로 오라클 10g부터는 별도의 PLAN_TABLE을 생성하지 않아도 SYS.PLAN_TABLE$ 테이블을 사용하여 실행 계획을 저장하는 것도 가능해졌습니다.

 

PLAN_TABLE의 구조

항목	설명
STATEMENT_ID	EXPLAIN PLAN 명령을 수행할 떄 사용자가 정의한 ID
PLAN_ID	데이터베이스 내에서 실행 계획이 갖게 되는 단일 속성
TIMESTAMP	EXPLAIN PLAN 명령을 수행한 날짜, 시간 정보
REMARKS	EXPLAIN PLAN 명령을 수행할 떄 사용자가 임의로 넣는 주석
OPERATION	INSERT, SELECT, UPDATE, DELETE 가운데 하나가 수행될 때 단계 별 작동형태를 정의
OPTIONS	OPERATION의 상세 옵션을 설명
OBJECT_NODE	참조하는 객체의 데이터베이스 링크 이름
OBJECT_OWNER	테이블 또는 인덱스의 소유주
OBJECT_NAME	테이블 또는 인덱스의 이름
OBJECT_ALIAS	SQL에서 정의된 테이블 또는 뷰의 유일한 별칭
OBJECT_INSTANCE	FROM절에 기술된 객체에 부여하는 번호
OJBECT_TYPE	객체의 유형
OPTIMIZER	옵티마이저 모드 정보
ID	수립된 각 실행 단계의 일련번호
PARENT_ID	ID에 해당하는 부모 ID
POSITION	동일 PARENT_ID를 가지는 ID 사이의 처리 순서
COST	각 처리 단계별 비용
CARDINALITY	명령 수행으로 인해 접근하게 될 행 수의 쿼리 최적화 방법에 의한 예측값
BYTES	명령 수행으로 인해 접근하게 될 바이트 수의 쿼리 최적화 방법에 의한 예측값

 

 SET AUTOTRACE 

사용 예시

SET AUTOTRACE ON; -- 자동 추적 사용

SELECT * FROM REGIONS A -- 쿼리문 실행
LEFT OUTER JOIN COUNTRIES B ON A.REGION_ID = B.REGION_ID;

SET AUTOTRACE 명령어를 사용하면 위와 같이 자동 추적 사용이라는 문구가 출력되고 쿼리문을 실행시킬 때마다 실행계획을 바로바로 확인하실 수 있습니다. EXPLAIN PLAN 명령과는 달리 한번의 명령으로 여러 개의 SQL에 대한 실행계획을 바로 볼 수 있다는 편리함 측면에서의 장점이 있고 또 AUTOTRACE의 다양한 옵션을 활용하여 데이터를 읽어 들일 수도 있고 데이터를 읽지 않게 할 수도 있으며 데이터를 읽게 된다면 I/O정보나 SORT 정보 등의 여러 가지 정보를 추가적으로 확인하실 수도 있다는 장점이 있습니다.

 

AUTOTRACE가 안될 경우

AUTOTRACE도 마찬가지로 PLAN_TABLE 테이블이 생성되어 있어야 합니다. 없다면 앞서 EXPLAIN PLAN에서 설명한대로 스크립트를 실행해서 만들어주셔야 합니다. 그리고 PLUSTRACE라는 롤을 받아야 하는데 아래 스크립트를 실행시키시면 됩니다.

$ORACLE_HOME/sqlplus/admin/plustrace.sql

그런 뒤 PLUSTRACE 권한에 GRANT를 활용하여 권한을 부여합니다.

SQL> conn /as system
Connected.
SQL> grant plustrance to hr;
Grant plustrace to hr

 

위와 같이 권한을 줄 수 있는 계정으로 로그인 해 필요한 계정에 권한을 부여하시면 됩니다.

 

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

 

 SQL TRACE 

SQL TRACE는 실행 계획뿐만이 아니라 여러 세션에서 수행한 SQL의 통계 정보, 수행 시간, 결과 등을 TRAE로 기록하여 이를 파일 형태로 저장하는 방법을 말합니다. SQL Trace는 실행되는 SQL 문의 실행 통계를 세션별로 모아서 Trace 파일을 만듭니다. 이때 만들어지는 파일은 .trc의 확장자를 갖습니다. 이 .trc 파일들은 바이너리 형태로 저장되기에 읽기 편한 형태로 파일을 변환하는 과정이 필요한데 TKPROF 유틸리티를 이용하면 쉽게 변환하실 수 있습니다. 또한 SQL Trace는 세션과 인스턴스 레벨에서 SQL 문장들을 분석할 수 있습니다. 인스턴스 레벨로 Trace를 수행시키면 전체적인 수행능력이 20~30% 정도 감소하므로, 될 수 있으면 세션 레벨로 Trace 파일을 생성해야 합니다.

 

사용 예시

SQL> conn /as system
Connected.
SQL> grant alter session to hr;
Grant succeeded.
-- session level 실행 방법
SQL> alter session set sql_trace = true;
SQL> execute dbms_system.set_sql_trace_in_session(true);
SQL> execute dbms_system.set_sql_trace_in_session(session_id, serial_id, true);
session altered.
-- session level로 종료 방법
SQL> alter session set sql_trace = false;
session altered.

 

저장 경로 확인

SQL> show parameter user_dump_dest; --경로 출력
$ cd admin/testdb/udump/
$ ls
파일이름.trc
$ vi 파일이름.trc -- 내용 분석

 

TKPROF 사용하여 확인 할수 있는 형식으로 바꾸기

tkprof trace_file.trc new_file.txt sys=no

위의 명령문을 터미널에서 실행시키면 텍스트 파일로 변환하는 것이 가능합니다. sys=no라는 옵션을 사용하면 시스템에서 내부적으로 수행되는 SQL 문장에 대해서는 변환을 제외해주므로 같이 사용하도록 합시다.

 

 실행 계획에 대해 더욱 자세히 알고 싶다면 아래 글을 참고해주세요. 

[DB] 데이터베이스 실행 계획에 대하여