- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/DBSTUDY/DBMS_XPLAN?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
DBMS_XPLAN
- Oracle 9i에서 소개됨
- Query 튜닝의 시작과 끝을담당하는 유일무이한 Tool
TEST 준비 스크립트 ( 기본적인 사용법 ) |
---|
SQL> select * from v$version where rownum <= 1 2 ; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi SQL> CREATE TABLE T1( C1 INT, C2 CHAR(10) ); 테이블이 생성되었습니다. SQL> INSERT INTO T1 2 SELECT LEVEL, 'DUMMY' 3 FROM DUAL 4 CONNECT BY LEVEL <= 10000; 10000 개의 행이 만들어졌습니다. SQL> COMMIT; 커밋이 완료되었습니다. SQL> CREATE INDEX T1_NL ON T1( C1 ); 인덱스가 생성되었습니다. SQL> SELECT COUNT( * ) CNT 2 FROM T1 3 WHERE C1 = 1; CNT ---------- 1 SQL> | |
기본 실행 계획 |
SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; WHERE C1 = 1 AND C2 = 'DUMMY' * 4행에 오류: ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다 ORA-00001: 무결성 제약 조건(JLIVE.PLAN_INDEX)에 위배됩니다 | |
어쭈 =_= |
SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME LIKE '%PLAN%'; truncate table PLAN_TABLE; SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY ); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 25 | 2 (0)| |* 2 | INDEX RANGE SCAN | T1_NL | 1 | | 1 (0)| -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='DUMMY') 2 - access("C1"=1) Note ----- - 'PLAN_TABLE' is old version <-- =_= 18 개의 행이 선택되었습니다. SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; WHERE C1 = 1 AND C2 = 'DUMMY' * 4행에 오류: ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다 ORA-00001: 무결성 제약 조건(JLIVE.PLAN_INDEX)에 위배됩니다 SQL> DROP TABLE PLAN_TABLE PURGE; 테이블이 삭제되었습니다. SQL> SQL> @?/rdbms/admin/utlxplan.sql 테이블이 생성되었습니다. SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; 해석되었습니다. SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY ); Plan hash value: 2154509930 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 25 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_NL | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='DUMMY') 2 - access("C1"=1) Note ----- - dynamic sampling used for this statement <-- ^^ 19 개의 행이 선택되었습니다. |
|
그럼 통계 정보가 있다면.?? =_= |
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1'); PL/SQL 처리가 정상적으로 완료되었습니다. SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; 해석되었습니다. SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY ); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2154509930 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_NL | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='DUMMY') 2 - access("C1"=1) 15 개의 행이 선택되었습니다. | |
그럼 유니크 인댁스를 사용한다면..?? =_= |
SQL> CREATE TABLE T2( C1 INT, C2 CHAR(10) ); 테이블이 생성되었습니다. SQL> INSERT INTO T2 2 SELECT LEVEL, 'DUMMY' 3 FROM DUAL 4 CONNECT BY LEVEL <= 10000; 10000 개의 행이 만들어졌습니다. SQL> CREATE UNIQUE INDEX T1_UNIQUE_INDEX ON T2( C1 ); 인덱스가 생성되었습니다. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2'); PL/SQL 처리가 정상적으로 완료되었습니다. SQL> SELECT /*+ gather_plan_statistics */* 2 FROM T1 3 WHERE C1 = 1 AND C2 = 'DUMMY'; C1 C2 ---------- ---------- 1 DUMMY SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST')); SQL_ID 4p1gp2192gq0k, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */* FROM T1 WHERE C1 = 1 AND C2 = 'DUMMY' Plan hash value: 2154509930 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------------- |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 4 | 2 | |* 2 | INDEX RANGE SCAN | T1_NL | 1 | 1 | 1 |00:00:00.01 | 3 | 2 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='DUMMY') 2 - access("C1"=1) 19 개의 행이 선택되었습니다. SQL> SQL> SAVE XPLAN_C file XPLAN_C.sql(이)가 생성되었습니다 SQL> SQL> SELECT /*+ gather_plan_statistics */* 2 FROM T2 3 WHERE C1 = 1 AND C2 = 'DUMMY'; C1 C2 ---------- ---------- 1 DUMMY SQL> @XPLAN_C SQL_ID 0fnqn7r64jg7m, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */* FROM T2 WHERE C1 = 1 AND C2 = 'DUMMY' Plan hash value: 3273381761 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------- |* 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 1 |00:00:00.01 | 3 | |* 2 | INDEX UNIQUE SCAN | T1_UNIQUE_INDEX | 1 | 1 | 1 |00:00:00.01 | 2 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"='DUMMY') 2 - access("C1"=1) | |
Access Predicate와 Filter Predicate가 표현 되는 방식에 대한 정확한 이해는 실행 계획 해석에 있어 필수적인 지식
준비 |
---|
SQL> DROP TABLE T1 PURGE; SQL> DROP TABLE T2 PURGE; SQL> SQL> CREATE TABLE T1 ( C1 INT, C2 INT ) QL> CREATE TABLE T2 ( C1 INT, C2 INT ); SQL> SQL> CREATE INDEX T1_N1 ON T1 ( C1 ); SQL> CREATE INDEX T1_N2 ON T2 ( C1 ); SQL> INSERT INTO T1 SELECT LEVEL, LEVEL 2 FROM DUAL 3 CONNECT BY LEVEL <= 1000; SQL> INSERT INTO T2 SELECT LEVEL, LEVEL 2 FROM DUAL 3 CONNECT BY LEVEL <= 1000; | |
NL |
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1'); SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2'); SQL> EXPLAIN PLAN FOR 2 SELECT /*+ USE_NL( T1 T2 ) */ * 3 FROM T1 4 , T2 5 WHERE T1.C1 = T2.C1 6 AND T1.C2 = 1; 해석되었습니다. SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY ); Plan hash value: 1090018354 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 2 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 14 | 5 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 7 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | T1_N2 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."C2"=1) 4 - access("T1"."C1"="T2"."C1") 17 개의 행이 선택되었습니다. SQL> SAVE @PLAN |
|
Hash Join |
SQL> EXPLAIN PLAN FOR 2 SELECT /*+ USE_HASH( T1 T2 ) */ * 3 FROM T1 4 , T2 5 WHERE T1.C1 = T2.C1 6 AND T1.C2 = 1; SQL> SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY ); Plan hash value: 1838229974 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 14 | 7 (15)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T1 | 1 | 7 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T2 | 1000 | 7000 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."C1"="T2"."C1") 2 - filter("T1"."C2"=1) | |
왜 NL Join과 Hash Join 에서 Access Predicate가 표현 되는 방식의 차이가 발생하는가??
- NL : 선행 Table을 읽으면서 후행 Table을 한번씩 Access하는 방식이다. 따라서 실제 Join은 후행 Table에 대한 Access에서 발생한다.
따라서 후행 Table을 읽는 단계가 Access Predicate가 된다. - Hash : 선행 Table을 먼저 Build한 후, 후행 Table 과 한번에 Join하는 방식이다.
따라서 실제 Join이 발생하는 Hash Join 단계가 Access Predicate로 표현한다.DMBS_XPLAN.DISPLAY Function이 제공하는 여러가지 Parameter.. SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY ); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=TO_NUMBER('DUMMY')) 2 - access("C1"=1) 15 개의 행이 선택되었습니다. SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'TYPICAL', NULL )); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=TO_NUMBER('DUMMY')) 2 - access("C1"=1) 15 개의 행이 선택되었습니다. SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'TEST' FOR 3 SELECT * 4 FROM T1 5 WHERE C1 = 1 AND C2 = 'DUMMY'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', 'TEST', 'TYPICAL', NULL )); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=TO_NUMBER('DUMMY')) 2 - access("C1"=1) 15 개의 행이 선택되었습니다. |
다양한 출력 Format 4가지
Basic Format : 실행 계획의 단계별 Operation과 Object이름만을 보여주는 말 그대로 매우 기본적인 Format 이다. ( 사용 거의 X )
SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'BASIC' ) ); Plan hash value: 1420382924 --------------------------------------------- | Id | Operation | Name | --------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | | 2 | INDEX RANGE SCAN | T1_N1 | ---------------------------------------------
Typical Format : 가장 일반적인 용도 ( 기본값 )
- Predicate 정보가 왜 그렇게 중요한가? 실행 계획이 복잡해지면 Where 절의 특정 조건이 실행 계획의 각 단계에서
정확하게 어떻게 상용되었는지가 매우 중요해진다. Query Transformation 이란는 특별한 과정 때문에 Predicate의 변형이
발생할 때는 이 정보가 특히 중요하다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'TYPICAL' ) ); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=TO_NUMBER('DUMMY')) 2 - access("C1"=1)
All Format : 실행 계획을 분석하는데 있어서 없어서는 안될 중요한 두 가지 정보를 추가적으로 제공한다.
- Query Block : SQL 문장을 Query Block이라는 단위로 나눈데 Transformation 및 Optimization의 기본 단위가 된다.
Query Block 명은 Inline View 와 Subquery가 많이 사용되는 복작한 Query를 해석할 때 특히 유용하다. - Column Projection : 실행 계획의 특정 단계에서 어떤 Column을 추출하는가를 의미한다.
Query Transformation 을 Troubleshooting 할 때 유용한 정보가 된다.All Format SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=TO_NUMBER('DUMMY')) 2 - access("C1"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "C1"[NUMBER,22], "C2"[NUMBER,22] 2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22] 27 개의 행이 선택되었습니다. |
QB_NAME Hint : Query Block 명을 직접 조작 할 수 있다. SQL> EXPLAIN PLAN FOR 2 SELECT /*+ QB_NAME( X ) */ * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - X / T1@X <-- 2 - X / T1@X Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=TO_NUMBER('DUMMY')) 2 - access("C1"=1) PLAN_TABLE_OUTPUT ----------------------------------------------------------- 1 - "C1"[NUMBER,22], "C2"[NUMBER,22] 2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22] 27 개의 행이 선택되었습니다. |
그럼 한번 해볼까나.?? =_= 안되네 음.. SQL> EXPLAIN PLAN FOR 2 SELECT /*+ FULL( @X ) NO_MERGE( A ) */ * 3 FROM ( 4 SELECT /*+ QB_NAME( X ) */ * 5 FROM T1 6 WHERE C1 = 1 AND C2 = '1' --gather_plan_statistics 7 ) A; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$6CE2C157 / T1@X 2 - SEL$6CE2C157 / T1@X Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=1) 2 - access("C1"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "C1"[NUMBER,22], "C2"[NUMBER,22] 2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22] 27 개의 행이 선택되었습니다. |
ㅋㅋㅋ SQL> EXPLAIN PLAN FOR 2 SELECT /*+ QB_NAME( MAIN )NO_MERGE( A@MAIN ) */* 3 FROM ( 4 SELECT /*+ QB_NAME( X ) */ * 5 FROM T1 6 WHERE C1 = 1 AND C2 = '1' 7 ) A 8 ; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); Plan hash value: 2394988879 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 | | 1 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - X / A@MAIN 2 - X / T1@X 3 - X / T1@X Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("C2"=1) 3 - access("C1"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."C1"[NUMBER,22], "A"."C2"[NUMBER,22] 2 - "C1"[NUMBER,22], "C2"[NUMBER,22] 3 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22] |
InLine View FULL 안되네 음... SQL> EXPLAIN PLAN FOR 2 SELECT /*+ QB_NAME( MAIN ) FULL( T1@SUB) NO_MERGE( A@MAIN ) */* 3 FROM ( 4 SELECT /*+ QB_NAME( SUB ) */ * 5 FROM T1 6 WHERE C1 = 1 AND C2 = '1' 7 ) A 8 ; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); Plan hash value: 2394988879 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 | | 1 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SUB / A@MAIN 2 - SUB / T1@SUB 3 - SUB / T1@SUB Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("C2"=1) 3 - access("C1"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."C1"[NUMBER,22], "A"."C2"[NUMBER,22] 2 - "C1"[NUMBER,22], "C2"[NUMBER,22] 3 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22] 30 개의 행이 선택되었습니다. |
NO_UNNEST SQL> EXPLAIN PLAN FOR 2 SELECT /*+ NO_UNNEST( @SUB ) */ * 3 FROM T1 4 WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 ) 5 AND C2 = '1'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); Plan hash value: 1322727408 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 4 (0)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| T1 | 1 | 7 | 3 (0)| 00:00:01 | |* 3 | FILTER | | | | | | |* 4 | INDEX RANGE SCAN| T1_N1 | 1 | 4 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 3 - SUB 4 - SUB / T1@SUB Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SUB") */ 0 FROM "T1" "T1" WHERE :B1<=3 AND :B2>=1 AND "C1"=:B3 AND "C1"<=3 AND "C1">=1)) 2 - filter("C2"=1) 3 - filter(:B1<=3 AND :B2>=1) 4 - access("C1"=:B1) filter("C1"<=3 AND "C1">=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "C1"[NUMBER,22], "C2"[NUMBER,22] 2 - "C1"[NUMBER,22], "C2"[NUMBER,22] |
UNNEST SQL> EXPLAIN PLAN FOR 2 SELECT /*+ UNNEST( @SUB ) */ * 3 FROM T1 4 WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 ) 5 AND C2 = '1'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); Plan hash value: 3994169173 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS SEMI | | 1 | 11 | 4 (0)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_N1 | 3 | | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | T1_N1 | 3 | 12 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$0C6FB14C 2 - SEL$0C6FB14C / T1@SEL$1 3 - SEL$0C6FB14C / T1@SEL$1 4 - SEL$0C6FB14C / T1@SUB Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("C2"=1) 3 - access("C1">=1 AND "C1"<=3) 4 - access("C1"="C1") filter("C1"<=3 AND "C1">=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) "C1"[NUMBER,22], "C2"[NUMBER,22] 2 - "C1"[NUMBER,22], "C2"[NUMBER,22] 3 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22] 34 개의 행이 선택되었습니다. SQL> EXPLAIN PLAN FOR 2 SELECT /*+ UNNEST( @SUB ) HASH_SJ( @SUB ) */ * 3 FROM T1 4 WHERE C1 IN ( SELECT /*+ QB_NAME( SUB ) */ C1 FROM T1 WHERE C1 BETWEEN 1 AND 3 ) 5 AND C2 = '1'; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ALL' ) ); Plan hash value: 2980426711 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 1 | 11 | 6 (17)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_N1 | 3 | | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | T1_N1 | 3 | 12 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$0C6FB14C 2 - SEL$0C6FB14C / T1@SEL$1 3 - SEL$0C6FB14C / T1@SEL$1 4 - SEL$0C6FB14C / T1@SUB Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("C1"="C1") 2 - filter("C2"=1) 3 - access("C1">=1 AND "C1"<=3) 4 - access("C1">=1 AND "C1"<=3) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "C1"[NUMBER,22], "C2"[NUMBER,22] 2 - "C1"[NUMBER,22], "C2"[NUMBER,22] 3 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22] 4 - "C1"[NUMBER,22] SQL> |
Outline Format : 실행 계획을 수립 하는데 필요한 Hint들의 목록을 의미한다.
SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY' 5 ; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'OUTLINE' ) ); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1")) <-- OUTLINE_LEAF(@"SEL$1") ALL_ROWS <-- OPTIMIZER_FEATURES_ENABLE('10.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=TO_NUMBER('DUMMY')) 2 - access("C1"=1) 28 개의 행이 선택되었습니다.
Advanced Format : All Format과 Outline Format을 합친 것과 같다.
SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM T1 4 WHERE C1 = 1 AND C2 = 'DUMMY' 5 ; 해석되었습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY( 'PLAN_TABLE', NULL, 'ADVANCED' ) ); Plan hash value: 1420382924 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 2 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1")) OUTLINE_LEAF(@"SEL$1") ALL_ROWS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=TO_NUMBER('DUMMY')) 2 - access("C1"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "C1"[NUMBER,22], "C2"[NUMBER,22] 2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22] 40 개의 행이 선택되었습니다.
Plan Statistics : 실행 계획의 단계별 일량 정보를 의미 ( 세가지 조건 중 하나를 만족 할때 )
- Statistics Level이 ALL 일때
- GATHER_PLAN_STATISTICS Hint를 사용할 때, Oracle 10g 부터 지원
- _ROWSOURCE_EXECUTION_STATISTICS Parameter 값을 true로 변경한 경우
기본적인 사용법 ( GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CUROR ) 준비 |
---|
SQL> CREATE TABLE T_PLAN( C1 VARCHAR2(10)); SQL> CREATE INDEX T_PLAN_IDX ON T_PLAN(C1); SQL> INSERT INTO T_PLAN 2 SELECT 'Many1' 3 FROM DUAL 4 CONNECT BY LEVEL <= 10000; SQL> COMMIT; SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'T_PLAN', CASCADE => TRUE, NO_INVALIDATE => FALSE ); | |
변경전 분포도 100% |
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T_PLAN
4 WHERE C1 = 'Many1';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );
Plan hash value: 2521935493
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 60000 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_PLAN | 10000 | 60000 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"='Many1')
|
|
변경 후 분포도 50% |
SQL> INSERT INTO T_PLAN
2 SELECT 'Many2'
3 FROM DUAL
4 CONNECT BY LEVEL <= 10000;
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM T_PLAN
4 WHERE C1 = 'Many2';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY );
Plan hash value: 1175009887
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 6 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"='Many2')
13 개의 행이 선택되었습니다.
SQL>
|
|
실제 플랜 GATHER_PLAN_STATISTICS + DBMS_XPLAN.DISPLAY_CUROR |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Many2';
COUNT(*)
----------
10000
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID bhxfgwb1bjqn2, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Many2'
Plan hash value: 2787416999
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 34 |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 1 | 10000 |00:00:00.01 | 34 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"='Many2')
|
|
통계 재생성 |
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'T_PLAN', CASCADE => TRUE, NO_INVALIDATE => FALSE );
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Many2';
COUNT(*)
----------
10000
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
SQL_ID bhxfgwb1bjqn2, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 =
'Many2'
Plan hash value: 4286448300
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 38 |
|* 2 | TABLE ACCESS FULL| T_PLAN | 1 | 10000 | 10000 |00:00:00.01 | 38 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1"='Many2')
19 개의 행이 선택되었습니다.
|
|
다양한 출력 Format
Basic Format |
---|
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; COUNT(*) ---------- 0 SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'BASIC' )); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few' Plan hash value: 2787416999 ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX RANGE SCAN| T_PLAN_IDX | ---------------------------------------- 15 개의 행이 선택되었습니다. | |
Typical Format |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'TYPICAL' ));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1
= 'Few'
Plan hash value: 2787416999
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 6 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"='Few')
20 개의 행이 선택되었습니다.
|
|
ALL Format |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; COUNT(*) ---------- 0 SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALL' )); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- SQL_ID 1a4gm5z0swug1, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few' Plan hash value: 2787416999 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 6 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T_PLAN@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"='Few') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 31 개의 행이 선택되었습니다. | |
ALLSTATS |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS' ));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few'
Plan hash value: 2787416999
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 3 | 1 | 3 |00:00:00.01 | 6 |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 3 | 1 | 0 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"='Few')
18 개의 행이 선택되었습니다.
SQL>
|
|
Allstats Last |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL>
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' ));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few'
Plan hash value: 2787416999
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 1 | 0 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"='Few')
18 개의 행이 선택되었습니다.
|
|
Iostats Format : I/O Statistics 즉, Read/Write 정보를 보여주겠다는 것을 의미 |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * )
2 FROM T_PLAN
3 WHERE C1 = 'Few';
COUNT(*)
----------
0
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'IOSTATS LAST' ));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID dymqsvydfcjpf, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few'
Plan hash value: 2787416999
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 1 | 0 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"='Few')
|
|
Memstats Format : Memory Statistics |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 , T_PLAN 4 WHERE ROWNUM <= 100000; COUNT(*) ---------- 100000 SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'MEMSTATS LAST' )); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------- SQL_ID 38uradzrgj5b6, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN , T_PLAN WHERE ROWNUM <= 100000 Plan hash value: 4051414749 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------ | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | | | | |* 2 | COUNT STOPKEY | | 1 | | 100K|00:00:00.12 | | | | PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------- | 3 | MERGE JOIN CARTESIAN| | 1 | 400M| 100K|00:00:00.02 | | | | | 4 | TABLE ACCESS FULL | T_PLAN | 1 | 20000 | 5 |00:00:00.01 | | | | | 5 | BUFFER SORT | | 5 | 20000 | 100K|00:00:00.01 | 568K| 461K| 504K (0)| | 6 | TABLE ACCESS FULL | T_PLAN | 1 | 20000 | 20000 |00:00:00.01 | | | | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(ROWNUM<=100000) |
|
+Peeked binds : Bind Peeking이 활성화 되어 있을 경우에만 동작한다. =_= |
SQL> var b1 varchar2(10); SQL> exec :b1 := 'Few'; PL/SQL 처리가 정상적으로 완료되었습니다. SQL> SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = :b1; COUNT(*) ---------- 0 SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST +PEEKED_BINDS' )); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------ SQL_ID 2jt5zbafx7dz7, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1 Plan hash value: 4286448300 --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 38 | |* 2 | TABLE ACCESS FULL| T_PLAN | 1 | 10000 | 0 |00:00:00.01 | 38 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("C1"=:B1) 18 개의 행이 선택되었습니다. | |
Bind Peeking |
SQL> SELECT A.KSPPINM NAME, 2 B.KSPPSTVL VALUE, 3 B.KSPPSTDF DEF_YN, 4 A.KSPPDESC DESCRIPTION 5 FROM X$KSPPI A, 6 X$KSPPSV B 7 WHERE A.INDX = B.INDX 8 and a.ksppinm like '%_optim_peek_user_binds%' 9 ; NAME -------------------------------------------------------------------------------- VALUE ------------------------------------------------------------------------------------------------------------------------------------------------------ DEF_YN --------- DESCRIPTION ------------------------------------------------------------------------------------------------------------------------------------------------------ _optim_peek_user_binds TRUE TRUE enable peeking of user binds SQL> alter session set "_optim_peek_user_binds"=true; 세션이 변경되었습니다. SQL> var b1 varchar2(10); SQL> exec :b1 := 'Few'; PL/SQL 처리가 정상적으로 완료되었습니다. SQL> SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = :b1; COUNT(*) ---------- 0 SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST +peeked_binds' )); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------- SQL_ID 2jt5zbafx7dz7, child number 2 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1 Plan hash value: 2787416999 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------ | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | |* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 1 | 0 |00:00:00.01 | 2 | ------------------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=846): 'Few' Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=:B1) 23 개의 행이 선택되었습니다. |
|
SQL Cursor |
SQL> SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT
2 FROM V$SQL
3 WHERE SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1%'
4 AND ROWNUM <= 1 ;
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------
2jt5zbafx7dz7 1
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1
SQL> COLUMN SQL_ID NEW_VALUE V_SQL_ID;
SQL> COLUMN CHILD_NUMBER NEW_VALUE V_CHILD_NUMBER;
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '&V_SQL_ID', '&V_CHILD_NUMBER', 'ALLSTATS' ));
구 1: SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '&V_SQL_ID', '&V_CHILD_NUMBER', 'ALLSTATS' ))
신 1: SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( '2jt5zbafx7dz7', ' 1', 'ALLSTATS' ))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID 2jt5zbafx7dz7, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1
Plan hash value: 2787416999
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 1 | 0 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=:B1)
18 개의 행이 선택되었습니다.
|
|
Dictionary View와 연동 라이브러리 캐쉬에서 밀려난듯 |
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT( * )
3 FROM T_PLAN
4 WHERE C1 = 'Few';
해석되었습니다.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2787416999
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 6 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - access("C1"='Few')
14 개의 행이 선택되었습니다.
SQL> SELECT P.PLAN_TABLE_OUTPUT
2 FROM (SELECT DISTINCT SQL_ID, CHILD_NUMBER
3 FROM V$SQL_PLAN S
4 WHERE S.OBJECT_NAME = 'T_PLAN_IDX') S,
5 TABLE( DBMS_XPLAN.DISPLAY_CURSOR( S.SQL_ID, S.CHILD_NUMBER, 'TYPICAL' ) ) P;
선택된 레코드가 없습니다.
|
|
한번 실행해서 올려놓구.. |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) 2 FROM T_PLAN 3 WHERE C1 = 'Few'; COUNT(*) ---------- 0 SQL> SELECT P.PLAN_TABLE_OUTPUT 2 FROM (SELECT DISTINCT SQL_ID, CHILD_NUMBER 3 FROM V$SQL S 4 WHERE SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS%') S, 5 TABLE( DBMS_XPLAN.DISPLAY_CURSOR( S.SQL_ID, S.CHILD_NUMBER, 'TYPICAL' ) ) P; PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID dymqsvydfcjpf, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = 'Few' Plan hash value: 2787416999 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | INDEX RANGE SCAN| T_PLAN_IDX | 1 | 6 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"='Few') SQL_ID 2jt5zbafx7dz7, child number 2 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1 NOTE: cannot fetch plan for SQL_ID: 2jt5zbafx7dz7, CHILD_NUMBER: 2 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_p lan) SQL_ID akh267xpw2cyf, child number 0 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */COUNT( * ) FROM T_PLAN WHERE C1 = :b1 NOTE: cannot fetch plan for SQL_ID: akh267xpw2cyf, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_p lan) 38 개의 행이 선택되었습니다. | |
Parallel Execution ( X ) |
SQL> ALTER TABLE T_PLAN PARALLEL 4; 테이블이 변경되었습니다. SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) 2 FROM T_PLAN 3 ; COUNT(*) ---------- 20000 SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' )); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID cbkdc4b0d9ntk, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) FROM T_PLAN Plan hash value: 1491809887 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | 2 | PX COORDINATOR | | 1 | | 4 |00:00:00.01 | 3 | PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | | 4 | SORT AGGREGATE | | 0 | 1 | 0 |00:00:00.01 | 0 | | 5 | PX BLOCK ITERATOR | | 0 | 20000 | 0 |00:00:00.01 | 0 | |* 6 | TABLE ACCESS FULL| T_PLAN | 0 | 20000 | 0 |00:00:00.01 | 0 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access(:Z>=:Z AND :Z<=:Z) 22 개의 행이 선택되었습니다. |
|
Parallel DML ( X ) |
SQL> ALTER SESSION ENABLE PARALLEL DML; 세션이 변경되었습니다. SQL> INSERT /*+ GATHER_PLAN_STATISTICS */ INTO T_PLAN SELECT * FROM T_PLAN T; 20000 개의 행이 만들어졌습니다. SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST' )); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------- SQL_ID guvtztakgk4jt, child number 0 ------------------------------------- INSERT /*+ GATHER_PLAN_STATISTICS */ INTO T_PLAN SELECT * FROM T_PLAN T Plan hash value: 3480535512 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------- | 1 | PX COORDINATOR | | 1 | | 4 |00:00:01.43 | 8 | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 20000 | 0 |00:00:00.01 | 0 | | | | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------- | 3 | LOAD AS SELECT | | 0 | | 0 |00:00:00.01 | 0 | 256K| 256K| | | 4 | PX RECEIVE | | 0 | 20000 | 0 |00:00:00.01 | 0 | | | | | 5 | PX SEND ROUND-ROBIN| :TQ10000 | 0 | 20000 | 0 |00:00:00.01 | 0 | | | | | 6 | PX BLOCK ITERATOR | | 0 | 20000 | 0 |00:00:00.01 | 0 | | | | |* 7 | TABLE ACCESS FULL| T_PLAN | 0 | 20000 | 0 |00:00:00.01 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - access(:Z>=:Z AND :Z<=:Z) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------- 23 개의 행이 선택되었습니다. SQL> ROLLBACK; 롤백이 완료되었습니다. | |
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/DBSTUDY/DBMS_XPLAN?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.