메뉴 건너뛰기

bysql.net

4. DBMS_XPLAN 패키지

2010.06.06 20:41

balto 조회 수:10461

- 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.17 154864
66 4. Prefetch file balto 2010.07.10 28431
65 5. 오라클 Lock file 휘휘 2010.06.07 26365
64 2. DB 버퍼 캐시 file 휘휘 2010.05.24 21914
63 3. SQL 트레이스 file balto 2010.06.06 21175
62 1. 기본 아키텍처 [1] file 휘휘 2010.05.23 19896
61 2. 트랜잭션 수준 읽기 일관성 file 휘휘 2010.06.07 19559
60 5. Undo file 토시리 2010.05.31 18634
59 11. Shared Pool file 실천하자 2010.05.31 18509
58 9. Static vs. Dynamic SQL [1] balto 2010.07.04 18341
57 4. Array Processing 활용 file 휘휘 2010.07.05 18235
56 1 장. 오라클 아키텍처 운영자 2010.05.20 17838
55 5. Fetch Call 최소화 file 휘휘 2010.07.05 16839
54 9. ASH(Active Session History) 실천하자 2010.06.14 15603
53 2. SQL 처리과정 file 휘휘 2010.06.28 15334
52 3. 버퍼 Lock [1] 휘휘 2010.05.24 15223
51 1. Explain Plan 실천하자 2010.06.06 14661
50 6. 바인드 변수의 부작용과 해법 file 실천하자 2010.06.28 14653
49 8. PL/SQL 함수 호출 부하 해소 방안 토시리 2010.07.11 14021
48 7. Result 캐시 휘휘 2010.07.19 12969