4. DBMS_XPLAN 패키지
2010.06.06 20:41
- DBMS_XPLAN 패키지를 이용하여 plan_table의 실행계획을 더 쉽게 출력해볼 수 있다.
▣ 예상 실행 계획 출력
- @/rdbms/admin/utlxpls 파일을 보면 dbms_xplan 패키지를 호출하고 있다.
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
- 2번째 인자 : 문장번호, 없으면 가장 마지막 explain plan 명령의 실행 계획
(문법)
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'BASIC'));
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'TYPICAL'));
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'SERIAL'));
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ALL'));
- 옵션 선택 사용 가능
(ROWS BYTES COST PARTITION PARALLEL PREDICATE PROJECTION ALIAS REMOTE NOTE)
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'BASIC ROWS BYTES COST'));
- 모두 볼 경우
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ALL'));
- 힌트 목록을 볼 경우
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'OUTLINE'));
- ALL+OUTLINE=ADVANCED
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ADVANCED'));
(실습)
explain plan set statement_id='SQL1' for
select * from emp e, dept d where d.deptno=e.deptno and e.sal > 1000;
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ADVANCED'));
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 627 | 4 (0)|
| 1 | NESTED LOOPS | | 11 | 627 | 4 (0)|
|* 2 | TABLE ACCESS FULL | EMP | 11 | 407 | 3 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)|
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
-----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL">1000)
4 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- 'PLAN_TABLE' is old version
▣ 캐싱된 커서의 실행 계획 출력
- 커서 : 하드파싱과정을 거쳐서 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area를 말한다.
- 오라클은 라이브러리 캐시에 캐싱되어 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰를 제공.
- 이와 함께 sql_id 값으로 조인에서 사용할 수 있도록 v$sql_plan, v$sql_plan_statistics, v$sql_plan_statistics_all 등의 뷰를 제공
(실습)
set serveroutput off
select * from emp e, dept d where d.deptno=e.deptno and e.sal >= 1000;
column prev_sql_id new_value sql_id
column prev_child_number new_value child_no
select prev_sql_id, prev_child_number from v$session
where sid=userenv('sid') and username is not null
and prev_hash_value <> 0;
- v$sql_plan 뷰를 일반 plan_table처럼 쿼리해서 조회할 수 있으나, dbms_xplan.display_cursor함수를 이용하면 편리.
select * from table(dbms_xplan.display_cursor('[sql_id]', [child_no], '[format]'));
select * from table(dbms_xplan.display_cursor('&sql_id', &child_no, 'BASIC ROWS BYTES COST PREDICATE'));
(실행결과)
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from emp e, dept d where d.deptno=e.deptno and e.sal >= 1000
Plan hash value: 351108634
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)|
| 1 | NESTED LOOPS | | 12 | 684 | 4 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL | EMP | 12 | 444 | 3 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)|
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL">=1000)
4 - access("D"."DEPTNO"="E"."DEPTNO")
- 참고로, dbms_xplan.display_awr함수를 이용하면 AWR에 수집된 과거 수행되었던 SQL에 대해서도 같은 분석작업을 진행할 수 있다.
▣ 캐싱된 커서의 Row Source별 수행통계 출력
- SQL문에 gather_plan_statistics 힌트를 사용하거나, 시스템 또는 세션레벨에서 statistics_level파라미터를 all로 설정하면, 오라클은 실제 SQL을 수행하는 동안의 실행계획 각 오퍼레이션 단계(Row Source)로 수행통계를 수집한다.
(실습)
set serveroutput off
select /*+ gather_plan_statistics */ *
from emp e, dept d
where d.deptno=e.deptno and e.sal >=1000
select *
from v$sql_plan_statistics_all
where sql_id='$sql_id' and child_number=&child_no
order by id;
- dbms_xplan.display_cursor 사용
select * from table(dbms_xplan.display_cursor('...', 0, 'IOSTATS'));
select * from table(dbms_xplan.display_cursor('...', 0, 'MEMSTATS'));
select * from table(dbms_xplan.display_cursor('...', 0, 'IOSTATS MEMSTATS'));
select * from table(dbms_xplan.display_cursor('...', 0, 'ALLSTATS'));
(실습)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 90x6aga13xhu3, child number 0
-------------------------------------
select * from emp e, dept d where d.deptno=e.deptno and e.sal >= 1000
Plan hash value: 351108634
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 12 | 10 |00:00:00.02 | 20 | 1 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 12 | 10 |00:00:00.01 | 8 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 1 | 10 |00:00:00.02 | 12 | 1 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 10 | 1 | 10 |00:00:00.02 | 2 | 1 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL">=1000)
4 - access("D"."DEPTNO"="E"."DEPTNO")
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
47 | 2. 트랜잭션 수준 읽기 일관성 | 휘휘 | 2010.06.07 | 20062 |
46 | 3. 비관적 vs. 낙관적 동시성 제어 | 휘휘 | 2010.06.07 | 8218 |
45 | 4. 동시성 구현 사례 | 토시리 | 2010.06.07 | 10963 |
44 | 5. 오라클 Lock | 휘휘 | 2010.06.07 | 26381 |
43 | 7. Response Time Analysis 방법론과 OWI | balto | 2010.06.13 | 8077 |
42 | 8. Statspack / AWR | balto | 2010.06.13 | 12789 |
41 | 9. ASH(Active Session History) | 실천하자 | 2010.06.14 | 15615 |
40 | 11. End-To-End 성능관리 | 휘휘 | 2010.06.14 | 8137 |
39 | 12. 데이터베이스 성능 고도화 정석 해법 | 휘휘 | 2010.06.14 | 7161 |
38 | 5. V$SYSSTAT [1] | 토시리 | 2010.06.14 | 9875 |
37 | 10. V$SQL | 실천하자 | 2010.06.14 | 12703 |
36 | 6. V$SYSTEM_EVENT | 토시리 | 2010.06.14 | 6534 |
35 | 3. 라이브러리 캐시 구조 | balto | 2010.06.28 | 9862 |
34 | 4. 커서 공유 | balto | 2010.06.28 | 9220 |
33 | 1. SQL과 옵티마이저 | 휘휘 | 2010.06.28 | 7221 |
32 | 2. SQL 처리과정 | 휘휘 | 2010.06.28 | 16956 |
31 | 4장. 라이브러리 캐시 최적화 원리 | 휘휘 | 2010.06.28 | 6927 |
30 | 5. 바인드 변수의 중요성 | 실천하자 | 2010.06.28 | 11778 |
29 | 6. 바인드 변수의 부작용과 해법 | 실천하자 | 2010.06.28 | 14728 |
28 | 7. 세션 커서 캐싱 | 토시리 | 2010.06.29 | 10634 |