메뉴 건너뛰기

bysql.net

4. DBMS_XPLAN 패키지

balto 2010.06.06 12:11 조회 수 : 7770

- 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'));


 PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
| 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'));

(실행결과)

 PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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'));

(실습)

 SQL>  select * from table(dbms_xplan.display_cursor('90x6aga13xhu3', 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")



번호 제목 글쓴이 날짜 조회 수
67 Front Page file 운영자 2010.05.16 152144
66 1 장. 오라클 아키텍처 운영자 2010.05.19 12121
65 1. 기본 아키텍처 [1] file 휘휘 2010.05.22 17533
64 3. 버퍼 Lock [1] 휘휘 2010.05.23 12811
63 2. DB 버퍼 캐시 file 휘휘 2010.05.23 19594
62 4. Redo file 휘휘 2010.05.24 9200
61 9. Snapshot too old balto 2010.05.30 5870
60 10. 대기 이벤트 balto 2010.05.30 5464
59 7. Consistent vs. Current 모드 읽기 휘휘 2010.05.30 7953
58 8. 블록 클린아웃 휘휘 2010.05.30 9798
57 11. Shared Pool file 실천하자 2010.05.30 16283
56 5. Undo file 토시리 2010.05.30 15708
55 1. 트랜잭션 동시성 제어 실천하자 2010.05.31 6177
54 6. 문장수준 읽기 일관성 file 토시리 2010.05.31 8235
53 2장. 트랜잭션과 Lock 운영자 2010.06.01 4227
52 1. Explain Plan 실천하자 2010.06.06 12530
51 2. AutoTrace 실천하자 2010.06.06 6347
50 3장. 오라클 성능 관리 운영자 2010.06.06 4047
49 3. SQL 트레이스 file balto 2010.06.06 18468
» 4. DBMS_XPLAN 패키지 balto 2010.06.06 7770