4._DBMS_XPLAN_패키지
2012.04.04 01:51
04 DBMS_XPLAN 패키지 오라클 10g부터는 라이브러리 캐시에 캐싱돼 있는 SQL 커서에 대한 실행계획은
물론 Row Source별 수행통계까지 손쉽게 출력해 볼 수 있도록 기능이 확장되었다. (1) 예상 실행계획 출력 @?/rdbms/admin/utlxpls 내부적으로 dbms_xplan 패키지를 호출 select plan_table_output from table(dbms_xplan.display('plan_table', null,
'serial'); 첫번째 인자 : 실행계획이 저장된 plan
table명 두번째 인자 : statement_id, null일 때는 가장 마지막 explain plan 명령에 사용했던 쿼리의 실행계획 병렬 쿼리에
대한 실행계획을 수집했다면 @?/rdbms/admin/utlxplp 스크립트를 수행 세번째 인자 : 다양한 포맷 옵션을 선택 (Basic,
Typical, Serial, All, Outline, Advanced) EXPLAIN PLAN SET STATEMENT_ID = 'SQL1' FOR SELECT * FROM
ECS.TB_ECS_POLICY_MSTS A, TB_ECS_RIDER_MSTS
B WHERE A.POLICYNO =
B.POLICYNO AND A.POLICYNO =
'1360079414' ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'BASIC')) ; ---------------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS | | 3 | INDEX UNIQUE SCAN | PK_TB_ECS_POLICY_MSTS | | 4 | TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS | | 5 | INDEX RANGE SCAN | TB_ECS_RIDER_MSTS_IDX03 | ---------------------------------------------------------------- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'TYPICAL')) ; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 4175 | 8 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 5 | 4175 | 8 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS | 1 | 514 | 3 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_TB_ECS_POLICY_MSTS | 1 | | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS | 5 | 1605 | 5 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | TB_ECS_RIDER_MSTS_IDX03 | 5 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."POLICYNO"='1360079414') 5 - access("B"."POLICYNO"='1360079414') SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'ALL')) ; -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 4175 | 8 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 5 | 4175 | 8 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS | 1 | 514 | 3 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_TB_ECS_POLICY_MSTS | 1 | | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS | 5 | 1605 | 5 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | TB_ECS_RIDER_MSTS_IDX03 | 5 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / A@SEL$1 3 - SEL$1 / A@SEL$1 4 - SEL$1 / B@SEL$1 5 - SEL$1 / B@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."POLICYNO"='1360079414') 5 - access("B"."POLICYNO"='1360079414') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) "A"."POLICYNO"[VARCHAR2,10], "A"."POL_STS_MAINCODE"[VARCHAR2,10], "A"."POL_STS_SUBCODE"[VARCHAR2,10], "A"."BEFORE_POL_STS_MAINCODE"[VARCHAR2,10], "A"."BEFORE_POL_STS_SUBCODE"[VARCHAR2,10], "A"."POL_STS_CHG_DATE"[DATE,7], "A"."BASE_PLAN_CODE"[VARCHAR2,10], "A"."INSURTYPE_CODE"[VARCHAR2,10], "A"."COVERAGE_PERIOD_CLASS"[VARCHAR2,10], "A"."COVERAGE_PERIOD_VALUE"[NUMBER,22], SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', ' OUTLINE')) ; Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$1" "B"@"SEL$1") LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1") INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("TB_ECS_RIDER_MSTS"."POLICYNO" "TB_ECS_RIDER_MSTS"."RIDER_NO" "TB_ECS_RIDER_MSTS"."PLAN_CODE")) INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("TB_ECS_POLICY_MSTS"."POLICYNO")) OUTLINE_LEAF(@"SEL$1") OPT_PARAM('query_rewrite_enabled' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'ADVANCED')) (2) 캐싱된 커서의 실제 실행계획 출력
커서 : 하드 파싱 과정을
거쳐 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는 필요한 정보를 담은 SQL Area 오라클은 라이브러리 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를
볼 수 있도록 v$sql 뷰를 제공 이 뷰와 함께 sql_id 값으로
조인해서 사용할 수 있도록 제공되는 뷰 중 활용도가 높은 것은 v$sql_plan과 v$sql_plan_statistics 이다. 그리고 이 뷰를 합쳐서
보여주는 것이 v$sql_plan_statistics_all 이다. v$sql_plan 뷰의 활용방법
v$sql_plan를 조회하려면 마지막 수행한 SQL의
sql_id와 child_number 값을 알아야 하는데,
아래 쿼리를 이용해 쉽게 찾을 수 있다. SQL> set serveroutput off SQL> select * 2 from
emp e, dept d 3 where
d.deptno = e.deptno 4 and
e.sal >= 1000;
12 rows selected.
SQL> column prev_sql_id new_value sql_id SQL> column prev_child_number new_value child_no SQL> select prev_sql_id, prev_child_number 2 from
v$session 3 where
sid = userenv('sid') 4 and
username is not null 5 and
prev_hash_value <> 0;
PREV_SQL_ID
PREV_CHILD_NUMBER ------------- ----------------- 704dj20tgv1b0 0 select *
from table(dbms_xplan.display_cursor('[sql_id]', [child_no], '[format]')); 위 함수는 라이브러리 캐시에 현재 캐싱돼 있는 SQL 커서의 실제 실행계획과, 실행계획을 만들면서 예상했던 Rows, Bytes, Cost, Time 정보를 보여준다. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC ROWS BYTES COST PREDICATE')); PLAN_TABLE_OUTPUT ------------------ EXPLAINED SQL STATEMENT: ------------------------ SELECT * FROM ECS.TB_ECS_POLICY_MSTS A, TB_ECS_RIDER_MSTS B WHERE A.POLICYNO = B.POLICYNO AND A.POLICYNO = '1360079414' Plan hash value: 3830427808 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 8 (100)| | 1 | NESTED LOOPS | | 5 | 4175 | 8 (0)| | 2 | TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS | 1 | 514 | 3 (0)| |* 3 | INDEX UNIQUE SCAN | PK_TB_ECS_POLICY_MSTS | 1 | | 2 (0)| | 4 | TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS | 5 | 1605 | 5 (0)| |* 5 | INDEX RANGE SCAN | TB_ECS_RIDER_MSTS_IDX03 | 5 | | 2 (0)| --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."POLICYNO"='1360079414') 5 - access("B"."POLICYNO"='1360079414') (3) 캐싱된 커서의 Row Source별 수행 통계 출력 SQL문에 gather_plan_staticstics 힌트를 사용하거나, 시스템 또는 세션 레벨에서 statistics_level 파라미터를 all로 설정하면 실제 SQL을 수행하는 동안의 실행계획 각 오퍼레이션
단계별로 수행 통계를 수집. 조회할 때는
v$sql_plan_statistics 또는 v$sql_plan_statistics_all 뷰를
이용 v$sql_plan_statistics
뷰에는 모든 통계항목에 대해 마지막 수행 통계치와 누적 통계치를 조회할 수 있도록
컬럼이 두 개씩 제공. Output_rows, last_output_rows Cr_buffer_gets, last_cu_buffer_gets Disk_reads, last_disk_reads Dbms_xplan.display_cursor
함수는 이러한 정보를 깔끔하게 포맷팅해주는 기능을 제공한다. Dbms_xplan.display
함수에는 없던 iostats, memstats,
allstats 옵션을 사용하면 실제 수행 시 Row Source별 수행통계를 보여준다. E-Rows :
SQL을 수행하기 전 옵티마이저가 각 Row
Source별로 예상했던 로우 수 (v$sql_plan) A-Rows : 실제 수행 시 읽었던 로우 수 (v$sql_plan_statistics) 이처럼 옵티마이저의 예상 로우 수와 수행 시 실제 로우 수를 비교해
보여주므로 옵티마이저의 행동을 관찰할 때 유용하게 사용 SELECT /*+ gather_plan_statistics */ * FROM
ECS.TB_ECS_POLICY_MSTS A, TB_ECS_RIDER_MSTS
B WHERE A.POLICYNO =
B.POLICYNO AND A.POLICYNO =
'1360079414' ;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); PLAN_TABLE_OUTPUT --------------------- SQL_ID 1rngkgr9m1xv3, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ * FROM ECS.TB_ECS_POLICY_MSTS A, TB_ECS_RIDER_MSTS B WHERE A.POLICYNO = B.POLICYNO AND A.POLICYNO = '1360079414' Plan hash value: 3830427808 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------ | 1 | NESTED LOOPS | | 1 | 5 | 11 |00:00:00.01 | 13 | | 2 | TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS | 1 | 1 | 1 |00:00:00.01 | 4 | |* 3 | INDEX UNIQUE SCAN | PK_TB_ECS_POLICY_MSTS | 1 | 1 | 1 |00:00:00.01 | 3 | | 4 | TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS | 1 | 5 | 11 |00:00:00.01 | 9 | |* 5 | INDEX RANGE SCAN | TB_ECS_RIDER_MSTS_IDX03 | 1 | 5 | 11 |00:00:00.01 | 4 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."POLICYNO"='1360079414') 5 - access("B"."POLICYNO"='1360079414')
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
46 | 9._Static_vs._Dynamic_SQL | 남송휘 | 2012.05.07 | 5426 |
45 | 2._User_Call_vs._Recursive_Call | 정찬호 | 2012.05.07 | 4427 |
44 | 1._Call_통계 | 정찬호 | 2012.05.07 | 4310 |
43 | 5장._데이터베이스_Call_최소화_원리 | 운영자 | 2012.05.07 | 4321 |
42 | 11._Static_SQL_구현을_위한_기법들 | dasini | 2012.05.07 | 3736 |
41 | 4._커서_공유 | 남송휘 | 2012.04.27 | 16112 |
40 | 6._바인드_변수의_부작용과_해법 | 시와처 | 2012.04.23 | 4645 |
39 | 5._바인드_변수의_중요성 | 시와처 | 2012.04.23 | 4299 |
38 | 8._애플리케이션_커서_캐싱 | 박영창 | 2012.04.22 | 5242 |
37 | 7._세션_커서_캐싱 | 박영창 | 2012.04.22 | 7793 |
36 | 10._V$SQL | 정찬호 | 2012.04.09 | 6544 |
35 | 9._ASH(Active_Session_History) | 정찬호 | 2012.04.09 | 6071 |
34 | 4장._라이브러리_캐시_최적화_원리 | dasini | 2012.04.09 | 2721 |
33 | 12._데이터베이스_성능_고도화_정석_해법 | 남송휘 | 2012.04.09 | 4574 |
32 | 11._End-To-End_성능관리 | 남송휘 | 2012.04.09 | 3032 |
31 | 3._라이브러리_캐시_구조 | dasini | 2012.04.06 | 5232 |
30 | 2._SQL_처리과정 | dasini | 2012.04.06 | 21688 |
29 | 1._SQL과_옵티마이저 | dasini | 2012.04.06 | 3612 |
28 | 5._V$SYSSTAT | AskZZang | 2012.04.04 | 4599 |
» | 4._DBMS_XPLAN_패키지 | AskZZang | 2012.04.04 | 5610 |