튜닝의 도구 – Explain Plan
Explain Plan은 실행 계획을 사용자에게 제공하는 명령 입니다. SQL문이 처음 실행될 때 서버 프로세스가 파싱을 한 후 SQL문법에 대한 오류를 확인하고 데이터딕셔너리를 통해 권한이나 객체에 대한 확인 작업을 거친 후 해당 SQL문장이 어떻게 실행 될 것인가에 대해 결정을 하게 되는데 이 결과를 실행 계획(execution Plan)이라 하며 이 결과를 확인 할 수 있는 도구가 Explain Plan 입니다.
Explain Plan을 통해 SQL문이 실행 될 때의 상태 정보를 확인 할 수 있으며 실행 계획은 실행 계획 테이블(PLAN_TABLE)에 저장 됩니다.
아래의 예문을 통해 쓰임새를 알아 보도록 합니다.
Explain Plan 명령을 사용 하기 위해서는 SQL을 분석한 결과를 저장 할 테이블(PLAN_TABLE)이 있어야 합니다. C 드라이브에 오라클이 설치되었다고 한다면 C:\oracle\ora92\rdbms\admin\Utlxplan.sql 이라는 파일을 통해 생성을 하면 됩니다.
기본 문법은 다음과 같습니다.
Explain Plan
[Set statement_id = ‘사용자가 지정한 아이디’]
[into 테이블명]
For SQL문장…
statement_id : SQL문에 대한 아이디를 줌으로써 다른 사용자의 것과 구분이 가능
데이블명 : 실행 계획을 저장 할 테이블 명, 별도로 지정 하지 않으면 PLAN_TABLE
SQL문장 : 실행 계획을 분석 하고자 하는 SQL문
SQL> @C:\oracle\ora92\rdbms\admin\Utlxplan.sql
테이블이 생성되었습니다.
SQL> explain plan
2 set statement_id='1'
3 for select ename, sal, deptno
4 from emp
5 where empno = 7369;
해석되었습니다.
SQL> select rtrim(lpad(' ',2*level) ||
2 rtrim(operation) || ' ' ||
3 rtrim(options) || ' ' || object_name) query_plan ,
4 cost,
5 cardinality
6 from plan_table
7 start with id=0 and statement_id='1'
8 connect by prior id = parent_id and statement_id = '1';
QUERY_PLAN COST CARDINALITY
---------------------------------------- ---------- -----------
SELECT STATEMENT 2 1
TABLE ACCESS BY INDEX ROWID EMP 2 1
INDEX UNIQUE SCAN EMP_PK 1 14
물론 아래처럼 Oracle에서 제공하는 utlxplp.sql 파일을 이용하여 PLAN_TABLE의 내용을 확인 할 수도 있습니다.
SQL> @C:\oracle\ora92\rdbms\admin\utlxplp.sql
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 17 | 2 |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | 14 | | 1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
2 - access("EMP"."EMPNO"=7369)
Note: cpu costing is off
15 개의 행이 선택되었습니다.
실행 계획의 해석
SELECT STATEMENT 2 1
TABLE ACCESS BY INDEX ROWID EMP 2 1
INDEX UNIQUE SCAN EMP_PK 1 14
들여 쓰기가 가장 많이 된 부분이 실행 되며 그래서 맨 아래 부분이 먼저 실행 된다는 겁니다. EMP_PK 인덱스를 이용하여 인덱스 Unique 검색을 수행하며 찾은 rowid로 TABLE ACCESS가 이루어지므로 2번째와 3번째는 한단계로 볼 수 있습니다. 즉 같은 수준이 되는 것입니다. 즉 위의 SQL 문장은 Primary Key 인덱스를 이용하여 찾은 데이터의 rowid를 이용하여 검색을 수행 한다는 것입니다.
다른 예제를 해보겠습니다.
SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from dept;
COUNT(*)
----------
4
혹시 이전의 실행 계획이 저장 되어 있을지 몰라 삭제 합니다.
SQL> delete from plan_table;
SQL> commit;
커밋이 완료되었습니다.
EMP Table의 deptno는 인덱스가 결려 있습니다. 오라클은 기본적으로 COST BASED OPTIMIZER 이므로 비용(COST)를 따지게 됩니다. 현재 데이터의 건수가 얼마 되지 않아 인덱스를 이용하는 것보다 테이블을 FULL SCAN하는 것이 효율이 좋아 이를 선택하게 됩니다.
SQL> explain plan
2 set statement_id = '1'
3 for
4 select emp.ename, dept.dname
5 from emp, dept
6 where emp.deptno = dept.deptno;
해석되었습니다.
SQL> col query_plan format a40
SQL> select rtrim(lpad(' ',2*level) ||
2 rtrim(operation) || ' ' ||
3 rtrim(options) || ' ' || object_name) query_plan ,
4 cost,
5 cardinality
6 from plan_table
7 start with id=0 and statement_id='1'
8 connect by prior id = parent_id and statement_id = '1';
QUERY_PLAN COST CARDINALITY
---------------------------------------- ---------- -----------
SELECT STATEMENT 5 14
HASH JOIN 5 14
TABLE ACCESS FULL DEPT 2 4
TABLE ACCESS FULL EMP 2 14
이 경우엔 DEPT 테이블을 FULL SCAN 후 EMP 테이블의 FULL SCAN된 데이터와 HASH JOIN하여 결과를 얻어내게 됩니다.
이번에 옵티마이저 모드를 RULE BASED OPTIMIZER로 변경해 보겠습니다. 이 경우엔 전체적인 수행의 비용보다 SQL 문법에 의존적이며 15개 정도 미리 정해져 있는 공식에 따라 가장 빠른 실행 방법이 결정 됩니다. 인덱스가 걸려 있으면 우선적으로 이용하게 되는 거죠… RBO(RULE BASED OPTIMIZER)에서 최 상위의 검색 방법은 ROWID를 이용하는 겁니다, 옵티마이저에 대해서는 추후 강좌에서 자세히 살펴 볼겁니다.
SQL> alter session set optimizer_goal = RULE
2 ;
세션이 변경되었습니다.
SQL> explain plan
2 set statement_id = '2'
3 for
4 select emp.ename, dept.dname
5 from emp, dept
6 where emp.deptno = dept.deptno;
해석되었습니다.
SQL> col query_plan format a40
SQL> select rtrim(lpad(' ',2*level) ||
2 rtrim(operation) || ' ' ||
3 rtrim(options) || ' ' || object_name) query_plan ,
4 cost,
5 cardinality
6 from plan_table
7 start with id=0 and statement_id='2'
8 connect by prior id = parent_id and statement_id = '2';
QUERY_PLAN COST CARDINALITY
---------------------------------------- ---------- -----------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID EMP
NESTED LOOPS
TABLE ACCESS FULL DEPT
INDEX RANGE SCAN EMP_DEPTNO
이 경우엔 DEPT TABLE을 FULL SCAN(DEPT 테이블이 드라이빙 테이블임)후 EMP Table의 인덱스인 EMP_DEPTNO를 이용하여 RANGE SCAN한 후 NESTED LOOP이므로 DEPT 테이블의 전체 레코드를 한건식 Access하여 EMP Table의 데이터를 ROWID로 얻어 내는 과정을 반복하게 됩니다.
다음은 PLAN_TABLE의 컬럼에 대한 설명이니 참고 바랍니다.
STATEMENT_ID explain plan에서 지정한 SQL문을 구별하기 위한 식별자(여러 개를 분석 할 수 있으므로)
TIMESTAMP explain plan 구분을 실행한 시간
REMARKS 주석
OPERATION 각 단계별로 수행할 연산자(HASH JOIN, TABLE ACCESS 등등)
OPTIONS 연산자에 대한 옵션(FULL SACN, RANGE SCAN등등)
OBJECT_NODE 사용된 객체가 참고하는데 사용된 데이터베이스 링크
OBJECT_OWNER 사용된 객체의 소유자
OBJECT_NAME 객체 이름
OBJECT_INSTANCE 원래 SQL문에 있는 FROM절의 객체의 위치를 정수화 한 값
OBJECT_TYPE 객체의 유형
OPTIMIZER 현재의 Optimizer Mode(RULE, CHOOSE, FIRST_ROW…)
SEARCH_COLUMNS 시작과 끝 키를 가진 인덱스 컬럼의 수
ID 실행 계획 각 단계의 ID
PARENT_ID 각 단계의 결과를 가지고 하는 상위 단계 ID
POSITION 두개의 간계가 같은 상위 ID를 가질 때 가장 낮은 ID가 먼저 실행
COST CBO 에 의해 추정된 현재의 연산 비용
CARDINALITY 현재 연산을 통해 추출 될 레코드 수
BYTES 현재 연산을 통해 추출된 바이트
OTHER_TAG OTHER 컬럼에서 SQL 텍스트의 기능을 표현
PARTITION_START 파티션 범위 검색 시 시작 파티션
PARTITION_STOP 파티션 범위 검색 시 종료 파티션
PARTITION_ID 파티션 범위 검색 시 시작 파티션과 파티션 범위 검색 시 종료 파티션 컬럼의 값을 계산
OTHER 병렬 실행 슬레이브와 병렬 쿼리에 대한 정보
DISTRIBUTION 병렬 실행 슬레이브가 어떻게 레코드를 추출하는가의 방법
CPU_COST 사용자 정의 CPU 비용
IO_COST 사용자 정의 IO 비용
Explain Plan은 실행 계획을 사용자에게 제공하는 명령 입니다. SQL문이 처음 실행될 때 서버 프로세스가 파싱을 한 후 SQL문법에 대한 오류를 확인하고 데이터딕셔너리를 통해 권한이나 객체에 대한 확인 작업을 거친 후 해당 SQL문장이 어떻게 실행 될 것인가에 대해 결정을 하게 되는데 이 결과를 실행 계획(execution Plan)이라 하며 이 결과를 확인 할 수 있는 도구가 Explain Plan 입니다.
Explain Plan을 통해 SQL문이 실행 될 때의 상태 정보를 확인 할 수 있으며 실행 계획은 실행 계획 테이블(PLAN_TABLE)에 저장 됩니다.
아래의 예문을 통해 쓰임새를 알아 보도록 합니다.
Explain Plan 명령을 사용 하기 위해서는 SQL을 분석한 결과를 저장 할 테이블(PLAN_TABLE)이 있어야 합니다. C 드라이브에 오라클이 설치되었다고 한다면 C:\oracle\ora92\rdbms\admin\Utlxplan.sql 이라는 파일을 통해 생성을 하면 됩니다.
기본 문법은 다음과 같습니다.
Explain Plan
[Set statement_id = ‘사용자가 지정한 아이디’]
[into 테이블명]
For SQL문장…
statement_id : SQL문에 대한 아이디를 줌으로써 다른 사용자의 것과 구분이 가능
데이블명 : 실행 계획을 저장 할 테이블 명, 별도로 지정 하지 않으면 PLAN_TABLE
SQL문장 : 실행 계획을 분석 하고자 하는 SQL문
SQL> @C:\oracle\ora92\rdbms\admin\Utlxplan.sql
테이블이 생성되었습니다.
SQL> explain plan
2 set statement_id='1'
3 for select ename, sal, deptno
4 from emp
5 where empno = 7369;
해석되었습니다.
SQL> select rtrim(lpad(' ',2*level) ||
2 rtrim(operation) || ' ' ||
3 rtrim(options) || ' ' || object_name) query_plan ,
4 cost,
5 cardinality
6 from plan_table
7 start with id=0 and statement_id='1'
8 connect by prior id = parent_id and statement_id = '1';
QUERY_PLAN COST CARDINALITY
---------------------------------------- ---------- -----------
SELECT STATEMENT 2 1
TABLE ACCESS BY INDEX ROWID EMP 2 1
INDEX UNIQUE SCAN EMP_PK 1 14
물론 아래처럼 Oracle에서 제공하는 utlxplp.sql 파일을 이용하여 PLAN_TABLE의 내용을 확인 할 수도 있습니다.
SQL> @C:\oracle\ora92\rdbms\admin\utlxplp.sql
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 17 | 2 |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | 14 | | 1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
2 - access("EMP"."EMPNO"=7369)
Note: cpu costing is off
15 개의 행이 선택되었습니다.
실행 계획의 해석
SELECT STATEMENT 2 1
TABLE ACCESS BY INDEX ROWID EMP 2 1
INDEX UNIQUE SCAN EMP_PK 1 14
들여 쓰기가 가장 많이 된 부분이 실행 되며 그래서 맨 아래 부분이 먼저 실행 된다는 겁니다. EMP_PK 인덱스를 이용하여 인덱스 Unique 검색을 수행하며 찾은 rowid로 TABLE ACCESS가 이루어지므로 2번째와 3번째는 한단계로 볼 수 있습니다. 즉 같은 수준이 되는 것입니다. 즉 위의 SQL 문장은 Primary Key 인덱스를 이용하여 찾은 데이터의 rowid를 이용하여 검색을 수행 한다는 것입니다.
다른 예제를 해보겠습니다.
SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from dept;
COUNT(*)
----------
4
혹시 이전의 실행 계획이 저장 되어 있을지 몰라 삭제 합니다.
SQL> delete from plan_table;
SQL> commit;
커밋이 완료되었습니다.
EMP Table의 deptno는 인덱스가 결려 있습니다. 오라클은 기본적으로 COST BASED OPTIMIZER 이므로 비용(COST)를 따지게 됩니다. 현재 데이터의 건수가 얼마 되지 않아 인덱스를 이용하는 것보다 테이블을 FULL SCAN하는 것이 효율이 좋아 이를 선택하게 됩니다.
SQL> explain plan
2 set statement_id = '1'
3 for
4 select emp.ename, dept.dname
5 from emp, dept
6 where emp.deptno = dept.deptno;
해석되었습니다.
SQL> col query_plan format a40
SQL> select rtrim(lpad(' ',2*level) ||
2 rtrim(operation) || ' ' ||
3 rtrim(options) || ' ' || object_name) query_plan ,
4 cost,
5 cardinality
6 from plan_table
7 start with id=0 and statement_id='1'
8 connect by prior id = parent_id and statement_id = '1';
QUERY_PLAN COST CARDINALITY
---------------------------------------- ---------- -----------
SELECT STATEMENT 5 14
HASH JOIN 5 14
TABLE ACCESS FULL DEPT 2 4
TABLE ACCESS FULL EMP 2 14
이 경우엔 DEPT 테이블을 FULL SCAN 후 EMP 테이블의 FULL SCAN된 데이터와 HASH JOIN하여 결과를 얻어내게 됩니다.
이번에 옵티마이저 모드를 RULE BASED OPTIMIZER로 변경해 보겠습니다. 이 경우엔 전체적인 수행의 비용보다 SQL 문법에 의존적이며 15개 정도 미리 정해져 있는 공식에 따라 가장 빠른 실행 방법이 결정 됩니다. 인덱스가 걸려 있으면 우선적으로 이용하게 되는 거죠… RBO(RULE BASED OPTIMIZER)에서 최 상위의 검색 방법은 ROWID를 이용하는 겁니다, 옵티마이저에 대해서는 추후 강좌에서 자세히 살펴 볼겁니다.
SQL> alter session set optimizer_goal = RULE
2 ;
세션이 변경되었습니다.
SQL> explain plan
2 set statement_id = '2'
3 for
4 select emp.ename, dept.dname
5 from emp, dept
6 where emp.deptno = dept.deptno;
해석되었습니다.
SQL> col query_plan format a40
SQL> select rtrim(lpad(' ',2*level) ||
2 rtrim(operation) || ' ' ||
3 rtrim(options) || ' ' || object_name) query_plan ,
4 cost,
5 cardinality
6 from plan_table
7 start with id=0 and statement_id='2'
8 connect by prior id = parent_id and statement_id = '2';
QUERY_PLAN COST CARDINALITY
---------------------------------------- ---------- -----------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID EMP
NESTED LOOPS
TABLE ACCESS FULL DEPT
INDEX RANGE SCAN EMP_DEPTNO
이 경우엔 DEPT TABLE을 FULL SCAN(DEPT 테이블이 드라이빙 테이블임)후 EMP Table의 인덱스인 EMP_DEPTNO를 이용하여 RANGE SCAN한 후 NESTED LOOP이므로 DEPT 테이블의 전체 레코드를 한건식 Access하여 EMP Table의 데이터를 ROWID로 얻어 내는 과정을 반복하게 됩니다.
다음은 PLAN_TABLE의 컬럼에 대한 설명이니 참고 바랍니다.
STATEMENT_ID explain plan에서 지정한 SQL문을 구별하기 위한 식별자(여러 개를 분석 할 수 있으므로)
TIMESTAMP explain plan 구분을 실행한 시간
REMARKS 주석
OPERATION 각 단계별로 수행할 연산자(HASH JOIN, TABLE ACCESS 등등)
OPTIONS 연산자에 대한 옵션(FULL SACN, RANGE SCAN등등)
OBJECT_NODE 사용된 객체가 참고하는데 사용된 데이터베이스 링크
OBJECT_OWNER 사용된 객체의 소유자
OBJECT_NAME 객체 이름
OBJECT_INSTANCE 원래 SQL문에 있는 FROM절의 객체의 위치를 정수화 한 값
OBJECT_TYPE 객체의 유형
OPTIMIZER 현재의 Optimizer Mode(RULE, CHOOSE, FIRST_ROW…)
SEARCH_COLUMNS 시작과 끝 키를 가진 인덱스 컬럼의 수
ID 실행 계획 각 단계의 ID
PARENT_ID 각 단계의 결과를 가지고 하는 상위 단계 ID
POSITION 두개의 간계가 같은 상위 ID를 가질 때 가장 낮은 ID가 먼저 실행
COST CBO 에 의해 추정된 현재의 연산 비용
CARDINALITY 현재 연산을 통해 추출 될 레코드 수
BYTES 현재 연산을 통해 추출된 바이트
OTHER_TAG OTHER 컬럼에서 SQL 텍스트의 기능을 표현
PARTITION_START 파티션 범위 검색 시 시작 파티션
PARTITION_STOP 파티션 범위 검색 시 종료 파티션
PARTITION_ID 파티션 범위 검색 시 시작 파티션과 파티션 범위 검색 시 종료 파티션 컬럼의 값을 계산
OTHER 병렬 실행 슬레이브와 병렬 쿼리에 대한 정보
DISTRIBUTION 병렬 실행 슬레이브가 어떻게 레코드를 추출하는가의 방법
CPU_COST 사용자 정의 CPU 비용
IO_COST 사용자 정의 IO 비용
오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급
www.oraclejavacommunity.com
평일주간(9:30~18:10) 개강
(7/28)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍
(7/28)[기업100%환급]안드로이드개발자과정
(8/04)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(8/04)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(8/08)[기업100%환급]SQL기초에서 Schema Object까지
(8/08)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(8/11)[채용예정교육]오라클자바개발잘하는신입뽑기프로젝트,교육전취업확정
평일야간(19:00~21:50) 개강
(7/24)SQL기초에서실무까지
(7/29)안드로이드개발자과정
(7/29)Spring3.X, MyBatis, Hibernate실무과정
(8/04)웹퍼블리싱 마스터
(8/05)JSP,Ajax,jQUERY,Spring,MyBatis,Hibernate속성과정
(8/08)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정
(8/28)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
주말(10:00~17:50) 개강
(7/26)Spring3.X, MyBatis, Hibernate실무과정
(7/26)개발자를위한PLSQL,SQL튜닝,힌트
(8/02)C#,ASP.NET마스터
(8/02)웹퍼블리싱 마스터
(8/02)SQL초보에서 Schema Object까지
(8/09)안드로이드개발자과정
(8/09)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(8/23)JAVA,Network&WEB&Framework(자바기초에서웹스프링까지)
주말저녁(18:30~22:20) 개강
(8/02)JAVA,Network&WEB&Framework
(8/09)SQL기초에서실무까지
www.oraclejavacommunity.com
평일주간(9:30~18:10) 개강
(7/28)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍
(7/28)[기업100%환급]안드로이드개발자과정
(8/04)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(8/04)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(8/08)[기업100%환급]SQL기초에서 Schema Object까지
(8/08)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(8/11)[채용예정교육]오라클자바개발잘하는신입뽑기프로젝트,교육전취업확정
평일야간(19:00~21:50) 개강
(7/24)SQL기초에서실무까지
(7/29)안드로이드개발자과정
(7/29)Spring3.X, MyBatis, Hibernate실무과정
(8/04)웹퍼블리싱 마스터
(8/05)JSP,Ajax,jQUERY,Spring,MyBatis,Hibernate속성과정
(8/08)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정
(8/28)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
주말(10:00~17:50) 개강
(7/26)Spring3.X, MyBatis, Hibernate실무과정
(7/26)개발자를위한PLSQL,SQL튜닝,힌트
(8/02)C#,ASP.NET마스터
(8/02)웹퍼블리싱 마스터
(8/02)SQL초보에서 Schema Object까지
(8/09)안드로이드개발자과정
(8/09)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(8/23)JAVA,Network&WEB&Framework(자바기초에서웹스프링까지)
주말저녁(18:30~22:20) 개강
(8/02)JAVA,Network&WEB&Framework
(8/09)SQL기초에서실무까지
댓글 없음:
댓글 쓰기